mardi 2 juin 2020

Implicitly map patterns to columns in Powershell

Im trying to append TXT to XLSX based on patterns. If the pattern matches with the column it should append. It works when I define the sheet columns and items explicitly. Example:

$sheet.cells.item($row, $Date.Column).value = $txtDate

I want to find and map the patterns to columns implicitly so I don't define the columns manually. However, I get the following error: HRESULT: 0x800A03EC. What's going wrong here?

$fileContent = @'
Node: ABC
Name: PC
Cluster: HS1
Node: XZZ
Name: CC
Cluster: HS2
Node: XYZ
Name: DD
Cluster: HS3
'@
$Testbestand = $fileContent.Split( [System.Environment]::NewLine,
          [System.StringSplitOptions]::RemoveEmptyEntries )

$linieIdLast = ''
$linieTemplate = [ordered]@{}
foreach ( $linie in $Testbestand ) {
    $linieId, $linieVal = $linie -split ":\s*",2
    if ( $linieId -in $linieTemplate.Keys ) {
        break
    } else {
        $linieIdLast = $linieId
        $linieTemplate.Add( $linieId, $Null )
    }
}

$linieComb = New-Object -TypeName PSCustomObject -Property $linieTemplate
$liniesAll = foreach ( $linie in $Testbestand ) {
    $linieId, $linieVal = $linie -split ":\s*",2
    $linieComb.$linieId = $linieVal.Trim()
    if ( $linieId -eq $linieIdLast ) {
        $linieComb
        $linieComb = New-Object -TypeName PSCustomObject -Property $linieTemplate
    }
}

$TargetFile = Targetfile
$sheetName    = Sheet
$objExcel     = New-Object -ComObject Excel.Application
$workBook     = $objExcel.Workbooks.Open($TargetFile)
$sheet    = $workBook.sheets.item($sheetName)
$row = 33     

    foreach ( $linieKey in $linieTemplate.Keys ) 
    {
       $column = ($sheet.Columns.Find($linieKey))
       $sheet.cells.item($row, $column).value = $linieTemplate.$linieKey
       $row++
    }

$workBook.Save()
$objExcel.Quit() 

Error:

At line:47 char:8
+        $sheet.cells.item(1, $column).value = $linieTemplate.$linieKey
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Aucun commentaire:

Enregistrer un commentaire