DEV Community: Wes Stahler

PowerShell ImportExcel Module - Part 3 Charts

Wes Stahler — Sat, 11 Jan 2020 18:21:25 +0000

Continuing on with our previous example, we will now dynamically include a chart in our spreadsheet. This is accomplished by using the New-ExcelChartDefinition cmdlet. Here is our example:

# 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 IconSet
$params = @{
    Range             = "G2:G7"
    ConditionalFormat = 'ThreeIconSet' 
    IconType          = 'Arrows'
}
$IconSet = New-ConditionalFormattingIconSet @params

# Create our various Excel parameters
$params = @{
    # Spreadsheet Properties
    Path                 = $path
    AutoSize             = $true
    AutoFilter           = $true
    BoldTopRow           = $true
    FreezeTopRow         = $true
    WorksheetName        = 'Data'
    ConditionalFormat    = $IconSet
    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'

# Let's add a "Total" column and format it
$params = @{
    Worksheet       = $WorkSheet
    Range           = "G1" 
    Value           = 'Total'
    Bold            = $true 
    BackgroundColor = 'Black'
    FontColor       = 'White'
}
Set-ExcelRange @params

# Fill the Total column
2..7 | ForEach-Object {
    $sum = "=SUM(C{0}:F{0})" -f $PSItem
    Set-ExcelRange -Worksheet $WorkSheet -Range "G$_" -Formula $sum 
}

# Format the new column as curraency
$params = @{
    Worksheet           = $WorkSheet
    Range               = "G:G"
    NumberFormat        = 'Currency'
    Width               = 15
    HorizontalAlignment = 'Center'
}
Set-ExcelRange @params 

# Add conditional formatting
$params = @{
    Worksheet       = $WorkSheet
    Address         = 'C2:F7'
    RuleType        = 'LessThan'
    ConditionValue  = 1000
    ForegroundColor = 'Red'
}
Add-ConditionalFormatting @params

$params = @{
    Worksheet       = $WorkSheet
    Address         = 'C2:F7'
    RuleType        = 'GreaterThanOrEqual'
    ConditionValue  = 1000
    ForegroundColor = 'Green'
}
Add-ConditionalFormatting @params 

# Create our chart parameters
$chartDefinition = @{
    YRange   = 'Total'
    XRange   = 'Name'
    Title    = 'Character Sales'
    Column   = 0
    Row      = 8
    NoLegend = $true
    Height   = 225
}
$Chart = New-ExcelChartDefinition  @chartDefinition 

$params = @{
    ExcelPackage         = $ExcelPackage
    ExcelChartDefinition = $chart
    WorksheetName        = $WorkSheet
    AutoNameRange        = $true
    Show                 = $true 
}
Export-Excel @params

Hope you following along, up next - Pivot Tables!

PowerShell ImportExcel Module - Part 2 Conditional Formatting

Wes Stahler — Fri, 10 Jan 2020 02:15:47 +0000

In the previous post, you were introduced to the awesome ImportExcel module. If you haven't already, make sure you check it out before continuing.

Moving forward, we will expand our previous example to include a few more things. Try the following in your favorite editor (Visual Studio Code).

# 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 IconSet
$params = @{
    Range             = "G2:G7"
    ConditionalFormat = 'ThreeIconSet' 
    IconType          = 'Arrows'
}
$IconSet = New-ConditionalFormattingIconSet @params

# Create our various Excel parameters
$params = @{
    # Spreadsheet Properties
    Path                 = $path
    AutoSize             = $true
    AutoFilter           = $true
    BoldTopRow           = $true
    FreezeTopRow         = $true
    WorksheetName        = 'Data'
    ConditionalFormat    = $IconSet
    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'

# Let's add a "Total" column and format it
$params = @{
    Worksheet       = $WorkSheet
    Range           = "G1" 
    Value           = 'Total'
    Bold            = $true 
    BackgroundColor = 'Black'
    FontColor       = 'White'
}
Set-ExcelRange @params

# Fill the Total column
2..7 | ForEach-Object {
    $sum = "=SUM(C{0}:F{0})" -f $PSItem
    Set-ExcelRange -Worksheet $WorkSheet -Range "G$_" -Formula $sum 
}

# Format the new column as curraency
$params = @{
    Worksheet           = $WorkSheet
    Range               = "G:G"
    NumberFormat        = 'Currency'
    Width               = 15
    HorizontalAlignment = 'Center'
}
Set-ExcelRange @params 

# Add conditional formatting
$params = @{
    Worksheet       = $WorkSheet
    Address         = 'C2:F7'
    RuleType        = 'LessThan'
    ConditionValue  = 1000
    ForegroundColor = 'Red'
}
Add-ConditionalFormatting @params

$params = @{
    Worksheet       = $WorkSheet
    Address         = 'C2:F7'
    RuleType        = 'GreaterThanOrEqual'
    ConditionValue  = 1000
    ForegroundColor = 'Green'
}
Add-ConditionalFormatting @params 

Export-Excel -ExcelPackage $ExcelPackage -Show

A lot happened here! We added:

A Total column
Conditional formatting
Used an iconset for a visual pop!

It may seem that the code examples are unnecessarily long. That really isn't the case as I use a lot of splatting when I code, especially if I am sharing as it is a lot easier to read than a single line of code that is extremely long.

Next, we will look at adding a chart!

PowerShell ImportExcel Module - Part 1 Introduction

Wes Stahler — Fri, 10 Jan 2020 02:11:47 +0000

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

The result should look similar to this:

Encourage you to watch Doug Finke's (ImportExcel creator) blog and videos:
https://dfinke.github.io/
https://www.youtube.com/playlist?list=PL5uoqS92stXioZw-u-ze_NtvSo0k0K0kq

Hope this helps and stay tuned for more articles in this series.