openpyxl if condition formula with reference fails

1
votes

Using python 2.7 and openpyxl, I am inserting an IF formula into excel worksheet that references fields in other sheet.

While other formulas work (CONTIF, COUTN, SUM, etc) I kind of do not find the reason for this "simple" IF formula does not to work. Only IF is the problem

This is my code 

for i in range(0,len(sortedWorkers)):
        for a, rr in enumerate(dates):
            row_nb = i + 3
            coll = 3 + a
            clmn_letter = get_column_letter(coll + 3)
            # =IF(Worked!I$2=7;Worked!I3;"")
            valFormula = '=IF(Worked!%s$2=7;Worked!%s%s;"")' %(clmn_letter,clmn_letter, str(row_nb))
            _cl = ws12.cell(column= coll, row= row_nb, value=valFormula)

in the comment you can se the formula. The format is correct. It also works if I manually insert it into excel.

Also field coordinates and everything matches.

Any suggestions? Thank you

3
pythonexcelopenpyxl

3 Answers

2
votes

Formulas are sometimes serialised in the XML differently than they appear in the GUI. They may have prefixes, or use different words, or, and I think this may be the case here, they use different separators between parameters. In countries which use a comma as the decimal separator, semi-colons are used to separate parameters in the GUI, but the XML will use commas.

You can check this by looking at the source of a file created by Excel.

1
votes

So at the end the whole problem was that with using IF formula commas have to be used "," not semi-colons ";"

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.

0
votes

encoutered the same kind of issue trying to insert an =IF statement in a formula.

As being said, you need to only use commas as separators and only formula implemented in openpyxl :

from openpyxl.utils import FORMULAE
print(FORMULAE)

In addition, the only way i've found making it work with IF is affecting the formula with this syntax :

worksheet[cell coords].value="IF(condition,if true, if false)

which would look like for example (working example) :

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1["A1"].value='=IF(1=1,"True","False")'
wb.save(filename = dest_filename)

Hope it'll help.