上下文
我从db中查询了一些数据为pd.dataframe
,并在xlsx.writer引擎中将其与pd.ExcelWriter
一起保存到格式化的xlsx模板中.
然后,保存的数据在另一个工作表中被动态公式和其他公式引用.
问题
In the excel, there are two sheets : "Raw Data" and "Format Data":
"Raw Data" Stores the fetched data, "Format Data" contains the formula and reference cells from "Raw Data"
After the ExcelWriter processed the excel file, the dynamic formula in "Processed Data" changed to array formula, which denied the formula to expand. For example, there is a formula:
=FILTER(UNIQUE('Raw Data'!$I$4:$I$2000),UNIQUE('Raw Data'!$I$4:$I$2000)<>0)
which should return all unique data from I4:I2000.
然而,Excel在ExcelWriter过程中将动态公式更改为I4:I400范围内的数组公式,因为原始模板只有396个唯一值.即使更新后的数据源具有超过396个唯一值,它也只能显示396个项目.
如果有人能好心地建议这个问题的原因,并提供一个解决方案来停止动态公式的变化,那就太好了.
Remarks: I am trying not to do the data processing with python so please don't comment on that.
代码嗅探FYR
writer = pd.ExcelWriter(excel_path, engine='openpyxl', mode='a', if_sheet_exists="overlay",)
for config in excelUpdateConfigs:
result = fetchSQL(db_conn, config["sql"])
result = result.astype(config["dtype"])
result.to_excel(writer, sheet_name="Raw Data", float_format="%.5f", startrow=2, startcol=config["startcol"], header=True, index=False, engine="io.excel.xlsx.writer")
writer.close()
我已经try
I have tried to do some research online but I can't see any related articles about dynamic formula right now.
I doubt that the low level logic of ExcelWriter is creating a new excel file based on the given path instead of editing on top of it so it breaks some of the equations and inserted items. Please advise if I'm wrong.