PowerShell拆分Excel时出现Worksheet类Paste属性获取失败如何解决

报错原因 #

• 循环内错误释放主Excel实例：你在for循环末尾执行了[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objexcelexis)，这个对象是打开源文件的全局Excel进程，第一次循环就会被释放，后续循环操作源文件的筛选、复制逻辑都会失效，剪贴板无有效数据就会触发Paste属性获取失败。
• 复制操作异步竞态：Excel的Copy()方法是异步执行的，处理70万行以上的大数据时，可能剪贴板还没完成数据写入就执行了Paste()，导致目标工作表没有可粘贴的内容。
• 重复创建Excel进程资源耗尽：每次生成新文件都调用Create-Excel-Spreadsheet新建独立的Excel COM实例，4000次循环会创建数千个Excel进程，系统内存、句柄资源被占满，部分操作超时失败。
• 无效COM对象重复释放：循环内反复释放$rangeForUnique.Columns.Item(1)这类仅初始化一次的对象，第一次释放后后续操作会产生无意义的COM异常，干扰正常执行流程。

修复后的完整脚本 #

Add-Type -AssemblyName System.Windows.Forms

Function Create-Excel-Spreadsheet {
    Param($ExcelInstance, $NameOfSpreadsheet)
    # 复用现有Excel实例，不新建进程减少资源消耗
    $workbook = $ExcelInstance.Workbooks.Add()
    $xl_wksht= $workbook.Worksheets.Item(1)
    $xl_wksht.Name = $NameOfSpreadsheet
    return $workbook
}

# 全局Excel实例仅初始化一次
$objexcelexis = New-Object -ComObject Excel.Application
$objexcelexis.Visible = $false
$objexcelexis.DisplayAlerts = $false

$wb = $objexcelexis.WorkBooks.Open("C:\Users\Desktop\test.xlsx") # 替换为源文件路径
$ws = $wb.Worksheets.Item(1)
$usedRange = $ws.UsedRange
$usedRange.AutoFilter()

$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++) {
    $currentValue = $UniqueListOfRowValues[$i]
    $usedRange.AutoFilter(1, $currentValue)

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

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

    # 剪贴板就绪判断，最多重试3次避免异步问题
    $retryCount = 0
    $pasteSuccess = $false
    while ($retryCount -lt 3 -and !$pasteSuccess) {
        Start-Sleep -Milliseconds 300
        if ([System.Windows.Forms.Clipboard]::ContainsData("XML Spreadsheet")) {
            try {
                $wksheet.Paste($wksheet.Range("A1"))
                $pasteSuccess = $true
            } catch {
                $retryCount++
            }
        } else {
            $retryCount++
        }
    }

    if (!$pasteSuccess) {
        Write-Warning "值 $currentValue 对应文件生成失败，已跳过"
        $workbook.Close($false)
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wksheet)
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
        [System.Windows.Forms.Clipboard]::Clear()
        continue
    }

    $workbook.Activesheet.Cells.EntireColumn.Autofit()
    $wksheet.PageSetup.Orientation = 2
    $workbook.SaveAs("C:\Users\Desktop\" + $currentValue, $xlFixedFormat) # 替换为保存路径
    $workbook.Close($false)

    # 仅释放当前循环生成的临时COM对象
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($range)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wksheet)
    [System.Windows.Forms.Clipboard]::Clear()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
}

# 所有操作完成后统一释放全局COM对象
$wb.Close($false)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($rangeForUnique.Columns.Item(1))
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($usedRange)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ws)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objexcelexis)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

核心修改说明 #

• 移除循环内的全局Excel实例释放逻辑，所有循环执行完成后统一释放资源，避免源文件操作中断
• 复用同一个Excel COM实例创建新工作簿，无需每次开启新进程，大幅降低系统资源消耗
• 新增剪贴板就绪判断和Paste失败重试逻辑，解决异步复制导致的粘贴失败问题
• 移除循环内无效的全局COM对象释放操作，避免空对象调用产生的异常
• 新增异常跳过逻辑，个别文件生成失败时不会中断整个拆分流程，可后续单独处理失败的取值

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