Split individual sheets in a spreadsheet xls file to csv files

Question

I have a daily-downloaded (using mechanize & whenever/sidekiq+redis) .xls file with two worksheets and want to split them into two files, with each having one sheet. I've tried many ways to no avail (tossed in a random delete method hoping that would work, nope):

# goal: start with 1 file w/2 sheets and split into 2 files with 1 sheet each

def split_file
  open_xls = Spreadsheet.open 'my_file.xls'
  open_xls.write 'destination1.xls'
  open_xls.write 'destination2.xls'
  File.delete('my_file.xls')

  # open first new file and try to delete one sheet
  open_xls1 = Spreadsheet.open 'destination1.xls'
  sheet1 = open_xls1.worksheet(0)
  open_xls1.sheet1.delete   # from log: "NoMethodError: undefined method `delete'"
  open_xls1.write 'destination_only_sheet2.xls'

  # "" other sheet
  # repeat on 2nd file to remove other sheet

end

Starting thinking "does spreadsheet gem treat workbooks like arrays--can I use Array methods?"...then threw up the hands.

Main spreadsheet-gem resources:

repo guide: https://github.com/zdavatz/spreadsheet/blob/master/GUIDE.md
rubydocs: http://www.rubydoc.info/gems/ruby-spreadsheet

score 0 · Answer 1 · answered Jan 23 '16 at 15:07

Using the roo with roo-xls gems (instead of spreadsheet alone) works. I am starting with an .xls file and cannot change that fact. The magic is Roo's default_sheet and simple first and last methods (because the .xls file is converted to a .csv, which only accepts saving a single sheet). I have this code in a broader .rb file where I also have require 'spreadsheet' and require 'csv' too (not sure if they're needed below):

# in this example, there are only two sheets in the .xls file

class SomeClassName

  require 'roo'
  require 'roo-xls'

  def split_file

    # relative path to file
    file_path = File.join(Rails.root, "lib", "assets", "downloaded_file.xls")

    # retrieve original xls and save first sheet as .csv
    oldxls = Roo::Spreadsheet.open(file_path)
    oldxls.default_sheet = oldxls.sheets.first
    oldxls.to_csv("#{file_path}/new_file_name1.csv")

    # retrieve original xls and save second sheet as .csv
    oldxls = Roo::Spreadsheet.open(file_path)
    oldxls.default_sheet = oldxls.sheets.last
    oldxls.to_csv("#{file_path}/new_file_name2.csv")

    # delete original .xls
    File.delete("#{file_path}/downloaded_file.xls")

  end

end

SomeClassName.split_file

Split individual sheets in a spreadsheet xls file to csv files

1 Answers1