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

Free Support Forum - aspose.com

Conditional Formatting of color to be applied on a merged cell

Dear Team,

I have a scenario where conditional formatting to be applied on a merged cell (i.e., A19).

If value in the merged cell is > 0 then merged cell color should change to green.
If value in the merged cell is < 0 then merged cell color should change to red.
If value in the merged cell is = 0 then merged cell color should change to gray.

After my excel generated if I try to modify the value in the merged cell then based on the above conditions the color should automatically change.

How to do this using java code ?


Hi Sanjeev,

Thank you for contacting Aspose support.

Please try the following piece of code against your presented requirements. Also attached is the resultant spreadsheet for your reference.


//Creates a instance of Workbook & access cells from first worksheet.
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();

//Merges some Cells (A1:B2) into a single Cell.
//Inserts initial value.

//Adds an empty conditional formatting.
int index = sheet.getConditionalFormattings().add();
FormatConditionCollection fcs = sheet.getConditionalFormattings().get(index);
//Sets the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 1;
ca.StartColumn = 0;
ca.EndColumn = 1;

//Sets condition formulas.
int conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE,OperatorType.GREATER_THAN,“0”,null);
FormatCondition fc = fcs.get(conditionIndex);
conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE,OperatorType.LESS_THAN,“0”,null);
fc = fcs.get(conditionIndex);
conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE,OperatorType.EQUAL,“0”,null);
fc = fcs.get(conditionIndex);

//Saves spreadsheet
workbook.save(dir + “output.xls”);

Please also go through the following articles for future reference.