Search code examples
pythonopenpyxllibreoffice-calc

Err:508 in formula written with Openpyxl

I have this script:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws['A1'] = 1 #any value
ws['B1'] = 0 #any value
ws['C1'] = 3 #any value
ws['D1'] = 0 #any value
ws['E1'] = "= IF(A1<>0;A1;1) * IF(B1<>0;B1;1) * IF(C1<>0;C1;1) * IF(D1<>0;D1;1)"
wb.save('error.xlsx')

When I open the file with LibreOffice Calc, I see Err:508 in E column:

Err:508

However, if I edit the formula in the command line, without changing nothing (i.e. add an space or erase any character and write the same again) the formula works.

Any idea where is the mistake?

Solution

  • Searching in https://openpyxl.readthedocs.io/en/stable/usage.html#using-formulae as @Charlie Clark suggested, it says:

    Warning

    NB you must use the English name for a function and function arguments must be separated by commas and not other punctuation such as semi-colons.`

    so this line:

    ws['E1'] = "= IF(A1<>0;A1;1) * IF(B1<>0;B1;1) * IF(C1<>0;C1;1) * IF(D1<>0;D1;1)"

    must be:

    ws['E1'] = "= IF(A1<>0,A1,1) * IF(B1<>0,B1,1) * IF(C1<>0,C1,1) * IF(D1<>0,D1,1)"