Formula referencing value in different sheet is broken

[ausangshukla]

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

[zdavatz]

1. hmm, can you share your test script please?
2. Are you creating a new file or are you modifying an existing one?

[ausangshukla]

test.xls

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

[ausangshukla]

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.

[zdavatz]

which software created the test.xls?

[ausangshukla]

LibreOffice on ubuntu
I also tried with XL on windows, same result

[zdavatz]

Try adding the formulas in LibreOffice, then save with LibreOffice, then modify the file with Ruby. Does that work?

[ausangshukla]

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)

test.xls

I will try with a windows XL also and report it here

[zdavatz]

I suppose you read the guide: https://github.com/zdavatz/spreadsheet/blob/master/GUIDE.md