Can't export Array to Excel with LoadFromArrays from Powershell

[ili101]

Hi
I'm not sure if this is a bug, future request or I just using the wrong syntax.
I asked at the forums and we did not manage to make it work.

I need to export large amount of data to Excel with PowerShell.
First I used this great module https://github.com/dfinke/ImportExcel that works but is to slow for my data as It's writing the data with EPPlus.dll cell by cell so it's slow.

The dll also has methods to load an array of objects or a DataTable at once which is much faster, I can get the DataTable method to work but I can't figure out how to load an array with "LoadFromArrays"

Example:

# Install and Load
Install-Package EPPlus -Scope CurrentUser -provider Nuget -Source https://www.nuget.org/api/v2
Add-Type -path "$env:LOCALAPPDATA\PackageManagement\NuGet\Packages\EPPlus.4.1.1\lib\net40\EPPlus.dll"
# Initialize Data
$Path = 'C:\Temp\Test.xlsx'
$pkg = [OfficeOpenXml.ExcelPackage]::new($Path)
$ws  = $pkg | Add-WorkSheet -WorkSheetname 'test'


# Single Cell Works
$ws.Cells['A1'].Value = 8

# DataTable Works
$myDataTable = New-Object System.Data.DataTable
$null = $myDataTable.Columns.Add( 'Column1' )
$null = $myDataTable.Columns.Add( 'Column2' )
$null = $myDataTable.Columns.Add( 'Column3' )
$null = $myDataTable.Rows.Add( 'value1','value2','value3' )
$null = $myDataTable.Rows.Add( 'valueA','valueB','valueC' )
$null = $myDataTable.Rows.Add( 'valueD','value2','valueE' )
$null = $ws.Cells['A1'].LoadFromDataTable($myDataTable,$true)

# How to Load an Arrays?
$Process = Get-Process
$null = $ws.Cells['A1'].LoadFromArrays($Process)
$Process = @([PSCustomObject]@{A = 1},[PSCustomObject]@{A = 2})
$null = $ws.Cells['A1'].LoadFromArrays($Process)
# I get the Error:
#[2,1] Cannot convert argument "Data", with value: "System.Object[]", for "LoadFromArrays" to type "System.Collections.Generic.IEnumerable`1[System.Object[]]": "Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Collections.Generic.IEnumerable`1[System.Object[]]"."


# Save to disk
$pkg.Save()
$pkg.Dispose()

Thank you

[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