Create excel file:
excelpath = str(PATH('testexcel.xlsx')) wb = Workbook() sheetrp = wb.create_sheet(title='Report') sheetrp['A1'] = 1 sheetrp['A2'] = 3 sheetrp['B20'] = '=SUM(A1:A2)' wb.save(excelpath)
Load existing excel file:
excelpath = str(PATH('testexcel.xlsx')) excelwb = load_workbook(excelpath, data_only=True) report = excelwb['Report']
a. After excel file is created, when reading the formulae result using "data_only=True", it always return None.
b. If write formulae into excel file using "data_only=True", it returns formulae before being saved and returns None after being saved
c. If write formulae into excel file/save excel using "data_only=False", and then read again using "data_only=True", it always return None.
b. Returns formulae result if excel file is manually created, or open and save the excel file manually
In a nutshell,
when I create a sheet by coding, I could not read the formulae result
when I read an existing excel file, the formulae I wrote into it is ineffective. I could not get the formulae result
I could only get the formulae after manually open and save the excel file. but this is ridiculous