I have a pandas dataframe that looks like this:
A B C
1 2 =A2+B2
3 4 =A3+B3
I write this to an Excel file using xlsxwriter in Python and convert the data frame to Excel. Now, when I read the Excel from Python, I get 0.0 as the value for C2 and not 3 (=A2+B2). However, if I open Excel manually, the formulas are evaluated and has '3' in 'C2'. So the problem occurs while reading from code.
Is there a way in Python to read Excel columns with formulas as values?
So the problem is while reading from code.
Not really.
The issue is that XlxsWriter doesn't write the value of a formula to an Excel file. From the 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)
I would save the Excel file as a .csv. Excel should automatically convert all formulae to values. You can then read the .csv into Python with the usual file methods.
