Read and write Excel spreadsheets with the ImportExcel PowerShell module

• Author
• Recent Posts

Adam Bertram

Adam Bertram is a 20-year IT veteran, Microsoft MVP, blogger, and trainer.

Latest posts by Adam Bertram (see all)

• Create a certificate-signed RDP shortcut via Group Policy - Fri, Aug 9 2019
• Monitor web server uptime with a PowerShell script - Tue, Aug 6 2019
• How to build a PowerShell inventory script for Windows Servers - Fri, Aug 2 2019

Whether it's just storing rows of data or it's serving as a full-on presentation tool with pie charts, graphs, and macros, we can't escape Excel. Excel workbooks have been around for a long time, and they store lots of data. And where the data is, we usually need to be.

When it comes to automation, Excel does not behave well. It's traditionally known for its complicated COM objects and slow performance in PowerShell. Thus, I would usually recommend converting the data into a CSV file before reading.

However, there are times to keep that Excel workbook intact and keep the data source untouched before reading the information. Luckily, the introduction of PowerShell 5.0 made reading a lot faster. And with a community module called ImportExcel, reading Excel worksheets has become a whole lot easier.

By using the ImportExcel module by Doug Finke, we're now able to read and manipulate Excel worksheets with no fuss at all!

To get the ImportExcel module, download it from the PowerShell Gallery by running Install-Module -Name ImportExcel. This will also install the module on your local computer. After this, you'll see a lot of commands are instantly available to you.

ImportExcel commands

You can see that we can do a whole lot more than just read worksheets. Let's dive in and see how this module works.

The simplest task to perform would probably be reading a worksheet from a workbook. In the old days, we had to manipulate COM objects and have Excel installed. Nowadays, with Doug's lovely module, this is no longer necessary. To read the contents of a worksheet, we'll use the Import-Excel command.

Import-Excel -Path C:\ExcelFile.xlsx

This returns all the cells in the first worksheet. You can see what the output looks like from the below screenshot. In this sample workbook, I have a single worksheet. But by using the Import-Excel command, I can also pick which worksheets to query by using the ‑Worksheet parameter as well.

The Import Excel cmdlet

Perhaps we don't need to enumerate all the cells in a particular worksheet, and instead, we need to gather information about the worksheets themselves. We can also do this by using the Get-ExcelSheetInfo cmdlet. This command enumerates each worksheet in an Excel workbook and returns information like the name, its position, and whether it's hidden or not. Get-ExcelSheetInfo is an excellent and quick way to see if you forgot those worksheets were hidden!

Another handy command in this module is Get-ExcelWorkbookInfo. By using this command, you can enumerate all kinds of information about the workbook itself, like the author, the version and platform of Excel the workbook was created on, when it was modified, created, and more.

Output of the Get ExcelWorkbookInfo cmdlet

To round out our review of this great module, let's go a little into not only reading Excel data but also manipulating it. One task I always have is copying worksheets. I sometimes keep a "template" worksheet in a workbook and have to generate copies from it.

Before the Copy-ExcelWorksheet command, I used to create a new worksheet, rename it, select all the cells in the "template" worksheet, and copy and paste them into the new worksheet. It was a pain. Now I can just specify the source and destination workbooks and worksheets to create a copy of a worksheet automatically.

Here's an example of how to copy an Excel worksheet.

Copy-ExcelWorkSheet -SourceWorkbook ('C:\WorkBook1.xlsx') -SourceWorkSheet WS1 -DestinationWorkSheet WS2 -DestinationWorkbook 'C:\Workbook2.xlsx'

By using a community module like ImportExcel, we no longer have to do things the hard way. I encourage you to download this module and try it out. You've seen from the image above that ImportExcel contains a lot more commands than the ones we've covered here. Install it, play around, and see what you can do with Excel and PowerShell!