Save Pandas DataFrames with formulas to xlsx files

After writing the df using table.to_excel(writer, sheet_name=...), I use write_formula() as in this example (edited to add the full loop). To write all the formulas in your dataframe, read each formula in your dataframe.

# replace the right side below with reading the formula from your dataframe # e.g., formula_to_write = df.loc(...)

rows = table.shape[0] for row_num in range(1 + startrow, rows + startrow + 1): formula_to_write = '=I{} * (1 - AM{})'.format(row_num+1, row_num+1) worksheet.write_formula(row_num, col, formula_to_write)

Later in the code (I seem to recall one of these might be redundant, but I haven't looked it up): writer.save() workbook.close()

Documentation is here.

Since you are using xlsxwriter, strings are parsed as formulas by default ("strings_to_formulas: Enable the worksheet.write() method to convert strings to formulas. The default is True"), so you can simply specify formulas as strings in your dataframe.

Example of a formula column which references other columns in your dataframe:

d = {'col1': [1, 2], 'col2': [3, 4]}
df = pd.DataFrame(data=d)
writer = pd.ExcelWriter("foo.xlsx", engine="xlsxwriter")
df["product"] = None
df["product"] = (
    '=INDIRECT("R[0]C[%s]", 0)+INDIRECT("R[0]C[%s]", 0)'
    % (
        df.columns.get_loc("col1") - df.columns.get_loc("product"),
        df.columns.get_loc("col2") - df.columns.get_loc("product"),
    )
)
df.to_excel(writer, index=False)
writer.save()

Produces the following output: