Search code examples
excelpowershellopenxml

New-Object returns empty object

I'm trying to extend this example using extarnal module to call generic methods. My goal is to create new xls file and write to it.

[Reflection.Assembly]::LoadWithPartialName("DocumentFormat.OpenXml") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("DocumentFormat.OpenXml.Packaging")
[Reflection.Assembly]::LoadWithPartialName("DocumentFormat.OpenXml.Spreadsheet")
[Reflection.Assembly]::LoadWithPartialName("OpenXmlPowerTools")

Import-Module (join-path (Split-Path $MyInvocation.MyCommand.Path) "GenericMethods.psm1")

$document = [DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]::Create("C:\Temp\text.xlsx", [DocumentFormat.OpenXml.SpreadsheetDocumentType]::Workbook)

$workbookPart = $document.AddWorkbookPart();
$workbookPart.Workbook = New-Object -TypeName DocumentFormat.OpenXml.Spreadsheet.Workbook

$worksheetPart = Invoke-GenericMethod -InputObject $workbookPart -MethodName AddNewPart -GenericType DocumentFormat.OpenXml.Packaging.WorksheetPart
$sheetData = New-Object -TypeName DocumentFormat.OpenXml.Spreadsheet.SheetData
$worksheetPart.Worksheet = New-Object -TypeName DocumentFormat.OpenXml.Spreadsheet.Worksheet -ArgumentList $sheetData

[DocumentFormat.OpenXml.Spreadsheet.Sheets]$foo = New-Object -TypeName DocumentFormat.OpenXml.Spreadsheet.Sheets
Invoke-GenericMethod -InputObject $document.WorkbookPart.Workbook -MethodName AppendChild -GenericType DocumentFormat.OpenXml.Spreadsheet.Sheets -ArgumentList $foo

$document.Close()

The problem is that this piece of code

[DocumentFormat.OpenXml.Spreadsheet.Sheets]$foo = New-Object -TypeName DocumentFormat.OpenXml.Spreadsheet.Sheets
Invoke-GenericMethod -InputObject $document.WorkbookPart.Workbook -MethodName AppendChild -GenericType DocumentFormat.OpenXml.Spreadsheet.Sheets -ArgumentList $foo

throws error Invoke-GenericMethod : No matching method was found. Throws because New-Object creates something that is treated as empty array by Invoke-GenericMethod function. So the module is looking for generic methods with no parameters. Note thet first call to Invoke-GenericMethod is working fine.

How should I call Invoke-GenericMethod with -ArgumentList parameter?

Solution

  • The problem with your code is that DocumentFormat.OpenXml.OpenXmlElement class (base class for DocumentFormat.OpenXml.Spreadsheet.Sheets) implement IEnumerable interface. That makes PowerShell interpret any instance of DocumentFormat.OpenXml.Spreadsheet.Sheets as collection, rather than singular object.

    When you write just

    $foo

    PowerShell will enumerate collection and display child elements instead (to be precise, you will see child of the child, because formatting cmdlets normally do another enumeration of collections). And since you just created this object, it will be empty and nothing will be displayed.

    To actually display $foo object itself, you need to write it like this:

    ,$foo | Format-Table -Expand CoreOnly

    or

    ,,$foo

    The fact, that PowerShell interpret instance of DocumentFormat.OpenXml.Spreadsheet.Sheets as collection, also affects how conversion to [Object[]] (type of -ArgumentList parameter) done. Instead of wrapping into single element array (as you want to call method with single argument), as it happens for singular objects, PowerShell create array from collection's elements.

    To solve your issue you need to do wrapping into single element array by yourself. You can do that with unary comma oprator:

    -ArgumentList (,$foo)