Reading a cell value that contains a formula returns 0.0 when using xlrd
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)
Answer
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.