Public/Compare-Worksheet.ps1

function Compare-Worksheet {
    [CmdletBinding(DefaultParameterSetName)]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidUsingWriteHost', '', Justification="Write host used for sub-warning level message to operator which does not form output")]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments', '', Justification="False positives when initializing variable in begin block")]
    param(
        [parameter(Mandatory=$true,Position=0)]
        $Referencefile ,
        [parameter(Mandatory=$true,Position=1)]
        $Differencefile   ,
        $WorkSheetName   = "Sheet1",
        $Property        = "*"    ,
        $ExcludeProperty ,
        [Parameter(ParameterSetName='B', Mandatory)]
        [String[]]$Headername,
        [Parameter(ParameterSetName='C', Mandatory)]
        [switch]$NoHeader,
        [int]$Startrow = 1,
        $AllDataBackgroundColor,
        $BackgroundColor,
        $TabColor,
        $Key             = "Name" ,
        $FontColor,
        [Switch]$Show,
        [switch]$GridView,
        [Switch]$PassThru,
        [Switch]$IncludeEqual,
        [Switch]$ExcludeDifferent
    )

    #if the filenames don't resolve, give up now.
    try    { $oneFile = ((Resolve-Path -Path $Referencefile -ErrorAction Stop).path -eq (Resolve-Path -Path $Differencefile  -ErrorAction Stop).path)}
    catch  { Write-Warning -Message "Could not Resolve the filenames." ; return }

    #If we have one file , we must have two different worksheet names. If we have two files we can have a single string or two strings.
    if     ($onefile -and ( ($WorkSheetName.count -ne 2) -or $WorkSheetName[0] -eq $WorkSheetName[1] ) ) {
        Write-Warning -Message "If both the Reference and difference file are the same then worksheet name must provide 2 different names"
        return
    }
    if     ($WorkSheetName.count -eq 2)       {$worksheet1 = $WorkSheetName[0] ;   $workSheet2 = $WorkSheetName[1]}
    elseif ($WorkSheetName -is [string])      {$worksheet1 = $workSheet2 = $WorkSheetName}
    else   {Write-Warning -Message "You must provide either a single worksheet name or two names." ; return }

    $params= @{ ErrorAction = [System.Management.Automation.ActionPreference]::Stop }
    foreach ($p in @("HeaderName","NoHeader","StartRow")) {if ($PSBoundParameters[$p]) {$params[$p] = $PSBoundParameters[$p]}}
    try    {
        $sheet1 = Import-Excel -Path $Referencefile  -WorksheetName $WorkSheet1 @params
        $sheet2 = Import-Excel -Path $Differencefile -WorksheetName $WorkSheet2 @Params
    }
    catch  {Write-Warning -Message "Could not read the worksheet from $Referencefile and/or $Differencefile." ; return }

    #Get Column headings and create a hash table of Name to column letter.
    $headings = $Sheet1[-1].psobject.Properties.name # This preserves the sequence - using Get-member would sort them alphabetically!
    $headings | ForEach-Object -Begin {$columns  = @{}  ; $i= 1 } -Process  {$Columns[$_] = [OfficeOpenXml.ExcelAddress]::GetAddress(1,($i ++)) -replace "\d","" }

    #Make a list of property headings using the Property (default "*") and ExcludeProperty parameters
    if ($Key -eq "Name" -and $NoHeader) {$Key  = "p1"}
    $propList = @()
    foreach ($p in $Property)           {$propList += ($headings.where({$_ -like    $p}) )}
    foreach ($p in $ExcludeProperty)    {$propList  =  $propList.where({$_ -notlike $p})  }
    if (($headings -contains $key) -and ($propList -notcontains $Key)) {$propList += $Key}
    $propList = $propList | Select-Object -Unique
    if ($propList.Count -eq 0)  {Write-Warning -Message "No Columns are selected with -Property = '$Property' and -excludeProperty = '$ExcludeProperty'." ; return}

    #Add RowNumber, Sheetname and file name to every row
    $firstDataRow = $startRow + 1
    if ($Headername -or $NoHeader) {$firstDataRow -- }
    $i = $firstDataRow ; foreach ($row in $Sheet1) {Add-Member -InputObject $row -MemberType NoteProperty -Name "_Row"   -Value ($i ++)
                                                    Add-Member -InputObject $row -MemberType NoteProperty -Name "_Sheet" -Value  $worksheet1
                                                    Add-Member -InputObject $row -MemberType NoteProperty -Name "_File"  -Value  $Referencefile}
    $i = $firstDataRow ; foreach ($row in $Sheet2) {Add-Member -InputObject $row -MemberType NoteProperty -Name "_Row"   -Value ($i ++)
                                                    Add-Member -InputObject $row -MemberType NoteProperty -Name "_Sheet" -Value  $worksheet2
                                                    Add-Member -InputObject $row -MemberType NoteProperty -Name "_File"  -Value  $Differencefile}

    if ($ExcludeDifferent -and -not $IncludeEqual) {$IncludeEqual = $true}
    #Do the comparison and add file,sheet and row to the result - these are prefixed with "_" to show they are added the addition will fail if the sheet has these properties so split the operations
    [PSCustomObject[]]$diff = Compare-Object -ReferenceObject $Sheet1 -DifferenceObject $Sheet2 -Property $propList -PassThru -IncludeEqual:$IncludeEqual -ExcludeDifferent:$ExcludeDifferent  |
                Sort-Object -Property "_Row","File"

    #if BackgroundColor was specified, set it on extra or extra or changed rows
    if      ($diff -and $BackgroundColor) {
        #Differences may only exist in one file. So gather the changes for each file; open the file, update each impacted row in the shee, save the file
        $updates = $diff.where({$_.SideIndicator -ne "=="}) | Group-object -Property "_File"
        foreach   ($file in $updates) {
            try   {$xl  = Open-ExcelPackage -Path $file.name }
            catch {Write-warning -Message "Can't open $($file.Name) for writing." ; return}
            if  ($PSBoundParameters.ContainsKey("AllDataBackgroundColor")) {
                $file.Group._sheet | Sort-Object -Unique | ForEach-Object {
                    $ws =  $xl.Workbook.Worksheets[$_]
                    if ($headerName) {$range = "A" +  $startrow      + ":" + $ws.dimension.end.address}
                    else             {$range = "A" + ($startrow + 1) + ":" + $ws.dimension.end.address}
                    Set-ExcelRange -Worksheet $ws -BackgroundColor $AllDataBackgroundColor -Range $Range
                }
            }
            foreach ($row in $file.group)  {
                $ws    = $xl.Workbook.Worksheets[$row._Sheet]
                $range = $ws.Dimension -replace "\d+",$row._row
                Set-ExcelRange -Worksheet $ws -Range $range -BackgroundColor $BackgroundColor
            }
            if  ($PSBoundParameters.ContainsKey("TabColor")) {
                if ($TabColor -is [string])         {$TabColor = [System.Drawing.Color]::$TabColor }
                foreach ($tab in ($file.group._sheet | Select-Object -Unique)) {
                    $xl.Workbook.Worksheets[$tab].TabColor = $TabColor
                 }
            }
            $xl.save()  ; $xl.Stream.Close() ; $xl.Dispose()
        }
    }
    #if font color was specified, set it on changed properties where the same key appears in both sheets.
    if      ($diff -and $FontColor -and (($propList -contains $Key) -or ($key -is [hashtable]))  ) {
        $updates = $diff.where({$_.SideIndicator -ne "=="})  | Group-object -Property $Key | Where-Object {$_.count -eq 2}
        if ($updates) {
            $XL1 = Open-ExcelPackage -path $Referencefile
            if ($oneFile ) {$xl2 = $xl1}
            else           {$xl2 = Open-ExcelPackage -path $Differencefile }
            foreach ($u in $updates) {
                 foreach ($p in $propList) {
                    if ($u.group[0]._file -eq $Referencefile) {
                        $ws1 =  $xl1.Workbook.Worksheets[$u.Group[0]._sheet]
                        $ws2 =  $xl2.Workbook.Worksheets[$u.Group[1]._sheet]
                    }
                    else {
                        $ws1 =  $xl2.Workbook.Worksheets[$u.Group[0]._sheet]
                        $ws2 =  $xl1.Workbook.Worksheets[$u.Group[1]._sheet]
                    }
                    if($u.Group[0].$p -ne $u.Group[1].$p ) {
                        Set-ExcelRange -Worksheet $ws1 -Range ($Columns[$p] + $u.Group[0]._Row) -FontColor $FontColor
                        Set-ExcelRange -Worksheet $ws2 -Range ($Columns[$p] + $u.Group[1]._Row) -FontColor $FontColor
                    }
                }
            }
            $xl1.Save()                     ; $xl1.Stream.Close() ; $xl1.Dispose()
            if (-not $oneFile) {$xl2.Save() ; $xl2.Stream.Close() ; $xl2.Dispose()}
        }
    }
    elseif  ($diff -and $FontColor) {Write-Warning -Message "To match rows to set changed cells, you must specify -Key and it must match one of the included properties." }

    #if nothing was found write a message which will not be redirected
    if (-not $diff) {Write-Host "Comparison of $Referencefile::$worksheet1 and $Differencefile::$WorkSheet2 returned no results."  }

    if      ($Show)               {
        Start-Process -FilePath $Referencefile
        if  (-not $oneFile)  { Start-Process -FilePath $Differencefile }
        if  ($GridView)      { Write-Warning -Message "-GridView is ignored when -Show is specified" }
    }
    elseif  ($GridView -and $propList -contains $key) {


             if ($IncludeEqual -and -not $ExcludeDifferent) {
                $GroupedRows = $diff | Group-Object -Property $key
             }
             else { #to get the right now numbers on the grid we need to have all the rows.
                $GroupedRows = Compare-Object -ReferenceObject $Sheet1 -DifferenceObject $Sheet2 -Property $propList -PassThru -IncludeEqual  |
                                        Group-Object -Property $key
             }
             #Additions, deletions and unchanged rows will give a group of 1; changes will give a group of 2 .

             #If one sheet has extra rows we can get a single "==" result from compare, but with the row from the reference sheet
             #but the row in the other sheet might so we will look up the row number from the key field build a hash table for that
             $Sheet2 | ForEach-Object -Begin {$Rowhash = @{} } -Process {$Rowhash[$_.$key] = $_._row }

             $ExpandedDiff = ForEach ($g in $GroupedRows)  {
                #we're going to create a custom object from a hash table. We want the fields to be ordered
                $hash = [ordered]@{}
                foreach ($result IN $g.Group) {
                    # if result indicates equal or "in Reference" set the reference side row. If we did that on a previous result keep it. Otherwise set to "blank"
                    if     ($result.sideindicator -ne "=>")      {$hash["<Row"] = $result._Row  }
                    elseif (-not $hash["<Row"])                  {$hash["<Row"] = "" }
                    #if we have already set the side, this is the second record, so set side to indicate "changed"
                    if     ($hash.Side) {$hash.side = "<>"} else {$hash["Side"] = $result.sideindicator}
                    #if result is "in reference" and we don't have a matching "in difference" (meaning a change) the lookup will be blank. Which we want.
                    $hash[">Row"] = $Rowhash[$g.Name]
                    #position the key as the next field (only appears once)
                    $Hash[$key]    = $g.Name
                    #For all the other fields we care about create <=FieldName and/or =>FieldName
                    foreach ($p in $propList.Where({$_ -ne $key})) {
                        if  ($result.SideIndicator -eq "==")  {$hash[("=>$P")] = $hash[("<=$P")] =$result.$P}
                        else                                  {$hash[($result.SideIndicator+$P)] =$result.$P}
                    }
                }
                [Pscustomobject]$hash
             }

             #Sort by reference row number, and fill in any blanks in the difference-row column
             $ExpandedDiff = $ExpandedDiff | Sort-Object -Property "<row"
             for ($i = 1; $i -lt $ExpandedDiff.Count; $i++) {if (-not $ExpandedDiff[$i].">row") {$ExpandedDiff[$i].">row" = $ExpandedDiff[$i-1].">row" } }
             #Sort by difference row number, and fill in any blanks in the reference-row column
             $ExpandedDiff = $ExpandedDiff | Sort-Object -Property ">row"
             for ($i = 1; $i -lt $ExpandedDiff.Count; $i++) {if (-not $ExpandedDiff[$i]."<row") {$ExpandedDiff[$i]."<row" = $ExpandedDiff[$i-1]."<row" } }

             #if we had to put the equal rows back, take them out; sort, make sure all the columns are present in row 1 so the grid puts them in, and output
             if ( $ExcludeDifferent) {$ExpandedDiff = $ExpandedDiff.where({$_.side -eq "=="}) | Sort-Object -Property "<row" ,">row"  }
             elseif ( $IncludeEqual) {$ExpandedDiff = $ExpandedDiff                           | Sort-Object -Property "<row" ,">row"  }
             else                    {$ExpandedDiff = $ExpandedDiff.where({$_.side -ne "=="}) | Sort-Object -Property "<row" ,">row"  }
             $ExpandedDiff | Update-FirstObjectProperties | Out-GridView -Title "Comparing $Referencefile::$worksheet1 (<=) with $Differencefile::$WorkSheet2 (=>)"
    }
    elseif  ($GridView     )  {Write-Warning -Message "To use -GridView you must specify -Key and it must match one of the included properties."  }
    elseif  (-not $PassThru)  {return ($diff | Select-Object -Property (@(@{n="_Side";e={$_.SideIndicator}},"_File" ,"_Sheet","_Row") + $propList))}
    if      (     $PassThru)  {return  $diff }
}