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