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 ?
Regards,
Sanjeev
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.
Java
//Creates a instance of Workbook & access cells from first worksheet.
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();
//Merges some Cells (A1:B2) into a single Cell.
cells.merge(0,0,2,2);
//Inserts initial value.
cells.get(“A1”).putValue(0);
//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;
fcs.addArea(ca);
//Sets condition formulas.
int conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE,OperatorType.GREATER_THAN,“0”,null);
FormatCondition fc = fcs.get(conditionIndex);
fc.getStyle().setBackgroundColor(Color.getGreen());
conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE,OperatorType.LESS_THAN,“0”,null);
fc = fcs.get(conditionIndex);
fc.getStyle().setBackgroundColor(Color.getRed());
conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE,OperatorType.EQUAL,“0”,null);
fc = fcs.get(conditionIndex);
fc.getStyle().setBackgroundColor(Color.getGray());
//Saves spreadsheet
workbook.save(dir + “output.xls”);
cells.merge(0,0,2,2);
//Inserts initial value.
cells.get(“A1”).putValue(0);
//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;
fcs.addArea(ca);
//Sets condition formulas.
int conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE,OperatorType.GREATER_THAN,“0”,null);
FormatCondition fc = fcs.get(conditionIndex);
fc.getStyle().setBackgroundColor(Color.getGreen());
conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE,OperatorType.LESS_THAN,“0”,null);
fc = fcs.get(conditionIndex);
fc.getStyle().setBackgroundColor(Color.getRed());
conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE,OperatorType.EQUAL,“0”,null);
fc = fcs.get(conditionIndex);
fc.getStyle().setBackgroundColor(Color.getGray());
//Saves spreadsheet
workbook.save(dir + “output.xls”);
Please also go through the following articles for future reference.