使用Python Pandas与OpenPyXL时Excel第三工作表无法显示的问题求助

问题根源分析 #

你的代码存在三个核心问题，导致Summary工作表无法成功创建：

1. 文件覆盖冲突：使用pd.ExcelWriter写入filtered_test_with_pivot.xlsx时，会创建全新文件，直接覆盖了之前用openpyxl加载并修改的filtered_test.xlsx中的所有操作（包括新建的Summary工作表）。
2. 对象类型混淆：pd.pivot_table返回的是pandas DataFrame对象，而非openpyxl的PivotTable对象。你后续调用的location、pivot_field_axis、add_pivot等方法都是openpyxl透视表专属方法，调用在DataFrame上会直接报错，导致代码中断，Summary表的逻辑根本没机会执行。
3. 工作簿操作脱节：加载filtered_test.xlsx后未写入任何数据就创建工作表，后续的透视表操作却写入了另一个新文件，两个工作簿操作完全独立，数据和工作表没有关联。

修正后的完整代码 #

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl import Workbook

# 加载CSV数据
df = pd.read_csv('E:/test.csv')

# 筛选所需列并处理空值
required_columns = ["Last Updated Time", "Device", "Site", "Time Difference", "Device Type", "Last Alarm Received Time"]
filtered_df = df[required_columns]
filtered_df['Last Alarm Received Time'] = filtered_df['Last Alarm Received Time'].fillna('No Alarms')

# 生成两个透视表的数据源
filter_values_pivot1 = [" second ago", " seconds ago", "minute ago", "minutes ago", "hour ago", " hours ago"]
filtered_df_pivot1 = filtered_df[~filtered_df["Last Alarm Received Time"].str.contains('|'.join(filter_values_pivot1))]

filter_values_pivot2 = ["0 s", "1 s", "2 s", "3 s"]
filtered_df_pivot2 = filtered_df[~filtered_df["Time Difference"].isin(filter_values_pivot2)]

# 创建或加载目标工作簿
try:
    workbook = load_workbook('E:/filtered_test_with_pivot.xlsx')
except FileNotFoundError:
    workbook = Workbook()
    # 删除默认生成的Sheet
    workbook.remove(workbook.active)

# 写入第一个透视表数据到对应工作表
sheet1_name = "Devices with No Alarms > 24hrs"
if sheet1_name in workbook.sheetnames:
    del workbook[sheet1_name]
worksheet1 = workbook.create_sheet(title=sheet1_name)
pivot_table1 = pd.pivot_table(filtered_df_pivot1, index=["Last Updated Time", "Device","Device Type", "Site", "Last Alarm Received Time"], aggfunc='count')
pivot_table1 = pivot_table1.drop('Time Difference', axis=1)
# 将DataFrame写入工作表（含表头）
df_to_write1 = pivot_table1.reset_index()
for r_idx, row in enumerate(df_to_write1.values, start=1):
    for c_idx, val in enumerate(row, start=1):
        worksheet1.cell(row=r_idx, column=c_idx, value=val)

# 写入第二个透视表数据到对应工作表
sheet2_name = "Devices with Time Difference > 3s"
if sheet2_name in workbook.sheetnames:
    del workbook[sheet2_name]
worksheet2 = workbook.create_sheet(title=sheet2_name)
pivot_table2 = pd.pivot_table(filtered_df_pivot2, index=["Last Updated Time", "Device", "Device Type", "Site", "Time Difference"], aggfunc='count')
pivot_table2 = pivot_table2.drop('Last Alarm Received Time', axis=1)
# 将DataFrame写入工作表（含表头）
df_to_write2 = pivot_table2.reset_index()
for r_idx, row in enumerate(df_to_write2.values, start=1):
    for c_idx, val in enumerate(row, start=1):
        worksheet2.cell(row=r_idx, column=c_idx, value=val)

# 创建并写入Summary工作表
sheet3_name = "Summary"
if sheet3_name in workbook.sheetnames:
    del workbook[sheet3_name]
worksheet3 = workbook.create_sheet(title=sheet3_name)
# 添加表头和数据
worksheet3.append(["Date", "Total Devices", "Devices with No Alarms >24hrs", "Devices with Time Difference >3s"])
worksheet3.append([pd.Timestamp.now().strftime("%Y-%m-%d"), len(df), len(filtered_df_pivot1), len(filtered_df_pivot2)])
# 添加表格样式
max_row = worksheet3.max_row
max_col = worksheet3.max_column
data_range3 = f"A1:{get_column_letter(max_col)}{max_row}"
worksheet3.add_table(data_range3, table_style="TableStyleMedium9")

# 保存工作簿
workbook.save('E:/filtered_test_with_pivot.xlsx')

关键修改说明 #

• 统一工作簿操作：所有工作表的创建、数据写入都基于同一个openpyxl工作簿对象，彻底避免文件覆盖问题。
• 修正透视表处理：直接将pandas生成的DataFrame数据逐行写入工作表，替代错误的openpyxl透视表方法调用（若需Excel原生透视表，可单独使用openpyxl的PivotTable类实现）。
• 容错处理：增加工作簿不存在时的创建逻辑，以及重复工作表的删除处理，避免命名冲突。
• 完整执行流程：确保Summary表的代码逻辑能正常执行，不会因前面的错误中断。

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