Add new column and populate with worksheet name using Powershell

Question

I'm trying to manipulate the code below to create a new column in the output and assign the sheetname in the new column.

$param = @{
    Path       = 'C:\TEMP\Template\TemplateName.xlsx'
    StartRow   = 5
    HeaderName = 'Property', 'Current settings', 'Proposed settings'
}

# foreach worksheet in this file
Get-ExcelSheetInfo -Path 'C:\TEMP\Template\TemplateName.xlsx' | ForEach-Object {
    # set the worksheetname name in $param
    $param['WorksheetName'] = $_.Name
    # import the worksheet and enumerate it
    foreach($line in Import-Excel @param) {
        $currSettings = $line.'Current settings'
        $propSettings = $line.'Proposed settings'
        # if the value of 'Current settings' cell is equal to the value of
        # 'Proposed settings' cell OR is empty / white spaces, skip it, go to next iteration
        if($currSettings -eq $propSettings -or [string]::IsNullOrWhiteSpace($currSettings)) {
            continue
        }
        # if we're here, condition before this was not true, hence we want to
        # output this line
        $line
    }
} | Export-Excel -Path C:\Temp\Template\Changes.xlsx -AutoSize

The sheetname is already assigned to the $_.Name variable, but I would like to add it in a new column (A) next to all rows. For eg. every row taken from a particular sheet should have the sheetname in Column A.

Answer 1

You can use a calculated property with Select-Object to recreate each object ($line) adding the WorksheetName property:

Get-ExcelSheetInfo -Path 'C:\TEMP\Template\TemplateName.xlsx' | ForEach-Object {
    $param['WorksheetName'] = $_.Name
    foreach($line in Import-Excel @param) {
        $currSettings = $line.'Current settings'
        $propSettings = $line.'Proposed settings'
        if($currSettings -eq $propSettings -or [string]::IsNullOrWhiteSpace($currSettings)) {
            continue
        }
        $line | Select-Object @{N='WorksheetName';E={$param['WorksheetName']}}, *
    }
} | Export-Excel -Path C:\Temp\Template\Changes.xlsx -AutoSize

Alternatively, you can add the new property to the existing object, instead of recreating it, however this would add the property on last position (last Column in the Excel file):

# update the object
$line.PSObject.Properties.Add([psnoteproperty]::new('WorksheetName', $_.Name))
# output the object, to be captured by `Export-Excel`
$line