The ImportExcel is a PowerShell module that allows you import to or export data directly from Excel spreadsheets without having Microsoft Excel installed on your computer. In this tutorial, you'll learn to work with Import-Excel and Export-Excel. The ImportExcel module runs on Windows, Linux, or Mac and now can be used in Azure functions and GitHub Actions. Simply put, if you need to generate reports for work, you must learn this module.

Doug Finke, a Microsoft MVP since 2009, builds and maintains the module. Doug is constantly improving the module and releases new module updates frequently. As of this writing, the module is at v7.1.3 and is continually being developed. His module is nearing 1 million downloads since its first release! Installing the module is a simple task with PowerShell code.

Install-Module -Name ImportExcel

Excel is not required to be installed for this module to work. The module installs a .net DLL named epplus.dll that allows the module to import Excel data or export to Excel format. This allows you to install the module on a server without having to install Office on the server.

Importing data from Excel ^

Getting started with the module is very easy. Let’s start by importing some data from Excel. In this first demo, I’ll be importing some simple data I have from a table in Excel.

Sample Excel table data for import

Sample Excel table data for import

To import data, I use the Import-Excel cmdlet and specify the path. In this example, I will also save the data to a variable called "Fruit" for later use.

Import-Excel "c:\temp\ExcelDemo.xlsx" -OutVariable Fruit
Excel data import in PowerShell

Excel data import in PowerShell

Now, we have a simple table with data organized in columns and rows. The table properties reveal that PowerShell has created a PSCustomObject with two note properties for the two columns.

Excel table properties

Excel table properties

But what if I have a large table of data? I can specify which data gets imported without having to pull in the entire table. Let’s look at how that works.

I have created a new tab in my spreadsheet that contains all the process info from my machine. I have named the tab "Processes." The spreadsheet has 69 columns of data. I could import all these columns and filter the data, but for this demonstration I just want the Name, ProcessName, CPU, and Memory columns.

Process info data in Excel

Process info data in Excel

Using the Import-Excel cmdlet, I can pull in just the data I am interested in. Let’s pull in the columns I mentioned earlier (Name, ProcessName, CPU, and Memory). For this demo, I only want 6 rows of data. To accomplish this, I use the -ImportColumns, -StartRow and -EndRow parameters.

To pick the columns, I simply count columns from left to right in my spreadsheet starting at 1. I know you can’t see the full spreadsheet, but I have already counted out the columns that I need. To select the columns I want, I will need columns 1, 6, 12, and 46. But if I want to keep them in the order I mentioned above, then the order would have to be 1, 46, 12, and 6.

import-excel C:\temp\ExcelDemo.xlsx -WorksheetName Processes -ImportColumns @(1, 46, 12, 6) -startrow 1 -endrow 7
Process info imported into PowerShell

Process info imported into PowerShell

Export data to Excel ^

As with the process of importing data, I can also export data to Excel easily with just one line of code. Let’s go back to my previous example: getting the process data. If I want to export all the process info on my machine, all I need to do is type one line:

Get-process | Export-Excel

This results in the Export-Excel cmdlet creating a spreadsheet. If I have Excel installed, it launches Excel and presents the file output to me.

Exporting data to Excel using default values

Exporting data to Excel using default values

Notice that I didn’t specify a filename or any other formatting information. However, the Export-Excel cmdlet created the spreadsheet and applied some default formatting (see callout 2) and created a temporary file for me (callout 1).

Of course, I can choose a filename and path on export, if I so desire, by using the -path parameter and inputting a value like so:

Get-process | Export-Excel C:\temp\ProcessList.xlsx

Adding data to an existing spreadsheet ^

At some point, you will need to add data to an existing spreadsheet. The -Append parameter adds data to an existing spreadsheet. I can specify a worksheet to add to with the -worksheet parameter or I can start a new worksheet with the same parameter but picking a new tab name.

So far, I have been working on a spreadsheet named "ExcelDemo.xlsx," which contains the Fruit and Processes worksheets. I want to add a new tab named "People" and copy in data from a small table I created.

Table of person and city info saved to the People variable

Table of person and city info saved to the People variable

Exporting this data to my existing Excel spreadsheet and creating a new worksheet would look like this:

$People | Export-Excel c:\temp\ExcelDemo.xlsx -Append -WorksheetName "People"
People table export

People table export

This is easy and doesn’t require much code. Below, we can see the worksheet tabs that have been created from Export-Excel.

Excel worksheet tabs created by Export Excel

Excel worksheet tabs created by Export Excel

When you look at the table, you’ll see that it has none of the familiar Excel spreadsheet formatting. I would like to add some formatting to my data. Let me show you how this can be done.

Exporting data with formatting ^

The Export-Excel cmdlet offers many options for formatting my data on export. I’ll highlight a few options, but make sure you review the parameters available for the Export-Excel cmdlet for a full list of formatting options.

I would like to export the data again. This time, however, I will add a table style and a title for my table, and I would like the table title to be bold. This is possible with Export-Excel. The code used to do this is slightly different from the previous example:

$People | Export-Excel c:\temp\ExcelDemo.xlsx -Append -WorksheetName "PeopleFormatted" -TableStyle Medium16 -title "Demo of Table Formatting" -TitleBold
Formatted version of the People table in Excel

