Parsowanie plików XLS i XLSX (MS Excel) za pomocą Ruby?

Ostatnio musiałem przeanalizować niektóre pliki Excela za pomocą Rubiego. Mnogość bibliotek i opcji okazała się myląca, więc napisałem o tym post na blogu.

Oto tabela różnych bibliotek Ruby i tego, co obsługują:

Jeśli zależy ci na wydajności, oto jak biblioteki xlsx porównują:

Mam przykładowy kod do odczytu plików xlsx z każdej obsługiwanej biblioteki tutaj

Tutaj przykłady odczytu plików xlsx z różnymi bibliotekami:

RubyXL

require 'rubyXL'

workbook = RubyXL::Parser.parse './sample_excel_files/xlsx_500_rows.xlsx'
worksheets = workbook.worksheets
puts "Found #{worksheets.count} worksheets"

worksheets.each do |worksheet|
  puts "Reading: #{worksheet.sheet_name}"
  num_rows = 0
  worksheet.each do |row|
    row_cells = row.cells.map{ |cell| cell.value }
    num_rows += 1
  end
  puts "Read #{num_rows} rows"
end

Maleństwo

require 'roo'

workbook = Roo::Spreadsheet.open './sample_excel_files/xlsx_500_rows.xlsx'
worksheets = workbook.sheets
puts "Found #{worksheets.count} worksheets"

worksheets.each do |worksheet|
  puts "Reading: #{worksheet}"
  num_rows = 0
  workbook.sheet(worksheet).each_row_streaming do |row|
    row_cells = row.map { |cell| cell.value }
    num_rows += 1
  end
  puts "Read #{num_rows} rows" 
end

Creek

require 'creek'

workbook = Creek::Book.new './sample_excel_files/xlsx_500_rows.xlsx'
worksheets = workbook.sheets
puts "Found #{worksheets.count} worksheets"

worksheets.each do |worksheet|
  puts "Reading: #{worksheet.name}"
  num_rows = 0
  worksheet.rows.each do |row|
    row_cells = row.values
    num_rows += 1
  end
  puts "Read #{num_rows} rows"
end

Simple_xlsx_reader

require 'simple_xlsx_reader'

workbook = SimpleXlsxReader.open './sample_excel_files/xlsx_500000_rows.xlsx'
worksheets = workbook.sheets
puts "Found #{worksheets.count} worksheets"

worksheets.each do |worksheet|
  puts "Reading: #{worksheet.name}"
  num_rows = 0
  worksheet.rows.each do |row|
    row_cells = row
    num_rows += 1
  end
  puts "Read #{num_rows} rows"
end

Oto przykład odczytu pliku xls przy użyciu biblioteki spreadsheet:

Arkusz kalkulacyjny

require 'spreadsheet'

# Note: spreadsheet only supports .xls files (not .xlsx)
workbook = Spreadsheet.open './sample_excel_files/xls_500_rows.xls'
worksheets = workbook.worksheets
puts "Found #{worksheets.count} worksheets"

worksheets.each do |worksheet|
  puts "Reading: #{worksheet.name}"
  num_rows = 0
  worksheet.rows.each do |row|
    row_cells = row.to_a.map{ |v| v.methods.include?(:value) ? v.value : v }
    num_rows += 1
  end
  puts "Read #{num_rows} rows"
end

The roo gem działa świetnie w Excelu (.xls i .xlsx) i jest aktywnie rozwijany.

Zgadzam się, że składnia nie jest świetna ani podobna do ruby. Ale to można łatwo osiągnąć za pomocą czegoś takiego:

class Spreadsheet
  def initialize(file_path)
    @xls = Roo::Spreadsheet.open(file_path)
  end

  def each_sheet
    @xls.sheets.each do |sheet|
      @xls.default_sheet = sheet
      yield sheet
    end
  end

  def each_row
    0.upto(@xls.last_row) do |index|
      yield @xls.row(index)
    end
  end

  def each_column
    0.upto(@xls.last_column) do |index|
      yield @xls.column(index)
    end
  end
end

Używam creek, który używa nokogiri. Jest szybki. Używane 8.3 sekund na stole 21x11250 XLSX na moim Macbook Air. Got it to work on ruby 1.9.3+. Format wyjściowy dla każdego wiersza jest skrótem nazwy wiersza i kolumny do zawartości komórki: {"A1"=>" komórka", "B1" = > "Inna komórka"} Hash nie gwarantuje, że klucze będą w pierwotnej kolejności kolumn. https://github.com/pythonicrubyist/creek

Dullard to kolejny świetny, który używa nokogiri. Jest super szybki. Używany 6,7 sekundy na Stolik 21x11250 XLSX na moim MacBooku Air. Got it to work on ruby 2.0.0+. Format wyjściowy dla każdego wiersza to tablica: ["komórka", " Inna komórka"] https://github.com/thirtyseven/dullard

