How to fix Cannot index into a null array in PowerShell-CodePudding

I'm trying to convert my CSV file to Excel file with some Table format and style but I'm getting "Cannot index into a null array" for some reason. I'll be really appreciated if I can get any help or suggestion. Thanks

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"

    $xlsx = foreach ($u in $modifiedFile) {
        $u.User = (Get-AzureADUser -ObjectId $u.User).DisplayName
        New-Object PsObject -Property @{
            User              = $u.User
            "Search Criteria" = $u."Search Criteria"
            "Result Status"   = $u."Result Status"
            "Date & Time"     = $u."Date & Time"
            "Type of Action"  = if (($actionReference | where-object { $_.Name -eq $u."Type of Action" }).Value -ne $null) { ($actionReference | where-object { $_.Name -eq $u."Type of Action" }).Value }
            else { $u."Type of Action" }
        } | Export-Excel @params

        $ws = $xlsx.Workbook.Worksheets[$params.Worksheetname]
        $ws.View.ShowGridLines = $false # => This will hide the GridLines on your file
        Close-ExcelPackage $xlsx
    } 
}

CodePudding user response：

You're closing the Excel Package on the first iteration of your loop hence why when it goes to the next it's trying to do something like this:

$null[$null] # => InvalidOperation: Cannot index into a null array

Try modifying your function so it looks like this instead:

First, construct the object[]:

$result = foreach ($u in $modifiedFile) {
    $u.User = (Get-AzureADUser -ObjectId $u.User).DisplayName
    New-Object PsObject -Property @{
        User              = $u.User
        "Search Criteria" = $u."Search Criteria"
        "Result Status"   = $u."Result Status"
        "Date & Time"     = $u."Date & Time"
        "Type of Action"  = if (($actionReference.........
        else { $u."Type of Action" }
    }
}

Then export it to Excel:

$xlsx = $result | Export-Excel @params
$ws = $xlsx.Workbook.Worksheets[$params.Worksheetname]
$ws.View.ShowGridLines = $false # => This will hide the GridLines on your file
Close-ExcelPackage $xlsx

One thing to note, PassThru = $true on the $params means that instead of saving the Excel directly we want to save the object on a variable for "further manipulation" and by further manipulation what I mean is, in this case, hiding the GridLines of the worksheet ($ws.View.ShowGridLines = $false) and then closing the package (store it on the disk).

If you don't require to perform any modifications over the worksheet you can just remove the PassThru altogether and do:

$result | Export-Excel @params

Which will close the package and store the Excel on your disk.