CODEWITHSUNDEEP
pythonxlsxopenpyxlread-write
Takashiwa Max
Takashiwa Max

Reputation: 33

Python - openpyxl read xlsx data after writing on existing xlsx with formula

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.

Upvotes: 3

Views: 4576

Answers (2)

Sebastian
Sebastian

Reputation: 49

I had the same problem. My approach is the following:

print('Reopen all files and save again')
for eachFile in glob.glob(path + fileCriteria)[:]:
    xl = DispatchEx('Excel.Application')
    xl.Visible = False
    wb = xl.Workbooks.Open(eachFile)
    wb.Close(True)

Upvotes: 0

Charlie Clark
Charlie Clark

Reputation: 19497

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

Upvotes: 2

Related Questions