Formatted version of the People table in Excel

You might wonder what the table style I selected (Medium16) in the last example is. The Export-Excel cmdlet has table styles built in that correspond to the table styles you see in Excel.

Export Excel table styles available

Export Excel table styles available

The table styles in Excel are the same. In the screen cap below, I clicked on the "Format As Table" at the top of the spreadsheet, which then displays the table styles. If you hover your mouse over a style, you’ll see some text that provides you the style details. The #1 callout is the style I hovered over. Notice that it says Medium16. This is how I got the name that I used in my previous code example for the table style parameter.

Corresponding Excel table styles

Corresponding Excel table styles

Creating charts ^

Export-Excel does more than just make spreadsheets. The cmdlet can export table data and turn that data into a chart inside an Excel spreadsheet. For my next example, I have created a table of some simple inventory items and sales data.

Sales data

Sales data

I would like to chart these sales in a simple bar graph that depicts units sold. To do this, I need to define the properties I want for my table. To do this, I use the New-ExcelChartDefinition cmdlet.

$ChartData = New-ExcelChartDefinition -XRange Item -YRange TotalSold -ChartType ColumnClustered -Title "Total Fruit Sales"

This line of code defines my table properties, and it tells Excel what to use for the xValue in the chart. I first use the Item column, then, I define the yValue (I am using the TotalSold column). Then, I specify a chart type. There are 69 chart types available in the cmdlet, all of which correspond to the chart types in Excel. I chose the "ColumnClustered" type for my example.

I then add a chart title, although this is not required. These values are saved to a variable named $ChartData. The next piece to add to the export cmdlet is this chart definition:

$data | Export-Excel C:\temp\ExcelDemo.xlsx -Append -WorksheetName FruitSalesChart -ExcelChartDefinition $ChartData -AutoNameRange -show -Title "Fruit Sales"

Let’s walk through this example. First, I send the $data variable to the Export-Excel cmdlet. The $data variable is our sales data. The syntax for Export-Excel is a continuation from my previous example. I export and append this to a spreadsheet named "ExcelDemo.xlsx." I create new worksheet tab named FruitSalesChart. This is all code we saw in the previous examples.

Then, I am add in the chart definition I created earlier by calling the $ChartData variable. Finally, I tell Excel that I want an auto name range. The -show parameter auto opens the spreadsheet after I create it.

Fruit Sales exported to Excel as a table and chart

Fruit Sales exported to Excel as a table and chart

Editing existing data in an Excel spreadsheet ^

I find it so easy to export data from PowerShell to Excel that I default to the Export-Excel cmdlet for much of my work. However, you can also update individual data values in an existing spreadsheet. I will connect to the spreadsheet that I used in the previous examples. To connect, use the Open-ExcelPackage cmdlet.

$ExcelPkg = Open-ExcelPackage -Path  "C:\temp\ExcelDemo.xlsx"

I can start to work with the data after opening the file.

Spreadsheet info in PowerShell

Spreadsheet info in PowerShell

The first five rows constitute the worksheet tabs I created earlier in the spreadsheet. I can view the data in any of the tabs with some simple code.

#Let's access the data in the "PeopleFormatted" worksheet
$WorkSheet = $ExcelPkg.Workbook.Worksheets["PeopleFormatted"].Cells
$WorkSheet[3,1] | select value

Value
-----
Jeremy

$WorkSheet[3,2] | select value

Value
-----
Loxahatchee

The code above probably doesn’t make much sense without a visual reference. Have a look at this screen cap below, which should help explain the code.

In the first code example, I called $WorkSheet[3,1] . If you look at the Excel spreadsheet, "3" represents the 3rd row. "1" represents the first column (starting from left of column A).

In the second code example, I called $WorkSheet[3,2] which is Row 3, Column2 (column B in spreadsheet).

Example of accessing Excel data values

Example of accessing Excel data values

Inserting a new value into the Excel data cell is done with a similar set of code. I will replace the name "Jeremy" with the name "Robert".

$WorkSheet[3,1].Value = "Robert"

$WorkSheet[3,1] | select value

Value
-----
Robert

It’s that easy to update a field in Excel! However, there’s one catch. This change I just made is still in memory inside PowerShell. The file needs to "closed" for the data to be written back into the file.

Close-ExcelPackage $ExcelPkg
Updated spreadsheet value

Updated spreadsheet value

Conclusion and links ^

Today, I showed you how to import data from an Excel spreadsheet, create a spreadsheet, create a simple chart, and manipulate the imported data in an existing Excel spreadsheet. The ImportExcel module makes these tasks and others operations simple to complete.

I have touched upon a just few of the many complex tasks you can perform with this module. If you would like to learn more, please visit Doug Finke’s GitHub page for many more examples of demo code you can try for yourself. He has a page dedicated to FAQs and a thorough analysis on examples that you should definitely check out.

Subscribe to 4sysops newsletter!

Many of the code examples in Doug’s module come from community members looking to use Excel in unique ways. If you have ideas for new ways to use his module, please submit a pull request to his repo so that others can learn from your use case.

0 Comments

Leave a reply

Your email address will not be published.

*

© 4sysops 2006 - 2022

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