Can Import-Excel Remove Conditional Formatting?

[ArduinoBen]

Hi all.

I've been using Import-Excel for the past couple weeks now. Wondering whether there is a way to remove conditional formatting rules for a range of cells or even just clear rules from an entire sheet?

Not sure whether this is presently a feature of Import-Excel, I've been looking through the documentation and have not found anything relevant so far.

Would appreciate any advice here. Thanks!

[dfinke]

Thanks for trying the module @ArduinoBen and good question!

Didn't have that scenario on the boards. The answer is maybe, there is no PowerShell function to do that.

When you Export or Open the xlsx, you can get to the underlying object model. There is a collection of conditionals for the sheet. I'm guessing that you could search and remove the one you want and save.

With that said. I've never tried.

[dfinke]

Yes, in the Excel object model you can do a RemoveAt. There are other Remove* methods that work on the collection.

Here is an example of how to get to the object model.

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$xlfile = "$PSScriptRoot\spike.xlsx" 
Remove-Item $xlfile -ErrorAction SilentlyContinue

$ct1 = New-ConditionalText -Text 'South'
$ct2 = New-ConditionalText -Text 'North' -BackgroundColor Yellow
$ct3 = New-ConditionalText -Text 'East' -BackgroundColor Green

$xlpkg = $data | Export-Excel -Path $xlfile -AutoSize -PassThru -ConditionalText $ct1, $ct2, $ct3

$ws = $xlpkg.Sheet1

# Removes the North conditional formatting
$ws.ConditionalFormatting.RemoveAt(1)

Close-ExcelPackage $xlpkg -show