Formula does not get evaluated upon writing to file

[dangnlg2]

I am currently trying to import a formula which add all the cells in a column together inside my code. After I added the formula to the bottom cell in the column, I opened the .xls file and saw the formula instead of the result.

Issue:

Here's my formula in the bottom cell '=SUM(B5:B7)', the issue is that I see the exact formula when I open the file.

Expected output:

I expected to see the sum of the specified column instead of the formula.

I know that I can simply add all the number and store the result in my code, but I am more interested in getting the formula to work in excel.

Thank you in advance.

[zdavatz]

Please attach your sample script and your sample file. What version does your OS have and what version is your Ruby and what version is your gem.

[alduethadyn]

I have the same issue. I'm using:

• Ruby 2.3.0
• OS X El Capitan 10.11.6
• Microsoft Excel for Mac 15.13.3 (150815)

Code

require 'spreadsheet'

Spreadsheet.client_encoding = 'UTF-8'
book = Spreadsheet::Workbook.new

sheet = book.create_worksheet name: 'Test'

row0 = sheet.row(0)
(0..4).each { |n| row0[n] = 1 }
row0[5] = "=SUM(A1:E1)"

book.write("out.xls")

I've additionally tried to do variations of row0[5] = Spreadsheet::Formulat.new.tap { |f| f.data = "=SUM(A1:E1)" } for giggles, but the data/formula gets lost (tried setting the value there too).

I realize Formula support is a bit limited - but do you have some pointers on where to go look to help find a solution?

Output

• out.xls.zip

[zdavatz]

Microsoft Excel for Mac does show strange behavior. Please test again with LibreOffice for Mac.

[alduethadyn]

Same results with LibreOffice 5.2.3.3:

[zdavatz]

Ok, you will have to dig into the source code to fix this.

[hothero]

@zdavatz Have this problem been solved? or any ideas to prevent this problem? I got the same here.

[Looooong]

Some problem in LibreOffice 6.0.3.2