import pandas as pd

def compare_po_and_amount(file1, file2):
    # 读取两个 Excel 文件
    df1 = pd.read_excel(file1)
    df2 = pd.read_excel(file2)

    # 确保两个表格中都存在 'PO' 和 'Amount' 列
    if 'PO' not in df1.columns or 'Amount' not in df1.columns:
        raise ValueError("File 1 is missing required columns: 'PO' or 'Amount'")

    if 'PO' not in df2.columns or 'Amount' not in df2.columns:
        raise ValueError("File 2 is missing required columns: 'PO' or 'Amount'")

    # 聚合数据，按照 'PO' 进行分组，并对 'Amount' 进行求和
    df1_grouped = df1.groupby('PO', as_index=False)['Amount'].sum()
    df2_grouped = df2.groupby('PO', as_index=False)['Amount'].sum()

    # 合并两个数据框，使用 'PO' 列进行比较
    merged_df = pd.merge(df1_grouped, df2_grouped, on='PO', how='outer', suffixes=('_file1', '_file2'))

    # 找出两列 'Amount' 不相同的行
    diff_df = merged_df[merged_df['Amount_file1'] != merged_df['Amount_file2']]

    # 如果没有差异，输出消息
    if diff_df.empty:
        print("The PO amounts are identical in both files.")
    else:
        print("The following POs have different amounts:")
        print(diff_df)


# 使用示例
compare_po_and_amount('file1.xlsx', 'file2.xlsx')
