【Python】xlrd/xlwt操作excel 及常用操作

目录

• 一、代码
• 二、效果
• 三、常用操作

回到顶部

一、代码

备注：封装好了（可直接调用）

"""
-*- coding:utf-8 -*-
@Time   :2021/12/10 20:02
@Author :
@File   :
@Version：1.0
"""
from typing import List

import xlwt


class JarExcelUtil:
    def __init__(self, header_list: List[list]):
        """
        :param header_list: 如下格式
            例1：默认列宽
            header_list = [
                ['序号'],    # 表格第0列[此列表头名称]
                ['姓名'],
                ['性别'],
                ['爱好'],
                ['生日']
            ]
            例2：自定义列宽（列宽值为int类型 英文字符长度 如：10 表示列宽为10个英文字符长度）
            header = [
                ['序号', 5],  # 表格第0列[此列表头名称,列宽]
                ['姓名', 10], # 表格第1列[此列表头名称,列宽]
                ['性别', 10],
                ['爱好', 10],
                ['生日', 20]
            ]
        """
        self.data = header_list
        self.__color_str = 'aqua 0x31\r\n\
black 0x08\r\n\
blue 0x0C\r\n\
blue_gray 0x36\r\n\
bright_green 0x0B\r\n\
brown 0x3C\r\n\
coral 0x1D\r\n\
cyan_ega 0x0F\r\n\
dark_blue 0x12\r\n\
dark_blue_ega 0x12\r\n\
dark_green 0x3A\r\n\
dark_green_ega 0x11\r\n\
dark_purple 0x1C\r\n\
dark_red 0x10\r\n\
dark_red_ega 0x10\r\n\
dark_teal 0x38\r\n\
dark_yellow 0x13\r\n\
gold 0x33\r\n\
gray_ega 0x17\r\n\
gray25 0x16\r\n\
gray40 0x37\r\n\
gray50 0x17\r\n\
gray80 0x3F\r\n\
green 0x11\r\n\
ice_blue 0x1F\r\n\
indigo 0x3E\r\n\
ivory 0x1A\r\n\
lavender 0x2E\r\n\
light_blue 0x30\r\n\
light_green 0x2A\r\n\
light_orange 0x34\r\n\
light_turquoise 0x29\r\n\
light_yellow 0x2B\r\n\
lime 0x32\r\n\
magenta_ega 0x0E\r\n\
ocean_blue 0x1E\r\n\
olive_ega 0x13\r\n\
olive_green 0x3B\r\n\
orange 0x35\r\n\
pale_blue 0x2C\r\n\
periwinkle 0x18\r\n\
pink 0x0E\r\n\
plum 0x3D\r\n\
purple_ega 0x14\r\n\
red 0x0A\r\n\
rose 0x2D\r\n\
sea_green 0x39\r\n\
silver_ega 0x16\r\n\
sky_blue 0x28\r\n\
tan 0x2F\r\n\
teal 0x15\r\n\
teal_ega 0x15\r\n\
turquoise 0x0F\r\n\
violet 0x14\r\n\
white 0x09\r\n\
yellow 0x0D'
        self.color_list = []  # [[]]   [['aqua', '0x31'], ['black', '0x08'], ...]
        for color in self.__color_str.split('\r\n'):
            color = color.split(' ')
            self.color_list.append(color)

    def write(
            self,
            out_file,
            data_body: List[list],
            sheet_name='sheet',
            frozen_row: int = None,
            frozen_col: int = 0,
            row_style: list = None,
            header_index_=0,
            before_header_data: list = None,
            body_col_align: list = None,
    ):
        """
        写入数据
        :param out_file: 保存文件（如：test.xlsx）
        :param data_body: data_body[0]为表格第0行数据  data_body[0][0]为表格第0行第0列单元格值
        :param sheet_name:
        :param frozen_row: 冻结行（默认表头行）
        :param frozen_col: 冻结列（默认不冻结）
        :param row_style: [4,6] 给第4、5行标注高亮背景色
        :param header_index_: 第几行为表头（默认第0行） 若before_header_data != None,则此属性失效
        :param before_header_data: 表头之前的数据 before_header_data=[[第0行第0列,第0行第1列],[第1行第0列,第1行第1列]]
        :param body_col_align: 表体指定列对齐方式（不含表头） body_col_align=[[第0行第0列,第0行第1列],[第1行第0列,第1行第1列]]
        """
        # step1 判断数据正确性（每行列数是否与表头相同）
        count = 0
        for pro in data_body:
            if len(pro) != len(self.data):
                raise Exception(
                    'data_body数据错误 第{}行（从0开始） 需为{}个元素 当前行{}个元素：{}'.format(count, len(self.data), len(pro), str(pro)))
            count += 1

        # step2 写入数据
        wd = xlwt.Workbook()
        sheet = wd.add_sheet(sheet_name)

        ali_horiz = 'align: horiz center'  # 水平居中
        ali_vert = 'align: vert center'  # 垂直居中
        fore_colour = 'pattern: pattern solid,fore_colour pale_blue'  # 设置单元格背景色为pale_blue色
        fore_colour_highlight = 'pattern: pattern solid,fore_colour light_orange'  # 设置单元格背景高亮

        # 表头格式（垂直+水平居中、表头背景色）
        style_header = xlwt.easyxf('{};{};{}'.format(fore_colour, ali_horiz, ali_vert))

        # 表体格式（垂直居中）
        style_body = xlwt.easyxf('{}'.format(ali_vert))
        # 表体格式（垂直居中+背景色）
        style_body_highlight = xlwt.easyxf('{};{}'.format(ali_vert, fore_colour_highlight))
        # 表体指定列对齐方式（水平居中）
        style_body_col_align = xlwt.easyxf('{}'.format(ali_horiz))

        # 表头之前的数据
        if before_header_data is not None:
            header_index_ = len(before_header_data)
            for pro in before_header_data:
                now_index = before_header_data.index(pro)
                sheet.row(now_index).height_mismatch = True
                sheet.row(now_index).height = 20 * 20  # 20为基数 * 20榜
                # 当前行是否需要添加高亮背景色
                for d in pro:
                    value = pro[pro.index(d)]
                    # 若值类型是int、float 直接写入 反之 转成字符串写入
                    if type(value) == int or type(value) == float:
                        sheet.write(now_index, pro.index(d), value, style_body)
                    else:
                        sheet.write(now_index, pro.index(d), str(value), style_body)

        # 表头
        header_index = header_index_
        for col in self.data:
            # 默认列宽
            if len(col) == 1:
                sheet.write(header_index, self.data.index(col), str(col[0]), style_header)
            # 自定义列宽
            if len(col) == 2:
                sheet.write(header_index, self.data.index(col), str(col[0]), style_header)
                # 设置列宽
                sheet.col(self.data.index(col)).width = 256 * col[1]  # 256为基数 * n个英文字符
        # 表头行高
        sheet.row(header_index).height_mismatch = True
        sheet.row(header_index).height = 20 * 20  # 20为基数 * 20榜

        # 表体
        body_index = header_index + 1
        for pro in data_body:
            sheet.row(body_index).height_mismatch = True
            sheet.row(body_index).height = 20 * 20  # 20为基数 * 20榜
            # 当前行是否需要添加高亮背景色
            sb = [ali_vert]  # 垂直居中
            if row_style is not None and body_index in row_style: sb.append(fore_colour_highlight)  # 背景色

            for d in self.data:
                value = pro[self.data.index(d)]
                if body_col_align is not None and self.data.index(d) in body_col_align:
                    sb.append(ali_horiz)  # 水平居中
                    s_body = xlwt.easyxf(';'.join(sb))
                    sb.pop()  # 删除sb中的水平居中 不然会影响下次循环
                    print(body_index, self.data.index(d), value)
                else:
                    s_body = xlwt.easyxf(';'.join(sb))

                # 若值类型是int、float 直接写入 反之 转成字符串写入
                if type(value) == int or type(value) == float:
                    sheet.write(body_index, self.data.index(d), value, s_body)
                else:
                    sheet.write(body_index, self.data.index(d), str(value), s_body)
            body_index += 1

        # 冻结（列与行）
        sheet.set_panes_frozen('1')
        sheet.set_horz_split_pos(header_index + 1 if frozen_row is None else frozen_row)  # 冻结前n行（默认表头行）
        sheet.set_vert_split_pos(frozen_col)  # 冻结前n列

        wd.save(out_file)

    def color_test(self):
        """
        测试颜色
        """
        body_t = []
        for color in self.color_list:
            print(color)
            body_t.append(color)
        wd = xlwt.Workbook()
        sheet = wd.add_sheet('sheet')

        index = 0
        for b in body_t:
            ali = 'align: horiz center;align: vert center'  # 垂直居中 水平居中
            fore_colour = 'pattern: pattern solid,fore_colour {}'.format(
                self.color_list[index][0])  # 设置单元格背景色为pale_blue色
            style_header = xlwt.easyxf(
                '{};{}'.format(fore_colour, ali))
            sheet.write(index, 0, str(b), style_header)
            sheet.col(0).width = 256 * 150  # 256为基数 * n个英文字符
            index += 1

        wd.save('颜色测试.xlsx')


