pandas输出excel文件添加表头标题,样式装饰器

以下装饰器将输出带有样式的excel文件。

import os
import pandas as pd
from xlsxwriter.utility import xl_range


def decoratorToExcel(excel_name: str = "test", excel_title: str = "test", excel_dir: str = None):
    """
    输出文件装饰器,作用于类方法
    :param excel_dir: 输出的文件夹
    :param excel_title: 文件名
    :param excel_name: 文件表头
    :return:
    """

    def midFunc(cls_func):

        def inner(self, *args, **kwargs):
            data: pd.DataFrame = cls_func(self, *args, **kwargs)
            try:
                if excel_dir:
                    os.makedirs(excel_dir)
            except BaseException:
                pass
            writer = pd.ExcelWriter(
                %s%s.xlsx % (
                    excel_dir + "/" if excel_dir else "", excel_name,), datetime_format=yyyy/mm/dd)
            data.to_excel(
                writer,
                engine=xlsxwriter,
                sheet_name=sheet,
                startrow=2,
                header=False,
                float_format="%.2f")
            workbook = writer.book
            worksheet = writer.sheets[sheet]
            worksheet.set_row(0, 20)
            worksheet.set_row(1, 30)
            header_format = workbook.add_format({
                bold: True,
                font_color: black,
                text_wrap: True,
                align: center,
                fg_color: #6BA81E,
                border: 1})
            rl = data.columns.tolist()
            rl.insert(0, 序号)
            cell_range = xl_range(0, 0, 0, len(rl) - 1)
            merge_format = workbook.add_format(
                {align: center, bold: True, fg_color: #009688, font_color: "#000000"})
            worksheet.merge_range(cell_range, excel_title, merge_format)
            for col_num, value in enumerate(rl):
                worksheet.write(1, col_num, value, header_format)
            writer.save()
            return

        return inner

    return midFunc


class Test:

    @decoratorToExcel(excel_name="测试表格", excel_title="测试sheet")
    def setExcelData(self) -> pd.DataFrame:
        """
        生成excel数据
        :return: DataFrame
        """
        data: pd.DataFrame = pd.DataFrame({"name": "张三 李四 王五".split(), "age": "12 13 15".split()})
        return data


if __name__ == __main__:
    Test().setExcelData()