← Back to Upcase

Ideas for writing data to a tab in an Excel file (.xls or .xlsx)?


(Jason Pierce) #1

I need to add data to a tab in an excel doc. The tab has nothing in it besides this data and the data is simple containing a few columns and less than a hundred rows. The excel doc can be either an .xls or .xlsx and contains dashboards and reports that cannot be broken.

I’ve tried Spreadsheet, Axlsx, RubyXL and reviewed many other gems like Roo that only read from Excel docs.

So far Spreadsheet can open a .xls doc and resave it correctly if I don’t try to add any data. However, if I try to add any data the saved file is corrupted.

This works:

   open_book = Spreadsheet.open('../data/exports/test_output_dashboard.xls') 
    puts "#{open_book.worksheet(0)}"
    puts "#{open_book.worksheet(1)}"
    puts "#{open_book.worksheet(2)}"
    open_book.write('../data/exports/test_output_dashboard_2.xls')

This does not:

open_book = Spreadsheet.open('../data/exports/test_output_dashboard.xls') 
puts "#{open_book.worksheet(0)}"
puts "#{open_book.worksheet(1)}"
puts "#{open_book.worksheet(2)}"
new_row_index = open_book.worksheet(1).last_row_index + 1
open_book.worksheet(1).insert_row(new_row_index, row_2)
open_book.write('../data/exports/test_output_dashboard_4.xls')

With RubyXL I can open and inspect the content of each tab but the saved doc cannot be opened by Excel.

workbook = RubyXL::Parser.parse("../data/exports/test_output_dashboard.xlsx")
puts "#{workbook.worksheets[0].inspect}"
puts "#{workbook.worksheets[1].inspect}"
puts "#{workbook.worksheets[2].inspect}"
workbook.write("../data/exports/test_output_dashboard_5.xlsx")

Does anyone have any ideas how I might pull this off? I appreciate any new ideas or direction.

Thanks


(Matthew Sumner) #2

Do you need to retain formatting or formulae or other tabs? If not I’d consider reading it and rewriting to a csv file (which excel can open).

Otherwise there is a railscast episode talking about writing to excel files. This mentions an xml format that excel understands with documentation here. Not sure that really helps though if your editing an existing file. May be worth looking into though.


(Jason Pierce) #3

Yes, unfortunately the other tabs have some rather complex formatting like pivot tables, charts, and drop-down menus that must be retained.

Thanks for the resources. I’ll check those out.


(Matthew Sumner) #4

A few other thoughts:

  • Do you need to be able to edit the data after it leaves your system? If it’s just for viewing you could always rewrite the excel logic into ruby and have it spit out a pdf.
  • There is a stackoverflow question here where someone suggests making a new file then having the other file import it through macros. I’m not an excel guru so wouldn’t know where to start with that.

Good luck


(Jason Pierce) #5

Some good ideas. The Excel doc I am trying to add data to does need to be able to be edited. It is essentially a big dashboard where the end user can add data to other tabs and use drop downs to customize the reports.

You are correct with linking to external files in Excel. We have a distributed team and the realities of having everyone access the same set of working files was something we hoped to avoid although we may have to go with this approach. Using an ODBC connection in Excel to refresh the data is another option. However, the clients security policy mandates that every database login have an IP address and that makes this approach difficult to implement.