Displaying Data in Excel (and why it won't work)

Tobias — Tue, 24 Apr 2012 05:00:00 GMT

It's pretty easy to send PowerShell results to Excel and display them nicely as a spreadsheet. Actually, here's a simple function called Out-ExcelReport. Pipe anything to it, and it shows up as an excel sheet (provided you have indeed installed excel beforehand):

function Out-ExcelReport {
param(
$path = "$env:temp\report$(Get-Date -format yyyyMMddHHmmss).csv",
[switch]$open
)
$Input |
Export-Csv $path -NoTypeInformation -UseCulture -Encoding UTF8
if($open) { Invoke-Item $path }
}

Now creating spreadsheets is as easy as this:

Get-Process | Out-ExcelReport -open

Why Does Excel Display Garbage?

Displaying results in Excel isn't so much a problem once you figure out how to export to CSV and then import that to excel.

The problem becomes evident a bit later, when you start to have fun and then suddenly run into lines such as this one:

Get-WMIObject Win32_NetworkAdapterConfiguration -filter 'IPEnabled=True' |
Select-Object Caption, DefaultIPGateway, MACAddress, IPAddress |
Out-ExcelReport -open

This line will indeed find your active network adapters, and it will open excel and display the network properties your code selected. But... the result probably looks similar to this:

Caption DefaultIPGateway MACAddress IPAddress
[00000007] Bro... System.String[] 68:A8:6D:0B:5F:CC System.String[]

Note how some properties display correctly (such as MACAddress), while others read "System.String[]". Where did the IP address go?

Shape Up Your Results!

The problem occurs whenever the property is NOT just text. Since excel can only deal with text, anything that is not text in the first place needs to be converted to text. "System.String[]" simply is the result of such a conversion. The property you tried to display was an array of strings. That's correct BTW. A network adapter can have multiple IP addresses, so the property IPAddress is by design an array.

To create meaningful reports, you need to convert such properties to text in a more clever way. Here are two things you can do:

Join Arrays: use the -join operator to join array elements and produce descriptive text
Out-String: send the property to Out-String and take advantage of PowerShell's own built-in conversion support

So how exactly would you implement these conversion methods? Well, here's a way:

$results = Get-WMIObject Win32_NetworkAdapterConfiguration -filter 'IPEnabled=True' |
Select-Object Caption, DefaultIPGateway, MACAddress, IPAddress

$results.DefaultIPGateway = $results.DefaultIPGateway -join ','
$results.IPAddress = $results.IPAddress | Out-String

$results | Out-ExcelReport -open

Note how I just took the result produced by Select-Object and saved that in $results. That's all. Since Select-Object always creates new objects with only the properties I want, and since objects produced by Select-Object are always read/write, I now can use whatever method I prefer to beautify those properties. Then, when all is fine, I can continue and send $results on to Out-ExcelReport. The result is a report with all the multivalues displaying fine.

Phew! Done - and you now can convert your PowerShell results to excel reports - and fine-tune any property that isn't showing up the way you want it to. Stay tuned...!

Tobias

Microsoft MVP PowerShell Germany

P.S.
If you live in Germany or other parts of Europe and your company would like to set up a truly great PowerShell training, just contact me! I regularly train mid- to large-size companies. Trainings are always a blast with tons of real-world-examples and solutions. Here's how to get in touch with me: tobias.weltner@scriptinternals.de

Search results for 'app:weblogs' matching tags 'Array', '-join', and 'Out-String'

Displaying Data in Excel (and why it won't work)

Why Does Excel Display Garbage?

Shape Up Your Results!