CODEWITHSUNDEEP
pythonxlsxwriter
Asta
Asta

Reputation: 41

Why is Python XLSWRITER Cell formula does not work programmatically?

I have these simple codes that get the SUM of A1, A2 and A3.

workbook = xlsxwriter.Workbook(path)
worksheet = workbook.add_worksheet()

worksheet.write(0, 0, 5, None)
worksheet.write(1, 0, 3, None)
worksheet.write(2, 0, 9, None)
worksheet.write(3, 0, '=SUM(A1:A3)', None)

workbook.close()

Theoretically it should work. But it's not. (See image below)

enter image description here

But if I manually change one of the 3 numbers then it will work.

enter image description here

Manually changing its numbers are no good bc the excel is a generated file via python program, I cannot just say to the client to manually change a 1 number of their choice and change it back when the sum formula works.

Send help senior Programmers.

Upvotes: 1

Views: 1399

Answers (2)

jmcnamara
jmcnamara

Reputation: 41524

From the XlsxWriter FAQ:

Q. Why do my formulas show a zero result in some, non-Excel applications?

Due to a wide range of possible formulas and the interdependencies between them XlsxWriter doesn’t, and realistically cannot, calculate the result of a formula when it is written to an XLSX file. Instead, it 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 several mobile applications, will only display the 0 results.

If required, it is also possible to specify the calculated result of the formula using the optional value parameter in write_formula():

worksheet.write_formula('A1', '=2+2', None, 4)

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, '')

From your screenshot it looks like you are using LibreOffice or OpenOffice so you can try the suggested workaround of adding a blank result:

worksheet.write(3, 0, '=SUM(A1:A3)', None, '')

Upvotes: 1

buran
buran

Reputation: 14233

From the 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 will only display the 0 results. Examples of such applications are Excel Viewer, PDF Converters, and some mobile device applications.

If required, it is also possible to specify the calculated result of the formula using the optional value parameter for write_formula()

Check the link for more info and examples.

Upvotes: 1

Related Questions