如何用openpyxl将Pandas透视表数据写入现有Excel工作表？

我来帮你搞定这个需求——你已经能通过openpyxl往现有Excel文件里写零散数据，但不知道怎么把Pandas生成的透视表精准填充到已有表头和索引的工作表中，对吧？下面给你两种实用方案，按需选择：

方案一：Openpyxl精准匹配写入（适合行顺序不确定的场景） #

这种方法会先定位现有工作表中每个site值对应的行号，再把透视表数据精准写入对应单元格，完全不用担心行顺序错位的问题。

import pandas as pd
import openpyxl as op

# ----------------------
# 1. 生成透视表（你的原有代码，保留即可）
# ----------------------
df2 = pd.read_csv("test_data.csv", encoding="latin-1")
df2['received'] = pd.to_datetime(df2['received'])
df2['sent'] = pd.to_datetime(df2['sent'])
pvt_all = df2.dropna(axis=0, how='all', subset=['received', 'sent'])\
    .pivot_table(index=['site'], values=['received','sent'],
                 aggfunc='count', margins=True, dropna=False)
pvt_all['to_send']= pvt_all['received'] - pvt_all['sent']
pvt_all = pvt_all[['received','sent','to_send']]

# ----------------------
# 2. 写入现有Excel工作表
# ----------------------
# 替换成你的目标Excel文件路径
target_wb_path = "你的现有Excel文件.xlsx"
# 替换成你要写入数据的工作表名称（比如'Sheet1'）
target_sheet_name = "目标工作表名称"

# 加载目标工作簿
wb = op.load_workbook(target_wb_path)
ws_target = wb[target_sheet_name]

# 第一步：建立site值到行号的映射（遍历A列找对应行）
site_to_row = {}
# 遍历A列的单元格（假设site索引在A列，从第2行开始）
for cell in ws_target['A']:
    cell_value = cell.value
    # 如果当前单元格的值在透视表的索引里，记录行号
    if cell_value in pvt_all.index:
        site_to_row[cell_value] = cell.row

# 第二步：遍历透视表，写入对应单元格
for site, row_data in pvt_all.iterrows():
    if site in site_to_row:
        row_num = site_to_row[site]
        # 写入对应列：received在B列，sent在C列，to_send在D列
        ws_target[f'B{row_num}'] = row_data['received']
        ws_target[f'C{row_num}'] = row_data['sent']
        ws_target[f'D{row_num}'] = row_data['to_send']

# 保存文件（建议先备份原文件，这里可以保存为新文件避免覆盖）
wb.save("test_updated.xlsx")

方案二：Pandas ExcelWriter快速写入（适合行顺序完全匹配的场景） #

如果你的透视表行顺序（2、3、4、5、All）和现有工作表的行顺序完全一致，用这个方法更简洁高效，一行代码就能搞定数据写入。

import pandas as pd

# ----------------------
# 1. 生成透视表（同方案一的代码）
# ----------------------
df2 = pd.read_csv("test_data.csv", encoding="latin-1")
df2['received'] = pd.to_datetime(df2['received'])
df2['sent'] = pd.to_datetime(df2['sent'])
pvt_all = df2.dropna(axis=0, how='all', subset=['received', 'sent'])\
    .pivot_table(index=['site'], values=['received','sent'],
                 aggfunc='count', margins=True, dropna=False)
pvt_all['to_send']= pvt_all['received'] - pvt_all['sent']
pvt_all = pvt_all[['received','sent','to_send']]

# ----------------------
# 2. 快速写入现有Excel
# ----------------------
with pd.ExcelWriter("你的现有Excel文件.xlsx", engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
    # startrow=1：从第2行开始写（跳过第1行的表头）
    # header=False：不写入透视表的表头（现有工作表已经有了）
    # index=False：不写入透视表的site索引（现有工作表已经有了）
    pvt_all.to_excel(writer, sheet_name='目标工作表名称', startrow=1, header=False, index=False)

注意事项 #

• 操作现有Excel前一定要备份原文件，避免误操作导致数据丢失。
• 方案一兼容性更强，不管现有工作表的行顺序如何，都能精准匹配写入；方案二更高效，但必须保证透视表和现有工作表的行顺序完全一致。
• 如果你的CSV文件是从网络获取的，直接把pd.read_csv里的路径替换成你本地保存的CSV路径即可。

内容的提问来源于stack exchange，提问作者MGB.py