计算Excel表格而不打开它们（openpyxl或xlwt） [英] Calculating Excel sheets without opening them (openpyxl or xlwt)

问题描述

我制作了一个打开xls的脚本。文件，写入一些新的值，然后保存该文件。

稍后，该脚本再次打开它，并希望在包含公式的某些单元格中找到答案。

如果我用openpyxl调用该单元格，我得到公式（即：= A1 * B1）。
如果我激活data_only，我什么都没有。

有没有办法让python计算xls文件？ （或者我应该尝试PyXll？）

解决方案

实际上有一个项目可以使用Excel公式并使用Python进行评估： Pycel 。 Pycel使用Excel本身（通过COM）来提取公式，因此在您的情况下，您将跳过该部分。该项目可能有一些有用的东西可以使用，但我不能保证其成熟度或完整性。它不是真正为公众开发的。

另一种方法是将公式的值和写入单元格（以便当您读取文件，你可以只拉取值，而不用担心公式）。在撰写本文时，我没有找到在OpenPyXL中执行此操作的方法，但 XlsxWriter 可以做到这一点从文档：

XlsxWriter不计算公式的值，而是将值0存储为公式结果。然后在XLSX文件中设置一个全局标志，表示打开文件时应重新计算所有公式和函数。这是Excel文档中推荐的方法，一般来说，它可以用于电子表格应用程序。但是，没有计算公式的功能的应用程序（如Excel Viewer）或某些移动应用程序将只显示0个结果。



如果需要，它也是可以使用options值参数指定公式的计算结果。当使用不计算公式值的非Excel应用程序时，这是偶尔需要的。计算值在参数列表的末尾添加：



worksheet.write_formula（'A1'，'= 2 + 2'，num_format，4）

如果你采用这种方法，你必须自己计算公式的值（在你的Python脚本中），然后将公式和值写在同一个时间如上述文件所示。当您读取该值时，您将使用OpenPyXL的 data_only 选项。 （对于阅读此答案的其他人：如果您使用xlrd，则只有该值可用。）

I made a script that opens a xls. file, writes a few new value's in it, then save the file.

Later the script opens it again, and wants to find the answers in some cells which contain formulas.

If I call that cell with openpyxl, I get the formula (ie: =A1*B1). And if I activate data_only, i get nothing.

Is there a way to let python calculate the xls file? (or should I try PyXll?)

解决方案

There is actually a project that takes Excel formulas and evaluates them using Python: Pycel. Pycel uses Excel itself (via COM) to extract the formulas, so in your case you would skip that part. The project probably has something useful that you can use, but I can't vouch for its maturity or completeness. It was not really developed for the general public.

Another approach is to write the value and the formula into a cell (so that when you read the file, you can just pull the value, and not worry about the formula at all). As of this writing, I haven't found a way to do it in OpenPyXL, but XlsxWriter can do it. From the documentation:

XlsxWriter doesn’t calculate the value of a formula and instead stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened. This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don’t have a facility to calculate formulas, such as Excel Viewer, or some mobile applications will only display the 0 results.

If required, it is also possible to specify the calculated result of the formula using the options value parameter. This is occasionally necessary when working with non-Excel applications that don’t calculate the value of the formula. The calculated value is added at the end of the argument list:

worksheet.write_formula('A1', '=2+2', num_format, 4)

If you take this approach, you have to calculate the value of the formula yourself (in your Python script), and then write both the formula and the value at the same time as shown in the above documentation. When it's time to read the value, you would use OpenPyXL's data_only option. (For other people reading this answer: If you use xlrd, then only the value is available anyway.)

这篇关于计算Excel表格而不打开它们（openpyxl或xlwt）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！