一、参考资料
[1].pandas.DataFrame.to_excel
[2].Working with Python Pandas and XlsxWriter
二、程序示例
1. pandas.DataFrame.to_excel最简单的写入
当写入的文件只占有一个sheet时,可以利用pandas.DataFrame.to_excel()直接写入。该函数的用法如下,关于参数的具体介绍,可以参考参考资料[1]的内容。
DataFrame.to_excel(excel_writer, sheet_name=‘Sheet1’, na_rep=’’, float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None, inf_rep=‘inf’, verbose=True, freeze_panes=None)
# 1. 基本模块的导入
import numpy as np
import pandas as pd
# 2. 创建写入数据
DF_1 = pd.DataFrame(np.random.rand(12).reshape(6,2),columns=list('AB'))
# 3. 将数据写入excel
DF_1.to_excel(r'C:\Users\Desktop\ExcelTest\DF_1.xlsx',sheet_name='DF_1',index=False)
print('DF_1 has been finished.')
2. 将多个数据框写入同一个Excel文件的不同Sheet中
# 1. 基本模块的导入
import numpy as np
import pandas as pd
# 2. 创建不同的写入数据
DF_1 = pd.DataFrame(np.random.rand(12).reshape(6,2),columns=list('AB'))
DF_2 = pd.DataFrame(np.random.rand(12).reshape(6,2),columns=list('CD'))+1
DF_3 = pd.DataFrame(np.random.rand(12).reshape(6,2),columns=list('EF'))+2
DF_4 = pd.DataFrame(np.random.rand(12).reshape(6,2),columns=list('GH'))+3
# 3. 数据写出
with pd.ExcelWriter(r'C:\Users\Desktop\ExcelTest\DF_All.xlsx') as writer:
DF_1.to_excel(writer,sheet_name='DF_1')
DF_2.to_excel(writer,sheet_name='DF_2')
DF_3.to_excel(writer,sheet_name='DF_3')
DF_4.to_excel(writer,sheet_name='DF_4')
writer.save()
writer.close()
print('Finished.')
3. 基于函数将多个数据框写入同一个Excel文件的不同Sheet中
# 1. 基本模块的导入
import os
import numpy as np
import pandas as pd
from openpyxl import load_workbook
# 2. 函数定义
def ExcelAddSheet(df,outfile,sheetname):
writer = pd.ExcelWriter(outfile,engine='openpyxl') # engine貌似是操作xlsx文件的引擎,此处用openpyxl
# 2.1 检查.xlsx文件是否存在,不存在则先创建
if os.path.exists(outfile) != True:
df.to_excel(writer,sheet_name=sheetname,index=False) # index=False,表示忽略行索引
else:
# book这句及后面的writer.book = book必须存在,否则sheet会被覆盖(为什么?)
book = load_workbook(writer.path)
writer.book = book
df.to_excel(writer,sheet_name=sheetname,index=False)
writer.save()
writer.close()
# 3. 测试函数
f = lambda x,y:x+y # 辅助列名的构建
for ii in range(1,5):
Columns = [f(i,'_'+str(ii)) for i in list('AB')]
DF = pd.DataFrame(np.random.rand(12).reshape(6,2),columns=Columns)
ExcelAddSheet(DF,r'C:\Users\Desktop\ExcelTest\DF_All_Test.xlsx','DF'+'_'+str(ii))
print('Finished.')