Zip包内.xlsx文件内容对比异常问题求助

现有一段PowerShell代码用于对比压缩包内文件内容，对.csv和.xml文件对比正常，但处理系统生成的.xlsx文件时出现异常：肉眼观察两个Excel文件内容一致，但通过Get-Content、Compare-Object及当前代码均判定为不同，仅复制同一文件对比时代码正常。

原代码如下：

[string]$EventID1 = Read-Host -Prompt "Input ID-1"
[string]$EventID2 = Read-Host -Prompt "Input ID-2"
 
# Function to compare the contents of files inside zip folders
function Compare-ZipContents {
    param (
        [string]$zipFile1,
        [string]$zipFile2
    )
    $zip1 = [System.IO.Compression.ZipFile]::OpenRead($zipFile1)
    $zip2 = [System.IO.Compression.ZipFile]::OpenRead($zipFile2)

    $xmlFilePath1 = $zip1.Entries | Where-Object { $_.Name -like "*.xml" }
    $xmlFilePath2 = $zip2.Entries | Where-Object { $_.Name -like "*.xml" }

    $csvFiles1 = $zip1.Entries | Where-Object { $_.Name -like "*.csv" }
    $csvFiles2 = $zip2.Entries | Where-Object { $_.Name -like "*.csv" }
 
    $excelFiles1 = $zip1.Entries | Where-Object { $_.Name -like "*.xlsx" -or $_.Name -like "*.xls" }
    $excelFiles2 = $zip2.Entries | Where-Object { $_.Name -like "*.xlsx" -or $_.Name -like "*.xls" }
 
    foreach ($xml1 in $xmlFilePath1) {
        foreach ($xml2 in $xmlFilePath2) {
            $stream1 = $xml1.Open()
            $stream2 = $xml2.Open()
            $reader1 = New-Object System.IO.StreamReader($stream1)
            $reader2 = New-Object System.IO.StreamReader($stream2)
            $content1 = $reader1.ReadToEnd()
            $content2 = $reader2.ReadToEnd()
            if ($content1 -eq $content2) {
                Write-Output "$($xml1.Name) and $($xml2.Name) are identical."
            }
            Else { Write-Output "$($xml1.Name) and $($xml2.Name) are different."}
            $reader1.Close()
            $reader2.Close()
            $stream1.Close()
            $stream2.Close()
        }
    }
    
    foreach ($csv1 in $csvFiles1) {
        foreach ($csv2 in $csvFiles2) {
            $stream1 = $csv1.Open()
            $stream2 = $csv2.Open()
            $reader1 = New-Object System.IO.StreamReader($stream1)
            $reader2 = New-Object System.IO.StreamReader($stream2)
            $content1 = $reader1.ReadToEnd()
            $content2 = $reader2.ReadToEnd()
            if ($content1 -eq $content2) {
                Write-Output "$($csv1.Name) and $($csv2.Name) are identical."
            }
            Else { Write-Output "$($csv1.Name) and $($csv2.Name) are different."}
            $reader1.Close()
            $reader2.Close()
            $stream1.Close()
            $stream2.Close()
        }
    }
 
    foreach ($excel1 in $excelFiles1) {
        foreach ($excel2 in $excelFiles2) {
            $stream1 = $excel1.Open()
            $stream2 = $excel2.Open()
            $reader1 = New-Object System.IO.StreamReader($stream1)
            $reader2 = New-Object System.IO.StreamReader($stream2)
            $content1 = $reader1.ReadToEnd()
            $content2 = $reader2.ReadToEnd()
            if ($content1 -eq $content2) {
                Write-Output "$($excel1.Name) and $($excel2.Name) are identical."
            }
            Else {Write-Output "$($excel1.Name) and $($excel2.Name) are different."}
            $reader1.Close()
            $reader2.Close()
            $stream1.Close()
            $stream2.Close()

        }
    }
 
    $zip1.Dispose()
    $zip2.Dispose()
}
 
# Path
$zipFile1 = "Address 1"
$zipFile2 = "Address 2"
Compare-ZipContents -zipFile1 $zipFile1 -zipFile2 $zipFile2

.xlsx本质是二进制压缩包格式，内部包含的不只是表格数据，还有系统生成的元数据（如文件创建时间、修改时间、内部唯一ID、文档属性等）。即便表格内容完全一致，这些元数据也会因生成时间、环境不同而存在差异，导致整个文件的二进制内容不同。直接用StreamReader读取二进制内容对比，会把这些元数据差异判定为文件内容不同。

要对比Excel文件的实际表格内容，需要解析文件提取数据，而非对比整个文件的二进制。这里使用EPPlus库（针对.xlsx的.NET处理库）来读取单元格数据并对比，同时优化文件匹配逻辑（按文件名对应对比，而非所有文件互相比）。

步骤1：安装EPPlus库 #

在PowerShell中运行以下命令安装EPPlus（需先安装NuGet包管理器）：

Install-Package EPPlus -Scope CurrentUser

步骤2：修改后的代码 #

[string]$EventID1 = Read-Host -Prompt "Input ID-1"
[string]$EventID2 = Read-Host -Prompt "Input ID-2"

# 加载EPPlus库
Add-Type -Path (Join-Path (Get-Package EPPlus).Source "lib/netstandard2.0/EPPlus.dll")

