Pandas 写入 Excel 的几种情形与方式,覆盖,新增,追加,对齐
# 以下代码默认已经导入 np,pd
import numpy as np
import pandas as pd
# 执行下面示例之前,最好先删除 ./test.xlsx 文件
覆盖所有原有数据,只保留最后一份数据:
# pandas:1.4.1 openpyxl:3.0.9
# 删除文件原有数据,只保留 s2 一份数据(最后一份)
s1 = pd.DataFrame(np.array([['s1', 's1', 's1', 's1']]), columns=['a', 'b', 'c', 'd'])
s2 = pd.DataFrame(np.array([['s2', 's2', 's2', 's2']]), columns=['a', 'b', 'c', 'd'])
s1.to_excel('test.xlsx', sheet_name="111", index=False)
s2.to_excel('test.xlsx', sheet_name="222", index=False) # 只保留此份数据
覆盖所有原有数据,保留当前写入的多份数据:
# pandas:1.4.1 openpyxl:3.0.9
# 删除文件原有数据,同时保留s1 和s2 两份数据
s1 = pd.DataFrame(np.array([['s1', 's1', 's1', 's1']]), columns=['a', 'b', 'c', 'd'])
s2 = pd.DataFrame(np.array([['s2', 's2', 's2', 's2']]), columns=['a', 'b', 'c', 'd'])
with pd.ExcelWriter("test.xlsx") as writer:
# 保留两份数据
s1.to_excel(writer, sheet_name="111", index=False)
s2.to_excel(writer, sheet_name="222", index=False)
保留原有数据,新开一个sheet 写入数据
# pandas:1.4.1 openpyxl:3.0.9
# 保留原有数据 s1,新开一个sheet 写入数据 s2
from openpyxl import load_workbook
s1 = pd.DataFrame(np.array([['s1', 's1', 's1', 's1']]), columns=['a', 'b', 'c', 'd'])
s2 = pd.DataFrame(np.array([['s2', 's2', 's2', 's2']]), columns=['a', 'b', 'c', 'd'])
# 先写入 s1 的数据(会新建excel文件)
s1.to_excel('test.xlsx', sheet_name='111', index=False)
book = load_workbook("test.xlsx") # 该文件必须存在,并且该语句必须在 with pd.ExcelWriter() 之前
with pd.ExcelWriter("test.xlsx") as writer:
writer.book = book
s2.to_excel(writer, sheet_name="222", index=False)
# 新增一个sheet 并写入,如果这里这里指定的sheet已经存在,那么会在该名称后追加1,2,3,... 创建一个新的sheet写入,不会在原有sheet上修改
重写指定sheet数据,保留原有的其余sheet数据
# pandas:1.4.1 openpyxl:3.0.9
# 重写指定sheet数据,保留原有的其余sheet数据
from openpyxl import load_workbook
s1 = pd.DataFrame(np.array([['s1', 's1', 's1', 's1']]), columns=['a', 'b', 'c', 'd'])
s2 = pd.DataFrame(np.array([['s2', 's2', 's2', 's2']]), columns=['a', 'b', 'c', 'd'])
s3 = pd.DataFrame(np.array([['s3', 's3', 's3', 's3']]), columns=['a', 'b', 'c', 'd'])
with pd.ExcelWriter("test.xlsx") as writer:
# 先写入两个sheet
s1.to_excel(writer, sheet_name="111", index=False)
s2.to_excel(writer, sheet_name="222", index=False)
book = load_workbook("test.xlsx")
with pd.ExcelWriter("test.xlsx") as writer:
writer.book = book
writer.sheets = {i.title: i for i in book.worksheets} # 指定sheet
s3.to_excel(writer, sheet_name="111", index=False)
修改指定sheet内的部分数据,其余保持不变
# pandas:1.4.1 openpyxl:3.0.9
# 修改指定sheet内的部分数据,其余保持不变
from openpyxl import load_workbook
s1 = pd.DataFrame(np.array([['s1', 's1', 's1', 's1']]), columns=['a', 'b', 'c', 'd'])
s2 = pd.DataFrame(np.array([['s2', 's2', 's2', 's2']]), columns=['a', 'b', 'c', 'd'])
with pd.ExcelWriter("test.xlsx") as writer:
# 先写入两个sheet
s1.to_excel(writer, sheet_name="111", index=False)
s2.to_excel(writer, sheet_name="222", index=False)
book = load_workbook("test.xlsx")
with pd.ExcelWriter("test.xlsx") as writer:
writer.book = book
sheet = book['222'] # 通过sheet名称 获取 sheet
sheet.cell(2, 1, 'hello') # 修改第二行第一列的值
sheet['b2'] = '你好' # 修改 b2 单元格的值
向 sheet 中追加数据
1.在Excel 中追加
# pandas:1.4.1 openpyxl:3.0.9
# 向sheet中追加数据(一),在Excel 中追加
from openpyxl import load_workbook
s1 = pd.DataFrame(np.array([['s1', 's1', 's1', 's1']]), columns=['a', 'b', 'c', 'd'])
s2 = pd.DataFrame(np.array([['s2', 's2', 's2', 's2']]), columns=['a', 'b', 'c', 'd'])
# s4 只有3列,并且列顺序被打乱,以模拟新数据与元数据的差异
s4 = pd.DataFrame(np.array([['s4b', 's4d', 's4c']]), columns=['b', 'd', 'c'])
with pd.ExcelWriter("test.xlsx") as writer:
# 先写入两个sheet
s1.to_excel(writer, sheet_name="111", index=False)
s2.to_excel(writer, sheet_name="222", index=False)
df = pd.read_excel('test.xlsx', sheet_name='111')
row = df.shape[0] # 获取原数据的行数
# 将 新数据 格式化成原数据的模样,以解决数据列之间的差异
s4 = pd.concat([pd.DataFrame(columns=df.columns), s4], ignore_index=True)
book = load_workbook("test.xlsx")
with pd.ExcelWriter("test.xlsx") as writer:
writer.book = book
writer.sheets = {sheet.title: sheet for sheet in book.worksheets}
# 追加新数据,追加前必须先格式化新数据,否则新数据缺少列,或是列顺序不对会导致数据紊乱
s4.to_excel(writer, sheet_name='111', startrow=row + 1, index=False, header=False)
向sheet中追加数据
2.在pandas中追加数据后,重写指定sheet
# pandas:1.4.1 openpyxl:3.0.9
# 向sheet中追加数据(二),在pandas中追加数据后,重写指定sheet
from openpyxl import load_workbook
s1 = pd.DataFrame(np.array([['s1', 's1', 's1', 's1']]), columns=['a', 'b', 'c', 'd'])
s2 = pd.DataFrame(np.array([['s2', 's2', 's2', 's2']]), columns=['a', 'b', 'c', 'd'])
# s4 只有3列,并且列顺序被打乱,以模拟新数据与元数据的差异
s4 = pd.DataFrame(np.array([['s4b', 's4d', 's4c']]), columns=['b', 'd', 'c'])
with pd.ExcelWriter("test.xlsx") as writer:
s1.to_excel(writer, sheet_name="111", index=False)
s2.to_excel(writer, sheet_name="222", index=False)
df = pd.read_excel('test.xlsx', sheet_name='111')
df = pd.concat([df, s4], ignore_index=True) # 合并数据
book = load_workbook("test.xlsx")
with pd.ExcelWriter("test.xlsx") as writer:
writer.book = book
writer.sheets = {sheet.title: sheet for sheet in book.worksheets}
df.to_excel(writer, sheet_name='111', index=False) # 重写sheet
