实例演示如何将pandas的DataFrame数据输出为带格式的excel文件(.xls) |
您所在的位置：网站首页 › dataframe导入Excel输出全部数据 › 实例演示如何将pandas的DataFrame数据输出为带格式的excel文件(.xls) |
实例演示如何将pandas的DataFrame数据输出为带格式的excel文件(.xls)
|
实例演示如何将pandas的DataFrame数据输出为带格式的excel文件数据（.xls）
背景数据与代码获取数据表与成果展示Pandas的DataFrame表（Notebook打印）xlwt输出的Excel表
代码展示0 库引用1 辅助程序1.1 辅助程序-单元格格式定义定义方法一定义方法二
1.2 辅助程序-数据正确录入工具
2 表内容录入2.0 单元格格式准备2.1 常用数据设置2.2 表头录入2.3 数据自动录入2.4 行高列宽设置
3 表保存
总结
背景
系统为win10，使用的是Anaconda3下的python3.7.4。最近在制作和整理各式各样的数据报表，针对不同需求一个程序从数据读取、清洗、筛选、计算、整合和输出的自动脚本浩浩荡荡写了有5000行(ps: notebook涉及到千行代码是真的卡，还好有nbextensions的Codefolding )，在此留下一些心得和代码方便大家使用。使用Pandas库的DataFrame制作、修改及计算数据表是真的十分方便，但是弊端是to_csv文件和to_excel输出的表格格式有限，无法满足使用需求，因此需要将DataFrame使用xlwt输出为带格式的excel文件。此演示同样适用于非pandas的各类数据使用xlwt写入excel，欢迎大家研究和反馈。
数据与代码获取
作者将数据和代码都上传到了GitHub,球球大家走过路过github的时候star一下(.=^・ェ・^=) 数据表与成果展示 Pandas的DataFrame表（Notebook打印）
可以根据需求对单个单元格进行定义，这个步骤十分重要且必要，因为不同的边框设置会有各种需求，所以写了这份辅助程序方便使用和定义，不然后期对字体或是大小的修改都会十分麻烦! 编写的初期作者使用的是方法一，但是后期因新的单元格设计需要而写了方法二，使用后发现方法二真的太方便了， 个人比较推荐大家使用方法二。 定义方法一 选择主边框，设定主边框和副边框粗细值，设置字体大小 def _get_style(borders_major='tblr',width_major=1,width_minor=1,font_size=10): ''' borders_major：选择主要边框 默认为4边全选 边框粗细为 width_major width_major：主要边框的粗细 默认为1 wdith_minor：次要边框的粗细 默认为1 font_size：字体大小 默认为10 ''' style = xlwt.XFStyle() # Create Style font = xlwt.Font() # Create Font borders = xlwt.Borders() # Create Borders alignment = xlwt.Alignment() # Create Alignment font.name = '宋体' # 设置字体为 宋体 font.height = font_size*20 # 设置字体大小为 10（10*20） alignment.horz = xlwt.Alignment.HORZ_CENTER # 可以选择: HORZ_GENERAL,HORZ_LEFT,HORZ_CENTER,HORZ_RIGHT,HORZ_FILLED, # HORZ_JUSTIFIED,HORZ_CENTER_ACROSS_SEL,HORZ_DISTRIBUTED alignment.vert = xlwt.Alignment.VERT_CENTER # 可以选择: VERT_TOP,VERT_CENTER,VERT_BOTTOM,VERT_JUSTIFIED,VERT_DISTRIBUTED alignment.wrap = 1 # 自动换行 # 设置边框宽度 borders.left = width_major if 'l' in borders_major else width_minor borders.right = width_major if 'r' in borders_major else width_minor borders.top = width_major if 't' in borders_major else width_minor borders.bottom = width_major if 'b' in borders_major else width_minor # 向style输入格式 style.font = font style.alignment = alignment style.borders = borders return style 定义方法二 设置每个边框粗细，设置字体大小在实例中未使用相较于方法一，这个方法就更简单粗暴一些，没有选择，直接给边框数值，方法一每个单元格只能提供两种边框粗细，但是方法二没有那么多嘻嘻哈哈， 直接设置单个边框粗细， 因此每个单元格可以有四种不一样的边框粗细，有时候也会用到。 def _get_style_2(l=1,r=1,b=1,t=1,font_size=20): ''' l：l for left ,左边框的粗细 默认为1 r：r for right ,右边框的粗细 默认为1 b：b for bottom,下边框的粗细 默认为1 t：t for top ,上边框的粗细 默认为1 font_size：字体大小 默认为20 ''' style = xlwt.XFStyle() # Create Style font = xlwt.Font() # Create Font borders = xlwt.Borders() # Create Borders alignment = xlwt.Alignment() # Create Alignment font.name = '宋体' # 设置字体为 宋体 font.height = font_size*20 # 设置字体大小为 20（20*20） alignment.horz = xlwt.Alignment.HORZ_CENTER # 可以选择: HORZ_GENERAL,HORZ_LEFT,HORZ_CENTER,HORZ_RIGHT,HORZ_FILLED, # HORZ_JUSTIFIED,HORZ_CENTER_ACROSS_SEL,HORZ_DISTRIBUTED alignment.vert = xlwt.Alignment.VERT_CENTER # 可以选择: VERT_TOP,VERT_CENTER,VERT_BOTTOM,VERT_JUSTIFIED,VERT_DISTRIBUTED # 设置边框宽度 borders.left = l borders.right = r borders.top = t borders.bottom = b # 向style输入格式 style.font = font style.alignment = alignment style.borders = borders return style 1.2 辅助程序-数据正确录入工具因为xlwt不支持Int64所以我写了辅助程序向worksheet.write输入正确的值，防止DataFrame里的Int64数据录入的时候程序报错。 def _Int_Print(value): ''' is number -> int(value) is nan -> '' not number -> value ''' try: if str(value).isnumeric(): return int(value) elif np.isnan(value): return '' else: return int(value) except: return value 注意：xlwt的写入不支持Int64型。这里在写入数字的时候对数字使用了int(number)是因为我的DataFrame使用的是Int64型（Int64支持存储nan，普通的int型不支持存储nan）,所以要对数据进行转换再写入worksheet。虽然这个演示中没有使用到百分比或Float，但是在这里我也开放给大家方便使用~ def _Percentage_Print(value): try: if str(value).isnumeric(): return str(int(value)*100)+'%' elif np.isnan(value): return '' else: return str(np.round((float(value)*100),2))+'%' except: return value def _Float_Print(value): try: if str(value).isnumeric(): return int(value) elif np.isnan(value): return '' else: return float(value) except: return value 2 表内容录入 2.0 单元格格式准备 # 表头 font_style_lrtb_0 = _get_style('lrtb',0) font_style_lrtb = _get_style('lrtb',2) font_style_lrt = _get_style('lrt',2) font_style_lrb = _get_style('lrb',2) font_style_rtb = _get_style('rtb',2) font_style_b = _get_style('b',2) font_style_lb = _get_style('lb',2) font_style_rb = _get_style('rb',2) # 表内容 font_style_lr = _get_style('lr',2) # font_style_lrb = _get_style('lrb',2) font_style_r = _get_style('r',2) # font_style_rb = _get_style('rb',2) font_style_normal = _get_style() # font_style_b = _get_style('b',2) 2.1 常用数据设置 normal_data = '空 气 温 度 （0.1℃）' # 一级 column name avg_data = '平均' # 一级&二级 column name max_data = '最高' # 一级&二级 column name min_data = '最低' # 一级&二级 column name year = str(2019) # 使用参数 month = '{:02}'.format(2) # 使用参数 # 展示的数据名为monthly_excel(pandas的DataFrame形式) # 把index放入column（写入表格时使用） monthly_excel_for_write = monthly_pandas_data.reset_index() # 创建xlwt的workbook workbook = xlwt.Workbook(encoding='UTF-8') # 添加sheet（名字为'空气温度（0.1℃）'） worksheet = workbook.add_sheet(normal_data.replace(' ','')) 2.2 表头录入 写入单个单元格使用worksheet.write(row,columns,content,cell_style),依次为（行，列，内容，格式）写入合并单元格使用worksheet.write_merge(row_from,row_to,columns_from,columns_to,content,cell_style)，依次为（） ## 日期 ### 设置初始行为0 cur_row = 0 ### 在第0行写入日期（2019年02月） worksheet.write_merge(cur_row,cur_row,2,4,str(year)+'年'+str(month)+'月',font_style_lrtb_0) ## 一级表头 ### 增加一行 cur_row += 1 ### 使用write_merge分别写入第一行的表头内容 ### 使用辅助程序按需求对单元格进行定义 worksheet.write_merge(cur_row,cur_row+1,0,0,'日期',font_style_lrtb_2) worksheet.write_merge(cur_row,cur_row,1,24,normal_data,font_style_lrt) worksheet.write_merge(cur_row,cur_row,25,26,avg_data,font_style_lrt) worksheet.write_merge(cur_row,cur_row+1,27,27,max_data,font_style_lrb) worksheet.write_merge(cur_row,cur_row+1,28,28,min_data,font_style_rtb) ## 二级表头 ### 增加一行 cur_row += 1 ### 使用write分别写入第二行的时间表头 ### 使用辅助程序按需求对单元格进行定义 for i in range(1,25): if i in [6,12,18,24]: ### left right bottom 为粗框，其余为细框 cur_style = font_style_lrb else: ### bottom 为粗框，其余为细框 cur_style = font_style_b worksheet.write(cur_row,i,int(monthly_excel_for_write[normal_data].columns.values[i-1]),cur_style) ### 使用write分别写入第二行的剩余表头 ### 使用辅助程序按需求对单元格进行定义 worksheet.write(cur_row,25,'4次',font_style_lb) worksheet.write(cur_row,26,'24次',font_style_rb) 注意：xlwt的写入不支持Int64型。这里在写入数字的时候对数字使用了int(number)是因为我的DataFrame使用的是Int64型（Int64支持存储nan，普通的int型不支持存储nan）,所以要对数据进行转换再写入worksheet。表头的编写并不麻烦，因此建议表头部分手动写死，后续也可以根据需求修改。 2.3 数据自动录入录入部分使用的是便利循环依次录入表格，需要注意的是我使用的是int录入到表格内，大家可以根据自己的需求编写（也可以不需要）录入工具，例如：_Percent_Print(),_Float_Print()等。 ## 自动录入数据 ### 增加一行 cur_row += 1 ## 使用便利循环录入每一个数值 for i in range(len(monthly_excel_for_write)): for j in range(len(monthly_excel_for_write.iloc[i].values)): if i == len(monthly_excel_for_write)-1: #最后一行，需要特别设置下粗框 if j in [0,6,12,18,24]: #设置第0，6，12，18，24列为左右下粗框 cur_style = font_style_lrb elif j in [26,28]: #设置第26，28列为左右下粗框 cur_style = font_style_rb else: #其余为正常细边框 cur_style = font_style_b else: #中间行，不需要下粗框 if j in [0,6,12,18,24]: #设置第0，6，12，18，24列为左右粗框 cur_style = font_style_lr elif j in [26,28]: #设置第26，28列为左右下粗框 cur_style = font_style_r else: #其余为正常细边框 cur_style = font_style_normal cur_data = monthly_excel_for_write.iloc[i].values[j] #获取data[i,j]数据 worksheet.write(cur_row,j,_Int_Print(cur_data),cur_style) #使用_Int_Print录入表格 ## 完成一行 增加一级 cur_row += 1 2.4 行高列宽设置这部分没啥好解释的，直接上代码 ♪(＾∀＾●)ﾉ for i in range(0,cur_row+1): worksheet.row(i).height_mismatch = True worksheet.row(i).height = 25*20 # 设置25行高 excel 1 行高 = 20 height worksheet.col(0).width = 256 * 14 # 设置14列宽 excel 1 列宽 = 256 width 3 表保存这部分也没啥好解释的，直接上代码 ヾ(o◕∀◕)ﾉヾ workbook.save(r'.\Sample_Result.xls') 总结过程其实很简单，主要内容为: 单元格格式提前定义表头及表内容录入行高列宽设置表保存希望此实例可以帮助到大家制作自己的脚本，减少大家的敲代码时间~ 最后求求大家去GitHub给颗星。ヾ(￣▽￣)ByeBye |
【本文地址】
今日新闻 |
推荐新闻 |