PowerShell into Excel:ImportExcel Module Part 1

ImportExcel - PowerShell module

While ago I needed to do some manipulation on Excel spreadsheets but had no Office installed on the server. For the specific case, I looked for a way to complete my task with the PowerShell. A quick look into Google search results and…

Even though the name is not specified, both articles talk about the ImportExcel module (if you scroll down the results a bit you will see this link).

What is ImportExcel?

I will use Doug’s description from the GitHub repository:

This PowerShell Module allows you to read and write Excel files without installing Microsoft Excel on your system. No need to bother with the cumbersome Excel COM-object. Creating Tables, Pivot Tables, Charts and much more has just become a lot easier.

That’s right! No need to install Microsoft Excel.

I have recently tested the 7.1.0 version and it contains about 60 functions. Some of the functions are:

Add-ConditionalFormatting
Add-ExcelTable
Compare-Worksheet
ConvertFrom-ExcelToSQLInsert
Export-Excel
Import-Excel
Join-Worksheet
PieChart
Pivot

ImportExcel – quick start

To get the module installed on my machine I have ran this short script:

# set the execution policy for the current process
Set-ExecutionPolicy Bypass -Scope Process

# install and import module
Install-Module -Name ImportExcel
Import-Module -Name ImportExcel

# confirm the module is installed
Get-Module -Name ImportExcel
<#
ModuleType Version    Name                                ExportedCommands                                                                                               
---------- -------    ----                                ----------------                                                                                               
Script     7.1.0      ImportExcel                         {Add-ConditionalFormatting, Add-ExcelChart, Add-ExcelDataValidationRule, Add-ExcelName...} 
#>

Let’s see what this tool can do! I would like to see all the commands from the module and export their names to the Excel spreadsheet:

# get all the functions from the module and export as a spreadsheet
Get-Command -Module ImportExcel | SELECT Name | Export-Excel

In the result I am getting new Excel windows open with temporary name tmpF12D located in $env:TEMP with output formatted as a spreadsheet table Table1.

ImportExcel - Export-Excel
Exported file in Excel spreadsheet format with named range (Excel table)

That was easy, but I want to see more.

ImportExcel – how to?

I have created an Excel spreadsheet from PowerShell, but what else I could do?

ImportExcel – how to work with worksheets?

The ImportExcel module allows to manipulate worksheets.

# go to the temporary location
Set-Location $env:TEMP

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename

# add an extra sheet to the end
Add-Worksheet -ExcelPackage $excel -WorksheetName Mikey -MoveToEnd

# and save it back to the file and show the file
Close-ExcelPackage -ExcelPackage $excel -Show

<# 
Unfortunately getting an error:
Exception calling "Save" with "0" argument(s): "Error saving file C:\Users\Mikey\AppData\Local\Temp\tmpF12D.xlsx"
At C:\Users\michaMikey\OneDrive\Documents\WindowsPowerShell\Modules\ImportExcel\7.1.0\Public\Close-ExcelPackage.ps1:27 char:29
+             else           {$ExcelPackage.Save()          }
+                             ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

#>

And the reason for that is simple – I had the file open. I am going to close it and repeat the above script:

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename

# close the open file
([Runtime.InteropServices.Marshal]::GetActiveObject('Excel.Application').workbooks | Where-Object {$_.FullNameURLEncoded -eq $excel.File }).Close($false)

# similar effect would be using the KillExcel switch
# close WHOLE Excel app (not just the specific file) and load to variable
$excel = Open-ExcelPackage -Path $filename -KillExcel

# add an extra sheet to the end
Add-Worksheet -ExcelPackage $excel -WorksheetName Mikey -MoveToEnd

# and save it back to the file and show the file
Close-ExcelPackage -ExcelPackage $excel -Show
ImportExcel - Add-Worksheet
New sheet ‘Mikey’ has been added to our file.

Adding empty sheet was amazingly easy (and I have learned a new thing about closing files). How about populating it with the data.

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename
([Runtime.InteropServices.Marshal]::GetActiveObject('Excel.Application').workbooks | Where-Object {$_.FullNameURLEncoded -eq $excel.File }).Close($false)

# add new worksheet at the beginning
Add-Worksheet -ExcelPackage $excel -WorksheetName Process -MoveToStart

