xlsxwriter and LibreOffice not showing formula's result

Question

I'm trying to create an Excel file with a simple formula:

import xlsxwriter
workbook = xlsxwriter.Workbook('testxlsx.xlsx', {'strings_to_numbers': True})
ws = workbook.add_worksheet()

ws.write('A2', 'Number one')
ws.write('B2', '1')
ws.write('A3', 'Number two')
ws.write('B3', "1000")
ws.write('A4', "Number three")
ws.write('B4', "1050")
ws.write('A5', "Number four")
ws.write('B5', "3")
ws.write('A6', "Result")
ws.write('B6', '=IF(B5=3,ROUND(100-(B3/B4*100),1),ROUND(100-(B3/(B4*1.502)*100),1))')

workbook.close()

The generated file works perfectly in Excel, but when opened in LibreOffice Calc the formula is not evaluated. I need to reenter the numeric values and then it works.

What am I doing wrong?

Ukase

Answer 1

From the xlsxwriter docs:

XlsxWriter doesn’t calculate the result of a formula and instead stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened. This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don’t have a facility to calculate formulas, such as Excel Viewer, or some mobile applications will only display the 0 results.

As for why the recalculation doesn't automatically occur, from an ask.libreoffice.org answer:

LibreOffice intentionally does not recalculate older spreadsheets, because as formulas are updated from version to version or between different spreadsheet programs, the results can be different. Go to Tools – Options – LibreOffice Calc, under 'Recalculation on file load', change the two drop-downs, 'Excel 2007 and newer' and 'ODF Spreadsheet (not saved by LibreOffice)', to 'Always recalculate'. Click Ok, close the spreadsheet and LibreOffice. Now open the file in LibreOffice and you should see that the formulas have recalculated.

Also go to Tools – Cell Contents and be sure that AutoCalculate is selected.

I've confirmed that setting "always recalculate" or "prompt" worked for me. Alternatively, you can always hit control-shift-F9.

Yeomanly

Just to extend the answer: In the same xlsxwriter-link it also mentions that we can include the calculated result of the formula (if we have it) as the 5th argument to .write() and store it in the worksheet. – Carinthia

Answer 2

From the FAQ of the xlsxwriter website:

Note: LibreOffice doesn’t recalculate Excel formulas that reference other cells by default, in which case you will get the default XlsxWriter value of 0. You can work around this by setting the “LibreOffice Preferences -> LibreOffice Calc -> Formula -> Recalculation on File Load” option to “Always recalculate” (see the LibreOffice documentation). Or, you can set a blank result in the formula, which will also force recalculation:

worksheet.write_formula('A1', '=Sheet1!$A$1', None, '')

I just tested this, and it indeed works.

Ortega

Answer 3

Add note to https://stackoverflow.com/users/761731/robin's answer: The cell value argument must be the empty string. If you use None instead, the formula will not be evaluated upon loading, and will display zero. (This should be a Comment, but I do not have enough rep.)

Fork