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