Simple_xlsx_reader o którym wspomniano jest świetny, trochę powolny. Używany 91 sekund na stole 21x11250 XLSX na moim MacBooku Air. Got it to work on ruby 1.9.3+. Format wyjściowy dla każdego wiersza to tablica: ["komórka", " Inna komórka"] https://github.com/woahdae/simple_xlsx_reader

Kolejnym ciekawym jest oxcelix. Używa parsera saksofonu Oxa, który podobno jest szybszy od DOM i parsera saksofonu nokogiri. Podobno wyprowadza matrycę. Nie mogłem go uruchomić. Ponadto pojawiły się pewne problemy z zależnościami z rubyzip. Nie polecam.

Podsumowując, jeśli używasz wersji ruby niższej niż 2.0.0, użyj creek. Jeśli używasz ruby 2.0.0+, użyj dullard, ponieważ jest szybszy i zachowuje kolumnę tabeli spokój.

Jeśli szukasz bardziej nowoczesnych bibliotek, spójrz na arkusz kalkulacyjny: http://spreadsheet.rubyforge.org/GUIDE_txt.html . Nie wiem, czy obsługuje pliki XLSX, ale biorąc pod uwagę, że jest aktywnie rozwijany, zgaduję, że tak (nie jestem na Windows, ani z Office, więc nie mogę przetestować).

W tym momencie wygląda na to, że roo jest znowu dobrą opcją. Obsługuje XLSX, umożliwia (niektóre) iterację poprzez użycie times z dostępem do komórki. Przyznaję, że to nie jest ładne. chociaż.

Ponadto RubyXL może teraz dać ci rodzaj iteracji za pomocą metody extract_data, która daje tablicę danych 2d, którą można łatwo iterować.

Alternatywnie, jeśli próbujesz pracować z plikami XLSX w systemie Windows, możesz użyć biblioteki Ruby Win32OLE, która pozwala na interfejs z obiektami Ole, takimi jak te dostarczane przez Word i Excel. jednak , jak wspomniał @PanagiotisKanavos w komentarzach, ma to kilka głównych wad:

• Excel musi być zainstalowany
• dla każdego dokumentu uruchamiana jest nowa instancja programu Excel
• zużycie pamięci i innych zasobów jest znacznie większe niż to, co jest niezbędne do prostego manipulowania dokumentami XLSX.

Ale jeśli zdecydujesz się go użyć, możesz nie wyświetlać programu Excel, załadować plik XLSX i uzyskać do niego dostęp. Nie jestem pewien, czy obsługuje iterację, jednak nie sądzę, że byłoby zbyt trudno zbudować wokół dostarczonych metod, ponieważ jest to pełne API Microsoft OLE dla Excel. Oto dokumentacja: http://support.microsoft.com/kb/222101 Oto klejnot: http://www.ruby-doc.org/stdlib-1.9.3/libdoc/win32ole/rdoc/WIN32OLE.html

Ponownie, opcje nie wyglądają dużo lepiej, ale obawiam się, że nie ma wiele więcej. trudno jest przeanalizować format pliku, który jest czarną skrzynką. A ci nieliczni, którym udało się go złamać, nie zrobili tego tak wyraźnie. Google Docs to closed source, a LibreOffice to tysiące linii C++.

The rubyXL gem pięknie parsuje pliki XLSX.

Przez ostatnie kilka tygodni ciężko pracowałem zarówno z arkuszem kalkulacyjnym, jak i rubyXL i muszę powiedzieć, że oba są świetnymi narzędziami. Jednak jednym z obszarów, które cierpią oba, jest brak przykładów na faktycznie wdrożenie czegokolwiek użytecznego. Obecnie buduję crawler i używam rubyXL do analizy plików xlsx i arkusza kalkulacyjnego dla wszystkiego xls. Mam nadzieję, że poniższy kod może służyć jako pomocny przykład i pokazać, jak skuteczne mogą być te narzędzia.

require 'find'
require 'rubyXL'

count = 0

Find.find('/Users/Anconia/crawler/') do |file|             # begin iteration of each file of a specified directory
  if file =~ /\b.xlsx$\b/                                  # check if file is xlsx format
    workbook = RubyXL::Parser.parse(file).worksheets       # creates an object containing all worksheets of an excel workbook
    workbook.each do |worksheet|                           # begin iteration over each worksheet
      data = worksheet.extract_data.to_s                   # extract data of a given worksheet - must be converted to a string in order to match a regex
      if data =~ /regex/
        puts file
        count += 1
      end      
    end
  end
end

puts "#{count} files were found"

require 'find'
require 'spreadsheet'
Spreadsheet.client_encoding = 'UTF-8'

