Conditional Formatting - Highlight Duplicates

[Andy Hewitt]

In Excel 2007 I can create a conditional formatting rule to show up duplicates contained within a range.

 

For example:  Select column D, Click Conditional Formatting -> Highlight Cells Rules -> Duplicate Values...

 

How do I set up the same in code using the XlsIO component?  I'm having trouble figuring it out.

 

Unfortunately the XlsIO code generator tool does not seem to show this when I use it to look at a spreadsheet that has been set up with this conditional formatting.

[Andy Hewitt]

I have not been able to find a way to do it as described above but I have found a way to achieve the same.  Would still be interested to know if there is a direct solution that matches Excel's Duplicates conditioning via XlsIO if anyone knows?

 

For benefit of anyone else wishing to achieve this, here is my solution:

 

                IConditionalFormats condition = MeasureSheet.Range["D2:D2000"].ConditionalFormats; 
                IConditionalFormat noDuplicates = condition.AddCondition();
                noDuplicates.FormatType = ExcelCFType.Formula;
                noDuplicates.FirstFormula = "countif(D:D,INDIRECT(ADDRESS(ROW(), COLUMN())))>1";
                noDuplicates.BackColor = ExcelKnownColors.Red;
                noDuplicates.FontColor = ExcelKnownColors.White;

[Prasanth M]

Hi Andy,

Thanks for your update.

XlsIO does not have support to find duplicates in Excel file. You can use your workaround to achieve this. We have added it to our feature request list and it can be tracked through our Features Management System:
http://www.syncfusion.com/support/directtrac/features/WF-13270 

Please let us know if you need any clarification.

Regards,
Prasanth