To convert Excel into CSV efficiently in ruby

Question

I used the spreadsheet gem to do this. It works but it can be very slow at times .I even tried the Roo gem, but that didn't improve the performance. Is there a better way to do this job? The weird thing is that some worksheets in the same excel work faster and some worksheets work very slowly, even taking up to 1 hour.

Can we use open office to open each worksheet(tab) in a single excel and convert them to csv much faster? If yes, how would I do it in ruby?

Or is there an even better solution?

Just adding a small example I tried with Roo gem

xls = Roo::Excel.new(source_excel_file)
xls.each_with_pagename do |name, sheet|
  # p sheet.to_csv(File.join(dest_csv_dir,name + ".csv"))
  #sheet.parse(:clean => true)#.to_csv(File.join(dest_csv_dir,name + ".csv"))
  puts name
  puts sheet.parse(:clean => true)
end

Answer 1

Cowardly Preface: I am SUPER new to ruby and know almost nothing of rails, but I have tangled with Excel before. I created a dummy workbook on my local machine with 5 sheets, each containing 10 columns and 1000 rows of randomly-generated numbers. I converted each sheet into its own CSV with this:

require 'win32ole'
require 'csv'

# configure a workbook, turn off excel alarms
xl = WIN32OLE.new('excel.application')
book = xl.workbooks.open('C:\stack\my_workbook.xlsx')
xl.displayalerts = false

# loop through all worksheets in the excel file
book.worksheets.each do |sheet|
  last_row = sheet.cells.find(what: '*', searchorder: 1, searchdirection: 2).row
  last_col = sheet.cells.find(what: '*', searchorder: 2, searchdirection: 2).column
  export = File.new('C:\\stack\\' + sheet.name + '.csv', 'w+')
  csv_row = []

  # loop through each column in each row and write to CSV
  (1..last_row).each do |xlrow|
    (1..last_col).each do |xlcol|
      csv_row << sheet.cells(xlrow, xlcol).value
    end
    export << CSV.generate_line(csv_row)
    csv_row = []
  end
end

# clean up
book.close(savechanges: 'false')
xl.displayalerts = true
xl.quit

An eyeball benchmark for this script was ~30 seconds, with each attempt coming in a few seconds above or below that.