We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Apply Conditional Formatting in Worksheets - Issue

Hi,

I'm trying to use the conditional formatting in Aspose.Cells. This does not work in all my cases.

See snippet below:-

//Instantiating a Workbook object

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

int index = sheet.ConditionalFormattings.Add();

FormatConditionCollection fcs = sheet.ConditionalFormattings[index];

//Sets the conditional format range.

CellArea ca = new CellArea();

ca.StartRow = 13;

ca.EndRow = 45;

ca.StartColumn = 0;

ca.EndColumn = 0;

fcs.AddArea(ca);

//Adds condition.

int conditionIndex1 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "0", "0.24999");

int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "0.25", "0.49999");

int conditionIndex3 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "0.50", "0.74999");

int conditionIndex4 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "0.75", "1.00");

//Sets the background color.

FormatCondition fc1 = fcs[conditionIndex1];

fc1.Style.BackgroundColor = Color.FromArgb(150, 209, 238);

FormatCondition fc2 = fcs[conditionIndex2];

fc2.Style.BackgroundColor = Color.FromArgb(204, 255, 204);

FormatCondition fc3 = fcs[conditionIndex3];

fc3.Style.BackgroundColor = Color.FromArgb(255, 255, 153);

FormatCondition fc4 = fcs[conditionIndex4];

fc4.Style.BackgroundColor = Color.FromArgb(255, 204, 0);

//Saving the Excel file

workbook.Save("C:\\output.xls", FileFormatType.Default);

The 1st and 4th condition do not work. Test values are 0.01, 0.25, 0.52 and 0.76, etc.

Please let me know asap.

Thanks,

V

Hi,


I have tested your sample code with latest fix/version v7.1.1.1, Please download: Aspose.Cells for .NET v7.1.1.1
It works fine.

Here is my code and find attached the output file.

Sample code:

//Instantiating a Workbook object

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

int index = sheet.ConditionalFormattings.Add();

FormatConditionCollection fcs = sheet.ConditionalFormattings[index];

//Sets the conditional format range.

CellArea ca = new CellArea();

ca.StartRow = 13;

ca.EndRow = 45;

ca.StartColumn = 0;

ca.EndColumn = 0;

fcs.AddArea(ca);

//Adds condition.

int conditionIndex1 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, “0”, “0.24999”);

int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, “0.25”, “0.49999”);

int conditionIndex3 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, “0.50”, “0.74999”);

int conditionIndex4 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, “0.75”, “1.00”);


//Sets the background color.

FormatCondition fc1 = fcs[conditionIndex1];

fc1.Style.BackgroundColor = Color.FromArgb(150, 209, 238);

FormatCondition fc2 = fcs[conditionIndex2];

fc2.Style.BackgroundColor = Color.FromArgb(204, 255, 204);

FormatCondition fc3 = fcs[conditionIndex3];

fc3.Style.BackgroundColor = Color.FromArgb(255, 255, 153);

FormatCondition fc4 = fcs[conditionIndex4];

fc4.Style.BackgroundColor = Color.FromArgb(255, 204, 0);

//Saving the Excel file

workbook.Save(“e:\test2\conddoutput.xls”);


Thank you.