6

When I add the following formula to a cell, the cell's value looks good when printed to the console. However, after I save the file, the formula has '@' inserted right after the '=' (for simplicity, I am providing the output from the console):

>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> ws['A1'] = '=CONCAT("Week ",TEXT(MID(' + get_column_letter(9) + '1,6,2)+ 1, "##"))'
>>> ws['A1'].value
'=CONCAT("Week ",TEXT(MID(I1,6,2)+ 1, "##"))'
>>> wb.save('formula.xlsx')
>>>

In the 'formula.xlsx' file, the formula looks like this:

=@CONCAT("Week ",TEXT(MID(I1,6,2)+ 1, "##"))

If, however, instead of '=CONCAT()' I specify '=SUM()', for example, it is saved as expected, i.e. without the '@' inserted.

I am using openpyxl 3.0.3 and Python 3.8.

Many thanks

-------- Udate --------

I have looked into the XML code of 'formula.xlsx'; but before doing that, I opened it in Excel, copied cell A1 into cell D1, and deleted '@' from the formula in cell D1, after which D1 started showing the correct value while A1 still showed the '#NAME?' error.

So, after my changes in cell D1, the XML code for the sheet showed the following:

<row r="1" spans="1:9" x14ac:dyDescent="0.45">
    <c r="A1" t="e"><f ca="1">_xludf.CONCAT("Week ",TEXT(MID(I1,6,2)+ 1, "##"))</f><v>#NAME?</v></c>
    <c r="D1" t="str"><f>_xlfn.CONCAT("Week ",TEXT(MID(I1,6,2)+ 1, "##"))</f><v>Week 68</v></c>
    <c r="I1"><v>12345678</v></c>
</row>

The _xludf prefix used by openpyxl for CONCAT in cell A1 above is described as "User Defined Function" on https://learn.microsoft.com/en-us/office/client-developer/excel/xludf.

Could it mean that the library did not recognise CONCAT as a standard Excel function, and therefore used _xludf instead of _xlfn for it?

----- End of update ---

bob39722
  • 73
  • 1
  • 5
  • Hi Bob, I tried to recreate your code and the issue but it seems to work fine for me. Is that the entire relevant code? – Dror Av. May 10 '20 at 11:49
  • Yes, absolutely. The code above is a screenshot of my test in the console. Are you on Windows/Linux or Mac (in my case it is Mac)? Maybe that makes the difference? Let me see if I can try this out in Windows... – bob39722 May 11 '20 at 16:55
  • I'm using a win machine, with version 3.0.3 of openpyxl and Python 3.8 (With PyCharm) – Dror Av. May 11 '20 at 18:23
  • I have opened that very same 'formula.xlsx' file in MS Windows, and the '@' symbol was not showing (while it shows in Excel for Mac). However, in the cell itself it still shows '#NAME?' error. As soon as I either delete '=' and retype it again, or if I type a space between '=' and 'CONCAT' and delete it, then the error disappears, and a correct value is calculated in the cell (you need a proper value present in 'I1' of course, otherwise you'll immediately have the '#VALUE!' error). So, there is some code inserted between '=' and 'CONCAT' during wb.save() completion. – bob39722 May 11 '20 at 21:04
  • 1
    try `ws['A1'] = '=_xlfn.CONCAT("Week ",TEXT(MID(' + get_column_letter(9) + '1,6,2)+ 1, "##"))'` – Dror Av. May 11 '20 at 21:17
  • Hey, that is in line with what I found on the net, but I didn't think one can use _xlfn explicitly in the python code. Just tried it out and it worked wonders. Thanks a lot! – bob39722 May 11 '20 at 21:41
  • Youre welcome, should have came up with it long ago as I had the same issue in the past and even answered someone here wit that answer (https://stackoverflow.com/questions/45635582/how-can-i-get-python-generated-excel-document-to-correctly-calculate-array-formu/45636531#45636531). Oh well glad we found the answer at the end :) – Dror Av. May 11 '20 at 22:03
  • 1
    I've also added a more detailed answer with a link to the documentation for any future reference and to help others if they stumble upon the issue. – Dror Av. May 11 '20 at 22:14

4 Answers4

8

As specified in the openpyxl documentation known formulas are used just by inserting the formula name.

One can use

>>> from openpyxl.utils import FORMULAE
>>> "CONCAT" in FORMULAE
False

To check if the formula is a known one in openpyxl. If the formula isn't you need to add _xlfn. just before the formula name, like so:

>>> ws['A1'] = '=_xlfn.CONCAT("Week ",TEXT(MID(' + get_column_letter(9) + '1,6,2)+ 1, "##"))

It is also mentioned in the documentation:

If you’re trying to use a formula that isn’t known this could be because you’re using a formula that was not included in the initial specification. Such formulae must be prefixed with _xlfn. to work.

Dror Av.
  • 1,184
  • 5
  • 14
3

I have the same problem using formulae in Spanish (I'm from Argentina). When I try to assign something like "=SUMA(A1:A20)" to a cell, it comes out as "=@SUMA(A1:A20)".

I tried the _xlfn. solution, but now it just ends up as "=@_xlfn.SUMA(A1:A20)"

If/when I find an answer I'll post it here.

SOLVED If you are using a non-English version of Excel, you still have to assign the cell the English name of the function, e.g. "=SUM(A1:A20)" Afterwards, when you check the content of the cell in the worksheet, it will have changed to the proper language, in this case the Spanish "=SUMA(A1:A20)"

Caveat: I've only checked it in the Spanish version, but I'm pretty sure it works for all.

Also: If you use another set of characters as separators, for example a comma (,) instead of a dot (.) for decimals, you still need to use the dot when assigning a formula to a cell, for example "= E8 * 0.5". You will see a comma when you check the cell. Using a comma in that string will result in a damaged-file error when opening the xlsx file.

jorge_mt
  • 31
  • 3
1

In my case I was trying to put =ARRED formulae and the character @ appears in the Excel file like =@ARRED.

ARRED is a Brazilian Portuguese prefix and is not recognized, so I replaced by ROUND and is worked perfectly.

Testing:

from openpyxl.utils import FORMULAE
"ARRED" in FORMULAE
False
"ROUND" in FORMULAE
True
Toni
  • 1,555
  • 4
  • 15
  • 23
Ordnael
  • 31
  • 1
  • 6
0

Specifying _xlfn prefix explicitly in the python code fixes the problem:

>>> ws['A1'] = '=_xlfn.CONCAT("Week ",TEXT(MID(' + get_column_letter(9) + '1,6,2)+ 1, "##"))'

Thanks goes to Dror Av. for guidance!

bob39722
  • 73
  • 1
  • 5