excel - 作为参数传递时值为空

Question

我正在从 excel 电子表格中读取以批量格式化一些文本。当我执行以下操作时，一切正常。

Function Read-From-Excel {
    Param(
        [string]$fileName,
        [string]$sheetName="Sheet1"
    )

    $excelObject = New-Object -ComObject Excel.Application
    $excelObject.Visible = $FALSE
    $excelBook = $excelObject.Workbooks.Open($fileName)
    $excelSheet = $excelBook.Sheets.Item($sheetName)
    $intRowMax =  ($excelSheet.UsedRange.Rows).count

    $col = 1
    $row = 1

    $headers= @()

    While ($TRUE) {
        $column = $excelSheet.cells.Item($row, $col).Text
        If ($column -eq "") {
            Break
        }
        $headers += $column
        $col ++
    }

    $text = ""

    For ($row = 2; $row -le $intRowMax; $row++) {
        # Wrapped in another function starting here    
        $cell = $excelSheet.cells.Item($row, 1).Text

        If ($cell.StartsWith("#")) {
            $text += "-- {0}`n" -f $cell.substring(1)
        } Else {
            $c1 = $cell
            $c2 = $excelSheet.cells.Item($row, 2).Text -creplace '\s+', ''
            $c3= $excelSheet.cells.Item($row, 3).Text -creplace '\s+', ''
            $c4 = $excelSheet.cells.Item($row, 4).Text
            $c5 = $excelSheet.cells.Item($row, 5).Text

            $text += Format-Row $c1 $c2 $c3 $c4 $c5
        }
        # Until here
    }

    Write-Host $headers
    Write-Host $text
    $excelObject.quit()
}

但是，当我将内部部分包装在函数中时，出现错误:

You cannot call a method on a null-valued expression.
At C:\Users\dannnno\desktop\FormatFromExcel.ps1:311 char:27
+     $cell = $excelSheet.cells.Item <<<< ($row, 1).Text
    + CategoryInfo               : InvalidOperation: (Item:String) [], RuntimeException
    + FullyQualifiedErrorId      : InvokeMethodOnNull

该功能看起来像这样

Function _Get-Next-Block-Excel-File {
    Param(
        [Object[]]$sheet,
        [int]$row
    )

    # This is all the same, except with the name changed to $sheet
}

并且原始代码将内部部分更改为

$text += _Get-Next-Block-Excel-File $excelSheet $row

有没有一种特殊的方法我必须在 PowerShell 中传递这个参数？

Answer 1

您不需要 Object[]数组作为 Function _Get-Next-Block-Excel-File 中的第一个参数, [Object]$sheet会很好，因为您没有将一组工作表传递给该函数。