如何修复PowerShell中CSV转Excel时出现的Cannot index into a null array错误

问题原因 #

• 核心错误为代码逻辑顺序错误：你将Export-Excel导出、工作表配置、关闭Excel包的逻辑全部放在了遍历CSV行的foreach循环内部。第一次循环执行完成后你就调用Close-ExcelPackage关闭了Excel对象，第二次循环时$xlsx已经为空，访问$xlsx.Workbook自然触发「无法索引空数组」报错。
• 潜在风险点：Get-AzureADUser如果未匹配到对应用户ID会返回空值，直接调用.DisplayName也会触发空引用报错。
• 冗余逻辑：每次循环都重复过滤$actionReference数组，执行效率较低。

修正后代码 #

function Convert-to-Excel{
    $params = @{
        AutoSize      = $true
        TableStyle    = 'Medium6'
        BoldTopRow    = $true
        WorksheetName = 'Audit Log'
        PassThru      = $true
        Path          = "C:\AuditLogSearch\$((Get-Date).AddDays(-7).ToString('yyyy-MM-dd')) _ $(Get-Date -Format "yyyy-MM-dd") Audit-Log-Records11.xlsx"
    }

    $modifiedFile = Import-Csv "C:\AuditLogSearch\Modified-Audit-Log-Records.csv"
    $actionReference = Import-Csv "C:\AuditLogSearch\Reference\Action.csv"
    # 转为哈希表提升匹配效率
    $actionMap = @{}
    foreach ($act in $actionReference) {
        $actionMap[$act.Name] = $act.Value
    }

    # 先批量处理所有行数据 再统一导出
    $outputData = foreach ($u in $modifiedFile) {
        # 兼容用户查询为空的场景
        $azureUser = Get-AzureADUser -ObjectId $u.User -ErrorAction SilentlyContinue
        $userName = if ($azureUser) { $azureUser.DisplayName } else { $u.User }
        # 匹配动作类型
        $actionType = if ($actionMap.ContainsKey($u."Type of Action")) { $actionMap[$u."Type of Action"] } else { $u."Type of Action" }
        
        [PSCustomObject]@{
            User              = $userName
            "Search Criteria" = $u."Search Criteria"
            "Result Status"   = $u."Result Status"
            "Date & Time"     = $u."Date & Time"
            "Type of Action"  = $actionType
        }
    }

    # 统一导出后再做表格配置
    $xlsx = $outputData | Export-Excel @params
    $ws = $xlsx.Workbook.Worksheets[$params.Worksheetname]
    $ws.View.ShowGridLines = $false # 隐藏网格线
    Close-ExcelPackage $xlsx
}

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