使用PowerShell按列值拆分xlsx文件时部分输出文件含完整原始数据求助

• 特殊字符导致匹配失效：对应20个异常文件的A列唯一值，大概率包含*、?、~等通配符，或是单/双引号等特殊符号。Excel的AutoFilter接口默认会将这类字符识别为通配符规则，而非精确匹配的文本内容，最终筛选失效，复制了全量数据。
• COM对象异步执行延迟：4000次批量循环操作中，Excel COM对象的异步执行特性会导致筛选操作还未完成就触发了后续的复制逻辑，拿到的还是未筛选的全量数据集。
• 特殊值匹配失败：如果对应20个值为空值、带格式的长数值（如科学计数法显示的手机号/身份证号）、格式异常的日期，也会出现匹配失效的情况。
• 资源占用过高：原有代码每次创建新文件都要启动新的Excel进程，4000次循环会导致系统资源占用过高，进一步加大操作异常的概率。

# 定义Excel常量
$xlFixedFormat = 51 # 对应xlsx格式，无需另存为其他格式可直接使用
$xlCellTypeVisible = 12

Function Create-Excel-Spreadsheet {
    Param($NameOfSpreadsheet, $excelInstance)
    # 复用已有Excel实例，避免重复创建进程
    $workbook = $excelInstance.Workbooks.Add()
    $xl_wksht= $workbook.Worksheets.Item(1)
    $xl_wksht.Name = $NameOfSpreadsheet
    return $workbook
}

$objexcel = New-Object -ComObject Excel.Application
$objexcel.Visible = $false # 后台运行，提升执行效率
$objexcel.DisplayAlerts = $False
$wb = $objexcel.WorkBooks.Open("C:\Temp\Test.xlsx")
$ws = $wb.Worksheets.Item(1)
$usedRange = $ws.UsedRange
$usedRange.AutoFilter()

# 提取A列唯一值，处理空值/单值场景
$rangeForUnique = $usedRange.Offset(1, 0).Resize($usedRange.Rows.Count-1) 
$rawValues = $rangeForUnique.Columns.Item(1).Value2
if ($rawValues -isnot [array]) {$rawValues = @($rawValues)}
[string[]]$UniqueListOfRowValues = $rawValues | sort -Unique

for ($i = 0; $i -lt $UniqueListOfRowValues.Count; $i++) {
    $currentValue = $UniqueListOfRowValues[$i]
    # 转义通配符，实现精确匹配
    $escapedValue = $currentValue -replace '~', '~~' -replace '\*', '~*' -replace '\?', '~?'
    # 执行筛选，指定精确匹配规则
    $usedRange.AutoFilter(1, $escapedValue, 1) # 第三个参数1对应xlAnd，强制精确匹配
    # 等待筛选完成，仅保留可见行大于1（表头+至少一行数据）的场景
    Start-Sleep -Milliseconds 50
    $visibleRows = $usedRange.SpecialCells($xlCellTypeVisible).Rows.Count
    if ($visibleRows -le 1) {continue}

    $workbook = Create-Excel-Spreadsheet $currentValue $objexcel
    $wksheet = $workbook.Worksheets.Item(1)

    $ws.UsedRange.Cells.Copy()
    $wksheet.Paste($wksheet.Range("A1"))
    # 处理文件名特殊字符，避免保存失败
    $safeFileName = $currentValue -replace '[\\/:*?"<>|]', '_'
    $workbook.SaveAs("C:\temp\$safeFileName.xlsx", $xlFixedFormat)
    $workbook.Close()
}

# 清理COM对象，避免残留进程
$wb.Close()
$objexcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objexcel) | Out-Null
Remove-Variable objexcel

• 代码中新增了通配符转义逻辑，确保所有A列值都能实现精确匹配
• 复用同一个Excel实例执行所有操作，大幅降低资源占用，减少异常概率
• 新增了文件名特殊字符替换逻辑，避免因值包含非法路径字符导致保存失败
• 新增了COM对象清理逻辑，避免Excel进程残留在后台占用资源

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