Summary: Guest blogger, Doug Finke talks about his PowerShell Excel module.

The PowerShell Excel Module is a brand new, exciting, and better way to interact with Microsoft Excel from Windows PowerShell. Plus for bonus points, you don’t need Excel installed on the target machine to create the spreadsheet. Many users of this module generate Excel spreadsheets on servers, and then others in the company pick up the reports from a central server. Or for the last step in their script, they can mail the .xlsx file.

The challenge

Until now, there have been a few ways to get data into Excel. One way is to create a comma-separated value file (.csv) by using Export-Csv, and then open it in Excel, for example:

Get-Process | Export-Csv –NoType c:\Temp\ps.csv

Invoke-Item c:\Temp\ps.csv

Another way to get data into Excel is to remotely control Excel. Use the Excel COM interface to spin it up, create a workbook or a worksheet, and then loop through your data to push it into the appropriate cells. You need to create headers and add the data to the correct row and column.

Here’s a snippet that creates Excel, makes it visible, and then adds a workbook:

$xl = New-Object -ComObject Excel.Application

$xl.Visible = $true

$xl.Workbooks.Add()

An alternative is to use .NET and Open Database Connectivity (ODBC). It takes some set up, and you need to write the looping and poking in the same way as the COM interface example. This approach is like working with SQL Server data.

Enter Office, open XML

What if you could just do this?

Get-Process | Export-Excel c:\temp\ps.xlsx –Show

This example creates a ps.xlsx file, a workbook, a worksheet, a header row, and organizes all the data in rows and columns. The –Show parameter launches Excel and opens the ps.xlsx file.

This is great (and it works with any data in PowerShell). The flat data is important, and so are the visuals.

Kick it up a notch

What if you could produce the following visual image? The PowerShell Excel module lets you create Excel pivot tables and charts from the transformed data. From the data generated above (and stored in a separate spreadsheet in the same workbook), you can easily create a pivot table and a chart.

Here’s the script:

Get-Process | Where Company |

    Export-Excel C:\Temp\ps.xlsx -Show `

    -IncludePivotTable -PivotRows Company -PivotData @Handles=”sum”}`

    -IncludePivotChart -ChartType PieExploded3D

The IncludePivotTable and IncludePivotChart cmdlets generate the pivot table and chart. ChartType lets you pick what type of chart you want (there are many to choose from). The PivotRows and PivotData parameters describe how to tabulate the data.

If you run Get-Process, you’ll see each process running on your system. The pivot table in Excel groups the information by using PivotRows and calculates measurements with PivotData. Here you tell it to sum the number of handles. In the previous image, the number of handles are totaled from Get-Process and grouped by company. Now you can see that the processes running from Microsoft Corporation have almost 50 K handles.

That’s the quick tour

I wrote the Excel module to plug in to the PowerShell ecosystem so you can easily export any data to Excel just as you would a .csv file. You can pipe the results of an SQL Server query or a REST API—the list goes on and on.

There is a lot more to this module to check out. Try the Import-Excel function, which lets you read an Excel spreadsheet and convert it to PowerShell objects. It lends itself to taking existing spreadsheets, applying a calculation (or adding more data from a look up), and quickly creating a new spreadsheet. Here is an example:

Import-Excel sales.xlsx |

    ForEach { “calc projections” } |

    Export-Excel futureSales.xlsx

Where to get it?

There are a couple of ways that you can download and install this module.

PowerShell Gallery  If you are running Windows PowerShell 5.0, you can use the new Install-Module ImportExcel command. It’ll pull down the module from the gallery.

GitHub  You can also get it from GitHub: dfinke/ImportExcel.

A few words about GitHub…

You can Star the project if you like it. You can also open issues if you have questions or find issues. Plus, you can install the module from GitHub. You can also clone or fork the project. This lets you make modifications that you want. If you want to share them, you can create a Pull Request to add it to the core project.

This PowerShell module makes you more productive. Plus it highlights how to interact with .NET DLLs and build complex functions. The project has grown in several ways, based on the community contributing updates to the scripts and making great suggestions and feature requests.

Join in! Hop over to GitHub and post what you’d like to see and how you’re working with it.

~Doug

Thanks, Doug. This is such a useful module.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy