PowerShell ImportExcel Module - Part 2 Conditional Formatting

Tags: powershell
Published: 2020-01-10 02:15:47

In the previous [post](https://dev.to/stahler/powershell-importexcel-module-part-1-introduction-1kak), 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).

```PowerShell
# 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
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/8y3zvleyekbsglvroplv.png)

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!