CODEWITHSUNDEEP
pythonexcelexcel-formulapython-3.5openpyxl
pjmorin0001
pjmorin0001

Reputation: 89

OpenPyXL Using Built-In Conditional Formatting ie: Duplicate and Unique Values

I am writing a python method that checks a specific column in Excel and highlights duplicate values in red (if any), then copy those rows onto a separate sheet that I will use to check to see why they have duplicate values. This is just for Asset Management where I want to check to make sure there are no two exact serial numbers or Asset ID numbers etc.

At this moment I just want to check the column and highlight duplicate values in red. As of now, I have this method started and it runs it just does not highlight of the cells that have duplicate values. I am using a test sheet with these values in column A,

(336,565,635,567,474,326,366,756,879,567,453,657,678,324,987,667,567,657,567)The number "567" repeats a few times.

def check_duplicate_values(self,wb):
    self.wb=wb
    ws=self.wb.active
    dxf = DifferentialStyle(fill=self.red_fill())
    rule = Rule(type="duplicateValues", dxf=dxf, stopIfTrue=None, formula=['COUNTIF($A$1:$A1,A1)>1'])
    ws.conditional_formatting.add('Sheet1!$A:$A',rule) #Not sure if I need this  
    self.wb.save('test.xlsx')

In Excel, I can just create a Conditional Format rule to accomplish this however in OpenPyXL I am not sure if I am using their built-in methods correctly. Also, could my formula be incorrect?

Upvotes: 1

Views: 2290

Answers (2)

John Paulo Rodriguez
John Paulo Rodriguez

Reputation: 1400

Just remove the formula and you're good to go.

duplicate_rule = Rule(type="duplicateValues", dxf=dxf, stopIfTrue=None)

You can also use unique rule:

unique_rule = Rule(type="uniqueValues", dxf=dxf, stopIfTrue=None)

Check this out for more info: https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/formatting/rule.html#RuleType

Upvotes: 0

Charlie Clark
Charlie Clark

Reputation: 19507

Whose built-in methods are you referring to? openpyxl is a file format library and, hence, allows you manage conditional formats as they are stored in Excel worksheets. Unfortunately, the details of the rules are not very clear from the specification so form of reverse engineering from an existing is generally required, though it's probably worth noting that rules created by Excel are almost always more verbose than actually required.

I would direct further questions to the openpyxl mailing list.

Upvotes: 1

Related Questions