Search code examples
pythonsubprocessopenpyxl

How to open an Excel file, have the formulas be recalculated, then close it after some time?

I need Python to open, save and then close an Excel file with the default software (e.g. MS Excel), because I need that Excel calculates some formulas, to read the numerical results within a Python code (I am using openpyxl). In DOS I would write something like:

@echo off
start myfile.xlsx
timeout /t 8 /nobreak
some save here...
taskkill /im excel.exe /f

Is there a Pythonic way to do this? I need it for Windows, but it would be perfect if working also in other OS.

I had a look at the subprocess and os modules, but I found the documentation quite cryptic for me :-( especially when coming to waiting and then closing the process.

Solution

  • Following @moken suggestion, I used xlwings, that also allows to open the excel file in background:

    import xlwings as xw

app = xw.App(visible=False)
book = app.books.open(filename_with_path)
sheet = book.sheets[worksheet_name]
print(sheet['C5'].value)
book.save() ## note that saving is optional if you simply need to get the numbers from the Excel formulas
book.close()
app.quit()

    This does all what I need!