python-2.7 - 使用 Python 代码(在 Linux 中)读取 excel 列并获取公式值

Question

我有一个如下所示的 pandas 数据框:

A    B    C
1    2    =A2+B2
3    4    =A3+B3

我使用 Python 中的 xlsxwriter 将其写入 Excel 文件，并将数据框转换为 Excel。现在，当我从 Python 读取 Excel 时，我得到 0.0 作为 C2 的值，而不是 3 (=A2+B2 )。但是，如果我手动打开 Excel，则会对公式求值并在“C2”中显示“3”。所以在读取代码时出现问题。

Python 中有没有一种方法可以读取以公式作为值的 Excel 列？

Answer 1

所以问题出在读取代码时。

不是真的。

问题在于 XlxsWriter 不会将公式的值写入 Excel 文件。来自XlsxWriter FAQ :

Formula results displaying as zero in non-Excel applications

Due to wide range of possible formulas and interdependencies between them XlsxWriter doesn’t, and realistically cannot, calculate the result of a formula when it is written to an XLSX file. Instead, it 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 several mobile applications, will only display the 0 results.

If required, it is also possible to specify the calculated result of the formula using the optional value parameter in write_formula():

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