Pandas向本地Excel已存在的工作表追加写入DataFrame

示例:本地存在一个Excel文件如下,下面我们希望将一个DataFrame写入到已存在数据的工作表中,并保留原始数据。

image-20220712111139950

如果我们想直接通过pandas的api实现几乎是不可能的,因为官方文档to_excel方法明确说了:

Once a workbook has been saved it is not possible write further data without rewriting the whole workbook.

但我个人在研究了pandas的Excel写出源码后,有了很简单的直接使用pandas向工作表追加数据的方法,后续再演示。

测试数据:

import pandas as pd

df = pd.DataFrame([[1, 2, 3],
                   [4, 5, 6],
                   [7, 8, 9]],
                  columns=['A', 'B', 'C'])

openpyxl向工作表追加Pandas数据

下面我们首先看看,直接用openpyxl如何实现,完整代码如下:

from openpyxl import load_workbook

book = load_workbook("first.xlsx")
sheet = book.active

address = "G8"
start_row, start_col = sheet[address].row-1, sheet[address].column-1
for j, v in enumerate(df.columns, 1):
    sheet.cell(start_row+1, start_col+j).value = v
for i, row in enumerate(df.values, 2):
    for j, v in enumerate(row, 1):
        sheet.cell(start_row+i, start_col+j).value = v
book.save("test.xlsx")

结果如下,成功向G8单元格写入数据:

image-20220712112007714

xlwings向工作表追加Pandas数据

如果我们windows系统本地有安装office或目前版本的wps,就可以通过xlwings向工作表追加Pandas数据,完整代码如下:

import xlwings as xw

app = xw.App(add_book=False)
wb = app.books.open("first.xlsx")
sht = wb.sheets.active
sht.range('G8').options(index=False).value = df
wb.save("test.xlsx")
app.quit()

实现了与上述openpyxl代码一样的效果,save方法与openpyxl也一样,不传参数则覆盖原始文件。

options参数可以设置DataFrame的写出形式,与to_excel的参数几乎一致。

Pandas直接向工作表追加数据

最终完整代码如下(在pandas1.2.4版本测试成功):

writer = pd.ExcelWriter("first.xlsx", engine='openpyxl', mode='a')
writer.sheets.update({sht.title: sht for sht in writer.book.worksheets})
df.to_excel(writer, sheet_name=writer.book.active.title,
            index=False, startrow=7, startcol=6)
writer.close()

上述代码会向Excel表中的激活的工作表追加参数,sheet_name参数也可以指定向哪个工作表追加写对应的字符串。

在1.4.0以上版本使用如下代码即可:

writer = pd.ExcelWriter("first.xlsx", engine='openpyxl',
                        mode='a', if_sheet_exists="overlay")
df.to_excel(writer, sheet_name=writer.book.active.title,
            index=False, startrow=7, startcol=6)
writer.close()

默认情况下pandas无法向Excel工作表追加数据的根本原因在于没有任何读取原本工作表的动作,根据源码可以看到永远都会新建工作表(Pandas 1.2.x以下版本的情况):

image-20220712113822927

这样我们只需要让sheets获取到已存在的工作表信息即可。

但是我在1.3.5版本中运行以上代码却报出如下错误:

image-20220712132719861

这是因为从1.3.0版本开始,pd.ExcelWriter增加了if_sheet_exists选项:

image-20220712132758890

上述我实现的功能在1.4.0以上版本已经实现,使用如下代码即可:

writer = pd.ExcelWriter("first.xlsx", engine='openpyxl',
                        mode='a', if_sheet_exists="overlay")
df.to_excel(writer, sheet_name=writer.book.active.title,
            index=False, startrow=7, startcol=6)
writer.close()

但是pandas对于python3.7最高仅支持到1.3.5的版本,1.4.X版本必须是python3.8才能使用。我为了在python3.7使用这个功能,修改了pandas的如下源码:

首先在pandas\io\excel\_base.py新增有效选项:

image-20220712134455227

然后在pandas\io\excel\_openpyxl.py中新增overlay的处理逻辑:

image-20220712134707493

以后我的环境的pandas可以升级到1.4.0以上版本后,代码依然兼容。

来源:小小明-代码实体

物联沃分享整理
物联沃-IOTWORD物联网 » Pandas向本地Excel已存在的工作表追加写入DataFrame

发表评论