Merge-Worksheet.ps1

function Merge-Worksheet {
    [CmdletBinding(SupportsShouldProcess=$true)]
    param(
         [parameter(ParameterSetName='A',Mandatory=$true,Position=0)]  #A = Compare two files default headers
         [parameter(ParameterSetName='B',Mandatory=$true,Position=0)]  #B = Compare two files user supplied headers
         [parameter(ParameterSetName='C',Mandatory=$true,Position=0)]  #C = Compare two files headers P1, P2, P3 etc
         $Referencefile ,

         [parameter(ParameterSetName='A',Mandatory=$true,Position=1)]
         [parameter(ParameterSetName='B',Mandatory=$true,Position=1)]
         [parameter(ParameterSetName='C',Mandatory=$true,Position=1)]
         [parameter(ParameterSetName='E',Mandatory=$true,Position=1)] #D Compare two objects; E = Compare one object one file that uses default headers
         [parameter(ParameterSetName='F',Mandatory=$true,Position=1)] #F = Compare one object one file that uses user supplied headers
         [parameter(ParameterSetName='G',Mandatory=$true,Position=1)] #G   Compare one object one file that uses headers P1, P2, P3 etc
         $Differencefile ,

         [parameter(ParameterSetName='A',Position=2)]  #Applies to all sets EXCEPT D which is two objects (no sheets)
         [parameter(ParameterSetName='B',Position=2)]
         [parameter(ParameterSetName='C',Position=2)]
         [parameter(ParameterSetName='E',Position=2)]
         [parameter(ParameterSetName='F',Position=2)]
         [parameter(ParameterSetName='G',Position=2)]
         $WorksheetName   = "Sheet1",

         [parameter(ParameterSetName='A')]  #Applies to all sets EXCEPT D which is two objects (no sheets, so no start row )
         [parameter(ParameterSetName='B')]
         [parameter(ParameterSetName='C')]
         [parameter(ParameterSetName='E')]
         [parameter(ParameterSetName='F')]
         [parameter(ParameterSetName='G')]
         [int]$Startrow = 1,

         [Parameter(ParameterSetName='B',Mandatory=$true)]  #Compare  object + sheet or 2 sheets with user supplied headers
         [Parameter(ParameterSetName='F',Mandatory=$true)]
         [String[]]$Headername,

         [Parameter(ParameterSetName='C',Mandatory=$true)]  #Compare  object + sheet or 2 sheets with headers of P1, P2, P3 ...
         [Parameter(ParameterSetName='G',Mandatory=$true)]
         [switch]$NoHeader,

         [parameter(ParameterSetName='D',Mandatory=$true)]
         [parameter(ParameterSetName='E',Mandatory=$true)]
         [parameter(ParameterSetName='F',Mandatory=$true)]
         [parameter(ParameterSetName='G',Mandatory=$true)]
         [Alias('RefObject')]
         $ReferenceObject ,
         [parameter(ParameterSetName='D',Mandatory=$true,Position=1)]
         [Alias('DiffObject')]
         $DifferenceObject ,
         [parameter(ParameterSetName='D',Position=2)]
         [parameter(ParameterSetName='E',Position=2)]
         [parameter(ParameterSetName='F',Position=2)]
         [parameter(ParameterSetName='G',Position=2)]
         $DiffPrefix = "=>" ,
         [parameter(Position=3)]
         [Alias('OutFile')]
         $OutputFile ,
         [parameter(Position=4)]
         [Alias('OutSheet')]
         $OutputSheetName = "Sheet1",
         $Property        = "*"    ,
         $ExcludeProperty ,
         $Key           = "Name" ,
         $KeyFontColor          = [System.Drawing.Color]::DarkRed ,
         $ChangeBackgroundColor = [System.Drawing.Color]::Orange,
         $DeleteBackgroundColor = [System.Drawing.Color]::LightPink,
         $AddBackgroundColor    = [System.Drawing.Color]::PaleGreen,
         [switch]$HideEqual ,
         [switch]$Passthru  ,
         [Switch]$Show
    )

 #region Read Excel data
    if ($Differencefile -is [System.IO.FileInfo]) {$Differencefile = $Differencefile.FullName}
    if ($Referencefile  -is [System.IO.FileInfo]) {$Referencefile  = $Referencefile.FullName}
    if ($Referencefile -and $Differencefile) {
         #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 $WorksheetName can be 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)  {$Worksheet2 = $DiffPrefix = $WorksheetName[1] ; $Worksheet1 = $WorksheetName[0]  ;  }
         elseif  ($WorksheetName -is [string]) {$Worksheet2 = $Worksheet1 = $WorksheetName    ;
                                                $DiffPrefix = (Split-Path -Path $Differencefile -Leaf) -replace "\.xlsx$","" }
         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     {
             $ReferenceObject  = Import-Excel -Path $Referencefile  -WorksheetName $Worksheet1 @params
             $DifferenceObject = Import-Excel -Path $Differencefile -WorksheetName $Worksheet2 @Params
         }
         catch   {Write-Warning -Message "Could not read the Worksheet from $Referencefile::$Worksheet1 and/or $Differencefile::$Worksheet2." ; return }
         if ($NoHeader) {$firstDataRow = $Startrow  } else {$firstDataRow = $Startrow + 1}
     }
     elseif (                $Differencefile) {
         if ($WorksheetName -isnot [string]) {Write-Warning -Message "You must provide a single Worksheet name." ; return }
         $params     =  @{WorksheetName=$WorksheetName; Path=$Differencefile; ErrorAction=[System.Management.Automation.ActionPreference]::Stop }
         foreach ($p in @("HeaderName","NoHeader","StartRow")) {if ($PSBoundParameters[$p]) {$params[$p] = $PSBoundParameters[$p]}}
         try            {$DifferenceObject = Import-Excel   @Params }
         catch          {Write-Warning -Message "Could not read the Worksheet '$WorksheetName' from $Differencefile::$WorksheetName." ; return }
         if ($DiffPrefix -eq "=>" ) {
             $DiffPrefix  =  (Split-Path -Path $Differencefile -Leaf) -replace "\.xlsx$",""
         }
         if ($NoHeader) {$firstDataRow = $Startrow  } else {$firstDataRow = $Startrow + 1}
     }
     else   { $firstDataRow = 1  }
 #endregion

 #region Set lists of properties and row numbers
     #Make a list of properties/headings using the Property (default "*") and ExcludeProperty parameters
     $propList         = @()
     $DifferenceObject = $DifferenceObject | Update-FirstObjectProperties
     $headings         = $DifferenceObject[0].psobject.Properties.Name # This preserves the sequence - using Get-member would sort them alphabetically! There may be extra properties in
     if ($NoHeader     -and "Name" -eq $Key)  {$Key     = "p1"}
     if ($headings     -notcontains    $Key -and
                              ('*' -ne $Key)) {Write-Warning -Message "You need to specify one of the headings in the sheet '$Worksheet2' as a key." ; return }
     foreach ($p in $Property)                { $propList += ($headings.where({$_ -like    $p}) )}
     foreach ($p in $ExcludeProperty)         { $propList  =  $propList.where({$_ -notlike $p})  }
     if (($propList    -notcontains $Key) -and
                           ('*' -ne $Key))    { $propList +=  $Key}    #If $Key isn't one of the headings we will have bailed by now
     $propList         = $propList   | Select-Object -Unique           #so, prolist must contain at least $Key if nothing else

     #If key is "*" we treat it differently , and we will create a script property which concatenates all the Properties in $Proplist
     $ConCatblock      = [scriptblock]::Create( ($proplist | ForEach-Object {'$this."' + $_ + '"'})  -join " + ")

     #Build the list of the properties to output, in order.
     $diffpart         = @()
     $refpart          = @()
     foreach ($p in $proplist.Where({$Key -ne $_}) ) {$refPart += $p ; $diffPart += "$DiffPrefix $p" }
     $lastRefColNo     = $proplist.count
     $FirstDiffColNo   = $lastRefColNo + 1

     if ($Key -ne '*') {
            $outputProps   = @($Key) + $refpart + $diffpart
            #If we are using a single column as the key, don't duplicate it, so the last difference column will be A if there is one property, C if there are two, E if there are 3
            $lastDiffColNo = (2 * $proplist.count) - 1
     }
     else {
            $outputProps   = @( )    + $refpart + $diffpart
            #If we not using a single column as a key all columns are duplicated so, the Last difference column will be B if there is one property, D if there are two, F if there are 3
            $lastDiffColNo = (2 * $proplist.count )
     }

     #Add RowNumber to every row
     #If one sheet has extra rows we can get a single "==" result from compare, with the row from the reference sheet, but
     #the row in the other sheet might be different so we will look up the row number from the key field - build a hash table for that here
     #If we have "*" as the key ad the script property to concatenate the [selected] properties.

     $rowHash = @{}
     $rowNo = $firstDataRow
     foreach ($row in $ReferenceObject)  {
        if   ($null -eq $row._row) {Add-Member -InputObject $row -MemberType NoteProperty   -Value ($rowNo ++)  -Name "_Row" }
        else {$rowNo++ }
        if   ($Key      -eq '*'  ) {Add-Member -InputObject $row -MemberType ScriptProperty -Value $ConCatblock -Name "_All" }
     }
     $rowNo = $firstDataRow
     foreach ($row in $DifferenceObject) {
         Add-Member       -InputObject $row -MemberType NoteProperty   -Value $rowNo       -Name "$DiffPrefix Row" -Force
         if   ($Key       -eq '*' )    {
               Add-Member -InputObject $row -MemberType ScriptProperty -Value $ConCatblock -Name "_All"
               $rowHash[$row._All] = $rowNo
         }
         else {$rowHash[$row.$Key] = $rowNo  }
         $rowNo ++
     }
     if ($DifferenceObject.count -gt $rowHash.Keys.Count) {
        Write-Warning -Message "Difference object has $($DifferenceObject.Count) rows; but only $($rowHash.keys.count) unique keys"
     }
     if ($Key -eq '*') {$Key = "_ALL"}
 #endregion
     #We need to know all the properties we've met on the objects we've diffed
     $eDiffProps  = [ordered]@{}
     #When we do a compare object changes will result in two rows so we group them and join them together.
     $expandedDiff = Compare-Object -ReferenceObject $ReferenceObject -DifferenceObject $DifferenceObject -Property $propList -PassThru -IncludeEqual |
                        Group-Object -Property $Key | ForEach-Object {
                            #The value of the key column is the name of the Group.
                            $keyVal = $_.name
                            #we're going to create a custom object from a hash table.
                            $hash = [ordered]@{}
                            foreach ($result in $_.Group) {
                                if     ($result.SideIndicator -ne "=>")      {$hash["_Row"] = $result._Row  }
                                elseif (-not $hash["$DiffPrefix Row"])       {$hash["_Row"] = "" }
                                #if we have already set the side, this must be the second record, so set side to indicate "changed"; if we got two "Same" indicators we may have a classh of keys
                                if     ($hash.Side) {
                                    if ($hash.Side -eq $result.SideIndicator) {Write-Warning -Message "'$keyVal' may be a duplicate."}
                                        $hash.Side = "<>"
                                }
                                else   {$hash["Side"] = $result.SideIndicator}
                                switch ($hash.side) {
                                    '==' {      $hash["$DiffPrefix is"] = 'Same'   }
                                    '=>' {      $hash["$DiffPrefix is"] = 'Added'  }
                                    '<>' { if (-not $hash["_Row"]) {
                                                $hash["$DiffPrefix is"] = 'Added'
                                            }
                                            else {
                                                $hash["$DiffPrefix is"] = 'Changed'
                                            }
                                         }
                                    '<=' {      $hash["$DiffPrefix is"] = 'Removed'}
                                    }
                                 #find the number of the row in the the "difference" object which has this key. If it is the object is only in the reference this will be blank.
                                 $hash["$DiffPrefix Row"] = $rowHash[$keyVal]
                                 $hash[$Key]              = $keyVal
                                 #Create FieldName and/or =>FieldName columns
                                 foreach  ($p in $result.psobject.Properties.name.where({$_ -ne $Key -and $_ -ne "SideIndicator" -and $_ -ne "$DiffPrefix Row" })) {
                                    if     ($result.SideIndicator -eq "==" -and $p -in $propList)
                                                                             {$hash[("$p")] = $hash[("$DiffPrefix $p")] = $result.$P}
                                    elseif ($result.SideIndicator -eq "==" -or $result.SideIndicator -eq "<=")
                                                                             {$hash[("$p")]                             = $result.$P}
                                    elseif ($result.SideIndicator -eq "=>")  {                $hash[("$DiffPrefix $p")] = $result.$P}
                                 }
                             }

                             foreach ($k in $hash.keys) {$eDiffProps[$k] = $true}
                             [Pscustomobject]$hash
     }  | Sort-Object -Property "_row"

     #Already sorted by reference row number, fill in any blanks in the difference-row column.
     for ($i = 1; $i -lt $expandedDiff.Count; $i++) {if (-not $expandedDiff[$i]."$DiffPrefix Row") {$expandedDiff[$i]."$DiffPrefix Row" = $expandedDiff[$i-1]."$DiffPrefix Row" } }

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

     $AllProps = @("_Row") + $OutputProps + $eDiffProps.keys.where({$_ -notin ($outputProps + @("_row","side","SideIndicator","_ALL" ))})

     if     ($PassThru -or -not $OutputFile) {return  ($expandedDiff | Select-Object -Property $allprops  | Sort-Object -Property  "_row", "$DiffPrefix Row"    )  }
     elseif ($PSCmdlet.ShouldProcess($OutputFile,"Write Output to Excel file")) {
         $expandedDiff =  $expandedDiff | Sort-Object -Property  "_row", "$DiffPrefix Row"
         $xl = $expandedDiff | Select-Object -Property   $OutputProps    | Update-FirstObjectProperties      |
           Export-Excel -Path $OutputFile -WorksheetName $OutputSheetName -FreezeTopRow -BoldTopRow -AutoSize -AutoFilter -PassThru
         $ws =  $xl.Workbook.Worksheets[$OutputSheetName]
         for ($i = 0; $i -lt $expandedDiff.Count; $i++ ) {
            if     ( $expandedDiff[$i].side -ne "==" )  {
                Set-ExcelRange -Worksheet $ws     -Range ("A" + ($i + 2 )) -FontColor       $KeyFontColor
            }
            elseif ( $HideEqual                      )  {$ws.row($i+2).hidden = $true }
            if     ( $expandedDiff[$i].side -eq "<>" )  {
                $range = $ws.Dimension -replace "\d+",  ($i + 2 )
                Set-ExcelRange -Worksheet $ws     -Range $range            -BackgroundColor $ChangeBackgroundColor
            }
            elseif ( $expandedDiff[$i].side -eq "<=" )  {
                $rangeR1C1 = "R[{0}]C[1]:R[{0}]C[{1}]" -f ($i + 2 ) , $lastRefColNo
                $range = [OfficeOpenXml.ExcelAddress]::TranslateFromR1C1($rangeR1C1,0,0)
                Set-ExcelRange -Worksheet $ws     -Range $range            -BackgroundColor $DeleteBackgroundColor
            }
            elseif ( $expandedDiff[$i].side -eq "=>" )  {
                if ($propList.count -gt 1) {
                    $rangeR1C1 = "R[{0}]C[{1}]:R[{0}]C[{2}]" -f ($i + 2 ) , $FirstDiffColNo , $lastDiffColNo
                    $range = [OfficeOpenXml.ExcelAddress]::TranslateFromR1C1($rangeR1C1,0,0)
                    Set-ExcelRange -Worksheet $ws -Range $range            -BackgroundColor $AddBackgroundColor
                }
                Set-ExcelRange -Worksheet $ws     -Range ("A" + ($i + 2 )) -BackgroundColor $AddBackgroundColor
            }
         }
         Close-ExcelPackage -ExcelPackage $xl -Show:$Show
     }
}