Formulas are becoming blank
Created originally on Bitbucket by kudrat (Kudrat Isabaev)
I trialed these two methods - both of which failed (second was a workaround).
Method - 1
- Updated spreadsheet cells using openpyxl and saved to new spreadsheet;
- Loaded new spreadsheet into pandas dataframe which read NaN. Pandas can read the same spreadsheet (not the one saved by openpyxl) fine even with formulas. When I check sheet.values - it is showing Excel formula rather than values (I even formatted the table to Numeric)
wbk = load_workbook(filename = "test.xlsx")
ws = wbk["test_tab"]
ws["B2"] = 15.0
ws["B3"] = 11.0
wbk.save("test_new.xlsx")
# formulas are read as NaN in "test_new.xlsx", but original "test.xlsx" formulas are read as correct values
df_xlsx = pandas.read_excel("test_new.xlsx", "other_tab", index=False)
Method - 2
After the above didn’t work.
- Updated spreadsheet cells using openpyxl and saved to new spreadsheet;
- Loaded new spreadsheet back this time using “data_only = True”;
- Saved the workbook with “data_only=True” which resulted the workbook having blank cells where there was formula.
wbk = load_workbook(filename = "test.xlsx")
ws = wbk["test_tab"]
ws["B2"] = 15.0
ws["B3"] = 11.0
wbk.save("test_new.xlsx")
wbk = load_workbook(filename="test_new.xlsx", data_only=True)
# blank cells where formulae
wbk.save("test_values.xlsx")
Is this a known issue and how can it be resolved please?