pandas 读写 Excel,可以用于将重复的数据加工工作交给 pandas,节省手工劳动,使用起来也比较方便,但输出的格式并不太美观。本文介绍 read_excel()
和 to_excel()
的部分细节,同时探讨如何输出一个较为美观的 Excel 工作表。
pandas 读取 Excel 文件
语法
DataFrame.read_excel()
的语法:
pandas.read_excel(io,
sheet_name=0,
header=0,
names=None,
index_col=None,
parse_cols=None,
usecols=None,
squeeze=False,
dtype=None,
engine=None,
converters=None,
true_values=None,
false_values=None,
skiprows=None,
nrows=None,
na_values=None,
keep_default_na=True,
verbose=False,
parse_dates=False,
date_parser=None,
thousands=None,
comment=None,
skip_footer=0,
skipfooter=0,
convert_float=True,
mangle_dupe_cols=True,
**kwds)
参数和返回值的说明请参考 pandas 文档 。
最简单的用法,只需要指定文件名参数,支持 xls 文和 xlsx 文件格式,函数的返回值为 DataFrame 类型的对象。比如读取 D 盘根目录下的 source.xlsx 文件:
import pandas as pd
df1 = pd.read_excel(r'D:/source.xlsx)
如果想读取 py 文件所在目录下的某个 Excel 文件,可以参考下面的代码:
import pandas as pd
import os
# get path of current directory
curr_path = os.path.dirname(os.path.abspath(__file__))
fname = os.path.join(curr_path, 'users.xlsx')
df2 = pd.read_excel(fname)
指定要读取的工作表
对于有多个工作表的 Excel 文件,pandas 默认读取第一个工作表( sheet_name=0
)。通过如下两种方法可以指定要读取的工作表:
# 方法一:通过 index 指定工作表
df3 = pd.read_excel(file_name, sheet_name=0)
# 方法二:指定工作表名称
df4 = pd.read_excel(file_name, sheet_name='Sheet1')
导入指定列
如果只想导入指定的列,通过 usecols
参数,比如想导入 A:D
和 H
这 4 列,有如下两种方法:
df6 = pd.read_excel(r'D:/source.xlsx', usecols='A:D,H')
# 或者
df6 = pd.read_excel(r'D:/source.xlsx', usecols=[0,1,2,3,7])
指定表头
默认情况下,pandas 假定第一行为表头 (header),如果 Excel 不是从第一行开始,header
参数用于指定将哪一行作为表头,表头在 DataFrame 中变成列索引 (column index) ,header 参数从 0 开始,比如第二行作为 header,则:
df = pd.read_excel(file_name, header=1)
pandas 写入 Excel
语法
DataFrame.to_excel()
的语法:
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)
参数和返回值的说明请参考 pandas 文档 。
数据写入 Excel,需要首先安装一个 engine,由 engine 负责将数据写入 Excel,pandas 使用 openpyx 或 xlsxwriter 作为写入引擎。
要将单一对象写入 Excel,只需要指定 file name 即可:
import pandas as pd
import os
path = os.path.dirname(os.path.abspath(__file__))
source_file = os.path.join(path, 'source.xlsx')
output_file = os.path.join(path, 'output.xlsx')
df = pd.read_excel(source_file, sheet_name=0)
df.to_excel(output_file)
如果 output.xlsx 文件已经存在,to_excel()
先删除 output.xlsx 文件,然后重新生成一个新的文件,并且默认添加一个索引列,索引为从 0 到 n 的整数。
不使用索引
导出 Excel,一般不需要索引,将 index
参数设为 False 即可:
df.to_excel(output_file, index=False)
多工作表导出
导出多个工作表需要明确给出 excel writer engine,然后调用 DataFrame.to_excel()
方法:
import pandas as pd
import os
path = os.path.dirname(os.path.abspath(__file__))
source_file = os.path.join(path, 'source.xlsx')
output_file = os.path.join(path, 'output.xlsx')
df1 = pd.read_excel(source_file, sheet_name=0)
df2 = pd.read_excel(source_file, sheet_name=0, usecols='A:D,H')
with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)
工作表美化
pandas 导出的工作表并不美观,如果想对工作表进行美化的话,可在 to_excel()
方法之后,通过Excel writer engine 的格式设置的功能来设置格式。根据测试,to_excel()
因为先删除文件,所以也不能使用 Template 来保存预定义格式。所以如果需要导出有格式的 Excel 文件,比如作为报表输出,可考虑 Template + Excel writer engine 手工代码的方式。
Creating Advanced Excel Workbooks with Python 这篇文章讲到了一个方法,使用 xlsxwriter 的 add_table()
方法,在 Excel 中创建一个 Table 对象(中文经常被称为智能表格),然后选择一个预定义的格式。我对代码进行了加工,使之更具普适性:
import pandas as pd
import os
def get_col_widths(dataframe):
return [max([len(str(s)) for s in dataframe[col].values]
+ [len(col)]) for col in dataframe.columns]
def fmt_excel(writer, sheetname, dataframe):
# Get the workbook and the summary sheet so we can add the formatting
workbook = writer.book
worksheet = writer.sheets[sheetname]
col_count = dataframe.shape[1]
row_count = dataframe.shape[0]
col_names = []
for i in range(0, col_count):
col_names.append({'header': dataframe.columns[i]})
# rng = 'A1:H{}'.format(row_count + 1)
worksheet.add_table(0, 0, row_count,col_count-1, {
'columns': col_names,
'style': 'Table Style Medium 20'
})
# auto column size
col_widths = get_col_widths(dataframe)
for i, width in enumerate(col_widths):
worksheet.set_column(i, i, width)
path = os.path.dirname(os.path.abspath(__file__))
source_file = os.path.join(path, 'source.xlsx')
output_file = os.path.join(path, 'output.xlsx')
df = pd.read_excel(source_file, sheet_name=0)
writer = pd.ExcelWriter(output_file, engine='xlsxwriter')
df.to_excel(writer, 'Sheet1', index=False)
fmt_excel(writer, 'Sheet1', df)
writer.save()