Excel Reports Using ImportExcel Module From PowerShell Gallery
You know why Excel is still one of the popular reporting tools because it is very easy to use, customizable as per your needs and moreover it is interactive. There are many ways that you can generate excel reports programmatically, but especially when you are using a scripting language like PowerShell there are tons of modules are already available with sophisticated features in the public repositories to reuse in your code, and they are very easy and simple to use.
Please be careful when you are installing the modules from the public repositories, because people around the world they publish their code into the repositories which work for them as per their environment and settings, but it may harmful for your environment. So please ensure the scripts work fine for you in your test environment first and then use it in your production environment.
ImportExcel PowerShell Module by Doug Finke from PowerShellGallery is a very popular and much helpful module, it works even without Excel installed on your computer, and again it’s an open source project in GitHub. This module is very rich in features and compact to use in your code, and I find this module is very useful and helpful.
Now let’s get started with ImportModule module in PowerShell Core …
Since PowerShellGallery is a default repository in PowerShell, you don’t need to set the repository again, just ensure that you have PSGallery as a default repository…
Get-PSRepository
Now it’s time to get the module installed and imported to the session…
# Find the module
Find-Module -Name ImportModule
# Install the modules
Find-Module -Name ImportModule | Install-Module
Install-Module -Name Install-Module
# Update the module
Update-Module -Name ImportExcel
# Verify the module is installed
Get-Module -Name ImportExcel -ListAvailable
# Import the module
Import-Module -Name ImportExcel
To check the CmdLets available in the ImportExcel module, run the CmdLet below…
Get-Command -Module ImportExcel
Now let’s see how to export the data to excel and various options…
Export-Excel CmdLet will do all the magic with various parameters; to simply export the data to excel, just pipe the output to Export-Excel, this will export the data to excel, apply filters, auto size the columns and pop up the excel window, but this will not save the file to disk.
Get-Service | Select Name, DisplayName, Status | Export-Excel
… just to export the data to excel and save the file to disk, use -Path flag with Export-Excel…
Get-Service | Select Name, DisplayName, Status | Export-Excel -Path C:\Test.xlsx
Observe the data in the excel opened after the file was created, where the columns are compact, not readable and no filters applied. By default without any parameters Export-Excel CmdLet will not save the file to disk, show the window, apply filters, auto size the columns, but if we use -Path and want to pop up the window, apply filters and auto size the columns we need to use the -Show, -AutoSize, -AutoFilter flags…
# To Show the window after the file save to disk
Get-Service | Select Name, DisplayName, Status | Export-Excel -Path .\Test.xlsx -Show
# To apply filters, and allow auto size the columns
Get-Service | Select Name, DisplayName, Status | Export-Excel -Path .\Test.xlsx -Show -AutoSize -AutoFilter
Now let’s see formatting the text in the excel reports…
# Get the services exported to excel and highlight the services state separately for services running and services are stopped.
$ConTxt1 = New-ConditionalText -Text 'Stopped' -ConditionalTextColor Red -BackgroundColor Yellow
$ConTxt2 = New-ConditionalText -Text 'Running' -ConditionalTextColor Yellow -BackgroundColor Green
Get-Service | Select Status, Name, DisplayName | Export-Excel -Path .\Test.xlsx -AutoSize -Show -ConditionalFormat $ConTxt1, $ConTxt2
# '-ConditionalFormat' parameter accepts arrays
Setting the icons to the values to represent the changes with in the given range…
# Get the processes, and represent the changes in the memory with the icons
$ConFmt = New-ConditionalFormattingIconSet -Range "C:C" -ConditionalFormat FiveIconSet -IconType Arrows
Get-Process | Select Company, Name, PM, Handles | Export-Excel -Path .\Process.xlsx -Show -AutoSize -AutoFilter -ConditionalFormat $ConFmt
# Also club it with the conditional text
$ConTxt = New-ConditionalText -Text 'Microsoft' -ConditionalTextColor Yellow -BackgroundColor Green
Get-Process | Select Company, Name, PM, Handles | Export-Excel -Path .\Process.xlsx -Show -AutoSize -AutoFilter -ConditionalFormat $ConFmt, $ConTxt
Now let’s see some creating pivot tables and charts…
# Get the services and identify the number of service are running & stopped and the services count per start type
$Data = Get-Service | Select-Object Status, Name, DisplayName, StartType | Sort-Object StartType
# Parmaters in a hashtable
$Param = @{
Show = $true
AutoSize = $true
IncludePivotTable = $true
PivotRows = 'StartType'
PivotData = 'StartType'
PivotColumns = 'Status'
}
# Create the pivot table
$Data | Export-Excel -Path C:\GitRepo\Test.xlsx @Param
# Get the services and identify the number of service are running & stopped and the services count per start type
$Data = Get-Service | Select-Object Status, Name, DisplayName, StartType | Sort-Object StartType
# Parmaters in a hashtable
$Param = @{
Show = $true
AutoSize = $true
PivotRows = 'StartType'
PivotData = 'StartType'
IncludePivotChart = $true
ChartType = 'PieExploded3D'
}
# Create the pivot charts
$Data | Export-Excel -Path C:\GitRepo\Test.xlsx @Param
There are plenty of options are available, so please explore the all the features in the ImportExcel and make the best use of this module. You can also achieve the same by writing your own code, but this is very compact and easy to use.
Many thanks to Doug Finke! #ImportExcel
