CODEWITHSUNDEEP
pythonopenpyxl
user5668024
user5668024

Reputation:

Openpyxl is not able to read function value refering to a cell which it wrote into previously

I have a problem with openpyxl package. To illustrate the isue, I have prepared a simple example.

I have an excel file which contains nothing but formula =A1 in B1 cell. I would like to (1) write a value of 123 into cell A1, (2) save the workbook, (3) open it again and (4) read a content of cell B1. Instead of 123 I get None. Below you can find a simple code, which (I hope) should do as just described. Can anyone see, what I am doing wrong?

import openpyxl

# open file and select sheet
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')

# write value into cell A1
sheet['A1'].value = 123

# save the file and close it
wb.save('example.xlsx')
wb.close()

# open the file again and select sheet
wb = openpyxl.load_workbook('example.xlsx', data_only=True)
sheet = wb.get_sheet_by_name('Sheet1')

# read value from cell containing referece to cell A1 => why it returns None?
print(sheet['B1'].value)

# close the file
wb.close()

Many thx,

Macky

PS: I am using python 3.5.5, openpyxl 2.5.6 and MS Office 2013 on Win7.

Upvotes: 0

Views: 1970

Answers (4)

Velton Angelis D'silva
Velton Angelis D'silva

Reputation: 1

A feedback to @Jake Morris answer:

Yes, openpyxl doesn't have the capability till data to perform a recalculation operation on a excel file. But we can still solved the above problem by opening the excel file through a COM object via win32com(pywin32) library, where we can just do the following:

  1. Open the excel file using win32com(pywin32) library.
  2. Perform Recalculation operation using win32com(pywin32) recalculate function.
  3. Save, Close and Quite the excel file.

If automatic recalculation is enabled in your excel file then you may skip step 2 and just perform step 1 and 3.

Thank you for your time
Velton Angelis D'silva

Upvotes: 0

Deepesh Verma
Deepesh Verma

Reputation: 19

Try setting 'data_only' parameter to True while loading workbook.

wb = load_workbook("example.xlsx", data_only=True)
print(sheet['B1'].value)

This will print the result after computing formula at B1 cell.

Upvotes: 1

user5668024
user5668024

Reputation:

Since the above comments do not fully answer my question, I will add a link to another complementary thread. Hopefully it will help others facing the same problem. I did not realize that the updated excel file has to be opened and saved using Excel application...

Regards,

Macky

Upvotes: 0

Jake Morris
Jake Morris

Reputation: 645

openpyxl does not and will not calculate the result of formulas, hence the formula B1=A1 will only be calculated when you open the excel sheet or use another program that will calculate it. There are other libraries that I believe can help, like pycel.

Upvotes: 1

Related Questions