实例演示如何将pandas的DataFrame数据输出为带格式的excel文件(.xls)

最新推荐文章于 2024-08-02 12:02:39 发布

大饼不会飞

最新推荐文章于 2024-08-02 12:02:39 发布

阅读量10w+

点赞数 18

文章标签： excel xlwt python 实例代码

本文链接：https://blog.csdn.net/weixin_46088071/article/details/107462908

版权

实例演示如何将pandas的DataFrame数据输出为带格式的excel文件数据（.xls）

背景
数据与代码获取
数据表与成果展示
- Pandas的DataFrame表（Notebook打印）
- xlwt输出的Excel表
代码展示
总结

背景

系统为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打印）

DataFrame

xlwt输出的Excel表

xlwt的excel表

代码展示

0 库引用

import pandas as pd
import numpy as np
import xlwt
import xlrd

1 辅助程序

1.1 辅助程序-单元格格式定义

可以根据需求对单个单元格进行定义，这个步骤十分重要且必要，因为不同的边框设置会有各种需求，所以写了这份辅助程序方便使用和定义，不然后期对字体或是大小的修改都会十分麻烦!
编写的初期作者使用的是方法一，但是后期因新的单元格设计需要而写了方法二，~~使用后发现方法二真的太方便了，~~ 个人比较推荐大家使用方法二。

定义方法一

选择主边框，设定主边框和副边框粗细值，设置字体大小

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给颗星。ヾ(￣▽￣)Bye_Bye

大饼不会飞

关注

18
点赞
踩
115

收藏

觉得还不错? 一键收藏
0
评论
实例演示如何将pandas的DataFrame数据输出为带格式的excel文件(.xls)

实例演示如何将pandas的DataFrame数据输出为带格式的excel文件数据（.xls）背景数据表与成果展示Pandas的DataFrame表（Notebook打印）xlwt输出的Excel表代码展示0 库引用1 辅助程序-单元格格式定义1.1 定义方法一定义方法二2 表内容录入2.0 常用数据设置2.1 表头录入2.2 单元格格式准备2.3 数据自动录入2.4 行高列宽设置3 表保存背景系统为win10，使用的是Anaconda3下的python3.7.4。最近在制作和整理各式各样的数据报表
复制链接

扫一扫