powershellでExcelファイル比較
powershellを使ってExcelファイルを比較する方法。オブジェクトとかは出来ないけど、同名シートをセル単位で比較して、異なるセルに色をつける。
# Diff excel file. param($aExcelPath = $(Read-Host "Enter one excel path"), $bExcelPath = $(Read-Host "Enter other excel path")) $ErrorActionPreference = "stop" $DebugPreference = "SilentlyContinue" # Continue SilentlyContinue Stop Inquire $commandPath = Split-Path -parent $myInvocation.MyCommand.path $commandName = Split-Path -leaf $myInvocation.MyCommand.path $commandBaseName = (gci $myInvocation.MyCommand.path).BaseName Set-Location $commandPath <# for test $aExcelPath = Join-Path $commandPath "testdata\aExcel.xlsx" $bExcelPath = Join-Path $commandPath "testdata\bExcel.xlsx" #> # const value $xlNumbers = 1 $xlCellTypeFormulas = -4123 $xlNone = 0 function main() { try { # check file if (-not (Test-Path $aExcelPath)) { Write-Host "$($aExcelPath) is not found !" return -1 } if (-not (Test-Path $bExcelPath)) { Write-Host "$($bExcelPath) is not found !" return -1 } $aExcelPath = $aExcelPath -replace """", "" $bExcelPath = $bExcelPath -replace """", "" # to abs path $aExcelPath = Convert-Path $aExcelPath $bExcelPath = Convert-Path $bExcelPath $aExcelCheckPath = Join-Path (Split-Path -parent $aExcelPath) ((gci $aExcelPath).BaseName + "_diffcheck" + (gci $aExcelPath).Extension) $bExcelCheckPath = Join-Path (Split-Path -parent $bExcelPath) ((gci $bExcelPath).BaseName + "_diffcheck" + (gci $bExcelPath).Extension) # backup cp $aExcelPath $aExcelCheckPath cp $bExcelPath $bExcelCheckPath $excel = New-Object -ComObject Excel.Application $excel.Visible = $false $excel.Application.DisplayAlerts = $false $excel.Application.ScreenUpdating = $false # tmp book $tBook = $excel.Workbooks.Add() $aBook = $excel.Workbooks.Open($aExcelCheckPath, 0, $false) $bBook = $excel.Workbooks.Open($bExcelCheckPath, 0, $false) Write-Host "============= excel check start =============" diffCheck $aBook $bBook $tBook Write-Host "============= excel check end =============" $aBook.Save() $bBook.Save() } catch { Write-Host "Error Occured ! $($error[0])" } finally { if ($excel) { $excel.Quit() } } } function rgb($r, $g, $b) { return ($b + ($g * 256) + ($r * 65536)) } function randomColor() { $r = $(150..255 | Get-Random) $g = $(150..255 | Get-Random) $b = $(150..255 | Get-Random) return rgb $r $g $b } function diffCheck($oneBook, $otherBook, $tBook) { trap { Write-Host "[diffCheck]: Error $($_)"; throw $_ } $oneBook.Worksheets | % { $oneSheet = $_ $tSheet = $tBook.Worksheets.Item(1) $tSheet.Cells.ClearContents() if (-not (isExistsWorksheet $otherBook $oneSheet.Name)) { Write-Host "$($otherBook.Name) has not $($oneSheet.Name)" return } $otherSheet = $otherBook.Worksheets.Item($oneSheet.Name) if ($oneSheet.Visible) { Write-Host "Check $($oneSheet.Name) ..." # reset color $oneSheet.Cells.Interior.ColorIndex = $xlNone $otherSheet.Cells.Interior.ColorIndex = $xlNone $tmpRange = $tSheet.Range(($oneSheet.Range($oneSheet.UsedRange, $oneSheet.Range($otherSheet.UsedRange.Address()))).Address()) # set tmp range to fomula $tmpRange.FormulaR1C1 = "=IF('[" + $oneBook.Name + "]" + $oneSheet.Name + "'!RC=" + "'[" + $otherBook.Name + "]" + $otherSheet.Name + "'!RC,"""",1)" $cnt = $excel.Application.WorksheetFunction.Count($tmpRange.Cells) Write-Host "Different count = [$($cnt)]" if ($cnt -gt 0) { $tRange = $tmpRange.SpecialCells($xlCellTypeFormulas, $xlNumbers) $rangeList = New-Object System.Collections.Generic.List[string] $rangeString = "" $tRange.Areas | % { if ($rangeList.Count -gt 10) { if ((($rangeList + $_.Address()) -join ",").Length -lt 255) { [void]$rangeList.Add($_.Address()) } else { $rangeString = $rangeList.ToArray() -join "," Write-Debug $rangeString.Length $color = randomColor $oneSheet.Range($rangeString).Interior.Color = $color $otherSheet.Range($rangeString).Interior.Color = $color $rangeList = New-Object System.Collections.Generic.List[string] [void]$rangeList.Add($_.Address()) } } else { [void]$rangeList.Add($_.Address()) } } if ($rangeList.Count -ne 0) { $rangeString = $rangeList.ToArray() -join "," Write-Debug $rangeString.Length $color = randomColor $oneSheet.Range($rangeString).Interior.Color = $color $otherSheet.Range($rangeString).Interior.Color = $color } } } } } function isExistsWorksheet($wBook, $wsName) { trap { Write-Host "[isExistsWorksheet]: Error $($_)"; throw $_ } $wBook.Worksheets | % { if ($_.Name -eq $wsName) { return $true } } return $false } # call main Measure-Command { main }
powershell楽しい。