DataFrame操作excel文件及表格样式调整

一、读取和保存文件

1.读取excel文件

pandas.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, squeeze=None, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, parse_dates=False, date_parser=None, thousands=None, decimal='.', comment=None, skipfooter=0, convert_float=None, mangle_dupe_cols=True, storage_options=None)

read_excel()支持的文件格式包括：xls、xlsx、xlsm、xlsb、odf、ods、odt。支持从本地文件系统或者URL读取文件。支持读取单个表格或者指定的一系列表格。

常用参数如下：

io：指定本地文件路径/URL以及文件名称，注意文件名要有正确的后缀。URL支持http、ftp、s3和文件。

sheet_name：不指定时默认为0，即读取文件中的第一个表格；使用list指定要读取的表格，如[0,1,”Sheet3”]表示读取第一个、第二个和名为“Sheet3”的表格；指定为None时表示读取文件中所有表格。

engine：指定操作文件的引擎，默认为None，根据io参数类型确定（io类型为odf/ods/odt时，使用odf；io类型为xls时，使用xlrd；类型为xlsb时，使用pyslsb；io类型为xlsx、xlsm时使用openpyxl）。

常用示例如下：

#读取指定文件中的表

df = pd.read_excel('600036.xlsx', sheet_name='600036')

#读取指定文件中的多个表：第一个、第二个和'600036'

df = pd.read_excel('600036.xlsx', sheet_name=[0,1,'600036'])

2.保存数据到excel文件

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=_NoDefault.no_default, inf_rep='inf', verbose=_NoDefault.no_default, freeze_panes=None, storage_options=None)

excel_writer：文件路径和文件名称。

sheet_name：工作表名称，默认为“sheet1”。

na_rep：表示缺失数据，默认为空。

columns：可选，需要写的列。

header：默认为True，保存的列名。可以用list来指定列名。

index：默认为True，保存索引。

index_label：索引的标签。

startrow：默认为0，起始行

startcol：默认0，起始列

engine：可选，操作excel的引擎，‘openpyxl’或‘xlsxwriter’。

常用示例如下：

#将df数据保存到文件“600036.xlsx”中的“600036”

df.to_excel("600036.xlsx", sheet_name='600036', index=False)

二、多个表保存到同一文件

多个表保存到同一个文件可以直接采用ExcelWriter()来实现。

pandas.ExcelWriter(path, engine=None, date_format=None, datetime_format=None, mode='w', storage_options=None, if_sheet_exists=None, engine_kwargs=None, **kwargs)

path：文件路径和文件名。

engine：可选，不指定时默认为与path参数类型有关（对于xls文件，使用xlwt；对于xlsx文件使用xlsxwriter或者openpyxl，优先使用xlsxwriter；对以ods文件使用odswriter）。

1.保存到不同表中

在使用to_excel()时指定数据对应的表名。例如df1保存到sheet1中，df2保存到sheet2中，代码如下：

excelwr= pd.ExcelWriter("example.xlsx") #指定文件名称
df1.to_excel(excelwr,sheet_name="df1")#df1的数据写到表df1
df2.to_excel(excelwr,sheet_name="df2") #df2的数据写到表df2
df3.to_excel(excelwr,sheet_name="df3") #df3的数据写到表df3
excelwr.save() #保存文件

注意：只指定表名的方式写入，新的数据会将文件中原有数据覆盖。

2.保存到同一个表中

将多个数据保存到同一个表中，可以通过指定每个待保存数据在表中的行和列来实现，注意要算好行列避免不同的表数据重叠。

excelwr= pd.ExcelWriter("example.xlsx") #指定文件名称
df1.to_excel(excelwr,sheet_name="sheet1")#默认从第0行第0列的位置开始写入
df2.to_excel(excelwr,sheet_name="sheet2",startrow = df2.shape[0]+1) #df2的数据写在df1数据的下边 
df3.to_excel(excelwr,sheet_name="sheet3",startcol = df2.shape[1]+1) #df3的数据写在df1数据的右边
excelwr.close() #保存文件

三、表格式设置

使用ExcelWriter()可以指定保存的表格的各种格式。格式设置的框架如下：

excelwr= pd.ExcelWriter(‘example.xlsx’)
df.to_excel(excelwr, 'Sheet1')  #保存到指定表格
#格式定义代码在此处加入
#可以利用xlswriter、openxyl等设置表格的格式
excelwr.close()

1.列宽调整

采用excelwriter()可以对列宽进行简单的调整。

excelwr= pd.ExcelWriter('example2.xlsx')
df.to_excel(excelwr, sheet_name='Sheet1',index= False)
worksheet = excelwr.sheets['Sheet1']
worksheet.set_column(0,1, 20)  #指定第1-2列为320像素宽度
worksheet.set_column(2,5, 5) #指定第3-6列为5像素宽度
excelwr.close()

对比如下图所示

2.表格格式设置

下述代码将表Sheet1的第一和第二列以及第二行设为黄底红字，加粗，左右居中，上下居中。

excelwr = pd.ExcelWriter('example2.xlsx')
df.to_excel(excelwr, sheet_name='Sheet1',index= False)
worksheet = excelwr.sheets['Sheet1']
#设置背景颜色
wb = excelwr.book
format = wb.add_format({'bg_color': 'yellow', 'font_color': 'red', 'bold': True, 'align': 'center', 'valign': 'vcenter'})
worksheet.set_column(0,1, 16, cell_format=format)
excelwr.close()

效果如下图：

常见的列格式参数为：

'bold':  True  # 字体加粗

'border': 1  # 单元格边框宽度

'align': 'left'  # 水平对齐方式，left、center、right

'valign': 'vcenter'  # 垂直对齐方式，top、vcenter、bottom

'bg_color': 'yellow'  # 单元格背景颜色

'font_color': ‘red’  # 字体颜色

'text_wrap': True,  # 是否自动换行

DataFrame的其它基本操作可参考《DataFrame常用操作实例》，更详细的文件操作可参考官方文档。

-----------------------------------

原创不易，请多支持！