Adding formulas to excel spreadsheet using python

Question

I am attempting to insert formulas into an excel spreadsheet using python. Examples of the formulas are:

=VLOOKUP(B3|"Settlement Info"!$B$2:$R$2199|17|FALSE)
=SUMIFS("Payment and Fees"!$I$2:$I$6445|"Payment and Fees"!$B$2:$B$6445|Summary!$B3)
=+E3-F3-G3-I3
=IF(AND(I3>0|I3-N3>=-0.1|I3-N3<=0.1)|"Yes"|"No")

I tried using xlsxwriter and when opening the ss in excel it repairs by removing the "unreadable" content and those cells show as 0. I've seen the comment that the recalculation should be done on the reopening of the sheet when using xlsxwriter but that does not look like is is being done (https://xlsxwriter.readthedocs.io/working_with_formulas.html)

Is there some way to get these formulas into excel without them being removed by excel on opening? Thanks for any pointers.

I simplified this down to a simple as possible:

When I run the below code and then attempt to open the excel spreadsheet I get an error saying "We found a problem with some content in ...Do you want us to try to recover..If you trust the source select yes" If I select yes then I get an error " Removed Records: Formula from /xl/worksheets/sheet1.xml part"

And if I continue the sheet opens and there is a 0 in the field.

from xlsxwriter.workbook import Workbook

workbook = Workbook('test.xlsx')
worksheet = workbook.add_worksheet('Summary')
worksheet.write_formula('A2', '=VLOOKUP(B3,"Settlement Info"!$B$2:$R$2199,17,FALSE)')

workbook.close()

If I look at the information at https://xlsxwriter.readthedocs.io/working_with_formulas.html there is the information:

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.

Which I may not be understanding as I believe that the formula should be left in the sheet.

jmcnamara jmcnamara · Accepted Answer · 2020-07-15T15:32:25

You can XlsxWriter to create any formula that Excel can handle. However, you need to be careful with the formatting of the formula to make sure that it matches the US version of Excel (which is the default format that formulas are stored in).

So your formulas should probably work as expected if you use a comma instead of a pipe:

=VLOOKUP(B3,"Settlement Info"!$B$2:$R$2199,17,FALSE)
=SUMIFS("Payment and Fees"!$I$2:$I$6445,"Payment and Fees"!$B$2:$B$6445,Summary!$B3)
=IF(AND(I3>0,I3-N3>=-0.1|I3-N3<=0.1),"Yes","No")

This one should work without modification:

=+E3-F3-G3-I3

See this section of the XlsxWriter docs on Working with Formulas.

Update in relation to the updated question:

The formula still has an error. You need to use single quotes instead of double quotes. You also need to add another worksheet for the formula to refer to. Like this:

from xlsxwriter.workbook import Workbook

workbook = Workbook('test.xlsx')
worksheet = workbook.add_worksheet('Summary')
worksheet.write_formula('A2', "=VLOOKUP(B3,'Settlement Info'!$B$2:$R$2199,17,FALSE)")

workbook.add_worksheet('Settlement Info')

workbook.close()

There is still an #N/A error in Excel but that is related to not having data in the VLOOKUP range:

Output:

Adding formulas to excel spreadsheet using python

1 Answers