一、参考资料
[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.'
)
