Worksheet format breaks when saving excel file with openpyxl

Question

I try to load excel file for change some values inside it and for read some formulas. if I get a new excel file (which didn't save with openpyxl), I can read formulas result with code:

from openpyxl import *

wb = load_workbook(filename="test1.xlsx",data_only=True)
sheet1 = wb.active
print(sheet1["b9"].value)
### b9 formul is '=SUM(A1+A2+A3)' that's also working in excel.
###output is :37

But, If a change something inside file as belown code:

from openpyxl import *

wb =load_workbook(filename="test1.xlsx")
sheet = wb.active

sheet["A1"]=42
sheet["A2"]=33
sheet["A3"]=22

wb.save(filename="test1.xlsx")

wb = load_workbook(filename="test1.xlsx",data_only=True)
sheet1 = wb.active
print(sheet1["b9"].value)
##output is : None

When I check excel file everythings seems good. formule is right, columns changed, but when I try to read excel formulas with python, I can't see the result, it turns None, if removed data_only=True from workbook also I can see formulas. What's wrong with my code.

Did you try to check the content of b9 before you do the edit? Does it contains the equation? — TocToc, Nov 13 '19 at 13:53
Possible duplicate of [Openpyxl 1.8.5: Reading the result of a formula typed in a cell using openpyxl](https://stackoverflow.com/questions/23350581/openpyxl-1-8-5-reading-the-result-of-a-formula-typed-in-a-cell-using-openpyxl) — stovfl, Nov 13 '19 at 14:13

score 0 · Accepted Answer · answered Nov 13 '19 at 14:11

0

Try with xlwings. You can open and see your data like:

import xlwings as xw
wb = xw.Book("test1.xlsx")
sht = wb.sheets["Your sheet"]
print(sht.range('B9').value)

Or modified it:

sht.range('A1').value = 42
sht.range('A2').value = 33
sht.range('A3').value = 22

And save it:

wb.save()

And reopen it:

other_wb = xw.Book("test1.xlsx")
sht_other_wb = other_wb["your sheet"]
print(sht_other_wb.range("B9").value)

answered Nov 13 '19 at 14:11

rvcristiand

442
7
19

yes it worked! xlwings save commands with microsoft excel, that's so format doesnt break as another libraries. – S.Arda Nov 15 '19 at 12:29

Worksheet format breaks when saving excel file with openpyxl

1 Answers1