pip install pandas openpyxl

import pandas as pd
from datetime import datetime

def compare_excel_sheets(file1, file2, key_columns, compare_columns):
    """
    智能對比兩個Excel文件
    :param file1: 第一個文件路徑
    :param file2: 第二個文件路徑  
    :param key_columns: 關(guān)鍵列列表，用于匹配行
    :param compare_columns: 需要對比的列列表
    """
    
    # 讀取Excel文件
    df1 = pd.read_excel(file1)
    df2 = pd.read_excel(file2)
    
    # 設(shè)置索引以便比較
    df1_set = df1.set_index(key_columns)
    df2_set = df2.set_index(key_columns)
    
    # 找出所有差異
    differences = []
    
    # 檢查新增和刪除的行
    common_keys = df1_set.index.intersection(df2_set.index)
    only_in_df1 = df1_set.index.difference(df2_set.index)
    only_in_df2 = df2_set.index.difference(df1_set.index)
    
    # 記錄增刪情況
    for key in only_in_df1:
        differences.append({
            '類型': '刪除行',
            '關(guān)鍵值': key,
            '詳情': '存在于第一個文件但第二個文件缺失'
        })
    
    for key in only_in_df2:
        differences.append({
            '類型': '新增行', 
            '關(guān)鍵值': key,
            '詳情': '存在于第二個文件但第一個文件缺失'
        })
    
    # 對比共同行的字段差異
    for key in common_keys:
        row1 = df1_set.loc[key]
        row2 = df2_set.loc[key]
        
        for col in compare_columns:
            val1 = row1[col]
            val2 = row2[col]
            
            # 處理NaN值和正常值的比較
            if pd.isna(val1) and pd.isna(val2):
                continue
            elif pd.isna(val1) or pd.isna(val2) or val1 != val2:
                differences.append({
                    '類型': '數(shù)據(jù)變更',
                    '關(guān)鍵值': key,
                    '字段': col,
                    '文件1值': val1,
                    '文件2值': val2
                })
    
    # 生成對比報告
    report_df = pd.DataFrame(differences)
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    report_file = f"對比報告_{timestamp}.xlsx"
    
    with pd.ExcelWriter(report_file) as writer:
        report_df.to_excel(writer, sheet_name='差異詳情', index=False)
        
        # 添加統(tǒng)計信息
        stats = {
            '對比時間': [timestamp],
            '總差異數(shù)': [len(differences)],
            '新增行數(shù)': [len(only_in_df2)],
            '刪除行數(shù)': [len(only_in_df1)],
            '數(shù)據(jù)變更數(shù)': [len(differences) - len(only_in_df1) - len(only_in_df2)]
        }
        pd.DataFrame(stats).to_excel(writer, sheet_name='統(tǒng)計摘要', index=False)
    
    return report_file

# 使用示例
if __name__ == "__main__":
    result_file = compare_excel_sheets(
        file1='銷售數(shù)據(jù)_月初.xlsx',
        file2='銷售數(shù)據(jù)_月末.xlsx',
        key_columns=['訂單編號'],  # 基于訂單號進(jìn)行匹配
        compare_columns=['客戶名稱', '產(chǎn)品數(shù)量', '訂單金額', '發(fā)貨狀態(tài)']
    )
    
    print(f"對比完成，報告已保存至: {result_file}")

def memory_efficient_compare(file1, file2, key_column):
    """分塊讀取對比，節(jié)省內(nèi)存"""
    chunksize = 10000
    results = []
    
    for chunk1 in pd.read_excel(file1, chunksize=chunksize):
        for chunk2 in pd.read_excel(file2, chunksize=chunksize):
            # 實(shí)現(xiàn)分塊對比邏輯
            merged = pd.merge(chunk1, chunk2, on=key_column, how='outer', indicator=True)
            differences = merged[merged['_merge'] != 'both']
            results.append(differences)
    
    return pd.concat(results)