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.
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
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
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 – 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
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
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
Permalink