Python pandas模块中的Excel格式化模块xlsxwriter速览

最近在研究项目,卡在了pandas输出到Execl这一部分.输出是很简单的,to_excel就行了,但是需要格式化的输出,就需要研究xlsxwriter这个模块了.下面是我这几天的研究成果,希望能帮助你快速识别这到底是不是你需要的模块

我们先设定变量df

>>> import pandas
>>> df = pandas.DataFrame(data={'标题1':[1,2],'标题2':[3,4]})
>>> df2 = pandas.DataFrame(data={'标题3':[5,6],'标题4':[7,8]})

先说输出

pandas默认的输出到Excel的方法是to_excel.输出分两种大概的情景:

• 情景1:单个DataFrame输出到单个Excel

>>> filepath = r'D:\excel\test.xlsx'
>>> df.to_excel(filepath)

• 情景2:多个DataFrame输出到单个Excel

>>> filepath = r'D:\excel\test.xlsx'
>>> with pandas.ExcelWriter(filepath) as f:
        df.to_excel(f,sheet_name='sheet1')
        df2.to_excel(f,sheet_name='sheet2)

嗯,输出方式很多,但是,没有格式化!!不好看

xlsxwriter速览

这是pandas依赖的一个单独模块,如果需要格式化Excel的话,还是得单独安装这个模块,要不然会报错

pip install xlsxwriter

在输出的时候,pandas可以选择xlsxwriter格式化,所以我仔细研究了这个模块
这个模块大致的结构如下.它将format单独输出了一个类

xlsxwriter
  |-- workbook类
  |-- worksheet类
  |-- format类

workbook类

这个类主要处理workbook级别的内容,常用的如创建,新增,删除.比较特殊的是它可以关联format类

>>> import xlsxwriter
>>> workbook = xlsxwriter.workbook(filepath) # 创建Excel
>>> workbook.add_worksheet()	# 新增worksheet
>>> workbook.add_format()	# 关联format类 这将是后面使用最多的语句
>>> workbook.close()	# 关闭Excel

>>> import xlsxwriter
>>> writer = pandas.xlsxwriter(filepath)
>>> workbook = wirter.book	# 已有情况下调用

还有一些其他的,例如增加图表,计算模式,定义名称,设置属性等等,有需要的可以去查询自取,这些应该都是用得很少的功能

worksheet类

这个类别处理sheet层级的内容,文档中最多的就是worksheet.write('A1','文本'),但是这是我最不需要的功能,因为我是直接df.to_excel的.十分头疼,因为其他方式并不一定能实现效果

>>> worksheet.write(0,0,'文本')	# 在A1单元格写入两个字:文本
>>> worksheet.write('A1','文本')	# 效果一样,它支持单元格定位和数字定位两种方式

其他的还有专用的写入文本,数字,公式,url等等,不追求精确的话,实际用处不大

这个类可以单独设置格式

>>> worksheet = writer.sheets['sheet1']	# 对!你没有看错!它的绑定和workbook无关!
>>> worksheet.set_row(0,20)		# 设置第一行行高为20
>>> worksheet.set_column(0,20)	# 设置第一列列宽为20

更强大的地方还是结合workbook和format类设置格式,这一块在下面format里面说

format类

这是这个模块的核心类,因为读写内容pandas就可以搞定,但是设置格式,必须得单独来这个模块
这个模块的使用方法有两种

>>> format1 = workbook.add_format({'bold':True})	# 字典嵌套

>>> format2 = workbook.add_format()					# 先绑类,然后调用方法
>>> format2.set_bold(True)

在实际使用过程中发现一个变量只能套一个格式,很不方便.并且使用两种方法都无法实现效果,不知道是不是官方bug

>>> format = worksheet.add_format({'font_size':10})
>>> worksheet.set_column(0,8,None,format)	# 设置字体大小失败

>>> format = worksheet.add_format()
>>> format.set_font_size(10)
>>> worksheet.set_column(0,8,None,format)	# 设置字体大小失败again

其他的worksheet.add_table,format.set_align('center')都可以实现

worksheet.add_table

这个类在使用过程中非常蛋疼.它不能直接套用表头!!!它不能直接套用表头!!!它不能直接套用表头!!!
套用表头的时候,需要先取消df.to_excel的header,然后按照它的模板一个个写进去.真的是非常的画蛇添足.但是我尝试了其他的方法,研究了所有参数,也只能这样.F!

>>> writer = pandas.ExcelWriter(filepath)
>>> sheet = writer.sheets['sheet1']
>>> df.to_excel(writer,index=False,header=False)
>>> head = [{'header':col} for col in df.columns]
>>> sheet.add_table(0,0,df.shape[0]-1,df.shape[-1]-1,{'columns':head})

也可以套用好看的格式,录制一个VBA就知道了

其他的

可以插入图表,插入图片,调用VBA(劝退)等等,可以去官方文档研究

XlsxWriter Documentation