24 11 2021
class ExcelStyleMachining(object):
    def __init__(self):
        """
        """
        self.style = self.global_style(border=True)

    def global_style(self, align_center=False, border=False, font_color_index=0, ):
        style = xlwt.XFStyle()  # 格式信息
        font = xlwt.Font()  # 字体基本设置
        font.name = '宋体'
        font.colour_index = font_color_index
        font.height = 10 * 20,
        style.font = font
        if border:
            border = xlwt.Borders()  # 给单元格加框线
            border.left = xlwt.Borders.THIN  # 左
            border.top = xlwt.Borders.THIN  # 上
            border.right = xlwt.Borders.THIN  # 右
            border.bottom = xlwt.Borders.THIN  # 下
            border.left_colour = 0x40  # 设置框线颜色,0x40是黑色,颜色真的巨多,都晕了
            border.right_colour = 0x40
            border.top_colour = 0x40
            border.bottom_colour = 0x40
            style.borders = border
            '''
            # 细实线:1,小粗实线:2,细虚线:3,中细虚线:4,大粗实线:5,双线:6,细点虚线:7
            # 大粗虚线:8,细点划线:9,粗点划线:10,细双点划线:11,粗双点划线:12,斜点划线:13
            borders.left = 1
            borders.right = 2
            borders.top = 3
            borders.bottom = 4
            borders.left_colour = i
            borders.right_colour = i
            borders.top_colour = i
            borders.bottom_colour = i
            '''
            # style.num_format_str = '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_)'

        if align_center:
            al = xlwt.Alignment()
            al.horz = 0x02  # 设置水平居中
            al.vert = 0x01  # 设置垂直居中
            style.alignment = al
        return style

    def set_style(self,
                  font_color_index=None,
                  font_name=None,
                  font_height=10 * 20,
                  font_bold=None,
                  font_underline=None,
                  font_italic=None,
                  alignment_horz=None,
                  alignment_vert=None,
                  borders_left=None,
                  borders_right=None,
                  borders_top=None,
                  borders_bottom=None,
                  back_ground_color_index=None,
                  ):
        import copy
        """
        不能用已经创建的style对象 否则prepare_style会生成三个相同的style对象。使用新实例化style对象或者使用已经实例化的style对象的深拷贝对象
        """
        # local_style = self.style
        # local_style = xlwt.XFStyle()
        local_style = copy.deepcopy(self.style)  # 不能用已经创建的style对象 否则prepare_style会生成三个相同的style对象
        if font_color_index:
            local_style.font.colour_index = font_color_index
        if font_name:
            local_style.font.name = font_name
        if font_height:
            local_style.font.height = font_height
        if font_bold:
            local_style.font.bold = font_bold
        if alignment_horz or alignment_vert:
            al = xlwt.Alignment()
            if alignment_horz:
                al.horz = alignment_horz
            if alignment_vert:
                al.vert = alignment_vert
            local_style.alignment = al
        if back_ground_color_index:
            pattern = xlwt.Pattern()
            pattern.pattern = xlwt.Pattern.SOLID_PATTERN
            pattern.pattern_fore_colour = back_ground_color_index
            local_style.pattern = pattern
        return local_style

    def write_to_excel(self, dict_list, dst_file_path, sheet_index=0, sheet_name=None, append_mode=False):
        from xlutils.copy import copy
        if append_mode:  # 如果文件存在 则在原来文件的基础上写入
            if os.path.exists(dst_file_path):  # 如果是追加模式 则 数据写入起始行数为已经存在的行数
                data = xlrd.open_workbook(dst_file_path, formatting_info=True)
                workbook = copy(data)
                worksheet = workbook.get_sheet(sheet_index)
                rows = int(xlrd.open_workbook(dst_file_path).sheets()[sheet_index].nrows)
                self.write_by_regulation(worksheet, CommonFunction.dict_list_to_list(dict_list), start_row=rows,
                                         start_column=0)
                workbook.save(dst_file_path)
            else:
                workbook = xlwt.Workbook(encoding='utf-8')
                worksheet = workbook.add_sheet(sheet_name)
                self.write_by_regulation(worksheet, CommonFunction.dict_list_to_list(dict_list), start_row=0,
                                         start_column=0)
                workbook.save(dst_file_path)
        else:  # 如果文件不存在 直接创建文件写入
            workbook = xlwt.Workbook(encoding='utf-8')
            worksheet = workbook.add_sheet(sheet_name)
            self.write_by_regulation(worksheet, CommonFunction.dict_list_to_list(dict_list), start_row=0,
                                     start_column=0)
            workbook.save(dst_file_path)

    def prepare_style(self):
        self.local_style_default = self.set_style()
        self.local_style_key = self.set_style(
            font_name='宋体',
            font_height=12 * 20,
            font_bold=True,
            font_color_index=1,
            alignment_horz=0x02,
            alignment_vert=0x01,
            back_ground_color_index=30,
        )
        self.local_style_value = self.set_style(
            font_name='宋体',
            font_height=11 * 20,
            alignment_horz=0x02,
            alignment_vert=0x01,
        )
        # self.local_style_2 = self.set_style(back_ground_color_index=43)

    def write_by_regulation(self, worksheet, data_list_2d, start_row, start_column, exist_keys=True):
        self.prepare_style()
        if exist_keys:
            for column in range(len(data_list_2d[0])):
                local_style = self.local_style_key
                worksheet.write(start_row + 0, start_column + column, data_list_2d[0][column],
                                local_style)
            for row in range(1, len(data_list_2d[1:])+1):
                for column in range(len(data_list_2d[row])):
                    local_style = self.local_style_value
                    worksheet.write(start_row + row, start_column + column, data_list_2d[row][column], local_style)

        for row in range(len(data_list_2d)):
            worksheet.row(row).height_mismatch = True
            worksheet.row(row).height = 3 * 256

        worksheet.col(0).width = 10 * 256
        worksheet.col(1).width = 10 * 256
        worksheet.col(2).width = 10 * 256
        worksheet.col(3).width = 30 * 256
        worksheet.col(4).width = 30 * 256
        worksheet.col(5).width = 45 * 256
        worksheet.col(6).width = 15 * 256
        worksheet.col(7).width = 15 * 256
        worksheet.col(8).width = 15 * 256
        worksheet.col(9).width = 15 * 256
        worksheet.col(10).width = 15 * 256
        worksheet.set_panes_frozen('1')
        worksheet.set_horz_split_pos(1)

 

延伸阅读
    < /body> < /html>