如何用PowerShell按Office列拆分AD用户Excel数据到不同工作表？

已通过Export-Excel将带有extensionAttribute "ABC"的所有AD用户导出到Datafile.xlsx，但这些用户来自不同办公室，希望将对应各办公室的用户列表自动拆分到Excel的独立工作表中。手动按Office列排序分表效率极低（涉及数百用户），而按办公室分别导出需要手动操作50余次，且不清楚所有办公室的确切名称。需要用PowerShell遍历Office列的所有条目，将同一办公室的用户数据批量填充到独立的Excel工作表中。

示例数据 #

期望结果 #

EastUS、WestUS、Europe各对应一个独立工作表，分别存放对应办公室的用户。

用户编写的无效代码 #

# Specify the path to your Excel file
$excelPath = 'C:\temp\DataFile.xlsx'
# Read the Excel data
$data = Import-Excel -Path $excelPath -WorksheetName 'WorksheetName'

# Sort the data by the "Office" column
$sortedData = $data | Sort-Object Office

# Create a new Excel workbook
$outputWorkbook = New-Object -TypeName OfficeOpenXml.ExcelPackage

# Iterate through each unique name and create a worksheet
 foreach ($name in $sortedData.Office | Get-Unique) {
 
 $filteredData = $sortedData | Where-Object { $_.Office -eq $name }
 $worksheet = $outputWorkbook.Workbook.Worksheets.Add($name)
 $worksheet.Cells.LoadFromCollection($filteredData, $true)
 $worksheet.Cells[$worksheet.Dimension.Address].AutoFitColumns()
 }

# Save the output workbook
$outputPath = 'C:\temp\DataFileSorted.xlsx'
$outputWorkbook.SaveAs($outputPath)
$outputWorkbook.Dispose()

Write-Host "Excel file with sorted tabs created at: $outputPath"

修正后的可行代码 #

# 确保已安装ImportExcel模块
# Install-Module -Name ImportExcel -Scope CurrentUser -Force

# 指定输入文件路径
$excelPath = 'C:\temp\DataFile.xlsx'
# 读取Excel数据（如果原表是默认Sheet，可省略-WorksheetName参数）
$data = Import-Excel -Path $excelPath

# 按Office分组处理
$groupedData = $data | Group-Object -Property Office

# 创建输出Excel文件
$outputPath = 'C:\temp\DataFileSorted.xlsx'

# 遍历每个分组，生成对应工作表
foreach ($group in $groupedData) {
    # 跳过空Office值的分组（如果有的话）
    if ([string]::IsNullOrWhiteSpace($group.Name)) {
        continue
    }
    # 将当前分组的数据导出到对应工作表
    $group.Group | Export-Excel -Path $outputPath -WorksheetName $group.Name -AutoSize -Append
}

Write-Host "分表完成，文件路径：$outputPath"

关键修正说明 #

• 使用Group-Object替代Get-Unique+Where-Object：更高效地按Office分组，避免重复过滤数据，尤其适合大量用户场景。
• 改用Export-Excel的-Append参数：无需手动操作ExcelPackage对象，ImportExcel模块已封装好工作表创建和数据写入逻辑，减少代码复杂度和潜在错误。
• 增加空值处理：跳过Office列为空的用户分组，避免创建无效名称的工作表。
• 简化Sheet读取：如果原数据在默认工作表，可省略-WorksheetName参数，避免因Sheet名称错误导致读取失败。

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