zJoinObject.JoinWorksheet.ps1

zJoinObject.JoinWorksheet.ps1

#Define some input data.
    $l = 1..5 | Foreach-Object {
        [pscustomobject]@{
            Name = "jsmith$_"
            Birthday = (Get-Date).adddays(-1)
        }
    }

    $r = 4..7 | Foreach-Object{
        [pscustomobject]@{
            Department = "Department $_"
            Name = "Department $_"
            Manager = "jsmith$_"
        }
    }

#Export it to a spreadsheet with specific worksheet names
    $l | export-xlsx -Path C:\temp\Join.xlsx -WorksheetName Left
    $r | export-xlsx -Path C:\temp\Join.xlsx -WorksheetName Right

#Get the worksheets:
    $Excel = New-Excel -Path C:\temp\Join.xlsx
    $LeftWorksheet = Get-Worksheet -Excel $Excel -Name 'Left'
    $RightWorksheet = Get-WorkSheet -Excel $Excel -Name 'Right'

#We have the data - join it where Left.Name = Right.Manager
    Join-Worksheet -Path C:\temp\Merged.xlsx -LeftWorksheet $LeftWorksheet -RightWorksheet $RightWorksheet -LeftJoinColumn Name -RightJoinColumn Manager
    $Excel | Close-Excel

#Verify the output:
    Import-XLSX -Path C:\temp\Merged.xlsx