单Excel多工作表存储分品类、分月份报表，手动拆分/合并Sheet效率极低，openpyxl引擎实现批量新建、读取、写入多Sheet，自动化批量产出多表单报表。场景：完整订单总表按商品品类拆分为独立Sheet写入同一Excel，同时读取多Sheet汇总全量数据做大盘统计。核心知识点：ExcelWriter多表单写入、sheet_name批量循环、openpyxl引擎复用文件句柄、多Sheet数据拼接汇总。① 字段含义说明
② 生成测试总表数据
import pandas as pdimport numpy as npnp.random.seed(101)df_all = pd.DataFrame({ "order_id": range(1, 301), "category": np.random.choice(["数码","家居","美妆"], 300), "sale_date": pd.date_range("2026-04-01", periods=300, freq="D").astype(str), "amount": np.random.uniform(80, 6500, 300).round(2), "buy_num": np.random.randint(1, 18, 300)})df_all.to_excel("order_total_raw.xlsx", index=False)print("全量订单总表测试数据生成完成")
③ 核心多Sheet读写代码
import pandas as pd# 读取原始总表df_total = pd.read_excel("order_total_raw.xlsx")category_list = df_total["category"].unique()# 1. 批量写入多Sheet：每个品类单独一个工作表with pd.ExcelWriter("category_split_report.xlsx", engine="openpyxl") as writer: for cate in category_list: sub_df = df_total[df_total["category"] == cate] sub_df.to_excel(writer, sheet_name=cate, index=False)print("按品类拆分多Sheet报表写入完成")# 2. 读取Excel内全部Sheet并合并汇总excel_file = pd.ExcelFile("category_split_report.xlsx")sheet_names = excel_file.sheet_namesmerge_list = []for sheet in sheet_names: sheet_df = pd.read_excel("category_split_report.xlsx", sheet_name=sheet) merge_list.append(sheet_df)df_merge_all = pd.concat(merge_list, ignore_index=True)# 大盘汇总指标total_income = df_merge_all["amount"].sum()total_order = len(df_merge_all)print(f"全品类总营收：{round(total_income,2)}，总订单数：{total_order}")print("\n合并后总表前6行：")print(df_merge_all.head(6))
结果展示
总结
使用ExcelWriter上下文管理器可复用文件句柄，一次性写入数十张Sheet，避免重复IO读写；循环读取多Sheet后concat合并，实现报表拆分与汇总双向自动化，是运营多表单报表标准自动化方案。
