Introducing the PowerShell Excel Module


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.

Image of spreadsheet

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.

Image of spreadsheet

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 

Comments (16)

  1. hpotsirhc says:

    Wow, this is really awesome!

    But there’s a small type in one of your examples. You lost a "{":

    Get-Process | Where Company |

    Export-Excel C:Tempps.xlsx -Show `

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

    -IncludePivotChart -ChartType PieExploded3D

  2. jkavanagh58 says:

    This is great! Thanks!

  3. Jason Fossen says:

    Very nice! When combined with PowerShell for the Math.NET Numerics math library and the Intel MKL for hardware acceleration, this Excel module helps to fill in the available tools for using PowerShell for scientific and engineering projects. I just tested
    the Import- and Export-Excel cmdlets from this module with a complex CSV file that has over a million lines, and no problems at all, very fast. Thanks!

  4. Doug Finke says:

    Thanks for catching the script error hpotsirhc.

    Thanks Jason for trying that out and letting us know, great info. Would love to hear more/get resources about those engineering projects and tools.

    Thanks again

    Doug

  5. Jason Fossen says:

    Hi Doug:

    Here’s some sample PowerShell code for Math.NET Numerics and the Intel MKL:

    https://cyber-defense.sans.org/blog/2015/06/27/powershell-for-math-net-numerics

    Cheers,
    J.

  6. Doug Finke says:

    Thanks for the link Jason. I found the link to your post, great write up PowerShell functions. I’ve tried other libraries in the past, this looks great and I’m looking forward to giving it a try.

    Doug

  7. roland says:

    Yay now we can let VBA slowly die the death it deserves!

  8. Doug Finke says:

    Hey Roland, VBA is not going away any time soon 🙂

    It’s still the way you create interactive solutions in Excel.

  9. Wow… This is so awesome. You don’t know how difficult it is to get my coworkers to understand what a CSV is or how to save an XLSX as one to use with my scripts. Plus, creating spreadsheets with the Excel ComObject is a pain and EXTREMELY unreliable.
    I’ve even had it where if you have an excel object visible while it’s being created, and you scroll, it will leave out cells. Your module is simply mind blowing. Thank you for putting it out.

  10. alex says:

    After installing the module I tried to run your script, but it appears that includepivottable and includepivotchart cmdlets are missing. Any info on this?

  11. alex says:

    Never mind. It was just the formatting the ISE didn’t like. Once I put it all into one line it worked perfectly.

  12. Inammathe says:

    Hi, absolutely loving your module. Thank you.

    One question, is there anyway to reproduce the -NoTypeInformation that export-csv can do? Thanks

  13. Doug Finke says:

    Hi Inammathe, sorry for the late reply.

    Export-Excel -NoHeader may solve that.

    Also, you can post questions here for quicker repliese
    https://github.com/dfinke/ImportExcel.

    Thanks
    Doug

    http://dougfinke.com/blog

  14. artem says:

    Do you have a version for PowerShell v2?

  15. Tim says:

    I am running into an issue in doing the “Import-Excel”. I am attempting to use a UNC path ( \\server\share$\folder\file.xlsx ). I get a error where it states “The given path’s format is not supported.” If I map the location to a drive letter ( X:\folder\file.xlsx ) then it works.

    Is there a plan to support UNC paths or will the location always have to be a local or mapped drive?

    Maybe I am just missing something…

    1. Tim says:

      I made a change in the “ImportExcel.psm1”. I commented out line 33 & 34

      #$Path = (Resolve-Path $Path).Path
      #write-debug “target excel file $Path”

      I believe that the resolve-path was causing my issue. After removing those two lines, the UNC path works. This is awesome. I have a need to export data for a group of managers to comment on and then bring the information back in. This is going to work wonders for me.

      Thanks…

Skip to main content