# 测试颜色
# if __name__ == '__main__':
#     header_t = [
#         ['颜色']
#     ]
#     JarExcelUtil(header_t).color_test()

if __name__ == '__main__':
    header = [
        ['序号', 5],
        ['姓名', 10],
        ['性别', 10],
        ['爱好', 10],
        ['生日', 20]
    ]

    # header = [
    #     ['序号'],
    #     ['姓名'],
    #     ['性别'],
    #     ['爱好'],
    #     ['生日']
    # ]

    body = [
        [1, '张三', '男', '篮球', '1994-07-23'],
        [2, '李四', '女', '足球', '1994-04-03'],
        [3, '王五', '男', '兵乓球', '1994-09-13']
    ]

    before_data = [
        ['地址', 'www.baidu.com'],
        ['env', '192.168.1.1']
    ]

    JarExcelUtil(header_list=header).write(out_file='测试.xlsx', data_body=body, before_header_data=before_data)

 

 

 

 

回到顶部

二、效果

生成的Excel

 

回到顶部

三、常用操作

3.1、设置行高

# 行高（第0行）
sheet.row(0).height_mismatch = True
sheet.row(0).height = 20 * 20  # 20为基数 * 20榜

 

3.2、设置列宽

# 列宽（第0列）
sheet.col(0).width = 256 * 30  # 256为基数 * 30个英文字符（约）

 

