Data created using custom classes may fail to export

[KevinWGagel]

I have a number of data modules that I'm gathering and exporting via Export-Excel. One wouldn't export anything more than the custom class name of the data being exported (later as I added more, some of them wouldn't too.) The data exports using Export-Csv just fine.

For the variables that wouldn't export I was using this format: Export-Excel -Path $DataFile -WorksheetName -InputObject $PrinterSettingsPolicySettings "C - Printer Settings" -AutoSize -AutoFilter -FreezeTopRow -TableStyle Medium7 For these ones the only thing that exports is the custom class' name.

I asked for help here PowerShell Forums and a work around was proposed. I found that it worked.

Switching the code to this resolves the issue for me: $PrinterSettingsPolicySettings | Select-Object -Property * | Export-Excel -Path $DataFile -WorksheetName "C - Printer Settings" -AutoSize -AutoFilter -FreezeTopRow -TableStyle Medium7

[dfinke]

Thanks for opening the issue. Could you post the version that works with the CSV and what doesn't work with PS Excel. That way I can run them and step thru the Excel approach and see it.

[KevinWGagel]

Doug,

Thank you for looking at this. I've put this example together for you to highlight the issue.

class PrinterSettings {
    [string]$Policy
    [string]$Section
    [string]$PolicyType
    [string]$Name
    [string]$Status
    [string]$bypassErrors
    [string]$GpoSettingOrder
    [string]$Filters
    [string]$lprQueue
    [string]$snmpCommunity
    [string]$Protocol
    [string]$portNumber
    [string]$doubleSpool
    [string]$snmpEnabled
    [string]$snmpDevIndex
    [string]$ipAddress
    [string]$Action
    [string]$Location
    [string]$LocalName
    [string]$Comment
    [string]$Default
    [string]$SkipLocal
    [string]$UseDNS
    [string]$UseIpV6
    [string]$Path
    [string]$DeleteAll
}

function Get-PrinterSetting {
    param (
        [Parameter(Mandatory = $true)]
        [System.Object]
        $Value
    )
    process {
        $ThisSetting = [PrinterSettings]::new()
        $ThisSetting.Policy = $gpo.Name
        $ThisSetting.Section = "Computer"
        $ThisSetting.PolicyType = $TypeofPolicy
        $ThisSetting.Name = $Value.Name
        $ThisSetting.Status = $Value.Status
        $ThisSetting.bypassErrors = $Value.bypassErrors
        $ThisSetting.GpoSettingOrder = $Value.GpoSettingOrder
        $ThisSetting.Filters = $Value.Filters
        $ThisSetting.lprQueue = $Value.lprQueue
        $ThisSetting.snmpCommunity = $proValue.Propertiesp.snmpCommunity
        $ThisSetting.Protocol = $Value.Protocol
        $ThisSetting.portNumber = $Value.portNumber
        $ThisSetting.doubleSpool = $Value.doubleSpool
        $ThisSetting.snmpEnabled = $Value.snmpEnabled
        $ThisSetting.snmpDevIndex = $Value.snmpDevIndex
        $ThisSetting.ipAddress = $Value.ipAddress
        $ThisSetting.Action = $Value.action
        $ThisSetting.Location = $Value.Location
        $ThisSetting.LocalName = $Value.LocalName
        $ThisSetting.Comment = $Value.Comment
        $ThisSetting.Default = $Value.Default
        $ThisSetting.SkipLocal = $Value.SkipLocal
        $ThisSetting.UseDNS = $Value.UseDNS
        $ThisSetting.UseIpV6 = $Value.UseIpV6
        $ThisSetting.Path = $Value.Path
        $ThisSetting.DeleteAll = $Value.DeleteAll
        return $ThisSetting
    }
}

[xml]$xmlgpo = Get-Content .\Example.xml

$Data = Get-PrinterSetting -Value $xmlgpo.GPO.Computer.ExtensionData.Extension.printers.portprinter.Properties

Export-Excel -Path .\Example.xlsx -InputObject $Data
Export-Csv -Path .\Example.csv -InputObject $Data

example.txt Rename the example.txt to xml and use both in the directory. It will output two files which demonstrate the issue.

[dfinke]

Wow that is a good one!

Sending the xml "directly" works. Need to take a deeper look.

$xlfile = "$psscriptroot\Example.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue

[xml]$xmlgpo = Get-Content .\Example.xml
$xmlgpo.GPO.Computer.ExtensionData.Extension.printers.portprinter.Properties | Export-Excel $xlfile -Show