如何将多个数据透视表按区域追加到Excel已有多工作表且不覆盖原有内容

我有多个配送与地址的关联数据，为5个区域分别生成了数据透视表列表，在Jupyter Notebook中使用for循环可按需求上下依次展示每个透视表。但我需要将这些透视表保存到已有的expedition.xlsx文件中，该文件内置北部、东北部、中西部、东南部、南部共5个工作表，每个工作表D1单元格已预置标题。
我多次尝试后要么仅保存最后生成的透视表，要么覆盖原有内容删除其他工作表，仅保留北部工作表。我设置了判断规则：检查D列单元格，若已填充则向下跳行，空单元格处写入对应区域的DataFrame，期望每个工作表内的多个透视表间隔2行上下排列。我使用openpyxl测试时可以在不删除其他工作表的前提下在空行写入示例文本，但无法写入透视表，需要实现按区域对应工作表追加写入多个上下排列的透视表。

import pandas as pd
import openpyxl
 
writer = pd.ExcelWriter("expedition.xlsx", engine='xlsxwriter')
 
# 为每个区域创建空的基础表
pivot1 = pd.DataFrame({'北部地区配送清单':  [' ']})
pivot2 = pd.DataFrame({'东北部地区配送清单':  [' ']})
pivot3 = pd.DataFrame({'中西部地区配送清单':  ['']})
pivot4 = pd.DataFrame({'东南部地区配送清单':  [' ']})
pivot5 = pd.DataFrame({'南部地区配送清单':  [' ']})
 
# 为每个区域创建工作表，标题放在D1单元格
pivot1.to_excel(writer, sheet_name='北部', index=False, startcol=3, freeze_panes=(1,0))
pivot2.to_excel(writer, sheet_name='东北部', index=False, startcol=3, freeze_panes=(1,0))
pivot3.to_excel(writer, sheet_name='中西部', index=False, startcol=3, freeze_panes=(1,0))
pivot4.to_excel(writer, sheet_name='东南部', index=False, startcol=3, freeze_panes=(1,0))
pivot5.to_excel(writer, sheet_name='南部', index=False, startcol=3, freeze_panes=(1,0))
 
writer.close()
 
# 各区域的分支编码列表
norte = ['PA_BEL', 'TO_PMW', 'AC_RBR']
nordeste = ['AL_MCZ', 'PB_JPA', 'BA_SSA', 'RN_NAT', 'PE_REC', 'CE_FOR', 'MA_IMP', 'MA_THE', 'PI_THE', 'BA_FEC']
centro_oeste = ['GO_GYN', 'DF_BSB', 'GO_BSB', 'MT_CGB', 'MS_CGR']
sudeste = ['ES_SRR', 'MG_BHZ', 'SP_PNM', 'SP_JDI', 'RJ_RIO', 'MG_UDI']
sul = ['RS_POA', 'PR_CWB', 'SC_CCM', 'RS_RIA', 'SC_FLN']
 
 
# 北部区域示例代码
if len(norte) > 0:
    frete_expresso_norte = 0
    for filial in norte:
        # 为每个分支生成透视表
        pivot1 = df[df.配送分支 == filial].pivot_table(
                         index=['业务单元', '收货区域', '配送分支', '收货方名称', '收货城市', '配送方式'],
                         values=['数量','体积', '托盘数', '净值'], aggfunc='sum',
                         margins=True)
        # 重排列并重命名总计行
        ordem_das_colunas = ['数量', '体积', '托盘数', '净值']
        pivot1 = pivot1[ordem_das_colunas].rename(index=dict(All='配送总计'))    
        # 计算子分类判断是否走加急配送
        total_palete_norte = pivot1.groupby('配送分支')['托盘数'].sum()[1]
        total_net_value_norte = pivot1.groupby('配送分支')['净值'].sum()[1]
        # 此处需要写入透视表到对应工作表的空行位置
        if total_palete_norte >= 29 or total_net_value_norte >= 2500000:
            frete_expresso_norte = frete_expresso_norte + 1
        display(pivot1)
        
else:
    expedicao_norte = '北部区域无待配送数据'
 
 
# 未完工的写入逻辑
import openpyxl
 
n = 0 # 0=北部/1=东北部/2=中西部/3=东南部/4=南部
planilha_cx = openpyxl.load_workbook("expedition.xlsx")
folhas = planilha_cx.sheetnames
folha = planilha_cx[folhas[n]]
 
coluna = 4  # 对应D列
linha = 1  # 从第一行开始
celula = folha.cell(linha, coluna).value
 
