powershell - 无法获取工作表类的粘贴属性

Question

我有下面的脚本，它将通过 PowerShell 脚本根据第一列的值拆分并保存一个 excel 文件。这是excel文件的构建方式（应用程序700k-1M行取决于哪个文件，因为我需要拆分多个文件）

´´´    Column1 #  Column2  #  Column3   ´´´
´´´ AA # data  #  data     #  data      ´´´
´´´ AA # data  #  data     #  data      ´´´
´´´ AB # data  #  data     #  data      ´´´
´´´ AC # data  #  data     #  data      ´´´
´´´ AC # data  #  data     #  data      ´´´

结果应该是多个文件，文件名为 AA.xlxs、AB.xlxs、AC.xlxs，当然还有相应的行数据。

该脚本正在运行，但我遇到的一个小问题是大约 4000 个文件，其中少数文件不包含数据。

发生这种情况时我收到错误消息；

    Unable to get the Paste property of the Worksheet class
At line:43 char:5
+   $wksheet.Paste($wksheet.Range("A1"))
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    +   CategoryInfo          : OperationStopped: (:) [], COMException
    +   FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

该文件仍然使用正确的命名约定创建并且保存良好，但它缺少与唯一值相关的数据。每次我运行脚本时也不会发生这种情况。有时它不会抛出错误，有时它可能是 1 个文件，也可能是 3 个或 4 个文件。它也永远不会是相同的文件名。

是否有任何理由说明为什么会发生这种情况以及我该如何解决？

我的脚本在下面；

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

# open excel
$objexcelnew = New-Object -ComObject Excel.application
$objexcelnew.visible = $false


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

return $workbook
}

$objexcelexis = New-Object -ComObject Excel.Application
$wb = $objexcelexis.WorkBooks.Open(($path = "C:\Users\Desktop\test.xlsx")) # Change the path for the location of the excel file.

$objexcelexis.Visible = $false
$objexcelexis.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

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"))
    $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
    $workbook.Activesheet.Cells.EntireColumn.Autofit();
    $wksheet = $Workbook.worksheets.Item(1)
    $wksheet.PageSetup.Orientation = 2
    $workbook.SaveAs("C:\Users\Desktop\" + $UniqueListOfRowValues[$i], $xlFixedFormat) # Change the save path for the xlsx files
    $workbook.Close($false)

    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($rangeForUnique.Columns.Item(1))
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ws.UsedRange.Cells)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($range)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wksheet)
    [System.Windows.Forms.Clipboard]::Clear()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objexcelexis)
    [System.GC]::Collect()

   }

标签： powershellpowershell-2.0powershell-3.0excel-2016