Adding formulas to excel spreadsheet using python

Question

I'm trying to use Python to add formulas to an Excel spreadsheet. The formulas include, for instance:

• =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") 

When I opened the SS in Excel after using xlsxwriter, it was repaired by eliminating the "unreadable" material, and those cells displayed as 0. I've read that when using xlsxwriter, the recalculation should be performed upon sheet reopening, but it doesn't appear that this is the case. (https://xlsxwriter.readthedocs.io/working_with_formulas.html)

Is there a method to import these formulas into Excel without having Excel delete them when you open the file? 

Answer 1

You can create any formula that Excel can handle with XlsxWriter. To ensure that the formula fits the US version of Excel, you must be cautious with the formatting (which is the default format that formulas are stored in).

Therefore, if you use a comma instead of a pipe, your formulas should function as expected:

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