10

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
Improve this question
1

4 Answers 4

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

but I assume it is for a windows machine .I guess I should have mentioned it but I think it won't work in mac/unix/linux right ?
This one is a good solution for windows users but unfortunately I never use windows machine .I use a Mac and my code run on linux machines .
super stupid question but is it not possible to get win32ole on *nix?
@DanPantry, very unlikely. under the hoods, win32ole is like "use installed microsoft office to do the work". it's very handy when you are on Windows+Office (all the examples in this comment are straightforward translation from Office VBA Help); but in any other environment you just can't get it.
2

I assume we are talking about old Excel format (xls), it seems spreadsheet gem can't work with xlsx anyways.

I'd give a try to one of command-line spreadhseet converters: either xls2csv from catdoc package (really fast, though not all Excel files are processed successfully) or ssconvert from gnumeric package (moderate speed, and requires to install entrie GNumeric, which sometimes not an option for server, but really robust).

NB: When parsing Excel, roo just requires spreadsheet and wraps it in own API, so it never can be faster or more reliable than spreadsheet.

NB2: If I remember correctly (thought it was some long years ago), trying to automate OpenOffice from ruby was a) really hard and b) really slow.

Improve this answer

4 Comments

Only problem I have with xls2csv is what If I have multiple worksheets in an single excel and want them to be separate CSV .
xls2csv has -b option - "sheet break string". So, if your xls is succesfully parsed with xls2csv (mine are not, so I can't test it), you can either split output file with some more bash magic, like here; or you can capture output into Ruby with popen, and just use regular split method on it.
Mine neither are getting parsed :( .
give a try to ssconvert, then (though, if on server, it provides no option besides install the full gnumeric, which, in its turn, require all gnome libraries)
2 
xls_file = Roo::Excelx.new('test.xlsx')
CSV.open('test.csv') do |csv|
    (2..xls_file.last_row).each do |i| # if you do not need header otherwise (1..xls_file.last_row)
        csv << a.row(i)
    end
end
Improve this answer

1 Comment

way too low :D. There is this also as an alternative github.com/scpike/excel2csv/blob/master/src/excel2csv.rb But mostly what shanky Munjal said
1

Be sure you're using the up-to-date Roo (1.13.2).

Also be sure you're using the patch for skipping trailing blank rows:

https://github.com/Empact/roo/blob/master/lib/roo/worksheet.rb

If you can post one of your spreadsheets that's taking a long time to parse, it may help people here help you. Just be sure to delete any confidential data.

Improve this answer

3 Comments

yes I m using the latest gem . I tried this example with roo gem but it was very slow .
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
can you give me an example of how to use Roo for multiple sheet in single excel .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.