Блог

У меня есть приведенный ниже сценарий, который разделит и сохранит файл Excel на основе значений первого столбца с помощью сценария PowerShell. Вот как создается файл 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:UsersDesktoptest.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:UsersDesktop"   $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()

   }