CODEWITHSUNDEEP
pythonxlsxxlsxwriterlibreoffice-calc
Luc
Luc

Reputation: 183

xlsxwriter and LibreOffice not showing formula's result

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?

Upvotes: 18

Views: 6558

Answers (3)

Paul McQuesten
Paul McQuesten

Reputation: 71

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.)

Upvotes: 4

Robin
Robin

Reputation: 323

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.

Upvotes: 8

DSM
DSM

Reputation: 352969

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.

Upvotes: 29

Related Questions