Does Validation and ConditionalFormatting work on the same area?

Aspose.Cells Product Family
1

I need to provide drop down lists for cells in a certain area and then highlight any duplicate values that the user selects from those dropdowns. My code is below. The dropdowns work properly, but when the same entry is selected in different cells in the area I get no conditional highlighting.

Thanks for you help,

Ron

Validations validations = ws.Validations;

Validation validation = validations[validations.Add()];

validation.Type = ValidationType.List;

validation.InCellDropDown = true;

validation.Formula1 = "=NAME0" ;

CellArea area;

area.StartColumn = 2;

area.EndColumn = 2;

area.StartRow = 4;

area.EndRow = 14;

validation.AreaList.Add(area);

int index = ws.ConditionalFormattings.Add();

FormatConditions fcs = ws.ConditionalFormattings[index];

fcs.AddArea(area);

int conditionIndex = fcs.AddCondtion(FormatConditionType.DuplicateValues);

FormatCondition fc = fcs[conditionIndex];

fc.Style.Font.Color = Color.Red;

fc.Style.BackgroundColor = Color.Yellow;

fc.Style.Pattern = BackgroundType.Solid;

Thanks for your help,

Ron

2
Hi,

Yes, sure validation and conditional formattings works on the same area. I think you might be saving your generated file in XLS format and not XLSX format. Mind you, the advanced conditional formatting features (e.g., Highlight Duplicate Values etc.) are only supported in MS Excel 2007 XLSX format as MS Excel 97-2003 XLS format does not support it.

Following is my code and attached is the generated file. The output file is just fine to implement both list validation and duplicate values conditional formattings. I use the latest version/fix (4.6.0.x) to run my code.

Sample code:

Workbook workbook = new Workbook();
Worksheet ws = workbook.Worksheets[0];
int i = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets[i];
Range range = worksheet2.Cells.CreateRange("E1", "E4");
range.Name = "NAME0";
range[0, 0].PutValue("Blue");
range[1, 0].PutValue("Red");
range[2, 0].PutValue("Green");
range[3, 0].PutValue("Yellow");
Validations validations = ws.Validations;
Validation validation = validations[validations.Add()];
validation.Type = Aspose.Cells.ValidationType.List;
validation.InCellDropDown = true;
validation.Formula1 = "=NAME0";
CellArea area;
area.StartColumn = 2;
area.EndColumn = 2;
area.StartRow = 4;
area.EndRow = 14;
validation.AreaList.Add(area);

int index = ws.ConditionalFormattings.Add();
FormatConditions fcs = ws.ConditionalFormattings[index];
fcs.AddArea(area);
int conditionIndex = fcs.AddCondtion(FormatConditionType.DuplicateValues);
FormatCondition fc = fcs[conditionIndex];
fc.Style.Font.Color = Color.Red;
fc.Style.BackgroundColor = Color.Yellow;
fc.Style.Pattern = BackgroundType.Solid;

workbook.Save("f:\\test\\valconditionaltest.xlsx",FileFormatType.Excel2007Xlsx);

Could you run your code and tell us if you find other results than the attached file.

Thank you.

3

I am running Excel2003 SP3 and my customer has provided me a sample spreadsheet that I can open that has the validation working. I am trying to replicate the sample with code.

When I attempt your suggested fix and save as 2007xlsx, my version of Excel won't open it.

Unfortunately I cannot force the customer to upgrade Excel to accommodate. Is there another mechanism that will provide this validation functionality in Excel2003??

4

Sorry, I misspeak (I must need more coffee). I did not mean “validation is working” I meant that “conditional formatting is working” in the sample sent by my customer. I am not having any problem with validation, I just want user selected duplicates to be highlighted.

5

Hi,

Thank you for considering Aspose.

Well, you can try fcs.AddCondition(FormatConditionType.Expression, OperatorType.None, “=AND(COUNTIF(‘=NAME0’, C5)>1,NOT(ISBLANK(C5)))”, “") to get your desired results in Excel 2003.

Please see the following Sample Code for you reference,

Sample Code:

Workbook book = new Workbook();

book.Open("C:\\test.xls");

Worksheet ws = book.Worksheets[0];

// Create a range in the second worksheet.

Range range = ws.Cells.CreateRange("E1", "E4");

// Name the range.

range.Name = "NAME0";

// Fill different cells with data in the range.

range[0, 0].PutValue("Blue");

range[1, 0].PutValue("Red");

range[2, 0].PutValue("Green");

range[3, 0].PutValue("Yellow");

Validations validations = ws.Validations;

Validation validation = validations[validations.Add()];

validation.Type = ValidationType.List;

validation.InCellDropDown = true;

validation.Formula1 = "=NAME0";

CellArea area;

area.StartColumn = 2;

area.EndColumn = 2;

area.StartRow = 4;

area.EndRow = 14;

validation.AreaList.Add(area);

int index = ws.ConditionalFormattings.Add();

FormatConditions fcs = ws.ConditionalFormattings[index];

fcs.AddArea(area);

int conditionIndex = fcs.AddCondition(FormatConditionType.Expression, OperatorType.None, "=AND(COUNTIF($C$5:$C$15, C5)>1,NOT(ISBLANK(C5)))", "");

FormatCondition fc = fcs[conditionIndex];

fc.Style.Font.Color = Color.Red;

fc.Style.BackgroundColor = Color.Yellow;

fc.Style.Pattern = BackgroundType.Solid;

book.Save("C:\\result.xls");

Thank You & Best Regards,

6

Thanks, this is a very good solution,