while celula != None:  # 遍历到D列空单元格为止
    celula = folha.cell(linha, coluna).value
    if celula == None:
        linha = linha + 2
        folha.cell(row=linha, column=1).value = '测试文本'  # 仅能写入文本，无法写入透视表
        planilha_cx.save("expedition.xlsx")
        break
    else:
        linha = linha + 1

问题原因 #

1. 初始使用xlsxwriter引擎创建ExcelWriter默认是覆盖模式，会清空原有文件的所有工作表
2. openpyxl原生仅支持写入单个单元格内容，无法直接写入DataFrame格式的透视表
3. 没有统一处理写入起始行的计算逻辑，导致每次写入位置错误

修正后完整代码 #

import pandas as pd
import openpyxl

# ---------------------- 初始化配置 ----------------------
# 区域配置：[工作表名, 分支编码列表]
region_config = [
    ['北部', ['PA_BEL', 'TO_PMW', 'AC_RBR']],
    ['东北部', ['AL_MCZ', 'PB_JPA', 'BA_SSA', 'RN_NAT', 'PE_REC', 'CE_FOR', 'MA_IMP', 'MA_THE', 'PI_THE', 'BA_FEC']],
    ['中西部', ['GO_GYN', 'DF_BSB', 'GO_BSB', 'MT_CGB', 'MS_CGR']],
    ['东南部', ['ES_SRR', 'MG_BHZ', 'SP_PNM', 'SP_JDI', 'RJ_RIO', 'MG_UDI']],
    ['南部', ['RS_POA', 'PR_CWB', 'SC_CCM', 'RS_RIA', 'SC_FLN']]
]
pivot_columns = ['数量', '体积', '托盘数', '净值']
file_path = "expedition.xlsx"

# 若文件不存在先初始化带标题的工作表（已存在可跳过这段）
# try:
#     open(file_path)
# except FileNotFoundError:
#     with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
#         for region_name, _ in region_config:
#             title_df = pd.DataFrame({f'{region_name}地区配送清单': ['']})
#             title_df.to_excel(writer, sheet_name=region_name, index=False, startcol=3, freeze_panes=(1,0))

# ---------------------- 批量写入透视表 ----------------------
for region_name, filial_list in region_config:
    if not filial_list:
        print(f'{region_name}无待配送数据')
        continue
    express_count = 0
    # 打开工作簿获取当前工作表的D列最后非空行，确定写入起始行
    wb = openpyxl.load_workbook(file_path)
    ws = wb[region_name]
    # 找D列最后一个有值的行
    last_row = 1
    for row in range(1, ws.max_row + 2):
        if ws.cell(row=row, column=4).value is None:
            last_row = row + 2 # 间隔2行
            break
    wb.close()
    
    # 循环写入每个分支的透视表
    for filial in filial_list:
        # 生成透视表
        pivot_df = df[df.配送分支 == filial].pivot_table(
            index=['业务单元', '收货区域', '配送分支', '收货方名称', '收货城市', '配送方式'],
            values=['数量','体积', '托盘数', '净值'], 
            aggfunc='sum',
            margins=True
        )
        pivot_df = pivot_df[pivot_columns].rename(index=dict(All='配送总计'))
        
        # 使用overlay模式追加写入，不覆盖原有内容
        with pd.ExcelWriter(
            file_path, 
            engine='openpyxl', 
            mode='a', 
            if_sheet_exists='overlay' # 核心参数：在原有工作表基础上追加，不覆盖
        ) as writer:
            pivot_df.to_excel(
                writer, 
                sheet_name=region_name, 
                startrow=last_row, # 从计算好的空行开始写
                index=True # 透视表的索引列要保留
            )
        
        # 判断是否加急
        total_pallet = pivot_df.groupby('配送分支')['托盘数'].sum()[1]
        total_value = pivot_df.groupby('配送分支')['净值'].sum()[1]
        if total_pallet >= 29 or total_value >= 2500000:
            express_count += 1
        
        # 更新下一个透视表的写入起始行：当前透视表行数 + 间隔2行
        last_row += len(pivot_df) + 2 + 1 # +1是表头行
        display(pivot_df)

关键说明 #

• if_sheet_exists='overlay'是pandas 1.4.0及以上版本支持的参数，可保证写入时不覆盖同工作表下其他已有内容，也不会删除其他工作表
• 每次写入前通过openpyxl计算当前工作表的最后非空行，加上2行间隔后作为下一次写入的起始位置，保证多个透视表上下排列、间隔2行
• 把5个区域的逻辑统一到循环中，避免重复代码，降低出错概率

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