将带有公式的Pandas数据框保存到xlsx文件中。

Question

在Pandas DataFrame中，我有一些包含值的“单元格”，还有一些需要包含Excel公式的“单元格”。我已经阅读到可以使用以下方法获取公式：

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

并将它们存储在数据框中。

当我尝试使用以下代码将数据框保存为xlsx文件时：

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()

我尝试将公式作为字符串写入我的数据帧，但Excel想要恢复文件内容，然后用0填充所有公式单元格。

编辑：我成功地使用常规字符串使其正常工作，但仍然对xlwt公式的解决方案感兴趣。

因此，我的问题是：如何将带有公式的数据框保存到xlsx文件中？

- Samuel Blickle

---

@Parfait 我想在数据框中添加到其他单元格的超链接。 - Samuel Blickle

添加了公式内容示例。 - Samuel Blickle

Answer 1

由于您正在使用xlsxwriter，字符串默认会被解析为公式("strings_to_formulas：启用worksheet.write()方法将字符串转换为公式。默认值为True")，因此您可以在数据帧中简单地将公式指定为字符串。

下面是一个公式列的示例，其引用了数据帧中的其他列：

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()

生成如下输出：

- Motin

---

这种解决方案有没有任何副作用？为什么要有 write_formula()？有什么区别？ - Mansur

值得注意的是，对于相对较新的公式，应添加“_xlfn.”前缀。我遇到了这个问题：https://dev59.com/71IssIgBPY-HTNNjEHza。 - Soren V. Raben

Answer 2

在使用table.to_excel(writer, sheet_name=...)写入数据后，我会像这个例子一样使用write_formula()。为了将数据框中的所有公式都写入，需要读取数据框中的每个公式。

 # 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)`

在代码的后面（我记得其中一个可能是多余的，但我没有查过）：

writer.save() workbook.close()

文档在这里。

- David Gaertner

Answer 3

• 你需要像往常一样保存，只需记住将公式写成字符串。
• 你也可以使用带有变量的 f 字符串。

    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()

- Elad Rubi