PowerShell读取Excel时更新Hash Table失败，请求排查原因

问题场景 #

一段用于读取Excel文件并将数据存入哈希表的PowerShell代码，运行时在$report[$vmname] = @{}行触发报错，错误提示数组索引为null。

报错信息 #

Index operation failed; the array index evaluated to null.
At C:\anil\scripts\get_vm_disk_usage_from_excel.ps1:20 char:26 #

$report[$vmname] = @{}

• CategoryInfo          : InvalidOperation: (:) [], RuntimeException
  FullyQualifiedErrorId : NullArrayIndex
  

原代码 #

# Open an Excel workbook first:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("c:\anil\test.xlsx",2,$true)
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("Sheet1")
# Get the number of filled in rows in the XLSX worksheet
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
$report = @{}
$outFile = "vm_info.csv"
"VM Name,Vcenter,Disk usage" | Out-File -FilePath $outFile
######
$data = $ExcelWorksheet.Range("A1:Z100000").Value2
for( $row = 2 ; $row -lt $data.GetUpperBound(0); $row++) { 
                         $vmname = $data[$row, 1]
                         if ( $vmname -notin $report.keys){
                         $report[$vmname] = @{}
                        $report[$vmname]['vcenter'] = $data[$row, 5]
                        $report[$vmname]['datasize'] = $data[$row, 12]
                                                           }
                         }

$ExcelWorkBook.close($true) 
        $report.GetEnumerator()|foreach {

        "$($_.name),$($_.value.vcenter),$($_.value.datasize)" | Out-File -FilePath $outFile -Append
                                        }

报错原因 #

1. 哈希表键为null：PowerShell哈希表不允许使用null作为键。当Excel表格中A列（对应$data[$row,1]）的某行是空值时，$vmname会被赋值为null，此时执行$report[$vmname] = @{}就会触发索引为空的错误。
2. 循环范围包含空行：代码中用Range("A1:Z100000")获取数据，会包含大量未填充的空行，这些空行的$vmname必然是null，导致循环到这些行时触发错误。

解决方案 #

方案1：增加null值判断 #

在处理哈希表前，先检查$vmname是否不为null，避免用null作为键：

# Open an Excel workbook first:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("c:\anil\test.xlsx",2,$true)
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("Sheet1")
# Get the number of filled in rows in the XLSX worksheet
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
$report = @{}
$outFile = "vm_info.csv"
"VM Name,Vcenter,Disk usage" | Out-File -FilePath $outFile
######
$data = $ExcelWorksheet.Range("A1:Z100000").Value2
for( $row = 2 ; $row -lt $data.GetUpperBound(0); $row++) { 
    $vmname = $data[$row, 1]
    # 增加null判断，仅处理非空的VM名称
    if ($vmname -and $vmname -notin $report.keys){
        $report[$vmname] = @{}
        $report[$vmname]['vcenter'] = $data[$row, 5]
        $report[$vmname]['datasize'] = $data[$row, 12]
    }
}

$ExcelWorkBook.close($true) 
$report.GetEnumerator()|foreach {
    "$($_.name),$($_.value.vcenter),$($_.value.datasize)" | Out-File -FilePath $outFile -Append
}

方案2：缩小循环范围到实际数据行 #

利用之前获取的$rowcount限制循环行数，避免遍历大量空行：

# Open an Excel workbook first:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("c:\anil\test.xlsx",2,$true)
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("Sheet1")
# Get the number of filled in rows in the XLSX worksheet
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
$report = @{}
$outFile = "vm_info.csv"
"VM Name,Vcenter,Disk usage" | Out-File -FilePath $outFile
######
# 仅获取实际有数据的范围，而不是固定到Z100000
$data = $ExcelWorksheet.UsedRange.Value2
# 循环范围改为从2到实际行数$rowcount
for( $row = 2 ; $row -le $rowcount; $row++) { 
    $vmname = $data[$row, 1]
    if ($vmname -and $vmname -notin $report.keys){
        $report[$vmname] = @{}
        $report[$vmname]['vcenter'] = $data[$row, 5]
        $report[$vmname]['datasize'] = $data[$row, 12]
    }
}

$ExcelWorkBook.close($true) 
$report.GetEnumerator()|foreach {
    "$($_.name),$($_.value.vcenter),$($_.value.datasize)" | Out-File -FilePath $outFile -Append
}

额外优化建议 #

• 添加$ExcelObj.Visible = $false隐藏Excel窗口，避免运行时弹出界面
• 最后释放COM对象，防止Excel进程残留：

  [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelWorkSheet) | Out-Null
  [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelWorkBook) | Out-Null
  $ExcelObj.Quit()
  

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