Applying ConditionalFormatting using FormatConditionType.ContainsBlanks

Aspose.Cells Product Family
1

Hi,


If i apply conditionalFormatting using the following expression its working fine

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

but i want to apply the conditional Formating using FormatConditionType.ContainsBlanks.

int condition2 = fcs.AddCondition(FormatConditionType.ContainsBlanks, OperatorType.None, cformat, null);

below is the complete code used for conditional formatting…

private void conditinalFormating(Workbook workbook, Worksheet sheet, int row, int col, string sectionName)
{

int rval = row + 1;
string celllocation = “=$E” + rval.ToString();

//Create empty conditional formattings in the worksheet

ConditionalFormattings cfs = sheet.ConditionalFormattings;

int index = cfs.Add();

//FormatConditionCollection fcs = cfs[index];
FormatConditions fcs = cfs[index];

//Set the conditional format range.
CellArea ca = new CellArea();

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

fcs.AddArea(ca);

FormatCondition fc = null;

int condition1 = fcs.AddCondition(FormatConditionType.Expression, OperatorType.None, “=ISBLANK(” + celllocation.Remove(0, 1) + “)”, null);

string cformat = null;


for (int i = 5; i <= col - 1; i++)
{

if (i == 5)
{
char c = Convert.ToChar(i + 65);
cformat = c.ToString() + rval.ToString() + “:”;
}
if (i == col - 1)
{
char c = Convert.ToChar(i + 65);
cformat = cformat + c.ToString() + rval.ToString();
}
}


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

int condition2 = fcs.AddCondition(FormatConditionType.ContainsBlanks, OperatorType.None, cformat, null);

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

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

}

plz give suggestions…

2

Hi,

Well, ContainsBlank conditional formatting type is supported in Ms Excel 2007 file formats, so you need to save the file to XLSX format instead of XLS. The XLS format does not support this advanced conditional formatting type.

I have tested your code with latest version/fix v5.1.4.x, it works fine.


Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
int row = 0;
int col = 5;
int rval = row + 1;
string celllocation = “=$E” + rval.ToString();

//Create empty conditional formattings in the worksheet
ConditionalFormattingCollection cfs = sheet.ConditionalFormattings;

int index = cfs.Add();
FormatConditionCollection fcs = cfs[index];

//Set the conditional format range.
CellArea ca = new CellArea();

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

fcs.AddArea(ca);

FormatCondition fc = null;
int condition1 = fcs.AddCondition(FormatConditionType.Expression, OperatorType.None, “=ISBLANK(” + celllocation.Remove(0, 1) + “)”, null);

string cformat = null;


for (int i = 5; i <= col - 1; i++)
{

if (i == 5)
{
char c = Convert.ToChar(i + 65);
cformat = c.ToString() + rval.ToString() + “:”;
}
if (i == col - 1)
{
char c = Convert.ToChar(i + 65);
cformat = cformat + c.ToString() + rval.ToString();
}
}


int condition2 = fcs.AddCondition(FormatConditionType.ContainsBlanks, OperatorType.None, cformat, null);
//int condition2 = fcs.AddCondtion(FormatConditionType.ContainsBlanks);

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

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


workbook.Save(“e:\test\output.xlsx”);

Thank you.