PowerShell ImportExcel Module - Part 2 Conditional Formatting

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!

Comments

[Thierry Paquot]

Hi,
This is very nice.
I was wondering if it was possible to use the conditional formatting to compare a column with a calculated value.
The idea is to get performance value from a blg counter (pal), extract in a csv
export in excel and on the 5 first row, calculate MAX, MIN, MEDIAN, AVG and variance.
I would like to put max values in RED
this seems to work
$val = $sheet7.cells["B2"].Value
$params = @{
worksheet = $sheet7
address = "B8:BJN27"
ruletype = 'Equal'
ConditionValue = "$val"
Foreground = 'red'}
 Add-ConditionalFormatting @params

but as you can see I have a lot of columns and if something like $columnname$maxrow was possible, as I used them in "Set-ExcelRow -Worksheet $sheet7 -StartColumn 2 -Value {"=max($columnname$debut : $columnname$lastrow)"} -row 2"
It would be very nice
Regards

[Thierry Paquot]

This worked and Highlighed in red every value that was equal to the maximum contained in (each) column 2 (a formula of course)
Add-ConditionalFormatting -Worksheet $sheet7 -Address '$B$8:$BJN$768' -RuleType Equal -ConditionValue '=B$2' -BackgroundColor red

have a nice week

[roelsanjose]

Can I seek help on how to copy cell range from WorksheetA to WorksheetB in the same workbook?

Appreciate your help.
Thank you.