DEV Community

Kinga
Kinga

Posted on

6 2

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

Speedy emails, satisfied customers

Postmark Image

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (0)

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay