使用 xlrd 时读取包含公式的单元格值返回 0.0 [英] Reading a cell value that contains a formula returns 0.0 when using xlrd

问题描述

我尝试读取单元格值，例如 Excel 工作表中的 E5 和 E5 包含公式=(A29 - A2)".我使用以下代码，它返回 0.00 而不是实际值 1.440408.有没有办法解决这个问题?我想打印正确的值.请帮我解决一下这个.谢谢.

book = xlrd.open_workbook('Test.xlsx')first_sheet = book.sheet_by_index(0)specific_cell_value = (first_sheet.cell_value(4,4))打印(particular_cell_value)

解决方案

Excel 文件独立存储公式和值. 如果文件直接由 Excel 保存，那么 Excel 会同时写入.但是，其他软件生成的文件，例如xlwt、OpenPyXL、XlsxWriter，只写公式.这是因为无法保证第三方包能够以与 Excel 完全相同的方式评估 Excel 的内置函数.因此，建议(实际上来自 Microsoft 本身)是让第三方包编写公式，将值设置为零，并将全局自动重新计算"标志设置为开启(因此，如果文件在 Excel 中打开，Excel 将继续计算所有公式自动生成).

XlsxWriter 在其 write_formula() 方法的文档中详细说明:

<块引用>XlsxWriter 不计算公式的值，而是将值 0 存储为公式结果.然后它会在 XLSX 文件中设置一个全局标志，以说明在打开文件时应重新计算所有公式和函数.这是 Excel 文档中推荐的方法，通常它适用于电子表格应用程序.但是，不具备计算公式功能的应用程序(例如 Excel 查看器)或某些移动应用程序将仅显示 0 结果.

然后继续告诉您如果您真的想在那里放置一个值，如何将值和公式写在一起.该值将是您选择的任何值，它不一定与 Excel 计算的值相同.在撰写本文时，xlwt 和 OpenPyXL 都不支持此功能.

所以，说了这么多，当谈到读取一个 Excel 文件时，xlrd 自然会选择 0，除非同时写入结果.您会注意到 xlrd 总是在 Excel 上次保存文件时获取正确的结果.如果您使用 XlsxWriter 编写了一个，它还会获取结果(可能正确也可能不正确).

I try to read a cell value, say E5 and E5 in the excel sheet contains a formula '=(A29 - A2)'. I use the following code and it returns me 0.00 instead of the actual value 1.440408 . Is there a way to solve this? I want to print the correct value. Please help me with this. Thank you.

book = xlrd.open_workbook('Test.xlsx')
first_sheet = book.sheet_by_index(0)
particular_cell_value = (first_sheet.cell_value(4,4))
print(particular_cell_value)

解决方案

Excel files store formulas and values independently. If the files are saved directly by Excel, then Excel will write both. However, files generated by other software, such as xlwt, OpenPyXL, and XlsxWriter, only write the formula. This is because there is no guarantee that a third-party package will be able to evaluate Excel's built-in functions exactly the same way that Excel does. Thus the recommendation (from Microsoft itself, actually) is for third-party packages to write the formula, set the value to zero, and set the global "auto-recalculate" flag on (so if the file is opened in Excel, Excel will go ahead and calculate all formula results automatically).

XlsxWriter spells it out in its documentation for the write_formula() method:

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.

It then goes on to tell you how to write the value and the formula together if you really want to put a value there. The value would be whatever you choose, which isn't necessarily the same value that Excel would calculate. As of this writing, neither xlwt nor OpenPyXL support this feature.

So, having said all that, when it comes to reading an Excel file, naturally xlrd is going to pick up 0, unless the result was also written at the same time. You will notice that xlrd always picks up the correct result when the file was last saved by Excel. It will also pick up the result (which may or may not be correct) if you wrote one using XlsxWriter.

这篇关于使用 xlrd 时读取包含公式的单元格值返回 0.0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！