通过和大佬们的讨论以及实践发现,网上很多Excel追加数据是没法做到的,按他们的复制粘贴后运行也会出错。报错有:zip file…l.; sheet xxx already exist…。可以插入并生成不同的sheet,就是无法在同一个sheet中添加数据,如今集众人之智慧制作出这个简单的好方法,方便大家使用。
import re
import pandas as pd
import numpy as np
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
df = pd.DataFrame(columns=['xuhao','id','name'],data=[('a','2','ss'),('b','2','33'),('c','4','bbb')])
df1 = pd.DataFrame(
{
"a": list("abc"),
"b": list(range(1, 4)),
"c": np.arange(3, 6).astype("u1")
}
)
path = r'test.xlsx'
df.to_excel(path,engine='openpyxl',index=False,sheet_name='aa')
wbook = load_workbook(path)
wsheet = wbook['aa']
for row in dataframe_to_rows(df1,header=False,index=False):
wsheet.append(row)
wbook.save(r'test.xlsx')
补充亲测有效的往一个Excel写入多个不同sheet
import pandas as pd
route = r'D:\aa.xlsx'
with pd.ExcelWriter(route) as writer:
df1.to_excel(writer, sheet_name='Sheet_name_1',index=False)
df2.to_excel(writer, sheet_name='Sheet_name_2',index=False)
参考:
https://zhuanlan.zhihu.com/p/113288852