如何使用PowerShell更新含已知名称多工作表的现有Excel文件

以下是适配多工作表场景的PowerShell代码，可读取现有Excel中的所有工作表，逐个完成AD信息填充后，导出为新的多工作表Excel文件：

Import-Module ActiveDirectory
Import-Module ImportExcel  # 确保已安装ImportExcel模块

# 定义文件路径
$sourcePath = "D:\users.xlsx"
$targetPath = "D:\new_users.xlsx"

# 获取源文件中所有工作表名称
$allWorksheets = Import-Excel -Path $sourcePath -ListWorksheet

# 初始化Excel包（首次导出时创建文件）
$excelPackage = $null

foreach ($worksheetName in $allWorksheets) {
    # 读取当前工作表数据
    $sheetData = Import-Excel -Path $sourcePath -WorksheetName $worksheetName

    if (-not $sheetData) {
        Write-Warning "工作表 $worksheetName 无数据，跳过处理"
        continue
    }

    # 获取现有列名并插入新列（位置6、7）
    $properties = [Collections.Generic.List[string]]$sheetData[0].psobject.Properties.Name
    $properties.Insert(6, "Name")
    $properties.Insert(7, "Team")

    # 遍历每行从AD获取用户信息并添加属性
    foreach ($row in $sheetData) {
        # 注意：此处Get-AD需替换为实际有效的AD查询逻辑（比如默认Get-ADUser）
        $user = Get-AD -CN $row.ID
        if ($user) {
            $row | Add-Member -Name 'Name' -Value $user[0] -MemberType NoteProperty
            $row | Add-Member -Name 'Team' -Value $user[1] -MemberType NoteProperty
        } else {
            # 处理AD查询失败的情况
            $row | Add-Member -Name 'Name' -Value "未找到" -MemberType NoteProperty
            $row | Add-Member -Name 'Team' -Value "未找到" -MemberType NoteProperty
        }
    }

    # 将处理后的数据导出到目标文件
    if (-not $excelPackage) {
        # 首次导出创建新文件
        $excelPackage = $sheetData | Select-Object -Property $properties | 
            Export-Excel -Path $targetPath -WorksheetName $worksheetName -PassThru
    } else {
        # 后续工作表追加到现有文件
        $sheetData | Select-Object -Property $properties | 
            Export-Excel -ExcelPackage $excelPackage -WorksheetName $worksheetName -PassThru | Out-Null
    }
}

# 关闭Excel包并打开文件
if ($excelPackage) {
    Close-ExcelPackage $excelPackage -Show
} else {
    Write-Warning "无有效工作表数据被处理"
}

关键逻辑说明 #

• 获取所有工作表：通过Import-Excel -ListWorksheet获取源文件中所有工作表名称，实现批量遍历。
• 逐表处理：对每个工作表重复单表处理逻辑，包括列插入、AD数据查询、属性添加。
• 多表导出：首次导出创建新文件，后续通过-ExcelPackage参数复用已打开的包对象，实现多工作表写入同一文件。
• 异常处理：添加AD查询失败的兜底逻辑，避免空值导致的导出错误。

注意事项 #

• 确保已安装ImportExcel模块：可通过Install-Module -Name ImportExcel命令安装。
• 调整AD查询逻辑：如果使用默认ActiveDirectory模块，需将Get-AD -CN $row.ID替换为实际查询语句，例如：

  $user = Get-ADUser -Filter {SamAccountName -eq $row.ID} -Properties DisplayName, Department
  $row.Name = $user.DisplayName
  $row.Team = $user.Department
  

内容的提问来源于stack exchange，提问作者John Doe