Cannot access properties of Sheet object in Powershell OpenXML-CodePudding

I have this powershell script which write the type of the last worksheet from an xlsm document :

Add-Type -Path "C:\Program Files (x86)\Open XML SDK\V2.5\lib\DocumentFormat.OpenXml.dll"
$Document = $null
$Document = [DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]::Open("C:\Users\200207121\Documents\Sandbox\FastPrep_S7_Modified_AVELIA_LZB.xlsm", $false)
$Sheets = $Document.WorkbookPart.Workbook.Sheets
$LastSheet = $Sheets.LastChild
Write-Host $LastSheet.GetType()
$Document.Close()

This outputs

DocumentFormat.OpenXml.Spreadsheet.Sheet

I need to know for sure that $LastSheet is an object of type Sheet, which must have all the properties listed in the documentation.

However, I can't access most of the properties specified in the official documentation

Write-Host "Type is $($LastSheet.GetType())"
Write-Host "State is $($LastSheet.State)"
Write-Host "state is $($LastSheet.state)"

This outputs

Type is DocumentFormat.OpenXml.Spreadsheet.Sheet
State is
state is

Why am I unable to access those properties ? Am I using the wrong library ? Is the doc outdated ? Is my syntax wrong ?

Edit : I have tried downloading the latest version of the package, and import it instead. This produces the exact same behaviour

CodePudding user response：

Two things:

It does seem you have an instance, and you could explore the properties, methods, and events of that instance using Get-Member:

Try:

$lastSheet | Get-Member # to see what the object can do

You might be able to sidestep this entirely by using the excellent module ImportExcel

CodePudding user response：

That is because State is null when the sheet is visible (default).

This makes sense when looking at the underlying tag <x:sheets> :

<x:sheet name="Sheet 1" sheetId="155" r:id="rId3"/>
<x:sheet name="Sheet 2" sheetId="140" state="veryHidden" r:id="rId4"/>
<x:sheet name="Sheet 3" sheetId="130" state="veryHidden" r:id="rId5"/>

The stateattribute is only specified for hidden/veryHidden sheets. If I update my powershell code to lookup a sheet that is actually hidden, I get the appropriate behaviour.

In my opinion, the doc is totally misleading regarding this property :

state (Visible State) Specifies the visible state of this sheet.

The default value for this attribute is "visible."

The possible values for this attribute are defined by the ST_SheetState simple type (§18.18.68).

The default value is NOT 'visible'. It is null.