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!
Top comments (3)
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
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
Can I seek help on how to copy cell range from WorksheetA to WorksheetB in the same workbook?
Appreciate your help.
Thank you.