考虑复用性，写入Excel最好能够指定sheetname，且做一些个性化设置。

• 样例1：openpyxl，多次调用write_to_excel_openpyxl可以追加写入。

from openpyxl.styles import PatternFill,NamedStyle,Font, Border, Side, PatternFill, Color
from openpyxl import Workbook,load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
    
    def write_to_excel_openpyxl(self,file_name,sheet_name="Sheet1",cols=7,merged_list=None,dfnew=None):
        """
        openpyxl 支持 多个sheetname。
        :cols: number ，列数，是几列就写几，不需要考虑加1
        """
        if merged_list is not None:
            # 将列表数据转换为 Pandas DataFrame
            df = pd.DataFrame(merged_list)
        else:
            df = dfnew

        # 加载现有工作簿或创建新工作簿
        try:
            workbook = load_workbook(file_name)
        except FileNotFoundError:
            workbook = Workbook()

        # 选择指定的工作表，如果不存在则创建
        try:
            worksheet = workbook[sheet_name]
        except KeyError:
            worksheet = workbook.create_sheet(sheet_name)

        # 清空工作表
        worksheet.delete_rows(1, worksheet.max_row)

        # # 将数据写入工作表，很简单的写入。废弃
        # for r in dataframe_to_rows(df, index=False, header=True):
        #     worksheet.append(r)

        # 设置表头单元格样式，'000000' 表示黑色，'FFFFFF' 表示白色,十六进制颜色代码#d9d9d9是极亮度的灰色阴影
        header_font = Font(name='Arial', size=11, bold=True, color='FFFFFF')
        header_fill = PatternFill(fill_type='solid', start_color='2F75B5')
        header_border = Border(left=Side(border_style='thin', color='000000'),
                               right=Side(border_style='thin', color='000000'),
                               top=Side(border_style='thin', color='000000'),
                               bottom=Side(border_style='thin', color='000000'))

        # 设置数据单元格样式
        data_font = Font(name='Arial', size=11, bold=False, color='000000')
        data_fill = PatternFill(fill_type='solid', start_color='FFFFFF')
        data_border = Border(left=Side(border_style='thin', color='000000'),
                             right=Side(border_style='thin', color='000000'),
                             top=Side(border_style='thin', color='000000'),
                             bottom=Side(border_style='thin', color='000000'))

        # 将数据写入工作表
        for r in dataframe_to_rows(df, index=False, header=True):
            row = []
            for cell in r:
                row.append(cell)
            worksheet.append(row)

        # 应用表头单元格样式
        for cell in worksheet[1]:
            cell.font = header_font
            cell.fill = header_fill
            cell.border = header_border

        # 应用数据单元格样式
        for row in worksheet.iter_rows(min_row=2):
            for cell in row:
                cell.font = data_font
                cell.fill = data_fill
                cell.border = data_border

        """
        # 设置单元格背景颜色
        min_row=2：指定要迭代的单元格的最小行号
        min_col=2：指定要迭代的单元格的最小列号
        max_col=2：指定要迭代的单元格的最大列号
        如果sheet_name == "Sheet1"，则给cols列改填充色。cell.value < 10000是为了让全部value都是黄色。
        """
        if sheet_name == "Sheet1":
            yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
            for row in worksheet.iter_rows(min_row=2, min_col=cols, max_col=cols):
                for cell in row:
                    #print(f"cell value:{cell.value}")
                    if cell.value is not None and cell.value < 10000:
                        cell.fill = yellow_fill

        # 删除默认工作表
        if 'Sheet' in workbook.sheetnames:
            workbook.remove(workbook['Sheet'])
        # 保存工作簿
        workbook.save(file_name)
        print("结果见：" + file_name)

• 样例2：xlsxwriter，先构造多个sheet写入对象，同时写入表格。

def write_to_excel(data, file_name):
    """
    本来xlsxwriter不支持多个sheet，但是这段借助df.to_excel支持了多个sheet同时写入。
    :param data: # data是字典，key是sheet名，value是pd.DataFrame.
    :param file_name: 写入文件名
    :return:
    """
    # TypeError: NAN/INF not supported in write_number() without 'nan_inf_to_errors' 报错的原因是在 worksheet.write() 方法中遇到了 NaN 或 Inf 的数据，而默认情况下，xlsxwriter 不能将这些特殊的数据写入 Excel 文件中。为了解决这个问题，可以在打开 ExcelWriter 时指定 nan_inf_to_errors=True。实际由于版本原因，已无option选项，改用df = df.replace([np.nan, np.inf, -np.inf], ['', '', ''])
    writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
    workbook = writer.book  # 获取工作簿
    # workbook.options['nan_inf_to_errors'] = True
    header_format = workbook.add_format({'bold': True, 'bg_color': '#2F75B5', 'border': 1})
    cell_format = workbook.add_format({'border': 1})

    for sheet_name, df in data.items():
        # 将 NaN 和 Inf 替换成空，避免TypeError: NAN/INF not supported in write_number() without 'nan_inf_to_errors' 报错
        df = df.replace([np.nan, np.inf, -np.inf], ['', '', ''])
        df.to_excel(writer, index=False, sheet_name=sheet_name)
        worksheet = writer.sheets[sheet_name]

        for col_num, value in enumerate(df.columns.values):
            # column_len固定列宽
            column_len = max(len(value), 10) + 2
            worksheet.set_column(col_num, col_num, column_len)
            worksheet.write(0, col_num, value, header_format)  # 写入蓝色表头

        for idx, row in df.iterrows():
            for col_idx, value in enumerate(row):
                worksheet.write(idx + 1, col_idx, value, cell_format)  # 写入数据
    writer._save()
    print("结果见：" + file_name)