DEV Community

Kinga
Kinga

Posted on

ImportExcel and Conditional Formatting

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.

Image description

Adding conditional formatting for rows and cells if two columns have the same values requires the following rules in Excel:
Image description

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)

Enter fullscreen mode Exit fullscreen mode

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) "
    }
}
Enter fullscreen mode Exit fullscreen mode

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 =)

Discussion (0)