使用 Powershell 添加新列并填充工作表名称

我正在尝试操作下面的代码以在输出中创建一个新列并在新列中分配工作表名称。

$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

工作表名称已分配给 $_.Name 变量，但我想将其添加到所有行旁边的新列 (A) 中。例如。从特定工作表中取出的每一行都应在 A 列中具有工作表名称。

您可以使用计算属性Select-Object https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/select-object?view=powershell-7.2#example-11--create-calculated-properties-for-each-inputobject重新创建每个对象（$line）添加WorksheetName财产：

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

或者，您可以将新属性添加到现有对象，而不是重新创建它，但这会将该属性添加到最后一个位置（Excel 文件中的最后一列）：

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