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

Free Support Forum - aspose.com

URGENT:Applying Conditional Formatting dynamically to excel sheet cells not working

How to apply the conditional formatting to each cell in the Excel sheet
by taking the row and column position of the cell dynamically from backend.

Using Aspose.Cells Version 4.4.0.0

I want to compare the ISBLANK( ) for particular cell and then apply conditional formatting.

But am getting the error like:

One conditional formatting can only contain up to 3 conditions…

I am trying in my code like::

ConditionalFormattings cfs = sheet.ConditionalFormattings;
int index = cfs.Add();
FormatConditions fcs = cfs[index];
CellArea ca = new CellArea();
fcs.AddArea(ca);

eg: int rowNumber = 50;

int col = 10;

ca.StartRow = rowNumber ;
ca.EndRow = rowNumber ;
ca.StartColumn = 4;
ca.EndColumn = col - 1;

FormatCondition fc = null;


int condition2 = 0;
for (int i = 5; i < col; i++)
{
char c = Convert.ToChar(i + 65);
string cval = “$” + c.ToString() + rowNumber.ToString();

condition2 = fcs.AddCondition(FormatConditionType.Expression, OperatorType.None, “=ISBLANK(” + cval + “)”, null);

fc = fcs[condition2];
fc.Style.BackgroundColor = Color.Red;

}


Please help me out…

Hi,

I have tested your scenario with the following sample code using latest version v5.1.4.x (attached), it works fine.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

ConditionalFormattingCollection cfs = sheet.ConditionalFormattings;
int index = cfs.Add();
FormatConditionCollection fcs = cfs[index];
CellArea ca = new CellArea();
fcs.AddArea(ca);

int rowNumber = 50;
int col = 10;

ca.StartRow = rowNumber;
ca.EndRow = rowNumber;
ca.StartColumn = 4;
ca.EndColumn = col - 1;

FormatCondition fc = null;


int condition2 = 0;
for (int i = 5; i < col; i++)
{
char c = Convert.ToChar(i + 65);
string cval = “$” + c.ToString() + rowNumber.ToString();

condition2 = fcs.AddCondition(FormatConditionType.Expression, OperatorType.None, “=ISBLANK(” + cval + “)”, null);

fc = fcs[condition2];
fc.Style.BackgroundColor = Color.Red;

}

workbook.Save(“e:\test\outputondition.xls”);

I think since you are using some older version of the product, so it might be an issue or limitation in your version, which is fixed/enhanced in later versions. Please use our latest version / fix (attached) as it works fine.

Thank you.