Save Pandas DataFrames with formulas to xlsx files

Question

In a Pandas DataFrame i have some "cells" with values and some that need to contain excel formulas. I have read that i can get formulas with

link = 'HYPERLINK("#Groups!A' + str(someInt) + '"; "LINKTEXT")'
xlwt.Formula(link)

and store them in the dataframe.

When i try to save my dataframe as an xlsx file with

writer = pd.ExcelWriter("pandas" + str(fileCounter) + ".xlsx", engine = "xlsxwriter")
df.to_excel(writer, sheet_name = "Paths", index = False)
# insert more sheets here
writer.save()

i get the error:

TypeError: Unsupported type <class 'xlwt.ExcelFormula.Formula'> in write()

So i tried to write my formula as a string to my dataframe but Excel wants to restore the file content and then fills all formula cells with 0's.

Edit: I managed to get it work with regular strings but nevertheless would be interested in a solution for xlwt formulas.

So my question is: How do i save dataframes with formulas to xlsx files?

@Parfait i want to add hyperlinks to other cells to the df. – Samuel Blickle Jul 15 '18 at 14:31 — Samuel Blickle, Jul 15 '18 at 14:31
added formula content example. – Samuel Blickle Jul 15 '18 at 14:39 — Samuel Blickle, Jul 15 '18 at 14:39

score 8 · Answer 1 · answered Sep 23 '19 at 12:36

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:

Is there any side effects for this solution? Why is there a `write_formula()` What is the difference? — Mansur, Feb 07 '22 at 23:03
It is worth noting that for relatively new formulas the `_xlfn.` prefix should be added. I encountered this problem: https://stackoverflow.com/q/76334911/11485896. — Soren V. Raben, May 26 '23 at 11:41

score 3 · Accepted Answer · edited May 04 '21 at 08:43

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.

Elad Rubi · Answer 3 · 2022-02-08T14:36:03.227

you need to save in as usual just keep in mind to write the formula as string.
you can use also f strings with vars.

    writer = pd.ExcelWriter(FILE_PATH ,mode='a', if_sheet_exists='overlay')
    
    col_Q_index = 3
    best_formula = f'=max(L1,N98,Q{col_Q_index})'
    formula_df = pd.DataFrame([[best_formula]])
    formula_df.to_excel(writer, sheet_name=SHEET_NAME, startrow=i, startcol=17, index=False, header=False)
    
    writer.save()

Save Pandas DataFrames with formulas to xlsx files

3 Answers3

Linked