PowerShell加速读取10万行Excel至哈希表时遇索引空值错误求助

问题背景 #

现有一份10万行25列的Excel文件，需求是读取第1列内容，并将对应行的第2列、第12列值存入哈希表。原逐行读取的PowerShell代码耗时数小时，尝试通过一次性读取单元格范围的优化方案时，触发「索引操作失败；数组索引计算为null」错误。

原逐行读取代码 #

# 先打开Excel工作簿:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\anil\VM_Data_1228_2022.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("VMAudit")
# 获取工作表中已填充的行数
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# 从第2行开始遍历第1列的所有行（这些单元格存储虚拟机名称）
$report = @{}
$outFile = "vm_info.csv"
"VM Name,Vcenter,Disk usage" | Out-File -FilePath $outFile
for($i=2;$i -le $rowcount;$i++){

$vmname = $ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
if ( $vmname -inotin $report.keys){
                        $report[$vmname] = @{}
                        $report[$vmname]['vcenter'] = $ExcelWorkSheet.Columns.Item(2).Rows.Item($i).Text
                        $report[$vmname]['datasize'] = $ExcelWorkSheet.Columns.Item(12).Rows.Item($i).Text

                                    }
}

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

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

优化尝试代码 #

$data = $ExcelWorksheet.Range("A1:Z100000").Value2
for( $row = $data.GetLowerBound(0); $row -lt $data.GetUpperBound(0); $row++) { 
                         # 输出虚拟机名称 
                         $vmname = $data[$row, 1]
                          
                         if ( $vmname -inotin $report.keys){
                        $report[$vmname] = @{}
                        $report[$vmname]['vcenter'] = $data[$row, 5]
                        $report[$vmname]['datasize'] = $data[$row, 12]
                                                           }

报错信息 #

索引操作失败；数组索引计算为null。
在 C:\anil\scripts\get_vm_disk_usage_from_excel.ps1:20 字符:25

• $report[$vmname] = @{}
• ~~~~~~~~~~~~~~~~~~~~~~
  • 类别信息: 无效操作: (:) [], 运行时异常
  • 完全限定错误ID: NullArrayIndex

索引操作失败；数组索引计算为null。
在 C:\anil\scripts\get_vm_disk_usage_from_excel.ps1:21 字符:25

• $report[$vmname]['vcenter'] = $data[$row, 5]
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • 类别信息: 无效操作: (:) [], 运行时异常
  • 完全限定错误ID: NullArrayIndex

索引操作失败；数组索引计算为null。
在 C:\anil\scripts\get_vm_disk_usage_from_excel.ps1:22 字符:25

• ... $report[$vmname]['datasize'] = $data[$row, 12]
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • 类别信息: 无效操作: (:) [], 运行时异常
  • 完全限定错误ID: NullArrayIndex

索引操作失败；数组索引计算为null。
在 C:\anil\scripts\get_vm_disk_usage_from_excel.ps1:20 字符:25

• $report[$vmname] = @{}
• ~~~~~~~~~~~~~~~~~~~~~~
  • 类别信息: 无效操作: (:) [], 运行时异常
  • 完全限定错误ID: NullArrayIndex

索引操作失败；数组索引计算为null。
在 C:\anil\scripts\get_vm_disk_usage_from_excel.ps1:21 字符:25

• $report[$vmname]['vcenter'] = $data[$row, 5]
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • 类别信息: 无效操作: (:) [], 运行时异常
  • 完全限定错误ID: NullArrayIndex
    }

问题分析与修正方案 #

报错原因 #

1. 哈希表键不能为Null：当读取空行或空单元格导致$vmname为Null时，尝试用Null作为哈希表$report的键，触发索引错误。
2. 列索引错误：原代码中vcenter对应第2列，但优化代码错误使用了第5列（$data[$row,5]），与需求不符。
3. 硬编码读取范围：A1:Z100000包含大量空行，导致读取到Null值，同时可能超出实际使用范围。
4. 循环范围问题：$row -lt $data.GetUpperBound(0)会漏掉最后一行数据，应使用-le。

修正后的优化代码 #

# 初始化Excel对象
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $false # 后台运行，不显示Excel窗口
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\anil\VM_Data_1228_2022.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("VMAudit")

# 获取实际使用的单元格范围，避免读取空行
$usedRange = $ExcelWorkSheet.UsedRange
$data = $usedRange.Value2
$report = @{}
$outFile = "vm_info.csv"
# 写入CSV表头
"VM Name,Vcenter,Disk usage" | Out-File -FilePath $outFile -Encoding UTF8

# 遍历数据行，从第2行开始（跳过表头）
for( $row = 2; $row -le $data.GetUpperBound(0); $row++) { 
    $vmname = $data[$row, 1]
    # 跳过空的虚拟机名称行
    if (-not $vmname) { continue }
    # 仅当虚拟机名称未在哈希表中时添加
    if ($vmname -notin $report.Keys) {
        $report[$vmname] = @{
            'vcenter' = $data[$row, 2] # 修正为第2列
            'datasize' = $data[$row, 12]
        }
    }
}

# 关闭Excel并释放资源，避免残留进程
$ExcelWorkBook.Close($true)
$ExcelObj.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelWorkSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelWorkBook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObj) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

# 将哈希表内容写入CSV
$report.GetEnumerator() | ForEach-Object {
    "$($_.Name),$($_.Value.vcenter),$($_.Value.datasize)" | Out-File -FilePath $outFile -Append -Encoding UTF8
}

额外优化建议 #

使用ImportExcel模块替代COM对象，读取速度更快且无需处理Excel进程残留：

# 安装模块（首次运行）
# Install-Module -Name ImportExcel -Scope CurrentUser -Force
$data = Import-Excel -Path "C:\anil\VM_Data_1228_2022.xlsx" -WorksheetName "VMAudit"
$report = @{}
$outFile = "vm_info.csv"
"VM Name,Vcenter,Disk usage" | Out-File -FilePath $outFile -Encoding UTF8
foreach ($row in $data) {
    $vmname = $row.PSObject.Properties[0].Value # 获取第1列值
    if (-not $vmname) { continue }
    if ($vmname -notin $report.Keys) {
        $report[$vmname] = @{
            'vcenter' = $row.PSObject.Properties[1].Value # 第2列（索引从0开始）
            'datasize' = $row.PSObject.Properties[11].Value # 第12列（索引从0开始）
        }
    }
}
$report.GetEnumerator() | ForEach-Object {
    "$($_.Name),$($_.Value.vcenter),$($_.Value.datasize)" | Out-File -FilePath $outFile -Append -Encoding UTF8
}

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