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

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

如果我们想直接通过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-1for j, v inenumerate(df.columns,1):
    sheet.cell(start_row+1, start_col+j).value = v
for i, row inenumerate(df.values,2):for j, v inenumerate(row,1):
        sheet.cell(start_row+i, start_col+j).value = v
book.save("test.xlsx")

结果如下，成功向G8单元格写入数据：

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以下版本的情况）：

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

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

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

上述我实现的功能在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()

但是目前阿里云还没有同步1.4.X版本的pip源，我目前还只能通过阿里云升级到1.3.5的版本。为了提前尝鲜，我修改了如下源码：

首先在

pandas\io\excel\_base.py

新增有效选项：

然后在

pandas\io\excel\_openpyxl.py

中新增overlay的处理逻辑：

这样我就提前享受到了这个效果，以后pandas升级到1.4.0以上版本后，代码依然兼容。