Excel删除Python插入的有效公式 [英] Excel removing valid formula inserted by Python
问题描述
我正在尝试使用 openpyxl 在Python中生成excel xlsx报告,但是存在问题. Excel会在一些特定的单元格中不断从我的工作表中删除公式.
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.
我打印了要插入的内容,这似乎绝对有效,如果我将其从控制台粘贴到显示正确结果的单元格中.我什至尝试插入不带前导"="的公式,当我在excel中将其打开并添加"="后,它便成功了.
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:
当我不在该特定单元格中插入任何内容时,所有内容均会平滑打开,而不会发出警告.但是,当我手动将其插入时, excel会显示警告,表示带有公式的单元格不受保护.不知道这是否应该成为问题. 我不能完全包住它. Excel错误日志一如既往的有用.
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.
推荐答案
Please pay attention to the warning in the documentation: http://openpyxl.readthedocs.io/en/default/usage.html#using-formulae
注意,对于函数,您必须使用英文名称,并且函数参数必须用逗号分隔,而不能使用其他标点符号(例如分号)分隔.
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.
这篇关于Excel删除Python插入的有效公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!