背景：被周报折磨的每个周一
我目前在一家电商公司负责部分业务的数据支持。我们团队每周一上午都要出一份销售周报，数据源是运营同事从后台导出的十几个Excel文件，每个文件对应一个销售渠道，比如天猫、京东、抖音等等。
我的任务就是把这些文件里的数据汇总到一张总表里，然后做一些简单的计算，比如各渠道销售额占比、环比增长等。听起来不复杂，对吧？但实际做起来特别磨人。每个文件的格式虽然大体一致，但总有细微差别：有的表头在第二行，有的在第三行；有的“销售额”列叫“GMV”；最头疼的是，有些文件里为了美观，用了大量的合并单元格。
手动操作流程是这样的：打开第一个文件，找到数据区域，复制，粘贴到总表；打开第二个文件，发现表头对不上，得手动调整列顺序；再打开第三个，遇到合并单元格，复制过来格式全乱了……一套流程下来，顺利的话也要一个半小时，要是中途分心贴错了行，又得从头检查。每个周一上午，我就像个没有感情的“Ctrl+C/V”机器。
我忍了三个月，终于在一个数据出错被领导点出后下定决心：必须用Python把这个过程自动化，把时间还给更有价值的分析工作。
问题分析：为什么不是pandas？
一开始，我理所当然地想到了pandas。pd.read_excel()和df.to_excel()多简单啊。我快速写了个脚本：
import pandas as pd
import os
all_data = []
folder_path = './sales_reports'
for file in os.listdir(folder_path):
if file.endswith('.xlsx'):
df = pd.read_excel(os.path.join(folder_path, file))
all_data.append(df)
result = pd.concat(all_data, ignore_index=True)
result.to_excel('weekly_summary.xlsx', index=False)
跑了一下，直接报错，而且问题一大堆：
- 表头位置不一致：
pandas默认把第一行当表头，但有的文件第一行是标题“XX渠道销售数据”，第二行才是真正的列名。导致读进来的DataFrame列名乱七八糟。 - 合并单元格灾难：
pandas会直接把合并单元格的值放在左上角单元格，其他位置变成NaN。当我按行复制时，很多关键数据就丢失了。 - 格式丢失：我需要保留原文件中的数字格式（比如金额的千位分隔符）、字体颜色（标红的下滑数据）等，
pandas完全做不到。 - 写入新问题：用
pandas生成的Excel，有时候列宽异常，还得手动调整，并没有完全“自动化”。
我意识到，pandas适合处理“纯数据”，但对于这种需要保持原有格式、结构不那么规范的Excel文件，有点力不从心。我需要一个能更精细操作Excel单元格的库。
核心实现：选择openpyxl与设计流程
搜索之后，我锁定了openpyxl。它是一个专门读写.xlsx格式的库，可以精确到单元格级别进行读写，还能处理样式。虽然不能读老旧的.xls格式，但我们公司早就全面升级了，所以没问题。
我的自动化脚本需要完成以下几个核心步骤：
- 智能定位数据：自动跳过表头，找到每个文件数据开始的真实位置。
- 破解合并单元格：将合并区域的值“填充”到每一个单元格，保证数据不丢失。
- 统一列映射：建立渠道列名与总表标准列名的映射关系，比如把“GMV”、“销售金额”都映射到“销售额”。
- 带格式写入：将数据连同基本的格式（字体、边框、数字格式）写入总表。
- 简单计算：在总表中自动计算合计、占比等。
下面我就分步拆解实现过程。
1. 智能定位数据起始行
这是第一个关键点。我观察发现，所有有效数据的表头行都包含“日期”这个关键词。所以，我的策略是：遍历文件的前10行（足够覆盖所有情况），找到第一个包含“日期”的单元格所在的行，那一行就是表头，下一行就是数据起始行。
from openpyxl import load_workbook
def find_data_start_row(ws, header_keyword="日期"):
"""
在工作表中查找包含指定关键词的表头行
返回数据起始行号（即表头行号+1）
"""
# 通常数据不会超过前20行开始
for row in ws.iter_rows(min_row=1, max_row=20, max_col=10):
for cell in row:
if cell.value and header_keyword in str(cell.value):
# 找到关键词，返回下一行作为数据开始
return cell.row + 1
# 如果没找到，默认从第2行开始（假设第一行是表头）
return 2
这里有个坑：cell.value可能是None，直接进行in判断会报错，所以必须先判断cell.value是否存在，再转换成字符串进行查找。
2. 破解合并单元格并建立列映射
这是最棘手的部分。openpyxl的worksheet.merged_cells.ranges属性可以获取所有合并单元格的范围。我的思路是：先记录下每个合并范围的值，然后在读取数据时，如果遇到属于合并范围的单元格，就使用记录的值。
同时，我需要建立一个列名映射字典。我手动定义了一个标准列名列表，然后根据每个文件表头行的内容进行模糊匹配。
def get_column_mapping(ws, header_row_idx):
"""
根据表头行，建立列索引到标准列名的映射。
例如：{1: '日期', 2: '销售额', ...}
"""
# 标准列名（总表使用的）
standard_headers = ['日期', '渠道', '商品ID', '商品名称', '销售额', '订单量']
mapping = {}
header_row = ws[header_row_idx]
for cell in header_row:
if cell.value:
header_text = str(cell.value).strip()
# 模糊匹配：如果标准列名是当前表头的一部分，或反之，则匹配
for std_header in standard_headers:
if std_header in header_text or header_text in std_header:
mapping[cell.column] = std_header # cell.column是列索引，如1,2,3
break # 匹配到一个就跳出内层循环
return mapping
注意这个细节：cell.column返回的是从1开始的列索引（整数），而不是字母。这比用column_index_from_string转换更方便。
处理合并单元格的读取逻辑，我把它整合到了遍历数据的函数里：
def read_sheet_data(ws, data_start_row, col_mapping, merged_cell_values):
"""
从指定行开始读取数据，应用列映射，并处理合并单元格值。
返回一个列表，每个元素是一行数据的字典。
"""
data = []
# 获取最大行和最大列（有数据的区域）
max_row = ws.max_row
max_column = ws.max_column
for row in range(data_start_row, max_row + 1):
row_data = {v: None for v in col_mapping.values()} # 用标准列名初始化字典
for col_idx, std_header in col_mapping.items():
cell = ws.cell(row=row, column=col_idx)
cell_value = cell.value
# 关键！处理合并单元格：如果当前单元格在合并范围内，则取合并区域的值
for merged_range in merged_cell_values:
if cell.coordinate in merged_range:
cell_value = merged_cell_values[merged_range]
break
row_data[std_header] = cell_value
# 如果一行数据全为空，则跳过（可能是末尾的空行）
if any(row_data.values()):
# 补充渠道信息（可以从文件名解析，这里简化处理）
row_data['渠道'] = os.path.basename(ws.parent.filepath).split('_')[0]
data.append(row_data)
return data
3. 带格式写入总表
数据都读出来并统一格式后，写入就相对简单了。我用openpyxl创建一个新的工作簿，先写入标准表头，并设置简单的样式（加粗、居中、背景色）。然后逐行写入数据，给数值列加上千位分隔符的数字格式。
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
def write_summary(data_list, output_path='周报汇总.xlsx'):
"""将整理好的数据列表写入新的Excel文件"""
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "销售周报汇总"
# 定义标准表头
headers = ['日期', '渠道', '商品ID', '商品名称', '销售额', '订单量']
# 设置表头样式
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
alignment = Alignment(horizontal="center", vertical="center")
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
top=Side(style='thin'), bottom=Side(style='thin'))
# 写入表头
for col_idx, header in enumerate(headers, start=1):
cell = ws.cell(row=1, column=col_idx, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = alignment
cell.border = thin_border
# 写入数据
row_idx = 2
for row_data in data_list:
for col_idx, header in enumerate(headers, start=1):
cell = ws.cell(row=row_idx, column=col_idx, value=row_data.get(header))
cell.border = thin_border
# 给销售额和订单量列设置数字格式
if header in ['销售额', '订单量'] and isinstance(cell.value, (int, float)):
if header == '销售额':
cell.number_format = '#,##0.00' # 千位分隔符，两位小数
else:
cell.number_format = '#,##0'
row_idx += 1
# 自动调整列宽（近似）
for col in ws.columns:
max_length = 0
column_letter = get_column_letter(col[0].column)
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = min(max_length + 2, 50) # 设置一个最大宽度
ws.column_dimensions[column_letter].width = adjusted_width
wb.save(output_path)
print(f"汇总完成！文件已保存至：{output_path}")
这里有个坑：openpyxl的自动调整列宽ws.column_dimensions[column_letter].bestFit并不总是可靠，我采用了计算每列最大字符长度的方法，虽然简单但效果不错。注意要捕获str(cell.value)的异常，因为cell.value可能是None。
4. 串联主流程与简单计算
最后，我把所有功能串联起来，并在写入数据后，在总表末尾添加两行：一行计算各渠道销售总额，一行计算占比。
# 在主函数中，读取所有文件数据后
all_data = []
for file_path in excel_files:
wb = load_workbook(file_path, data_only=True) # data_only=True只读值，不读公式
ws = wb.active
ws.filepath = file_path # 给ws对象加个属性，方便后面取文件名
# 找到表头行和数据起始行
header_row_idx = find_data_start_row(ws) - 1
data_start_row = header_row_idx + 1
# 获取列映射
col_mapping = get_column_mapping(ws, header_row_idx)
if not col_mapping:
print(f"警告：在文件 {file_path} 中未找到匹配的表头，跳过。")
continue
# 预处理合并单元格的值
merged_cell_values = {}
for merged_range in ws.merged_cells.ranges:
# 获取合并区域左上角单元格的值
top_left_cell = ws[merged_range.min_row][merged_range.min_col - 1] # 注意索引转换
merged_cell_values[merged_range] = top_left_cell.value
# 读取数据
sheet_data = read_sheet_data(ws, data_start_row, col_mapping, merged_cell_values)
all_data.extend(sheet_data)
# 写入汇总文件
write_summary(all_data)
# 在write_summary函数内部，数据写入后，可以添加汇总行
# ... 数据写入循环之后 ...
summary_row = row_idx + 1
ws.cell(row=summary_row, column=4, value="渠道总计").font = Font(bold=True)
# 使用公式计算每个渠道的销售额总和（假设渠道名列是B，销售额列是E）
# 这里简化，实际可以按渠道分组计算
ws.cell(row=summary_row, column=5, value=f"=SUMIF(B2:B{row_idx}, B{summary_row}, E2:E{row_idx})")
完整代码
"""
Excel销售周报自动化汇总脚本
作者：实战踩坑记录
功能：自动读取多个渠道销售Excel，统一格式后汇总至一个文件。
"""
import os
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
def find_data_start_row(ws, header_keyword="日期"):
"""定位数据起始行"""
for row in ws.iter_rows(min_row=1, max_row=20, max_col=10):
for cell in row:
if cell.value and header_keyword in str(cell.value):
return cell.row + 1
return 2
def get_column_mapping(ws, header_row_idx):
"""建立列索引到标准列名的映射"""
standard_headers = ['日期', '渠道', '商品ID', '商品名称', '销售额', '订单量']
mapping = {}
header_row = ws[header_row_idx]
for cell in header_row:
if cell.value:
header_text = str(cell.value).strip()
for std_header in standard_headers:
if std_header in header_text or header_text in std_header:
mapping[cell.column] = std_header
break
return mapping
def read_sheet_data(ws, data_start_row, col_mapping, merged_cell_values):
"""读取单文件数据，处理合并单元格"""
data = []
max_row = ws.max_row
for row in range(data_start_row, max_row + 1):
row_data = {v: None for v in col_mapping.values()}
for col_idx, std_header in col_mapping.items():
cell = ws.cell(row=row, column=col_idx)
cell_value = cell.value
# 处理合并单元格
for merged_range, merged_value in merged_cell_values.items():
if cell.coordinate in merged_range:
cell_value = merged_value
break
row_data[std_header] = cell_value
if any(row_data.values()):
# 简单从文件名提取渠道名，可根据实际情况修改
filename = os.path.basename(ws.parent.filepath)
channel = filename.split('_')[0] if '_' in filename else filename.replace('.xlsx', '')
row_data['渠道'] = channel
data.append(row_data)
return data
def write_summary(data_list, output_path='周报汇总.xlsx'):
"""写入汇总文件并添加格式"""
wb = Workbook()
ws = wb.active
ws.title = "销售周报汇总"
headers = ['日期', '渠道', '商品ID', '商品名称', '销售额', '订单量']
# 样式定义
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
align_center = Alignment(horizontal="center", vertical="center")
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
top=Side(style='thin'), bottom=Side(style='thin'))
# 写表头
for col_idx, header in enumerate(headers, start=1):
cell = ws.cell(row=1, column=col_idx, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = align_center
cell.border = thin_border
# 写数据
row_idx = 2
for row_data in data_list:
for col_idx, header in enumerate(headers, start=1):
cell = ws.cell(row=row_idx, column=col_idx, value=row_data.get(header))
cell.border = thin_border
if header in ['销售额', '订单量'] and isinstance(cell.value, (int, float)):
cell.number_format = '#,##0.00' if header == '销售额' else '#,##0'
row_idx += 1
# 调整列宽
for col in ws.columns:
max_len = 0
col_letter = get_column_letter(col[0].column)
for cell in col:
try:
cell_len = len(str(cell.value))
if cell_len > max_len:
max_len = cell_len
except:
pass
adjusted_width = min(max_len + 2, 50)
ws.column_dimensions[col_letter].width = adjusted_width
wb.save(output_path)
print(f"✅ 汇总完成！文件已保存至：{output_path}")
def main():
# 配置路径
reports_folder = './sales_reports' # 存放所有渠道Excel文件的文件夹
output_file = './周报汇总.xlsx'
if not os.path.exists(reports_folder):
print(f"错误：文件夹 '{reports_folder}' 不存在。")
return
all_data = []
excel_files = [os.path.join(reports_folder, f) for f in os.listdir(reports_folder)
if f.endswith(('.xlsx', '.xlsm'))]
if not excel_files:
print("未找到Excel文件。")
return
for file_path in excel_files:
print(f"正在处理：{os.path.basename(file_path)}")
try:
wb = load_workbook(file_path, data_only=True)
ws = wb.active
ws.parent.filepath = file_path # 临时存储路径
header_row_idx = find_data_start_row(ws) - 1
data_start_row = header_row_idx + 1
col_mapping = get_column_mapping(ws, header_row_idx)
if not col_mapping:
print(f" ⚠️ 跳过，未识别到表头。")
continue
# 处理合并单元格
merged_values = {}
for merged_range in ws.merged_cells.ranges:
top_left_cell = ws[merged_range.min_row][merged_range.min_col - 1]
merged_values[merged_range] = top_left_cell.value
sheet_data = read_sheet_data(ws, data_start_row, col_mapping, merged_values)
all_data.extend(sheet_data)
print(f" ✅ 读取到 {len(sheet_data)} 行数据。")
except Exception as e:
print(f" ❌ 处理文件时出错：{e}")
if all_data:
write_summary(all_data, output_file)
print(f"总计处理 {len(excel_files)} 个文件，合并 {len(all_data)} 行数据。")
else:
print("未读取到任何有效数据。")
if __name__ == "__main__":
main()
踩坑记录
-
data_only=True的误解：一开始我没加这个参数，结果有些单元格值是公式（如=SUM(A1:A10)），读出来就是公式字符串本身，而不是计算结果。加上data_only=True后，openpyxl会读取上次Excel保存时计算好的值。但要注意，如果文件从未被Excel计算保存过，读出的公式值可能是None。 -
合并单元格的坐标判断：
cell.coordinate返回的是字符串（如'A1'），而merged_range是一个CellRange对象。我最初直接用cell.coordinate == merged_range，永远为False。正确做法是使用cell.coordinate in merged_range，CellRange对象支持in操作符来判断坐标是否在其范围内。 -
列索引与列字母的混淆：
openpyxl中，cell.column返回整数索引（1-based），cell.column_letter返回字母。在遍历和存储映射关系时，用整数索引更不容易出错。但在设置列宽ws.column_dimensions[column_letter]时，又必须用字母。这个转换需要用get_column_letter()函数。 -
中文路径或文件名报错：在Windows上，如果文件路径或工作表名称包含中文，有时会报编码相关的错误。一个稳妥的做法是使用
os.path模块来构建路径，并确保Python脚本文件本身保存为UTF-8编码。如果问题依旧，可以尝试在文件路径字符串前加r（原始字符串）防止转义。
小结
这个自动化脚本把我每周一上午的“体力活”压缩到了3分钟以内，而且完全避免了手动操作可能带来的错误。核心收获是：处理非标准格式的Excel文件，openpyxl这种单元格级操作的库比pandas更灵活、更强大。下一步可以考虑加入邮件自动发送功能，或者用PyInstaller打包成可执行文件，分享给不会编程的同事使用。
