Formatting cells

Aspose.Cells Product Family
1

Hi, I'm trying to add formatting to my report.

The problem is that if the cellvalue is empty, the color should be green.
If there is any value in the cell, the color should be red.
I've been trying several things but I just can't get it done.

Can anyone help me?

Worksheet sheet = workbook.Worksheets[0];
int index = sheet.ConditionalFormattings.Add();

FormatConditions fcs = sheet.ConditionalFormattings[index];

CellArea ca = new CellArea();
ca.StartRow = 1;
ca.EndRow = ExportReplacerAvailabilityDataTable.Count;
ca.StartColumn = 2;
ca.EndColumn = 40;

fcs.AddArea(ca);
//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, "Kantoor", "");
int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, "HOLI", "");
int conditionIndex3 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.NotEqual, "Kantoor", "HOLI");

//Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Style.BackgroundColor = Color.Red;

FormatCondition fc2 = fcs[conditionIndex2];
fc2.Style.BackgroundColor = Color.Red;

FormatCondition fc3 = fcs[conditionIndex3];
fc3.Style.BackgroundColor = Color.Green;

I'm using Aspose.Cells 4.3.0.0

2

Hi,

Please try the our latest fix/version: Aspose.Cells for .NET (Latest Version) , it works fine for your needs using the following code.

Sample code:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
CellArea ca = new CellArea();
ca.StartRow = 1;
ca.EndRow = 100;
ca.StartColumn = 2;
ca.EndColumn = 40;
fcs.AddArea(ca);
//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, “Kantoor”, “”);
int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, “HOLI”, “”);
int conditionIndex3 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.NotEqual, “Kantoor”, “HOLI”);
//Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Style.BackgroundColor = Color.Red;
FormatCondition fc2 = fcs[conditionIndex2];
fc2.Style.BackgroundColor = Color.Red;
FormatCondition fc3 = fcs[conditionIndex3];
fc3.Style.BackgroundColor = Color.Green;
workbook.Save(“e:\test2\outputconditiona.xlsx”);

I have attached the output file here for your reference.

Moreover, since you are using older version of the product e.g v 4.3.0, so, I am afraid we cannot help you much here. We can only recommend you to kindly upgrade to latest versions of the product which is more robust and has more feature rich APIs. Over the years, we have incorporated new enhancements for existing features and included new features for Excel 97 - 2010.

Also, if there is some issues in the older version, we cannot resolve it, we can only resolve the issues based on the new versions of the product.

Thanks for your understanding!

3

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please also see this document for your complete reference of Conditional Formatting support of Aspose.Cells.

http://www.aspose.com/docs/display/cellsnet/Conditional+Formatting

4

The problem is that I have no idea what the text might be in the cell.
It can be any value.

5

Hi,


I think you may try as following, see the updated code especially in bold, you may modify the code accordingly for your needs.

Sample code:


Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];
int index = sheet.ConditionalFormattings.Add();

FormatConditionCollection fcs = sheet.ConditionalFormattings[index];

CellArea ca = new CellArea();
ca.StartRow = 1;
ca.EndRow = 100;
ca.StartColumn = 2;
ca.EndColumn = 40;

fcs.AddArea(ca);
//Adds conditions.
//If there is some text/value or data in the cells (any value).
int conditionIndex = fcs.AddCondition(FormatConditionType.NotContainsBlanks);
//If there is no text/data/value in the cells.
int conditionIndex3 = fcs.AddCondition(FormatConditionType.ContainsBlanks);


//Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Style.BackgroundColor = Color.Red;

FormatCondition fc3 = fcs[conditionIndex3];
fc3.Style.BackgroundColor = Color.Green;


workbook.Save(“e:\test2\outputconditiona.xlsx”);

And, I am using latest version/fix of the product.

Thank you.