3.3、冻结（列与行）

# 冻结（列与行）
sheet.set_panes_frozen('1')
sheet.set_horz_split_pos(2)  # 冻结前2行
sheet.set_vert_split_pos(3)  # 冻结前3列
        
# 冻结首行
sheet.set_panes_frozen('1')
sheet.set_horz_split_pos(1)  # 冻结前1行（即首行）

 

3.4、设置单元格对齐方式

# 方式1
style_1 = xlwt.XFStyle()
al_1 = xlwt.Alignment()
al_1.horz = xlwt.Alignment.HORZ_CENTER  # 水平居中
al_1.vert = xlwt.Alignment.VERT_CENTER  # 垂直居中
style_1.alignment = al_1
sheet.write(0, 0, '第0行第0列单元格值', style_1)
        
# 方式2（推荐）
ali_horiz = 'align: horiz center'  # 水平居中
ali_vert = 'align: vert center'  # 垂直居中
style_2 = xlwt.easyxf('{};{}'.format(ali_horiz, ali_vert))
sheet.write(0, 0, '第0行第0列单元格值', style_2)

 

3.5、设置单元格背景色

# 设置单元格背景色
fore_colour = 'pattern: pattern solid,fore_colour pale_blue'  # 设置单元格背景色为pale_blue色 （具体颜色值 参考上面代码JarExcelUtil类中的color_test方法的运行结果）
style = xlwt.easyxf('{}'.format(fore_colour))
sheet.write(0, 0, '第0行第0列单元格值', style)