Doug Finke's PowerShell module offers a variety of cmdlets that allow you read and write Excel spreadsheets easily without fiddling with COM objects.

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

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

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

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.

Subscribe to 4sysops newsletter!

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!

+3
avataravatar
6 Comments
  1. Ron 4 years ago

    Is there a way to set the position for the series legend for a chart to the bottom rather than the right?

    0

  2. Bob 3 years ago

    While I was able to use the command to create a new worksheet, it did produce an exact copy (missing data and formatting) of the source worksheet.  Is there a way to do that?  If not, do you have an example to create the new sheet and then copy the range (with formatting) in the source sheet to the new/destination sheet?  Thanks.

    +2

  3. Sai 3 years ago

    Hi Adam,

    thanks for the post, however when I run command "Import-Excel" it throws error that  """Import-Excel : The term 'Import-Excel' is not recognized as the name of a cmdlet, function, script file, or operable
    program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again ""

    Regards,

    sai

    +2

    • @Sai

      You missed the part of the article where Adam explained how to install the module.

      To get the ImportExcel module, download it from the PowerShell Gallery by running Install-Module -Name ImportExcel

      +1

  4. Dave Laundry 2 years ago

    Can I use this to create an excel file, with an excel-table in it from a powershell array with collumn headings.

    for example. can I create powershell array with fileld names "Name" and "Address", with 10 names and addresses.  then use export-excell to save this to an excell file, with a table in sheet1, and say the table name in excell could be the array name from powershell?

    or perhaps import a CSV, but use export-excel to save this to an actuall excell file, with the csv data in an excel table.

    +1

  5. christoph 3 months ago

    Honestly, this article could be great (you are writing a very good style and English), if it described at least a few practical examples on how to actually use the cmdlets of the ImportExcel-Module. 

    How to read specific cells?
    How to read from specific rows/columns?
    How to read only cells with values in them?
    How to  use the values of rows/columns, e.g.:

    $data = Import-Excel -file 'xyz.xlsx' -header $true
    ForEach ($line In $data) {
        $ColumnA = $data.ColumnA
        $ColumnB = $data.ColumnB
        $ColumnN = $data.ColumnN
        <do something with it>
        }

    And so much more.

    It's quite a pity but this is basically an article about the sheer existance of ImportExcel.

    +1
    avatar

Leave a reply

Please enclose code in pre tags

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2021

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account