Python

Pandas 追加写入 Excel 表格

DataFrame 写入 Excel 文件

下面两段代码用于直接生成 Excel 文件, 如果文件已经存在, 会被覆盖

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# sht2, sht3 两张表都会被写入
with pd.ExcelWriter(path=r'path_to_file.xlsx') as writer:
    df.to_excel(ew, sheet_name='sht2', index=False)
    df.to_excel(ew, sheet_name='sht3', index=False)

# 第二次会覆盖掉第一次的
# 只剩下 sht3
with pd.ExcelWriter(path=r'path_to_file.xlsx') as writer:
    df.to_excel(ew, sheet_name='sht2', index=False)
with pd.ExcelWriter(path=r'path_to_file.xlsx') as writer:
    df.to_excel(ew, sheet_name='sht3', index=False)

DataFrame 追加写入 Excel 文件

如果要往一份已经存在的Excel文件中追加内容, 如往含有标题的模板中填写数据或者在已有数据末尾追加数据

pandas 1.4.0之前的版本

在 pandas 1.4.0 之前, 可以使用如下方法将 DataFrame 追加写入 Excel 文件

首先需要指定 engine 为 openpyxl(pandas 写Excel文件默认使用的库是xlsxwriter, 不支持追加写入)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

from openpyxl import load_workbook

wb = load_workbook(file_path)

with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
    # 指定 workbook 对象
    writer.book = wb
    writer.sheets = dict((ws.title, ws) for ws in wb.worksheets)
    # 或者可以用表达式的写法
    writer.sheets = {ws.title: ws for ws in wb.worksheets}
    df.to_excel(writer, sheet_name=sht_name, index=False, header=False, startrow=len(book)+1, )

pandas 1.4.0及之后的版本

在 pandas 1.4.0 之后, to_excel() 函数的 if_sheet_exists 新增了overlay属性

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

'''
if_sheet_exists : {{'error', 'new', 'replace', 'overlay'}}, default 'error'
How to behave when trying to write to a sheet that already exists (append mode only).
* error: raise a ValueError.
* new: Create a new sheet, with a name determined by the engine.
* replace: Delete the contents of the sheet before writing to it.
* overlay: Write contents to the existing sheet without removing the old contents.
versionadded:: 1.3.0
versionchanged:: 1.4.0
Added `overlay` option

You can also write multiple DataFrames to a single sheet. Note that the
``if_sheet_exists`` parameter needs to be set to ``overlay``:

with ExcelWriter("path_to_file.xlsx",
    mode="a",
    engine="openpyxl",
    if_sheet_exists="overlay",
) as writer:
    df1.to_excel(writer, sheet_name="Sheet1")
    df2.to_excel(writer, sheet_name="Sheet1", startcol=3)
'''

对于pandas 1.4.0 及以上的版本, 可以使用下面的方法来追加写入

首先, 依然是设置engine为 openpyxl

然后, 设置 if_sheet_exists="overlay" 的同时, 还需要设置 mode="a" 表示追加写入

1
2
3
4
5
6
7

with ExcelWriter("path_to_file.xlsx",
    mode="a",
    engine="openpyxl",
    if_sheet_exists="overlay",
) as writer:
    df1.to_excel(writer, sheet_name="Sheet1")
    df2.to_excel(writer, sheet_name="Sheet1", startcol=3)

Append existing excel sheet with new dataframe using python pandas - Stack Overflow

python - append dataframe to excel with pandas - Stack Overflow