Powershell Script error "Cannot find an overload for "LoadFromCollection" and the argument count: "1"."

Question

I am trying to load an collection object using LoadfromCollection into an excel file. I am using EPPLUS library to do so. But I keep on receiving the error "Cannot find an overload for "LoadFromCollection" and the argument count: "1"." Since I am a newbie to PowerShell, not sure how to resolve the issue. Also, is there any other way around to load collection object using EPPLUS library?

$pkg = [OfficeOpenXml.ExcelPackage]::new($Path)
$ws  = $pkg | Add-WorkSheet -WorkSheetname 'test'

$DemoData = New-Object System.Collections.ArrayList

$DemoData = 1..10 | Foreach-Object{

        $BookID = Get-Random -Minimum 1 -Maximum 1000
        $Date = (Get-Date).adddays(-$BookID)

        New-Object -TypeName PSObject -Property @{
            Name = "Book$_"
            BookID = $BookID
            Date = $Date
        } | Select Name, BookID, Date
    }
$null = $ws.Cells['A1'].LoadFromCollection($DemoData)

Answer 1

.LoadFromCollection is a generic method with the following signature:

public ExcelRangeBase LoadFromCollection<T>(
    IEnumerable<T> Collection
)

In order for PowerShell to be able to call a generic method, it must be able to infer the concrete type T of the elements of the IEnumerable<T> instance.

Therefore, given that your $DemoData array is not strongly typed - it is of type [object[], PowerShell's regular array type - PowerShell cannot find a suitable .LoadFromCollection method overload, resulting in the error you saw.

(Note that your $DemoData = New-Object System.Collections.ArrayList statement has no effect: the subsequent $DemoData = 1..10 ... statement overrides it, and assigns whatever the command returns, which is an [object[]] array.)

The immediate fix would be to ensure that you use a strongly typed array, which would mean: [pscustomobject[]] $DemoData = 1..10 ..., given that [pscustomobject] is the type of the objects that the Select-Object call returns.

This may be enough, depending on what reflection approach(es) the method uses to determine the input objects' properties:

• If it is capable of discovering the properties via the IDynamicMetaObjectProvider interface, which [pscustomobject] implements, then the [pscustomobject[]] type constraint is enough.

• If not, you'll have to create a regular .NET type via a (PSv5+) custom class, as shown below:

# PowerShell v5 and above.

$pkg = [OfficeOpenXml.ExcelPackage]::new($Path)
$ws  = $pkg | Add-WorkSheet -WorkSheetname 'test'

# Define a custom class with the properties of interest.
class Custom {
  [string]   $Name
  [int]      $BookID
  [datetime] $Date
}

[Custom[]] $DemoData = 1..10 | Foreach-Object{

  $BookID = Get-Random -Minimum 1 -Maximum 1000
  $Date = (Get-Date).adddays(-$BookID)

  # Create and output a [Custom] instance with the specified property values.
  [Custom] @{ Name = "Book$_"; BookId = $BookID; Date = $Date }

}

# Call .LoadFromCollection with the strongly typed array.
$null = $ws.Cells['A1'].LoadFromCollection($DemoData)