fuzunspm fuzunspm - 6 months ago 47
Python Question

write_formula gives error unless i copy and paste exactly the same formula

I have a python script that writes excel file in the end with xlsxwriter. Everything works but a formula is giving error upon launching and if i copy and paste the exactly same formula it gives the results expected.
here is the line:

worksheet.write_formula('I2', '=SUMIF(B2:B{0};1;F2:F{0})'.format(len(df.index)+1))


edit: i try to export as xml and i saw that xlsxwriter writes ; as |. I mean the error giving formula from xlsxwriter is:

<Cell ss:Formula="of:=SUMIF([.B2:.B11]|1|[.F2:.F11])">
<Data ss:Type="String">Err:508</Data>


Copy and pasted working formula is:

<Cell ss:Formula="of:=SUMIF([.B2:.B11];1;[.F2:.F11])">
<Data ss:Type="Number">485</Data>


I don't know what's the issue here. Thank you

Answer

Goto the given link i believe you will find your answer: XlsxWriter: Working with Formulas

Specifically the Non US Excel functions and syntax says:

Excel stores formulas in the format of the US English version, regardless of the language or locale of the end-user's version of Excel. Therefore formulas must be written with the US style separator/range operator which is a comma (not semi-colon). A formula with multiple values should be written as follows:

worksheet.write_formula('A1', '=SUM(1, 2, 3)')   # OK 

worksheet.write_formula('A2', '=SUM(1; 2; 3)')   # Semi-colon. Error on load.

Hope this helps.