# get the data into a new sheet as a named table
# freeze the first row and column with an auto-sized length
Get-Process | Export-Excel -ExcelPackage $excel -WorksheetName Process -TableName Process -Show -AutoSize -FreezeTopRow -FreezeFirstColumn 
ImportExcel - Export-Excel + Fireworks
New sheet ‘Process’ has been added to our file.

Perfect, we have some data. Pivot? Why not.

ImportExcel – how to make pivot tables?

Using Add-PivotTable is straightforward (and from now on I am closing the whole Excel). Note the Activate switch at the end. When you open the Excel file the worksheet that was used in the command will show up first.

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename -KillExcel

# add a simple pivot table
Add-PivotTable -ExcelPackage $excel -PivotRows Name -PivotColumns PriorityClass -PivotData @{'CPU' = 'sum'} -SourceWorkSheet process -PivotTableName 'Pivot' -Activate

# save it to the file and display
Close-ExcelPackage -ExcelPackage $excel -Show

Something went wrong. We need to recreate the pivot table. No problem. Drop the worksheet and create new pivot table.

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename -KillExcel

# drop the pivot table worksheet (using the filename, not the package)
Remove-Worksheet -FullName $filename -WorksheetName Pivot 

# add some more stuff to the pivot table
Add-PivotTable -ExcelPackage $excel -PivotRows Name -PivotColumns PriorityClass -PivotData @{'CPU' = 'sum'; 'Company' = 'count'} -SourceWorkSheet process -PivotTableName 'Pivot' -Activate -NoTotalsInPivot -PivotTableStyle Dark1

# save it to the file and display
Close-ExcelPackage -ExcelPackage $excel -Show

Here are the results of both operations:

ImportExcel - Add-PivotTable
Simple pivot table added to the spreadsheet
ImportExcel - Add-PivotTable + Formatting
Pivot table after some modifications

ImportExcel – how to make pivot table charts?

It is common to have a pivot table chart alongside and ImportExcel module supports that too.

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename -KillExcel

# setup definition for the first pivot table; note the IncludePivotChart
$PTDef =  New-PivotTableDefinition -PivotTableName "P1" -SourceWorkSheet "Process" -PivotRows "Company" -PivotData @{'Cpu' = 'average'} -IncludePivotChart -ChartType BarClustered3D

# join the definition for the second pivot table
$PTDef += New-PivotTableDefinition -PivotTableName "P2" -SourceWorkSheet "Process" -PivotRows "Company" -PivotData @{'Cpu' = 'average'} -IncludePivotChart -ChartType Pie3D

# Export with the PivotTableDefinition
Export-Excel -ExcelPackage $excel -PivotTableDefinition $PTDef -Show -Activate
ImportExcel - New-PivotTableDefinition
Pivot table chart #1
ImportExcel - New-PivotTableDefinition
Pivot table chart #2

That does not limit the user to create pivot table charts on a new worksheet. It is quite simple to add more charts on the worksheet. Just to avoid overlapping of the charts and tables set the Address (for placing table [A]) and ChartRow / ChartColumn (to set location for a chart [B]).

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename -KillExcel

# define parameters for another pivot table and chart
$PTParams = @{
    PivotTableName    = "P3"
    Address           = $excel.P2.cells["A22"] # top-left corner of the table
    SourceWorkSheet   = $excel.Process
    PivotRows         = @("Company")
    PivotData         = @{'Cpu' = 'average'}
    PivotTableStyle   = 'Light21'
    IncludePivotChart = $true
    ChartType         = "BarClustered3D"
    ChartRow          = 22 # place the chart below row 22nd
    Activate          = $true
}

# add the objects
Add-PivotTable @PTParams -PassThru

# save it to the file and display
Close-ExcelPackage $excel -Show
ImportExcel - Add-PivotTable -IncludePivotChart
Pivot table chart #3 added to the existing worksheet

Amazing! It would be a day-long session to show all the scenarios just for pivot tables and charts.

Summary

ImportExcel PowerShell module has many features and it is a powerful task. I showed you today how to do basic operations on worksheets and pivot tables/charts. Next week will describe more about regular charts and conditional formatting.

Thank you,
Mikey

1 Comment on "PowerShell into Excel:ImportExcel Module Part 1"


Leave a Reply