Don't you love ImportExcel?
One example I couldn't find was a comparison of two cells with each other. With some directions from Doug Finke, I made it work.
Adding conditional formatting for rows and cells if two columns have the same values requires the following rules in Excel:
They may be created using Add-ConditionalFormatting command:
Add-ConditionalFormatting -Worksheet $excel.sheet1 -Range "C2:D10" -ConditionValue '=$C2=$D2' -RuleType Expression -BackgroundColor ([System.Drawing.Color]::Thistle) -Bold
Add-ConditionalFormatting -Worksheet $excel.sheet1 -Range "A2:D10" -ConditionValue '=$C2=$D2' -RuleType Expression -BackgroundColor ([System.Drawing.Color]::LavenderBlush)
And in case you already have some conditional formatting rules, and you would like to reuse them in your PS script, there's an easy way to "export them":
Import-Module ImportExcel
$xlSourcefile = "YOUR FILE NAME"
$excel = Open-ExcelPackage -Path $xlSourcefile
$excel.Workbook.Worksheets | ForEach-Object {
$_.ConditionalFormatting | ForEach-Object {
Write-Host "Add-ConditionalFormatting -Worksheet `$excel[""$worksheetName""] -Range '$($_.Address)' -ConditionValue '$($_.Formula)' -RuleType $($_.Type) "
}
}
The above script prints the Add-ConditionalFormatting commands which you can use in your script. It doesn't export the formatting, so make sure to add them =)
Top comments (0)