xlsx-compare
SKILL.md
Excel 数据对比技能
概述
本技能用于对比两份 Excel 表格数据，自动识别差异并生成详细的对比报告和差异文件。适用于数据审核、版本对比、数据迁移验证等场景。
触发场景
当用户提到以下需求时，应使用此技能：
- 对比两个 Excel 文件的差异
- 找出两份数据表的不同之处
- 生成数据差异报告
- 验证数据迁移或转换的准确性
- 分析数据变化的原因和趋势
工作流程
第一步：理解需求
在开始对比前，确认以下信息：
- 两份 Excel 文件的路径
- 对比的粒度（整表对比、指定工作表、指定字段）
- 差异的容忍度（是否需要精确匹配，还是允许一定误差）
- 输出格式要求（差异文件、分析报告、或两者都要）
第二步：读取并分析数据结构
使用 pandas 读取两个 Excel 文件：
import pandas as pd
import numpy as np
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
# 读取 Excel 文件
file1_path = 'path/to/file1.xlsx'
file2_path = 'path/to/file2.xlsx'
# 获取所有工作表名称
excel1 = pd.ExcelFile(file1_path)
excel2 = pd.ExcelFile(file2_path)
print(f"文件 1 工作表：{excel1.sheet_names}")
print(f"文件 2 工作表：{excel2.sheet_names}")
第三步：逐表对比数据
对每个工作表进行对比分析：
def compare_dataframes(df1, df2, sheet_name, tolerance=0):
"""
对比两个 DataFrame，返回差异信息
"""
diff_info = {
'sheet': sheet_name,
'added_rows': [],
'removed_rows': [],
'modified_cells': [],
'structural_diff': False
}
# 检查行列数是否一致
if df1.shape != df2.shape:
diff_info['structural_diff'] = True
diff_info['file1_shape'] = df1.shape
diff_info['file2_shape'] = df2.shape
# 检查列名是否一致
if list(df1.columns) != list(df2.columns):
diff_info['columns_changed'] = True
diff_info['file1_columns'] = list(df1.columns)
diff_info['file2_columns'] = list(df2.columns)
# 精确匹配对比（逐单元格）
min_rows = min(len(df1), len(df2))
min_cols = min(len(df1.columns), len(df2.columns))
for i in range(min_rows):
for j in range(min_cols):
val1 = df1.iloc[i, j]
val2 = df2.iloc[i, j]
# 处理数值类型的容忍度
if isinstance(val1, (int, float)) and isinstance(val2, (int, float)):
if abs(val1 - val2) > tolerance:
diff_info['modified_cells'].append({
'row': i + 2, # Excel 行号（从 1 开始，加表头）
'column': chr(65 + j), # Excel 列号
'col_name': df1.columns[j],
'old_value': val1,
'new_value': val2
})
# 非数值类型精确匹配
elif val1 != val2:
# 处理 NaN 的情况
if not (pd.isna(val1) and pd.isna(val2)):
diff_info['modified_cells'].append({
'row': i + 2,
'column': chr(65 + j),
'col_name': df1.columns[j],
'old_value': val1,
'new_value': val2
})
# 检查新增的行
if len(df2) > len(df1):
diff_info['added_rows'] = list(range(len(df1) + 1, len(df2) + 1))
# 检查删除的行
if len(df1) > len(df2):
diff_info['removed_rows'] = list(range(len(df2) + 1, len(df1) + 1))
return diff_info
第四步：生成差异文件
创建一个新的 Excel 文件，标记所有差异：
def create_diff_excel(df1, df2, file1_name, file2_name, output_path):
"""
生成标记差异的 Excel 文件
"""
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
# 创建新工作簿
wb = Workbook()
summary_sheet = wb.active
summary_sheet.title = "差异摘要"
# 定义样式
added_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid") # 绿色 - 新增
removed_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid") # 红色 - 删除
modified_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid") # 黄色 - 修改
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
# 创建工作表对比结果
for sheet_idx, sheet_name in enumerate(df1.keys()):
if sheet_name in df2.keys():
diff_sheet = wb.create_sheet(title=f"{sheet_name}_对比")
df_old = df1[sheet_name]
df_new = df2[sheet_name]
# 写入表头
columns = list(df_old.columns)
for col_idx, col_name in enumerate(columns, 1):
cell = diff_sheet.cell(row=1, column=col_idx, value=col_name)
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal='center')
# 添加状态列
status_col = len(columns) + 1
diff_sheet.cell(row=1, column=status_col, value="状态")
diff_sheet.cell(row=1, column=status_col).font = header_font
diff_sheet.cell(row=1, column=status_col).fill = header_fill
# 写入数据并标记差异
max_rows = max(len(df_old), len(df_new))
for row_idx in range(max_rows):
status = "未变更"
# 写入旧数据
for col_idx, col_name in enumerate(columns, 1):
if row_idx < len(df_old):
value = df_old.iloc[row_idx, col_idx - 1]
else:
value = None
status = "新增"
cell = diff_sheet.cell(row=row_idx + 2, column=col_idx, value=value)
# 检查是否有差异
if row_idx < len(df_new):
new_value = df_new.iloc[row_idx, col_idx - 1]
if pd.notna(value) and pd.notna(new_value):
if isinstance(value, (int, float)) and isinstance(new_value, (int, float)):
if abs(value - new_value) > 0.0001:
cell.fill = modified_fill
status = "修改"
elif value != new_value and not (pd.isna(value) and pd.isna(new_value)):
cell.fill = modified_fill
status = "修改"
elif pd.notna(value):
cell.fill = removed_fill
status = "删除"
# 写入新数据（如果有新增列）
if row_idx < len(df_new) and len(df_new.columns) > len(columns):
for col_idx in range(len(columns) + 1, len(df_new.columns) + 1):
value = df_new.iloc[row_idx, col_idx - 1]
cell = diff_sheet.cell(row=row_idx + 2, column=col_idx, value=value)
cell.fill = added_fill
status = "修改" if status == "未变更" else status
# 写入状态
status_cell = diff_sheet.cell(row=row_idx + 2, column=status_col, value=status)
if status == "新增":
status_cell.fill = added_fill
elif status == "删除":
status_cell.fill = removed_fill
elif status == "修改":
status_cell.fill = modified_fill
# 调整列宽
for col in diff_sheet.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
if cell.value:
max_length = max(max_length, len(str(cell.value)))
adjusted_width = min(max_length + 2, 50)
diff_sheet.column_dimensions[column].width = adjusted_width
# 写入摘要信息
summary_sheet['A1'] = "Excel 数据对比报告"
summary_sheet['A1'].font = Font(bold=True, size=16)
summary_sheet['A2'] = f"文件 1: {file1_name}"
summary_sheet['A3'] = f"文件 2: {file2_name}"
summary_sheet['A5'] = "对比摘要"
summary_sheet['A5'].font = Font(bold=True)
summary_sheet.append(["工作表", "状态", "新增行数", "删除行数", "修改单元格数"])
summary_sheet['A6'].fill = header_fill
summary_sheet['A6'].font = header_font
wb.save(output_path)
return output_path
第五步：生成分析报告
创建详细的文本分析报告：
def generate_analysis_report(diff_results, output_path):
"""
生成数据分析报告
"""
report_lines = []
report_lines.append("=" * 60)
report_lines.append("Excel 数据对比分析报告")
report_lines.append("=" * 60)
report_lines.append("")
total_added = 0
total_removed = 0
total_modified = 0
for result in diff_results:
sheet_name = result['sheet']
added = len(result.get('added_rows', []))
removed = len(result.get('removed_rows', []))
modified = len(result.get('modified_cells', []))
total_added += added
total_removed += removed
total_modified += modified
report_lines.append(f"工作表：{sheet_name}")
report_lines.append("-" * 40)
if result.get('structural_diff'):
report_lines.append(f"⚠️ 结构性差异")
report_lines.append(f" 文件 1 维度：{result.get('file1_shape', 'N/A')}")
report_lines.append(f" 文件 2 维度：{result.get('file2_shape', 'N/A')}")
if result.get('columns_changed'):
report_lines.append(f"⚠️ 列结构变更")
report_lines.append(f" 文件 1 列：{result.get('file1_columns', [])}")
report_lines.append(f" 文件 2 列：{result.get('file2_columns', [])}")
report_lines.append(f"✓ 新增行数：{added}")
report_lines.append(f"✓ 删除行数：{removed}")
report_lines.append(f"✓ 修改单元格数：{modified}")
if modified > 0:
report_lines.append(f"\n修改详情（前 10 条）:")
for i, cell in enumerate(result['modified_cells'][:10], 1):
report_lines.append(f" {i}. {cell['col_name']} @ R{cell['row']}{cell['column']}: {cell['old_value']} → {cell['new_value']}")
if modified > 10:
report_lines.append(f" ... 还有 {modified - 10} 处修改")
report_lines.append("")
# 总结
report_lines.append("=" * 60)
report_lines.append("总体统计")
report_lines.append("=" * 60)
report_lines.append(f"总新增行数：{total_added}")
report_lines.append(f"总删除行数：{total_removed}")
report_lines.append(f"总修改单元格数：{total_modified}")
report_lines.append(f"总变更数：{total_added + total_removed + total_modified}")
report_lines.append("")
# 分析结论
report_lines.append("=" * 60)
report_lines.append("分析结论")
report_lines.append("=" * 60)
if total_added + total_removed + total_modified == 0:
report_lines.append("✓ 两份数据完全一致，无差异发现")
else:
if total_added > total_removed:
report_lines.append(f"• 文件 2 相比文件 1 净增 {total_added - total_removed} 行数据")
elif total_removed > total_added:
report_lines.append(f"• 文件 2 相比文件 1 净减 {total_removed - total_added} 行数据")
else:
report_lines.append(f"• 两份数据行数相同")
if total_modified > 0:
report_lines.append(f"• 共发现 {total_modified} 处数据修改")
report_lines.append(f"• 数据变更率：{(total_modified / max(total_added + total_removed + total_modified, 1)) * 100:.2f}%")
report_lines.append("\n建议操作:")
if total_added > 0:
report_lines.append(" 1. 核实新增数据的来源和准确性")
if total_removed > 0:
report_lines.append(" 2. 确认删除数据是否为预期操作")
if total_modified > 0:
report_lines.append(" 3. 审查关键业务数据的变更原因")
report_content = "\n".join(report_lines)
with open(output_path, 'w', encoding='utf-8') as f:
f.write(report_content)
return report_content
第六步：整合完整流程
def compare_excel_files(file1_path, file2_path, output_dir='.', tolerance=0):
"""
主函数：对比两个 Excel 文件并生成所有输出
"""
import os
from datetime import datetime
# 读取所有工作表
df1 = {}
df2 = {}
excel1 = pd.ExcelFile(file1_path)
excel2 = pd.ExcelFile(file2_path)
for sheet in excel1.sheet_names:
df1[sheet] = pd.read_excel(file1_path, sheet_name=sheet)
for sheet in excel2.sheet_names:
df2[sheet] = pd.read_excel(file2_path, sheet_name=sheet)
# 对比每个工作表
diff_results = []
all_sheets = set(df1.keys()) | set(df2.keys())
for sheet in all_sheets:
if sheet in df1 and sheet in df2:
result = compare_dataframes(df1[sheet], df2[sheet], sheet, tolerance)
diff_results.append(result)
elif sheet in df1:
diff_results.append({
'sheet': sheet,
'status': '仅在文件 1 中存在',
'added_rows': [],
'removed_rows': list(range(1, len(df1[sheet]) + 1)),
'modified_cells': []
})
else:
diff_results.append({
'sheet': sheet,
'status': '仅在文件 2 中存在',
'added_rows': list(range(1, len(df2[sheet]) + 1)),
'removed_rows': [],
'modified_cells': []
})
# 生成输出文件名
timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
file1_name = os.path.basename(file1_path)
file2_name = os.path.basename(file2_path)
diff_excel_path = os.path.join(output_dir, f'差异对比_{timestamp}.xlsx')
report_path = os.path.join(output_dir, f'分析报告_{timestamp}.txt')
# 生成差异文件
create_diff_excel(df1, df2, file1_name, file2_name, diff_excel_path)
# 生成分析报告
generate_analysis_report(diff_results, report_path)
return {
'diff_excel': diff_excel_path,
'report': report_path,
'summary': diff_results
}
# 使用示例
# result = compare_excel_files('data_v1.xlsx', 'data_v2.xlsx')
# print(f"差异文件：{result['diff_excel']}")
# print(f"分析报告：{result['report']}")
输出说明
1. 差异 Excel 文件
- 包含所有工作表的对比结果
- 使用颜色标记差异类型：
- 🟢 绿色：新增的数据行
- 🔴 红色：删除的数据行
- 🟡 黄色：修改的单元格
- 包含"差异摘要"工作表，提供整体统计
2. 分析报告（TXT 格式）
包含以下内容：
- 对比概要（文件名、时间戳）
- 每个工作表的详细差异统计
- 修改详情（显示具体变更内容）
- 总体统计数据
- 分析结论和建议
使用示例
示例 1：简单对比
用户：帮我对比这两个文件：sales_2024.xlsx 和 sales_2025.xlsx
示例 2：指定工作表对比
用户：只对比"销售数据"和"客户信息"这两个工作表
示例 3：容忍度设置
用户：对比这两个文件，数值差异在 0.01 以内不算差异
最佳实践
- 数据结构一致性检查：先检查行列结构，再进行逐单元格对比
- 智能字段匹配：通过列名匹配而非仅靠位置
- NaN 处理：正确处理空值，避免误报差异
- 数值容忍度：对浮点数设置合理的容忍度
- 清晰的视觉标记：使用颜色编码让用户快速定位差异
- 详尽的报告：提供可操作的分析和结论
注意事项
- 大文件可能需要较长处理时间
- 确保两个文件的列名和数据类型尽可能一致
- 对于公式单元格，对比的是计算后的值而非公式本身
- 不支持宏和 VBA 代码的对比
本技能基于 xlsx 技能构建，遵循相同的 Excel 处理最佳实践