Python操作Excel表格概括

文中涉及到Python操作Excel表格的邊框、字型、單元格寬度自适應、單元格合并居中、單元格加背景色以及資料寫入

Python xlwt庫操作Excel彙總（全）

import xlwt
from conf import operation_conf as config


class SummaryOperationReport(object):
    """
    彙總任務報告
    """

    def __init__(self):
        # 設定字型
        self.font = xlwt.Font()
        self.font.bold = True

        # 設定邊框
        self.borders = xlwt.Borders()
        self.borders.left = xlwt.Borders.THIN
        self.borders.right = xlwt.Borders.THIN
        self.borders.top = xlwt.Borders.THIN
        self.borders.bottom = xlwt.Borders.THIN

        # 設定居中
        self.alignment = xlwt.Alignment()
        self.alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水準方向
        self.alignment.vert = xlwt.Alignment.VERT_TOP  # 垂直方向

        # 設定背景顔色pattern2(淺灰)
        self.pattern2 = self.set_background_for_cell(0x16)

        # 設定背景顔色pattern3(深灰)
        self.pattern3 = self.set_background_for_cell(0x37)

        # 設定表格樣式
        self.style1 = self.set_style_for_cell()
        self.style2 = self.set_style_for_cell(self.pattern2)
        self.style3 = self.set_style_for_cell(self.pattern3)
        self.style4 = xlwt.easyxf('font:height 220;')

        self.workbook = xlwt.Workbook(encoding='utf-8')

    @staticmethod
    def set_background_for_cell(rgb):
        """
        為單元格設定背景色
        :param rgb:
        :return:
        """
        pat = xlwt.Pattern()
        pat.pattern = xlwt.Pattern.SOLID_PATTERN
        pat.pattern_fore_colour = rgb  # 背景顔色
        return pat

    def set_style_for_cell(self, pat=None):
        """
        為單元格設定樣式
        :param pat:
        :return:
        """
        style = xlwt.XFStyle()
        style.borders = self.borders
        if pat:
            style.font = self.font
            style.pattern = pat
            style.alignment = self.alignment
        return style

    @staticmethod
    def len_byte(value):
        """
        擷取字元串長度，一個中文的長度為2
        :param value:
        :return:
        """
        length = len(value)
        utf8_length = len(value.encode('utf-8'))
        length = (utf8_length - length) / 2 + length
        if length > config.MAX_COLUMN_WIDTH:
            return config.MAX_COLUMN_WIDTH
        return int(length)

    def merge_chains_excel_header(self, worksheet):
        """
        合并Chains表頭
        :param worksheet:
        :return:
        """
        # 設定單元格的高度(Chains)
        row0 = worksheet.row(0)
        row0.set_style(self.style4)

        # Chains表 合并表頭
        init_row = 0
        for key, val in config.CHAINS_MERGE_TITLE.items():
            worksheet.write_merge(0, 0, init_row, val + init_row, label=key, style=self.style3)
            init_row += (val + 1)

    def calculate_cell_width(self, title_list, report_iter, worksheet):
        """
        計算單元格寬度
        :param title_list:
        :param report_iter:
        :param worksheet:
        :return:
        """
        col_width = []

        # 第一行（标題行）确定每個單元格的寬度
        for title_dict in title_list:
            col_width.append(self.len_byte(title_dict.get("title")))

        for report in report_iter:
            for column_y, title_dict in enumerate(title_list):
                field_key = title_dict.get("key")
                field_value = report.get(field_key)

                if col_width[column_y] < self.len_byte(str(field_value)):
                    col_width[column_y] = self.len_byte(str(field_value))

        # 寬度小于10時采用預設寬度
        for i in range(len(col_width)):
            if col_width[i] > 10:
                worksheet.col(i).width = 256 * (col_width[i] + 1)

    def write_report_to_excel(self, report_type, report_iter, row0):
        """
        将各種類型的報告寫入excel表格
        :param report_type:
        :param report_iter:
        :param row0: 起始行 （chains: 1, others: 0）
        :return:
        """

        worksheet = self.workbook.add_sheet(report_type, cell_overwrite_ok=True)
        # 确定表頭
        if report_type == "Chains":
            title_list = config.CHAINS_EXCEL_TITLE
        elif report_type == "Agent":
            title_list = config.AGENT_EXCEL_TITLE
        else:
            title_list = config.OPERATION_EXCEL_TITLE

        # 确定單元格寬度
        self.calculate_cell_width(title_list, report_iter, worksheet)

        # 寫标題
        for column_y, title_dict in enumerate(title_list):
            worksheet.write(row0, column_y, label=title_dict.get("title"), style=self.style2)

        # 寫内容
        for row_x, report in enumerate(report_iter, row0 + 1):
            # TODO 如果超過excel最大行數，則建立一個sheet
            for column_y, title_dict in enumerate(title_list):
                field_key = title_dict.get("key")
                field_value = str(report.get(field_key, ""))
                if len(field_value) > config.MAX_CHARACTERS:
                    field_value = field_value[:config.MAX_CHARACTERS] + "..."
                worksheet.write(row_x, column_y, label=field_value, style=self.style1)

        # 合并Chains表的表頭
        if report_type == "Chains":
            self.merge_chains_excel_header(worksheet)

    def run_handle_report(self, chains_report, agent_report, operation_report, filename):
        """
        開始處理Agent、Chains、Operation報告表格
        :param chains_report:
        :param agent_report:
        :param operation_report:
        :param filename:
        :return:
        """
        # Chains報告表格
        self.write_report_to_excel("Chains", chains_report, 1)
        # Agent報告表格
        self.write_report_to_excel("Agent", agent_report, 0)
        # Operation報告表格
        self.write_report_to_excel("Operation", operation_report, 0)
        self.workbook.save(filename)


           

抟扶搖而上者九萬裡