将带有公式的Pandas DataFrame保存到xlsx文件 [英] Save Pandas DataFrames with formulas to xlsx files
问题描述
在Pandas DataFrame中,我有一些带有值的单元格",而有些则需要包含excel公式.我已经读到我可以使用公式
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.
当我尝试将数据框另存为xlsx文件时
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()
我得到了错误:
TypeError: Unsupported type <class 'xlwt.ExcelFormula.Formula'> in write()
所以我试图将公式作为字符串写到数据框中,但是Excel希望还原文件内容,然后用0
填充所有公式单元格.
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.
我设法使它可以与常规字符串一起使用,但仍然对xlwt公式的解决方案感兴趣.
I managed to get it work with regular strings but nevertheless would be interested in a solution for xlwt formulas.
所以我的问题是:如何将带有公式的数据框保存到xlsx文件?
So my question is: How do i save dataframes with formulas to xlsx files?
推荐答案
在使用table.to_excel(writer, sheet_name=...)
编写df之后,如本例所示,我使用write_formula()(已编辑以添加完整循环).要在数据框中写入所有公式,请阅读数据框中的每个公式.
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(...)
# 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)
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()
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()
文档位于此处.
这篇关于将带有公式的Pandas DataFrame保存到xlsx文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!