PowerShell拆分含两行表头的Excel文件问题求助

问题描述 #

我有一个包含两行表头的Excel文件，希望按「Test Folder ID」作为唯一标识拆分为多个Excel文件。目前代码已能生成对应数量的文件，但新文件无法保留两行表头，现附上原代码寻求帮助：

Function Create-Excel-Spreadsheet {
    Param($NameOfSpreadsheet)

    # open excel
    $excel = New-Object -ComObject excel.application
    $excel.visible = $true

    # add a worksheet
    $workbook = $excel.Workbooks.Add()
    $xl_wksht= $workbook.Worksheets.Item(1)
    $xl_wksht.Name = $NameOfSpreadsheet

    return $workbook
    $objexcel.Quit()
    [System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel) | Out-Null
    Remove-Variable excel
}


$objexcel = New-Object -ComObject Excel.Application
$wb = $objexcel.WorkBooks.Open("C:\\temp\\SPLIT\\PanayaTestCaseUpload-Avi.xlsx") # Changing path for test.xlsx file.

$objexcel.Visible = $true
$objexcel.DisplayAlerts = $False
$ws = $wb.Worksheets.Item(1)

$usedRange = $ws.UsedRange
$usedRange.AutoFilter()

$totalRows = $usedRange.Rows.Count

$rangeForUnique = $usedRange.Offset(1, 0).Resize($UsedRange.Rows.Count-1) 
[string[]]$UniqueListOfRowValues = $rangeForUnique.Columns.Item(1).Value2 | sort -Unique
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault

for ($i = 0; $i -lt $UniqueListOfRowValues.Count; $i++) {
    $newRange = $usedRange.AutoFilter(1, $UniqueListOfRowValues[$i])

    $workbook = Create-Excel-Spreadsheet $UniqueListOfRowValues[$i]
    $wksheet = $workbook.Worksheets.Item(1)

    $range = $ws.UsedRange.Cells
    $range.Copy()

    $wksheet.Paste($wksheet.Range("A1"))
    $workbook.SaveAs("C:\\temp\\SPLIT\\OUTPUT\\" + $UniqueListOfRowValues[$i], $xlFixedFormat)
    $workbook.Close()
}

$objexcel.Quit()
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel) | Out-Null
Remove-Variable excel

示例Excel中，按Test Folder ID应生成2个新文件，但新文件缺失两行表头。

问题原因 #

1. 唯一值范围错误：原代码从第2行提取Test Folder ID的唯一值，而实际存在两行表头，应该从第3行开始提取数据行的唯一值，否则会将第2行表头内容计入唯一值列表。
2. AutoFilter起始行错误：直接对整个UsedRange应用筛选，筛选器默认放在第1行，导致筛选过程中可能丢失第二行表头。
3. 函数资源泄漏：Create-Excel-Spreadsheet函数中return语句后的资源释放代码永远不会执行，会造成Excel进程残留。

修正后的代码 #

Function Create-Excel-Spreadsheet {
    Param($NameOfSpreadsheet)

    # 打开Excel应用（设为不可见避免弹窗干扰）
    $excel = New-Object -ComObject excel.application
    $excel.Visible = $false

    # 创建工作簿并命名工作表
    $workbook = $excel.Workbooks.Add()
    $xl_wksht = $workbook.Worksheets.Item(1)
    $xl_wksht.Name = $NameOfSpreadsheet

    # 保存Excel实例引用，后续在外部释放资源
    $script:excelInstance = $excel
    return $workbook
}


$objexcel = New-Object -ComObject Excel.Application
$objexcel.Visible = $false
$objexcel.DisplayAlerts = $False
$wb = $objexcel.WorkBooks.Open("C:\\temp\\SPLIT\\PanayaTestCaseUpload-Avi.xlsx")
$ws = $wb.Worksheets.Item(1)

$usedRange = $ws.UsedRange
# 将筛选器应用在第二行（两行表头的最后一行），确保表头完整保留
$ws.Range("A2").AutoFilter()

$totalRows = $usedRange.Rows.Count

# 从第3行开始提取Test Folder ID的唯一值（跳过前两行表头）
$rangeForUnique = $usedRange.Offset(2, 0).Resize($UsedRange.Rows.Count - 2) 
[string[]]$UniqueListOfRowValues = $rangeForUnique.Columns.Item(1).Value2 | Where-Object { $_ -ne $null } | Sort-Object -Unique
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault

foreach ($folderId in $UniqueListOfRowValues) {
    # 按当前Test Folder ID筛选数据
    $ws.Range("A2").AutoFilter(1, $folderId)

    # 创建新工作簿
    $workbook = Create-Excel-Spreadsheet $folderId
    $wksheet = $workbook.Worksheets.Item(1)

    # 复制原工作表的可见单元格（包含两行表头和筛选后的数据）
    $ws.UsedRange.SpecialCells([Microsoft.Office.Interop.Excel.XlCellType]::xlCellTypeVisible).Copy()
    $wksheet.Paste($wksheet.Range("A1"))

    # 保存并关闭新工作簿
    $workbook.SaveAs("C:\\temp\\SPLIT\\OUTPUT\\$folderId", $xlFixedFormat)
    $workbook.Close()

    # 释放新创建的Excel实例资源
    $script:excelInstance.Quit()
    [System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($script:excelInstance) | Out-Null
    Remove-Variable excelInstance -Scope Script
}

# 释放原Excel实例资源
$objexcel.Quit()
[System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($objexcel) | Out-Null
Remove-Variable objexcel

关键修改说明 #

• 调整筛选起始行：用$ws.Range("A2").AutoFilter()替代$usedRange.AutoFilter()，确保筛选器从第二行表头开始，完整保留两行表头。
• 修正唯一值提取范围：$usedRange.Offset(2, 0).Resize($UsedRange.Rows.Count - 2) 从第3行开始提取数据行的Test Folder ID，避免包含表头内容。
• 修复资源泄漏：将Create-Excel-Spreadsheet函数中的Excel实例引用传递到外部，在循环中及时关闭并释放资源。
• 复制可见单元格：使用SpecialCells(xlCellTypeVisible)只复制筛选后的可见内容，避免复制隐藏行。

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