使用Powershell比较两个EXCEL文件以删除重复数据

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$workbook1 = $excel.Workbooks.Open("路径\文件1.xlsx")
$workbook2 = $excel.Workbooks.Open("路径\文件2.xlsx")

$worksheet1 = $workbook1.Worksheets.Item(1)
$worksheet2 = $workbook2.Worksheets.Item(1)

$dataRange1 = $worksheet1.UsedRange
$dataRange2 = $worksheet2.UsedRange

$dataArray1 = $dataRange1.Value2
$dataArray2 = $dataRange2.Value2

$duplicateRows = @()

for ($i = 1; $i -le $dataArray1.GetLength(0); $i++) {
    for ($j = 1; $j -le $dataArray2.GetLength(0); $j++) {
        if ($dataArray1[$i, 1] -eq $dataArray2[$j, 1] -and $dataArray1[$i, 2] -eq $dataArray2[$j, 2]) {
            $duplicateRows += $i
            break
        }
    }
}

foreach ($row in $duplicateRows) {
    $dataRange1.Rows.Item($row).Delete()
}

$workbook1.Save()
$workbook1.Close()
$workbook2.Close()

$excel.Quit()

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$workbook1 = $excel.Workbooks.Open("路径\文件1.xlsx")
$workbook2 = $excel.Workbooks.Open("路径\文件2.xlsx")

$worksheet1 = $workbook1.Worksheets.Item(1)
$worksheet2 = $workbook2.Worksheets.Item(1)

$dataRange1 = $worksheet1.UsedRange
$dataRange2 = $worksheet2.UsedRange

$dataArray1 = $dataRange1.Value2
$dataArray2 = $dataRange2.Value2

$duplicateRows = @()

for ($i = 1; $i -le $dataArray1.GetLength(0); $i++) {
    for ($j = 1; $j -le $dataArray2.GetLength(0); $j++) {
        if ($dataArray1[$i, 1] -eq $dataArray2[$j, 1] -and $dataArray1[$i, 2] -eq $dataArray2[$j, 2]) {
            $duplicateRows += $i
            break
        }
    }
}

foreach ($row in $duplicateRows) {
    $dataRange1.Rows.Item($row).Delete()
}

$workbook1.Save()
$workbook1.Close()
$workbook2.Close()

$excel.Quit()