count = 0

Find.find('/Users/Anconia/crawler/') do |file|             # begin iteration of each file of a specified directory
  if file =~ /\b.xls$\b/                                   # check if a given file is xls format
    workbook =  Spreadsheet.open(file).worksheets          # creates an object containing all worksheets of an excel workbook
    workbook.each do |worksheet|                           # begin iteration over each worksheet
      worksheet.each do |row|                              # begin iteration over each row of a worksheet
        if row.to_s =~ /regex/                             # rows must be converted to strings in order to match the regex
          puts file
          count += 1
        end
      end
    end
  end
end

puts "#{count} files were found"

Nie mogłem znaleźć satysfakcjonującego parsera xlsx. RubyXL nie wykonuje typowania daty, Roo próbował typować liczbę jako datę, a oba są bałaganem zarówno w api, jak i w kodzie.

Więc napisałem simple_xlsx_reader . Musisz jednak użyć czegoś innego do xls, więc może nie jest to pełna odpowiedź, której szukasz.

Większość przykładów online, w tym Strona autora arkusza kalkulacyjnego gem, pokazuje odczyt całej zawartości pliku Excel do pamięci RAM. W porządku, jeśli Twój arkusz kalkulacyjny jest mały.

xls = Spreadsheet.open(file_path)

Dla każdego, kto pracuje z bardzo dużymi plikami, lepszym sposobem jest stream-read zawartość pliku. Gem arkusza kalkulacyjnego obsługuje to-choć nie jest dobrze udokumentowane w tym czasie (około 3/2015).

Spreadsheet.open(file_path).worksheets.first.rows do |row|
  # do something with the array of CSV data
end

CITE: https://github.com/zdavatz/spreadsheet

Biblioteka RemoteTable używa wewnętrznieroo . Ułatwia czytanie arkuszy kalkulacyjnych w różnych formatach (XLS, XLSX, CSV itp. ewentualnie zdalne, ewentualnie przechowywane w zip, gz, itp.):

require 'remote_table'
r = RemoteTable.new 'http://www.fueleconomy.gov/FEG/epadata/02data.zip', :filename => 'guide_jan28.xls'
r.each do |row|
  puts row.inspect
end

Wyjście:

{"Class"=>"TWO SEATERS", "Manufacturer"=>"ACURA", "carline name"=>"NSX", "displ"=>"3.0", "cyl"=>"6.0", "trans"=>"Auto(S4)", "drv"=>"R", "bidx"=>"60.0", "cty"=>"17.0", "hwy"=>"24.0", "cmb"=>"20.0", "ucty"=>"19.1342", "uhwy"=>"30.2", "ucmb"=>"22.9121", "fl"=>"P", "G"=>"", "T"=>"", "S"=>"", "2pv"=>"", "2lv"=>"", "4pv"=>"", "4lv"=>"", "hpv"=>"", "hlv"=>"", "fcost"=>"1238.0", "eng dscr"=>"DOHC-VTEC", "trans dscr"=>"2MODE", "vpc"=>"4.0", "cls"=>"1.0"}
{"Class"=>"TWO SEATERS", "Manufacturer"=>"ACURA", "carline name"=>"NSX", "displ"=>"3.2", "cyl"=>"6.0", "trans"=>"Manual(M6)", "drv"=>"R", "bidx"=>"65.0", "cty"=>"17.0", "hwy"=>"24.0", "cmb"=>"19.0", "ucty"=>"18.7", "uhwy"=>"30.4", "ucmb"=>"22.6171", "fl"=>"P", "G"=>"", "T"=>"", "S"=>"", "2pv"=>"", "2lv"=>"", "4pv"=>"", "4lv"=>"", "hpv"=>"", "hlv"=>"", "fcost"=>"1302.0", "eng dscr"=>"DOHC-VTEC", "trans dscr"=>"", "vpc"=>"4.0", "cls"=>"1.0"}
{"Class"=>"TWO SEATERS", "Manufacturer"=>"ASTON MARTIN", "carline name"=>"ASTON MARTIN VANQUISH", "displ"=>"5.9", "cyl"=>"12.0", "trans"=>"Auto(S6)", "drv"=>"R", "bidx"=>"1.0", "cty"=>"12.0", "hwy"=>"19.0", "cmb"=>"14.0", "ucty"=>"13.55", "uhwy"=>"24.7", "ucmb"=>"17.015", "fl"=>"P", "G"=>"G", "T"=>"", "S"=>"", "2pv"=>"", "2lv"=>"", "4pv"=>"", "4lv"=>"", "hpv"=>"", "hlv"=>"", "fcost"=>"1651.0", "eng dscr"=>"GUZZLER", "trans dscr"=>"CLKUP", "vpc"=>"4.0", "cls"=>"1.0"}