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.

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

Blog

ImportExcel and Conditional Formatting

Kinga

Join Our Newsletter. No Spam, Only the good stuff.

Related