问题描述
所以我有这个简单的代码，应该将公式插入到单元格中:
so I have this simple code that should insert a formula into a cell:
wb = Workbook()
ws = wb.create_sheet('General')
ws['A1'].value = 'Hello'
ws['B1'].value = 'World'
#Now cell C1 should display 'Hello World'
ws['C1'].value = "=CONCAT(A1,B1)"
#ws['C1'].value = "=CONCATENATE(A1,B1)"
#ws['C1'].value = "=TEXTJOIN(,,A1,B1)"
wb.save('Test.xlsx')
我已经手动输入了在Excel中输入的公式，它们起作用了，但是当通过openpyxl分配时，它们返回了#NAME?
I've manually entered entered the formula in Excel and they work, but when assigned via openpyxl they return #NAME?
单元格C1中的公式检出，如果我选择了该单元格并按Enter或双击它，然后在外部单击，它会显示正确的结果；我也尝试过但仍然没有运气:
The formula in cell C1 checks out, if I select the cell and hit enter or double click it and then click outside, it shows the correct result; I've also tried with but still no luck:
ws['C1'].set_explicit_value("=CONCAT(A1,B1)","f")
推荐答案
问题是OOXML规范仅涵盖原始版本中的公式，而不适用于Microsoft在后续版本中添加的公式.
The problem is that the OOXML specification only covers the formulae that were in the original release and not those that Microsoft has added in subsequent releases.
您可以轻松地检查公式是否正确:
You can easily check whether a formula is ok:
from openpyxl.utils.formulas import FORMULAE
'CONCAT' in FORMULAE
False
'CONCATENATE' in FORMULAE
True
'TEXTJOIN' in FORMULAE
False
为了使用最新的公式，这些公式必须以'_xlfn.'为前缀.如果仍然存在问题，则需要查看相关文件的XML源.
In order to use more recent formulae, these must be prefixed with '_xlfn.'. If there are still problems then you will need to look at the XML source of the relevant files.
这篇关于Concat，Concatenate，TextJoin，都返回"#NAME?"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！