展开查看具体步骤 →
- 准备Excel数据：创建
销售报表/ 文件夹，放入多个门店的销售Excel（产品名、数量、单价、日期等字段）
- 安装依赖：执行
pip install openpyxl pandas
- 批量合并脚本：创建
merge_report.py，自动遍历文件夹读取所有xlsx文件，合并为一个DataFrame
- 汇总统计：用pandas的
groupby 做产品维度汇总（总销量、总金额、订单数）
- 美化报表：用openpyxl设置表头样式（加粗+蓝色填充+居中）→ 输出带格式的
销售汇总报表.xlsx
▶ 在 Colab 运行
↗
import pandas as pd
import glob, os
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
# ===== 配置 =====
input_folder = r"销售报表"
output_file = "销售汇总报表.xlsx"
files = glob.glob(os.path.join(input_folder, "*.xlsx"))
print(f"📋 找到 {len(files)} 个文件")
all_data = []
for f in files:
df = pd.read_excel(f)
df['来源'] = os.path.basename(f)
all_data.append(df)
merged = pd.concat(all_data, ignore_index=True)
summary = merged.groupby('产品名称').agg(
总销量=('数量', 'sum'), 总金额=('金额', 'sum'), 订单数=('数量', 'count')
).reset_index()
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
merged.to_excel(writer, sheet_name='明细数据', index=False)
summary.to_excel(writer, sheet_name='汇总统计', index=False)
wb = load_workbook(output_file)
ws = wb['汇总统计']
for cell in ws[1]:
cell.font = Font(bold=True, color="FFFFFF", size=11)
cell.fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
cell.alignment = Alignment(horizontal="center")
wb.save(output_file)
print(f"🎉 报表已生成: {output_file}")