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


Share it on     |   |   |   | 
  Prev:  

‘Clear-Recyclebin’ Is Not Recognized As The Name Of A Cmdlet In Powershell Core

  :Next  

Select-Object With Calculated Properties In PowerShell.

comments powered by Disqus