Can't export Array to Excel with LoadFromArrays from Powershell

[jankallman]

Can't export Array to Excel with LoadFromArrays from Powershell

[dfinke]

@ili101 Interesting. This might also be a good addition to the ImportExcel module for loading large datasets.

[SheepRock]

@ili101 Well, I don't have knowledge about PowerShell, but in C# the type expected is IEnumerable<object[]>, for instance, List<object[]>

[blairdaviesbc]

The problem is that the Get-Process function returns an array of object arrays, not an IEnumerable<Object[]>

(Get-Process).GetType()


IsPublic IsSerial Name                                     BaseType                                                                                                                                                                                              
-------- -------- ----                                     --------                                                                                                                                                                                              
True     True     Object[]                                 System.Array        

You can force the Array<Object[]> into IEnumerable<Object[]> as follows:

$Process = New-Object "System.Collections.Generic.List``1[System.Object[]]"
Get-Process | ForEach { $Process.Add( [object[]] @( $_.Id, $_.ProcessName  )) }

$null = $ws.Cells['A1'].LoadFromArrays([System.Collections.Generic.IEnumerable``1[System.Object[]]]$Process)

You can also use > $null at the end of the line instead of $null = at the beginning of the line, and it makes the code a bit clearer

[ili101]

@blairdaviesbc Thank you for the explanation.
So I can't really import a "classical Powershell object stricture" (array of [PSObject]s) with this.
We have to replace the array with a list, and break the [PSObject]s to arrays, and add the headers.

$process = [Collections.Generic.List[Object[]]]::new()
$PsProcess = Get-ChildItem
$Process.Add($PsProcess[0].PsObject.Properties.Name)
$PsProcess | ForEach-Object { $Process.Add($_.PsObject.Properties.Value) }
$null = $ws.Cells['A1'].LoadFromArrays($Process)

But now I see that we can use LoadFromCollection for that by only converting the array to a list like this:

$process = [Collections.Generic.List[System.IO.FileSystemInfo]]::new()
$TestObject = Get-ChildItem
foreach ($item in $TestObject)
{
    $Process.Add($item)
}
$BindingFlags = [Reflection.BindingFlags] 'NonPublic, Static'
$MemberInfo = [System.IO.FileSystemInfo].GetMembers().Where({$_.Name -in 'Name','LastWriteTime'})

[OfficeOpenXml.Table.TableStyles]$TableStyle = 'Medium6'
$null = $ws.Cells['A1'].LoadFromCollection($process,$true,$TableStyle,$BindingFlags,$MemberInfo)

[JanKallman]

This seems to be solved, so I'll close it