How to programmatically change the Icon value and type on a conditionnal formatting

Aspose.Cells Product Family
1

Hello,

In an Excel file, I want to add, on specific cells, a conditionnal formatting, displaying a green arrow when the value is strictly positive, yellow arrow for 0, and red for negative values.
Thus, I’ve used the following (simplified) code:

Workbook book = new Workbook();
Worksheet sheet = book.getWorksheets().get(0);
// Add some data for the example…
sheet.getCells().get(“B2”).setValue(-0.1);
sheet.getCells().get(“C2”).setValue(0);
sheet.getCells().get(“D2”).setValue(0);
sheet.getCells().get(“E2”).setValue(1);
// Set conditional formatting info…
ConditionalFormattingCollection cfc = sheet.getConditionalFormattings();
int index = cfc.add();
FormatConditionCollection fcc = cfc.get(index);
CellArea area = new CellArea();
area.StartRow = 1;
area.EndRow = 1;
area.StartColumn = 1;
area.EndColumn = 4;
// Use Icon Set
index = fcc.addCondition(FormatConditionType.ICON_SET);
fcc.addArea(area);
FormatCondition fc = fcc.get(index);
fc.getIconSet().setType(IconSetType.ARROWS_3);
fc.getIconSet().setShowValue(false);
// There is something missing here…

// Save
book.save(“foo.xlsx”);

However, with this code, the conditions are based on % values (green for >= 66%, yellow for 33%, red otherwise) - cf. “what I have.jpg” screenshot.

So I want to change that configuration programmatically (cf. “what I want.jpg” screenshot). How do I do that?

What I’ve tried is to use the fc.getIconSet().getCfvos() method, but without success, for example doing that changed nothing in my Excel file :

ConditionalFormattingValueCollection cfvc = fc.getIconSet().getCfvos();
index = cfvc.add(FormatConditionValueType.NUMBER, “0”);
ConditionalFormattingValue cfv = cfvc.get(index);
cfv.setGTE(false);

Thanks.

Romain.

ps: We are using Java 1.6, Aspose Cells 7.0.3.

2

Hi,

Please download and try the latest version:

Aspose.Cells for Java 7.1.2

If the problem still occurs, then please provide us your actual output xls/xlsx file and your expected output xls/xlsx file.

You can create both of them manually using Ms-Excel 2010 and post here.

It will help us to figure out a solution for your problem and we will provide you a sample code or a workaround.

3

Hi,

Because for the iconset ARROWS_3, there are only the first three value objects take effect and they have been initialized when you set the iconset type, so please change your code as following:

Java


…

ConditionalFormattingValueCollection cfvc = fc.getIconSet().getCfvos();

ConditionalFormattingValue cfv = cfvc.get(1);

cfv.setType(FormatConditionValueType.NUMBER);

cfv.setValue(0);

cfv = cfvc.get(2);

cfv.setType(FormatConditionValueType.NUMBER);

cfv.setValue(0);


4

Hello,

Thanks for your answer, I succeed to solve my issue. The exact code is:

int index = fcc.addCondition(FormatConditionType.ICON_SET);
fcc.addArea(area);
FormatCondition fc = fcc.get(index);
fc.getIconSet().setType(IconSetType.ARROWS_3);
fc.getIconSet().setShowValue(false);
// Set conditions
ConditionalFormattingValueCollection cfvc = fc.getIconSet().getCfvos();
ConditionalFormattingValue cfv = cfvc.get(1);
cfv.setType(FormatConditionValueType.NUMBER);
cfv.setValue(0);
cfv.setGTE(true);
cfv = cfvc.get(2);
cfv.setType(FormatConditionValueType.NUMBER);
cfv.setValue(0);
cfv.setGTE(false);

5

Hi,

Thanks for sharing the code example.

Its good to know your problem is resolved now. If you get any other question or face any problem, please feel free to let us know, we will help you asap.