Open ausangshukla opened 2 years ago
class XlExporter
DATA = [["Tim", 20], ["Dan", 30], ["Rich", 40]]
def self.export
open_book = Spreadsheet.open('test.xls')
new_row_index = 0
header = ["Name", "Age"]
open_book.worksheet(0).row(new_row_index).concat(header)
DATA.each do |d|
new_row_index += 1
open_book.worksheet(0).row(new_row_index).concat( [d[0], d[1]] )
puts "Wrote row #{new_row_index}"
end
open_book.write('test_new.xls')
end
end
Note that the test.xls has 2 formulas, one on sheet 1 and the other on sheet2, which are exactly the same, ie sum of col B in sheet 1, but the generated XL shows only the sum on sheet 1, and an error on sheet 2.
Please lmk if you need more clarification.
which software created the test.xls?
LibreOffice on ubuntu I also tried with XL on windows, same result
Try adding the formulas in LibreOffice, then save with LibreOffice, then modify the file with Ruby. Does that work?
Not working using a newly created doc on LibreOffice on Ubuntu. In the result file, it is stripping out the row references from a different sheet, so the formula becomes invalid. So =SUM($Sheet1.B2:B11) becomes =SUM() and hence becomes invalid (See cell A1 in sheet2)
I will try with a windows XL also and report it here
I suppose you read the guide: https://github.com/zdavatz/spreadsheet/blob/master/GUIDE.md
Formulas in the same sheet work well, but same formula for values in a different sheet break. Essentially sheet name within formulas is not copied in the created sheet.
Ruby 3.1.2 Ubuntu LibreOffice 7.3.2.2