PowerShell ImportExcel Module - Part 1 Introduction

#powershell

A common task in PowerShell is to generate CSV files that are later imported into Microsoft Excel for analysis, graphics, etc. An example:

Get-Process | Export-Csv -Path C:\TEMP\Processes.csv -NoTypeInformation

Imagine being able to export data with PowerShell directly into Excel without having Excel installed with the following capabilities:

Conditional Formatting
PivotTables
Charts
Sparklines
SQL data to Excel

Interested?

If so, before you continue reading, go grab the ImportExcel module.

https://github.com/dfinke/ImportExcel
https://www.powershellgallery.com/packages/ImportExcel/7.0.1
Find-Module -Name ImportExcel | Import-Module

Once installed, use Get-Command to explore the various cmdlets that are available.

Get-Command -Module ImportExcel -Verb Get,Export

Next, I recommend you look at the help for Export-Excel.

Get-Help Export-Excel -ShowWindow #AND
Get-Help Export-Excel -Examples

And finally, let's go ahead and generate an Excel file with some basic formatting.

# remove our example file
$path = 'C:\TEMP\Example1.xlsx'
Remove-Item -Path $path -ErrorAction SilentlyContinue

# Create some data
$data = @"
Name,ID,Quarter1,Quarter2,Quarter3,Quarter4
Greef Karga,0001,1100,1200,1300,1400
Kuiil,0002,1000,1000,1000,0
IG-11,0003,1200,1200,1400,1500
Cara Dune,0004,800,700,700,300
Mayfeld,0005,400,500,600,200
Din Djarin,0006,2000,2200,2100,500
"@ | ConvertFrom-Csv

# Create our various Excel parameters
$params = @{
    # Spreadsheet Properties
    Path                 = $path
    AutoSize             = $true
    AutoFilter           = $true
    BoldTopRow           = $true
    FreezeTopRow         = $true
    WorksheetName        = 'Data'
    PassThru             = $true
}        

# Create the Excel file
$ExcelPackage = $data | Export-Excel @params
$WorkSheet = $ExcelPackage.Data

# Apply some basic formatting
Set-ExcelRange -Worksheet $WorkSheet -Range "A1:F1" -BackgroundColor Black -FontColor White
Set-ExcelRange -Worksheet $WorkSheet -Range "B1:B7" -HorizontalAlignment Center
Set-ExcelRange -Worksheet $WorkSheet -Range "C2:F7" -NumberFormat 'Currency'

Close-ExcelPackage -ExcelPackage $ExcelPackage -Show