Michal Fašánek Michal Fašánek - 3 years ago 231
Python Question

Excel removing valid formula inserted by Python

I am trying to generate an excel xlsx report in Python, using openpyxl, but there is a problem. Excel keeps removing formula from my sheet in a few specific cells.

I printed what is being inserted and it seems absolutely valid, if I paste it from console to the cell it shows the exact result. I even tried to insert the formula without the leading '=' and it did so, when I opened it in excel and added '=' it worked.

This is the given formula, just a SUM of cells on a condition of some other value in matching rows:

=SUMIF($C$27:$C$243;A249;$I$27:$I$243)

The error on opening workbook is as follows:

enter image description here
enter image description here

When I do not insert anything in that particular cell everything opens smooth, no warnings. When I insert it manually, however, excel shows warning saying that the cell with formula is not protected. Not sure if that should be in any way an issue.
I cannot quite wrap my head around it. Excel error log is as useful as ever.

Answer Source

Please pay attention to the warning in the documentation: http://openpyxl.readthedocs.io/en/default/usage.html#using-formulae

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download