I am trying to read a xlsx file after writing on the existing xlsx excel file using openpyxl.
My Excel file file1.xlsx having value 1 on A1, value 2 on A2 and value A1 + A2 on A3, which is 3 at this point.
def updateFile(a):
wb = load_workbook('file1.xlsx')
ws = wb.active
#Update specific column
ws['A1'] = a
wb.save('file1.xlsx')
def readFile():
wb = load_workbook('file1.xlsx')
sheet = wb['Sheet1']
print(sheet['A3'].value)
My program is going to update A1 on file1.xlsx and read the data on A3. For example, calling updateFile(5) will update A1 to 5, and perhaps giving me 7 on A3.
Unfortunately, after updateFile(5) is called, readFile() will gives = A1 + A2 as output, instead of 7.
This is mainly because the data on Excel file is updated but not saved. And if I want to let readFile() to output 7, I have to open file1.xlsx manually, Save it, and Close it.
Are there anyway or am I misusing read/write on openpyxl to resolve this issues? I believe I had not Save the file properly or I have to figure a way to open, save, close an Excel file programmatically.
What you are seeing is expected behaviour. When cells have formulae then Excel saves the result of the formula as a cached value. openpyxl never evaluates formulae so never maintains a cache and invalidates any existing cache. Instead, if you want the results of a formula then you can load the file with the data_only=True parameter. Though this will replace the formula with the value.
This is covered in the documentation: http://openpyxl.readthedocs.org/en/stable/usage.html#read-an-existing-workbook