Weekly Module Spotlight: ImportExcel

In my previous job, I worked at a customer site that had multiple Windows and Unix Servers. One of the every day tasks was to report the disk space utilization from these servers. When I joined the customer site, engineers there used to collect the statistics manually and create an Excel spreadsheet manually. This was not just time consuming but boring as well. This task is something that needs to be automated. Period.

So, I went on to create a rather long VBScript that uses WMI for collecting disk usage statistics from Windows servers and uses Excel COM object to generate spreadsheets that contain the reports. It certainly made my job easy. I just had to run this sitting at my local workstation and within a few seconds I would have the Excel report that I can mail to the IT manager.

But, those of you who worked on Excel COM objects and PowerShell would know that it is not the best thing. Working with VBScript is a pain. When that combined with COM object, the pain of writing and testing a script increases a few folds.

You would be glad to hear that you don’t have to do that anymore if you know PowerShell even a little bit. Thanks to ImportExcel.

ImportExcel allows you to read and write Excel files without installing Microsoft Excel on your system. With this module, there is no need to bother with the cumbersome Excel COM-object. With ImportExcel, creating Tables, Pivot Tables, Charts and much more has becomes a lot easier.

Before you try any of the following examples, install ImportExcel module from the PowerShell Gallery.

Here is the simple first example for you!

This a command exports the values of selected properties from the process object and opens an Excel spreadsheet automatically.

Here is another example from ImportExcel GitHub repository that generates charts.

Finally, here is something I showed at the PowerShell Conference Europe 2019. This uses the speaker and session data JSON and generates a spreadsheet.

There are many other ways you can use this module in creating report dashboards. The GitHub repository contains several examples that you can use as a starting point.

About the author: Ravikanth C

Ravikanth is the founder and editor of the PowerShell Magazine. He is also a PowerShell MVP who works at Dell Inc. He blogs at http://www.ravichaganti.com/blog and you can follow him on Twitter @ravikanth.

Related Posts