openpyxl:保存到文件时在公式中插入"@" [英] openpyxl: '@' is inserted to formula when saving to file

问题描述

当我在单元格中添加以下公式时，该单元格的值在打印到控制台时看起来不错.但是，保存文件后，公式在'='之后插入了'@'(为简单起见，我从控制台提供了输出):

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')
>>> 

在"formula.xlsx"文件中，公式如下所示:

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

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

但是，例如，如果我指定的是"= SUM()"而不是"= CONCAT()"，那么它将按预期方式保存，即未插入"@".

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

我正在使用openpyxl 3.0.3和Python 3.8.

I am using openpyxl 3.0.3 and Python 3.8.

非常感谢

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

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

我研究了"formula.xlsx"的XML代码；但在此之前，我在Excel中将其打开，将单元格A1复制到单元格D1中，并从单元格D1中的公式中删除了"@"，此后D1开始显示正确的值，而A1仍显示"#NAME?".错误.

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.

因此，在单元格D1中进行了更改之后，工作表的XML代码显示以下内容:

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>

openpyxl在上面的单元格A1中为CONCAT使用的_xludf前缀在

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

这是否意味着该库无法将CONCAT识别为标准Excel函数，因此使用_xludf而不是_xlfn?

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 ---

推荐答案

我在使用西班牙语公式时也遇到了同样的问题(我来自阿根廷).当我尝试分配"== SUMA(A1:A20)"之类的内容时，到单元格，则显示为"= @ SUMA(A1:A20)".

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)".

我尝试了_xlfn.解决方案，但现在它最终变成了" = @ _ xlfn.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.

已解决如果您使用的是非英语版本的Excel，则仍必须为单元格分配函数的英语名称，例如"= SUM(A1:A20)"之后，当您检查工作表中单元格的内容时，它将更改为正确的语言，在这种情况下，西班牙语为"= SUMA(A1:A20)"

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.

也:如果您使用另一组字符作为分隔符，例如用逗号(，)代替小数点(.)，则在将公式分配给单元格时仍需要使用点，例如"; = E8 * 0.5".当您检查单元格时，您将看到一个逗号.打开xlsx文件时，在该字符串中使用逗号将导致文件损坏.

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.

这篇关于openpyxl:保存到文件时在公式中插入"@"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！