How can I calculate and hide my formulas in excel-CodePudding

I have excel file with a few sheets. After I load data to some sheets, on main sheet formulas must calculate some data.But if tried to load only data from file it s empty because, until I open the excel file with excel the colums with formulas are not calculating. How can I calculate formulas with out opening ms excel. I am working wit python

CodePudding user response：

Openpyxl builds xlsx files, it does not interact with Excel so there is no instance of Excel and formulas are not executed. Openpyxl will provide either the value or formula in a cell depending of how the workbook is 'opened'.
You need to interact with Excel so maybe something like this will help;

Xlwings interacts with Excel and therefore requires a local installation of Excel to use.
In the example cells A1 - A3 have integer values written and cell A4 has a formula written to sum A1:A3. The result of the summing, i.e. value of cell A4 is then printed which shows the value 16911.
You will see that Excel is opened but the workbook is hidden so no data can be seen. At the completion of the code run all instances of Excel are closed.
I have put a slight delay in the code so you can see that the Excel instance does not show the workbook or any of the data.

import time
import xlwings as xw


with xw.App() as app:
    app.display_alerts = False
    app.screen_updating = False

    filename = "formula_test.xlsx"
    wb = xw.Book(filename)
    ws = wb.sheets['Sheet1']

    ws.range("A1").value = 1234
    ws.range("A2").value = 5678
    ws.range("A3").value = 9999

    ws.range("A4").value = '=SUM(A1:A3)'
    time.sleep(10)  # Delay to check Excel instance 

    print("Result :", ws.range("A4").value)

    wb.save(filename)
    wb.close()

There are other modules that can help with Excel formula calculation if this does not meet your requirement.

xlwings