有没有办法只用 pandas 将公式写入 .xlsx 文件，即不使用 xlsxwriter/openpyxl 之类的工具? [英] Is there a way of writing a formula to the .xlsx file just with pandas, i.e. without using tools like xlsxwriter / openpyxl?

问题描述

我编写了一个脚本来读取

Excel 给出了一个公式警告(绿色三角形)，因为它错误地认为您也应该将 G 列添加到公式中.希望您可以忽略它，或者在数据框中切换 F 列和 G 列.

它可能也适用于作为引擎的 openpyxl.

I wrote a script that reads this .xlsx file, then creates a column that is the sum of the three other columns, and saves everything into a new file:

import pandas 

df = pandas.read_excel("excel-comp-data.xlsx")

df["total"] = df["Jan"] + df["Feb"] + df["Mar"]

df.to_excel("excel-comp-data-formula-by-pandas.xlsx")

The issue with this code is that it does not create a formula, it just adds everything up and places the result in the newly created column.

When I later access the newly created file in libreoffice calc and manually modify any data in "Jan", "Feb", or "March" the corresponding data in the column "total" does not get updated.

I've have found some code snippets on SO that create formulas, but all of them use tools as xlsxwriter. How might I create a formula in pandas without using such tools?

Is it at all doable?

解决方案

How might I create a formula in pandas without using such tools?

Pandas uses xlsxwriter (or openpyxl) anyway to create the xlsx file so you are already using them.

Anyway, you can add formulas, instead of a static sum, like this:

import pandas 

df = pandas.read_excel("excel-comp-data.xlsx")

df["total"] = [f'=SUM(H{row}:J{row})' for row in range(2, df.shape[0] + 2)]

df.to_excel("excel-comp-data-formula-by-pandas.xlsx", engine='xlsxwriter')

Output:

Excel gives a formula warning (green triangle) since it thinks, incorrectly, that you should be adding column G to the formula as well. Hopefully you can just ignore that, or switch columns F and G in the dataframe.

It probably also works with openpyxl as an engine as well.

这篇关于有没有办法只用 pandas 将公式写入 .xlsx 文件，即不使用 xlsxwriter/openpyxl 之类的工具?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！