Python openpyxl Modify Excel Interworking and Read

I'd like to modify the file from Python to openpyxl and read the modified data. However, an error occurs.

The following files already exist: The name of the file is 'Add'.It's xlsx'.

If you look at it, you can see that the cell C3 is made up of a formula.

The part I want to modify is to change the cells 'A1' and 'B1' to 1 and 2, respectively. Finally, what I want to get is the value of cell C3 which is the result of calculating the sum of cell A1 and B1.

To modify the value of the file, I wrote it as follows.

from openpyxlimport load_workbook
wb = load_workbook(filename = 'plus.xlsx', read_only=False, data_only=False)
ws = wb['Sheet1']
ws['a1'] = 1
ws['b1'] = 2
a1 = ws['a1']
b1 = ws['b1']
c1 = ws['c1']
print(a1.value)
print(b1.value)
print(c1.value)
wb.save('plus1.xlsx')`

The file was renamed and saved to retain the original file.

The output is as follows.

1
2
=SUM(A1:B1)

When loading the file, the expression was output, not the value calculated by specifying 'data_only=False'.

The reason why the file was loaded with 'data_only=True' instead of 'data_only=False' is because all expressions disappear when you save them.

Now it's time to read the saved 'plus one' file. I wrote the following to read the file.

from openpyxlimport load_workbook
wb = load_workbook (filename = 'plus 1.xlsx', read_only=False, data_only=True)
ws = wb['Sheet1']
a1 = ws['a1']
b1 = ws['b1']
c1 = ws['c1']
print(a1.value)
print(b1.value)
print(c1.value)

At this time, we changed it to 'data_only=True' because only 'value' is required, not 'expression'.

The output is as follows:

1
2
None

The output shows that the value has been successfully changed, but the cell 'C3' is 'None' as if it were an empty cell.

However, if you open the 'plus 1' Excel file that was actually created, you can see that the Excel file is made as you want as shown below.

I've tried to solve this problem, and I've found some things that I can figure out, but I haven't finally solved it yet. Here's what I found out.

If you execute the coding content as described above, the 'None' value will be obtained. Strangely enough, however, when opening and closing the generated 'plus1' file,

Although it is a file that has already been created, the following message appears, and if you save it, the normal result will be printed when you run the second coding content again, and if you do not save it, the value of 'None' will continue to be printed. So I think it's an error caused by something not being saved properly.

The above problem only occurs when you modify an existing file to Python and read a newly created file, not when you just read an existing file that already exists. For example, when you directly create and save an Excel file containing multiple formulas, and load the file as 'data_only' to read the value, there is no error. This also occurs only when you modify and save existing files with Python and read the 'value of the calculation formula' next, so it is assumed that there is something missing in the process of saving.

I'm having a hard time because I'm a beginner, so I'm writing and reading, but there's an error. Please help me Thank you so much for reading it! Happy New Year! Haha