Calculating Excel sheets without opening them (openpyxl or xlwt)

Question

I made a script that opens a .xls file, writes a few new values in it, then saves the file.

Later, the script opens it again, and wants to find the answers in some cells which contain formulas.

If I call that cell with openpyxl, I get the formula (ie: "=A1*B1"). And if I activate data_only, I get nothing.

Is there a way to let Python calculate the .xls file? (or should I try PyXll?)

Answer 1

I realize this question is old, but I ran into the same problem and extensive searching didn't produce an answer.

The solution is in fact quite simple so I will post it here for posterity.

Let's assume you have an xlsx file that you have modified with openpyxl. As Charlie Clark mentioned openpyxl will not calculate the formulas, but if you were to open the file in excel the formulas would be automatically calculated. So all you need to do is open the file and then save it using excel.

To do this you can use the win32com module.

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')
workbook = excel.Workbooks.Open(r'absolute/path/to/your/file')
# this must be the absolute path (r'C:/abc/def/ghi')
workbook.Save()
workbook.Close()
excel.Quit()

That's it. I've seen all these suggestions to use Pycel or Koala, but that seems like a bit of overkill if all you need to do is tell excel to open and save.

Granted this solution is only for windows.

Answer 2

xlcalculator can do this job. https://github.com/bradbase/xlcalculator

from xlcalculator import ModelCompiler
from xlcalculator import Model
from xlcalculator import Evaluator

filename = r'use_case_01.xlsm'
compiler = ModelCompiler()
new_model = compiler.read_and_parse_archive(filename)
evaluator = Evaluator(new_model)

# First!A2
# value is 0.1
#
# Fourth!A2
# formula is =SUM(First!A2+1)

val1 = evaluator.evaluate('Fourth!A2')
print("value 'evaluated' for Fourth!A2:", val1)

evaluator.set_cell_value('First!A2', 88)
# now First!A2 value is 88
val2 = evaluator.evaluate('Fourth!A2')
print("New value for Fourth!A2 is", val2)

Which results in the following output;

file_name use_case_01.xlsm ignore_sheets []
value 'evaluated' for Fourth!A2: 1.1
New value for Fourth!A2 is 89