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

Free Support Forum - aspose.com

URGENT:ConditionalFormatting Code is not working

Hi,

I am using following function for applying conditional formating, but its not working…

But when manully open excel sheet
ConditionalFormating —> Edit Rules --> Apply

Then my code is working

please help me out…using Aspose 4.4.0.0 version

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

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

//Create an empty conditional formatting in the first worksheet
ConditionalFormattings cfs = sheet.ConditionalFormattings;

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

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

ca.StartRow = row;
ca.EndRow = row;
ca.StartColumn = 2;
ca.EndColumn = col -1;
fcs.AddArea(ca);

int condition1 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterOrEqual, “=$A$1”, null);
int condition2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.LessThan, “=$A$1”, null);

//Set the Font text color.
FormatCondition fc = null;

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

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


}

Hi,

Since you are using some older version for which I am not sure about it, it might be an issue with v4.4.x. But I have tested your scenario with the following sample code using our latest version v5.1.4.3, it works fine.

Please try the attached version.

Sample code:
//Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Cells[“A1”].PutValue(10);

//Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];

//Sets the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 9;
ca.StartColumn = 1;
ca.EndColumn = 1;
fcs.AddArea(ca);



//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterOrEqual, “=$A$1”, null);
int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.LessThan, “=$A$1”, null);
FormatCondition fc = fcs[conditionIndex];
//Sets the background color.
fc.Style.BackgroundColor = Color.Red;
//Sets the foreground color.
fc.Style.ForegroundColor = Color.Green;
//Sets the pattern
fc.Style.Pattern = BackgroundType.DiagonalCrosshatch;


FormatCondition fc2 = fcs[conditionIndex2];
//Sets the background color.
fc2.Style.BackgroundColor = Color.Yellow;

//Saving the Excel file
workbook.Save(“e:\test\condtionalformattingoutput.xls”);

Note: If you don’t want to use latest version or your subscription does not allow this. I am afraid, you got any two options:
1) You need to upgrade your subscription to use latest versions/fixes. This is more beneficent as we have incorporated lot of new and advanced features. We have enhanced the existing feature in it. We have also re-organized the API structures in the recent versions too.
2) You may try some older versions which should be released before your subscription expiry date.

Hope, you understand and thanks.

Thanks for reply…my code working with the current version 4.4.0.0 only after doing the following change::

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

Changed to:
string celllocation = “=$B” + rval.ToString();