fuzunspm - 1 year ago 95

Python Question

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.

Source (Stackoverflow)