# Function to compare the contents of files inside zip folders
function Compare-ZipContents {
    param (
        [string]$zipFile1,
        [string]$zipFile2
    )
    $zip1 = [System.IO.Compression.ZipFile]::OpenRead($zipFile1)
    $zip2 = [System.IO.Compression.ZipFile]::OpenRead($zipFile2)

    # --- 对比XML文件 ---
    foreach ($xml1 in $zip1.Entries | Where-Object { $_.Name -like "*.xml" }) {
        $xml2 = $zip2.Entries | Where-Object { $_.Name -eq $xml1.Name } | Select-Object -First 1
        if (-not $xml2) {
            Write-Output "$($xml1.Name) not found in second zip."
            continue
        }
        $stream1 = $xml1.Open()
        $stream2 = $xml2.Open()
        $reader1 = New-Object System.IO.StreamReader($stream1)
        $reader2 = New-Object System.IO.StreamReader($stream2)
        $content1 = $reader1.ReadToEnd()
        $content2 = $reader2.ReadToEnd()
        if ($content1 -eq $content2) {
            Write-Output "$($xml1.Name) and $($xml2.Name) are identical."
        }
        Else { Write-Output "$($xml1.Name) and $($xml2.Name) are different."}
        $reader1.Close()
        $reader2.Close()
        $stream1.Close()
        $stream2.Close()
    }
    
    # --- 对比CSV文件 ---
    foreach ($csv1 in $zip1.Entries | Where-Object { $_.Name -like "*.csv" }) {
        $csv2 = $zip2.Entries | Where-Object { $_.Name -eq $csv1.Name } | Select-Object -First 1
        if (-not $csv2) {
            Write-Output "$($csv1.Name) not found in second zip."
            continue
        }
        $stream1 = $csv1.Open()
        $stream2 = $csv2.Open()
        $reader1 = New-Object System.IO.StreamReader($stream1)
        $reader2 = New-Object System.IO.StreamReader($stream2)
        $content1 = $reader1.ReadToEnd()
        $content2 = $reader2.ReadToEnd()
        if ($content1 -eq $content2) {
            Write-Output "$($csv1.Name) and $($csv2.Name) are identical."
        }
        Else { Write-Output "$($csv1.Name) and $($csv2.Name) are different."}
        $reader1.Close()
        $reader2.Close()
        $stream1.Close()
        $stream2.Close()
    }
 
    # --- 对比XLSX文件 ---
    foreach ($excel1 in $zip1.Entries | Where-Object { $_.Name -like "*.xlsx" }) {
        $excel2 = $zip2.Entries | Where-Object { $_.Name -eq $excel1.Name } | Select-Object -First 1
        if (-not $excel2) {
            Write-Output "$($excel1.Name) not found in second zip."
            continue
        }

        # 读取Excel文件流并解析数据
        $stream1 = $excel1.Open()
        $stream2 = $excel2.Open()

        $package1 = New-Object OfficeOpenXml.ExcelPackage($stream1)
        $package2 = New-Object OfficeOpenXml.ExcelPackage($stream2)

        $isIdentical = $true

        # 对比每个工作表
        foreach ($ws1 in $package1.Workbook.Worksheets) {
            $ws2 = $package2.Workbook.Worksheets[$ws1.Name]
            if (-not $ws2) {
                Write-Output "Worksheet $($ws1.Name) missing in $($excel2.Name)"
                $isIdentical = $false
                break
            }

            # 对比单元格内容
            $maxRow = [Math]::Max($ws1.Dimension.End.Row, $ws2.Dimension.End.Row)
            $maxCol = [Math]::Max($ws1.Dimension.End.Column, $ws2.Dimension.End.Column)

            for ($row = 1; $row -le $maxRow; $row++) {
                for ($col = 1; $col -le $maxCol; $col++) {
                    $val1 = $ws1.Cells[$row, $col].Value
                    $val2 = $ws2.Cells[$row, $col].Value
                    if ($val1 -ne $val2) {
                        Write-Output "Cell ($row, $col) differs in $($excel1.Name) and $($excel2.Name)"
                        $isIdentical = $false
                        break 2
                    }
                }
            }
        }

        if ($isIdentical) {
            Write-Output "$($excel1.Name) and $($excel2.Name) are identical in content."
        }
        Else {
            Write-Output "$($excel1.Name) and $($excel2.Name) are different in content."
        }

        $package1.Dispose()
        $package2.Dispose()
        $stream1.Close()
        $stream2.Close()
    }

    # --- 备注：XLS文件处理 ---
    # 若需处理旧版.xls文件，需使用Microsoft.Office.Interop.Excel或NPOI库，逻辑类似但需调整解析方式
    foreach ($xls1 in $zip1.Entries | Where-Object { $_.Name -like "*.xls" }) {
        $xls2 = $zip2.Entries | Where-Object { $_.Name -eq $xls1.Name } | Select-Object -First 1
        if (-not $xls2) {
            Write-Output "$($xls1.Name) not found in second zip."
            continue
        }
        Write-Output "Warning: .xls file comparison not implemented. Please add XLS parsing logic."
    }
 
    $zip1.Dispose()
    $zip2.Dispose()
}
 
# Path
$zipFile1 = "Address 1"
$zipFile2 = "Address 2"
Compare-ZipContents -zipFile1 $zipFile1 -zipFile2 $zipFile2

代码说明 #

1. 文件匹配优化：不再嵌套循环对比所有同类型文件，而是按文件名匹配对应文件，避免不必要的交叉对比。
2. Excel内容解析：通过EPPlus读取每个工作表的单元格内容，直接对比实际数据，忽略元数据差异。
3. 异常处理：添加文件/工作表缺失的提示，方便排查问题。

内容的提问来源于stack exchange，提问作者Amitabh Srivastava