如何将嵌套pandas DataFrame导出为美观紧凑的Excel文件

尝试将包含DataFrame的pandas嵌套结构导出为美观的Excel文件时遇到两个核心问题：

• 指定startrow和startcol参数后，to_excel仍会覆盖整个工作表，无法保留已写入的数据
• 导出整周训练数据时，每日内容被挤在单个单元格，格式混乱

单日数据导出的可用代码：

Writer=ExcelWriter(path, "xlsxwriter")
for weekindex, week in enumerate(WeekList):
    for dayindex, day in enumerate(week):
        for exercise in week[day]:
            exercise.to_excel(Writer, sheet_name=f"Week {weekindex+1}", index=False, header=True, merge_cells=False)
Writer.close()

整周数据导出的无效代码（格式混乱）：

for weekindex, week in enumerate(WeekList):
    for day in week:
        week.to_excel(Writer, sheet_name=f"Week {weekindex+1}", index=True, header=True, merge_cells=False)
Writer.close()

期望实现类似PrettyTable的效果：每个单元格对应一天，单元格内列出当天所有训练项目（但PrettyTable无法导出Excel），参考代码：

DailyTableList=[]
WeeklyTable=PrettyTable()
WeeklyTable.set_style(ORGMODE)
WeeklyTable.field_names=Days
for week in WeeksOfProgram:
    for day in week.ProgramDays:
        DailyTable = PrettyTable()
        DailyTable.set_style(ORGMODE)
        DailyTable.field_names=["Exercise", "Sets", "Reps", "PercentageOfOneRepMax","INOL"]
        for index, exercise in enumerate(day.ExerciseList):
            DailyTable.add_row([exercise.Name, exercise.NumberOfSets, exercise.NumberOfReps, exercise.Intensity, exercise.INOL])
            if(index % windowsize == windowsize - 1 or index==len(day.ExerciseList)-1):
                #print(DailyTable)  only here for debugging purposes          
                DailyTableList.append(deepcopy(DailyTable))
                DailyTable.clear_rows()
    WeeklyTable.add_row(DailyTableList)
    DailyTableList.clear()
print(WeeklyTable)

疑问：是否必须使用xlsxwriter原生API？之前尝试过但操作繁琐，想确认是否为最优方案。

为什么startrow/startcol无效？ #

pandas的to_excel默认会重置工作表的起始位置，即便指定startrow/startcol，若未正确处理工作簿状态（比如未用追加模式或未加载已有工作簿），仍会覆盖原有内容。xlsxwriter引擎不支持追加模式，改用openpyxl引擎可解决此问题。

实现目标格式的两种方案 #

方案1：优化pandas写入逻辑，精准控制位置 #

无需直接使用xlsxwriter原生API，通过openpyxl引擎加载已有工作簿，动态计算写入位置，确保每日内容写入对应区域：

import pandas as pd
from openpyxl import load_workbook

path = "workout_plan.xlsx"
# 初始化空文件避免加载报错
pd.DataFrame().to_excel(path, index=False)

for weekindex, week in enumerate(WeekList):
    sheet_name = f"Week {weekindex+1}"
    # 加载已有工作簿，启用追加模式
    book = load_workbook(path)
    writer = pd.ExcelWriter(path, engine='openpyxl', mode='a', if_sheet_exists='replace')
    writer.book = book
    
    start_col = 0  # 每周从第1列开始
    for dayindex, day in enumerate(week):
        # 合并当天所有训练项目为单个DataFrame
        day_df = pd.concat([exercise for exercise in week[day]], ignore_index=True)
        # 写入到指定列，第1行留作日期标题
        day_df.to_excel(writer, sheet_name=sheet_name, startrow=1, startcol=start_col, index=False, header=True)
        # 写入当天标题
        worksheet = writer.sheets[sheet_name]
        worksheet.cell(row=1, column=start_col+1).value = f"Day {dayindex+1}"
        # 更新下一天的起始列（预留空列分隔）
        start_col += len(day_df.columns) + 2
    
    writer.close()

此方案依赖openpyxl引擎的追加能力，能快速实现基础布局，代码复杂度较低。

方案2：使用xlsxwriter原生API（精细格式控制） #

若需要单元格内换行、边框、字体样式等美观效果，xlsxwriter原生API是最优选择，虽代码繁琐但可控性极强：

import xlsxwriter

path = "workout_plan.xlsx"
workbook = xlsxwriter.Workbook(path)

# 预定义格式：允许换行、带边框
wrap_format = workbook.add_format({'text_wrap': True, 'border': 1})
bold_format = workbook.add_format({'bold': True, 'border': 1})

for weekindex, week in enumerate(WeekList):
    worksheet = workbook.add_worksheet(f"Week {weekindex+1}")
    start_col = 0
    
    for dayindex, day in enumerate(week):
        # 写入日期标题
        worksheet.write(0, start_col, f"Day {dayindex+1}", bold_format)
        # 组装当天训练内容（表头+每行数据，用制表符分隔列，换行分隔行）
        content_lines = ["\t".join(["Exercise", "Sets", "Reps", "PercentageOfOneRepMax", "INOL"])]
        for exercise in week[day]:
            # 提取单行数据并转为字符串
            row_vals = [str(val) for val in exercise.iloc[0].values]
            content_lines.append("\t".join(row_vals))
        # 合并为带换行的单元格内容
        day_content = "\n".join(content_lines)
        # 写入单元格并应用换行格式
        worksheet.write(1, start_col, day_content, wrap_format)
        # 调整列宽适配内容
        worksheet.set_column(start_col, start_col, 45)
        # 预留空列分隔不同日期
        start_col += 2

workbook.close()

这种方式完全复刻PrettyTable的单元格内列表效果，还可自定义字体、颜色、对齐方式等格式。

总结 #

• 仅需基础布局：选择方案1，用openpyxl优化pandas写入逻辑，快速实现需求
• 需要美观格式：选择方案2，xlsxwriter原生API能提供完全的格式控制，是最优解

内容的提问来源于stack exchange，提问作者Nimble Capricorn