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

Aspose.Cells Product Family
1

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…

2

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.