如何在openpyxl中保存而不丢失公式? [英] How to save in openpyxl without losing formulae?

问题描述

因为我需要解析然后使用单元格中的实际数据，所以我在openpyxl中使用data_only = True打开一个xlsm.

Because I need to parse and then use the actual data in cells, I open an xlsm in openpyxl with data_only = True.

这被证明是非常有用的.现在，尽管同样需要在单元格中包含formuale的xlsm，但是当我保存更改时，保存的版本中缺少公式.

This has proved very useful. Now though, having the same need for an xlsm that contains formuale in cells, when I then save my changes, the formulae are missing from the saved version.

data_only = True和公式是否互斥?如果没有，保存时如何在不丢失公式的情况下访问单元格中的实际值?

Are data_only = True and formulae mutually exclusive? If not, how can I access the actual value in cells without losing the formulae when I save?

当我说我失去公式时，似乎保留了公式的结果(总和，串联).但是，单击单元格时，不再显示实际的公式.

When I say I lose the formulae, it seems that the results of the formulae (sums, concatenattions etc.) get preserved. But the actual formulaes themselves are no longer displayed when a cell is clicked.

更新:

要确认是否保留了公式，我重新打开了保存的xlsm，这次将data_only保留为False.我已经检查了使用公式构造的单元格的value.如果保留了公式，则在将data_only设置为False的情况下打开xlsm应该会返回该公式.但是它会返回实际的文本值(这不是我想要的值).

To confirm whether or not the formulaes were being preserved or not, I've re-opened the saved xlsm, this time with data_only left as False. I've checked the value of a cell that had been constructed using a formula. Had formulae been preserved, opening the xlsm with data_only set to False should have return the formula. But it returns the actual text value (which is not what I want).

推荐答案

部分问题是: data_only = True和公式是否互斥?

Part of your question was: Are data_only = True and formulae mutually exclusive?

在openpyxl中，答案是肯定的.

The answer to that, in openpyxl, is yes.

但这不是Excel固有的.您可以拥有像openpyxl这样的库，它使您可以访问公式及其结果.这不太可能发生，因为openpyxl的维护者在哲学上反对该想法.

But this is not intrinsic to Excel. You could have a library like openpyxl which gives you access to both the formulas and their results. This is unlikely to happen, since the maintainer(s) of openpyxl are philosophically opposed to this idea.

因此，期望如何在openpyxl中处理这种情况是将工作簿加载两次:使用data_only=True一次只是读取数据(保存在内存中)，然后再次将其作为带有data_only=False的不同"工作簿以获取可写版本.

So, how you're expected to handle your kind of situation in openpyxl is to load the workbook twice: once with data_only=True just to read the data (which you keep in memory), then load it again as a "different" workbook with data_only=False to get a writable version.

在保留其他所有内容(包括格式，公式，图表，宏等)的同时，使用Python修改现有工作簿的规范"方法是使用COM接口(例如 PyWin32 或更高级别的包装器，例如 xlwings )来控制正在运行的实例Excel.当然，只有在安装了Excel的计算机上运行时，这才可能.

The "canonical" way of modifying an existing workbook with Python while preserving everything else (including formatting, formulas, charts, macros, etc.) is to use a COM interface (such as PyWin32, or higher-level wrappers like pywinauto or xlwings) to control a running instance of Excel. Of course, this is only possible if you are running on a machine with Excel installed.

这篇关于如何在openpyxl中保存而不丢失公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！