Introduction
Companies everywhere work with Microsoft Excel spreadsheets. They are used for everything from creating simple invoices to building complex models. One of the features that make Microsoft Excel so attractive is conditional formatting. Conditional formatting allows users to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell, or the value of a formula. For example, they can have the text in a cell appear bold only when the value of the cell is greater than 100.
This article describes how to use Aspose.Cells for Java to create a simple dashboard in a spreadsheet by applying conditional formatting.
Aspose.Cells for Java is a Microsoft Excel spreadsheet API that allows Java developers to create, manipulate and convert Excel files. Aspose.Cells for Java provides a variety of features which are simple to integrate and easy to work with. One feature that makes it so flexible is that it is independent of Microsoft Office Automation – it does not require Microsoft Excel to be installed on the system in order to work.
Get a free Aspose.Cells trial.
Application Overview
The scenario for this application is a sales team where each individual receives a spreadsheet every week that contains sales by product. To give the sales professional an idea of their performance, the spreadsheet has a dashboard as well as the sales numbers for each product. The dashboard compares this week’s performance to last week’s, projections and targets. When a target it met, or a projection exceeded, the dashboard shows green.
Data
For the purposes of this application, we’re using pre-defined data for weekly sales. (The pre-defined data can easily be replaced with input from a data source.) The sample data used is:
- Product: A range of products
- Price: A product’s unit price
- Units: Number of units sold last week
- Sub-total: Price * Units
- This Week's Total: =Sum(sub-total row)
- Last Week’s Total: the grand total for last week.
- Target: the total sales target for this week agreed by the sales team.
- Projection: the total sales for this week suggested by trend analysis.
Conditional Formatting
After populating the data, the application performs the following operations:
- Apply conditional formatting to the sub-total row in order to highlight the five best and worst performing products.
- Create a dashboard that shows:
- This week: a comparison of sales for the current and previous weeks. Conditional formatting is applied to highlight performance. The cell is green if sales this week were higher than last week; red if lower.
- Target: actual sales this week compared to target sales. The cell is green if the actual sales number is higher than the target; red if lower.
- Projection: actual sales compared to projected sales. The cell is green if the actual sales number is greater than the projected sales; red if lower.
To illustrate how to apply conditional formatting, we’re using a very simple scenario. In a real application, the rules would likely be more complex, and each data item would be calculated or imported from a data source.
The screenshot below shows the output XLS file with conditional formatting applied as per the defined rules.
The dashboard gives the sales professional a quick way to evaluate their performance and the raw data is included so they can do detailed analysis.
The screenshot below shows the conditional formatting rules applied using Aspose.Cells for Java as they look in Microsoft Excel.
Translating Conditional Formatting into Code
Below is a section of code that shows the method used to set up the dashboard and applying conditional formatting to the sum total column using Aspose.Cells for Java.
Get a free Aspose.Cells trial.
int idx = worksheet.getConditionalFormattings().add();
FormatConditionCollection fcc = worksheet.getConditionalFormattings()
.get(idx);
CellArea ca = CellArea.createCellArea("C5", "C5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc = fcc.get(idx);
fc.setFormula1("=$C$18");
fc.setOperator(OperatorType.GREATER_THAN);
fc.getStyle().setBackgroundColor(Color.fromArgb(198, 239, 206));
fc.getStyle().getFont().setColor(Color.fromArgb(0, 97, 0));
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
ca = CellArea.createCellArea("C5", "C5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
fc = fcc.get(idx);
fc.setFormula1("=$C$18");
fc.setOperator(OperatorType.LESS_OR_EQUAL);
fc.getStyle().setBackgroundColor(Color.fromArgb(255, 199, 206));
fc.getStyle().getFont().setColor(Color.fromArgb(156, 0, 6));
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
ca = CellArea.createCellArea("F5", "F5");
fcc.addArea(ca);
ca = CellArea.createCellArea("I5", "I5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc2 = fcc.get(idx);
fc2.setFormula1("=$C$5");
fc2.setOperator(OperatorType.LESS_THAN);
fc2.getStyle().setBackgroundColor(Color.fromArgb(198, 239, 206));
fc2.getStyle().getFont().setColor(Color.fromArgb(0, 97, 0));
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
ca = CellArea.createCellArea("F5", "F5");
fcc.addArea(ca);
ca = CellArea.createCellArea("I5", "I5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
fc2 = fcc.get(idx);
fc2.setFormula1("=$C$5");
fc2.setOperator(OperatorType.GREATER_OR_EQUAL);
fc2.getStyle().setBackgroundColor(Color.fromArgb(255, 199, 206));
fc2.getStyle().getFont().setColor(Color.fromArgb(156, 0, 6));
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
ca = CellArea.createCellArea("C17", "AA17");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.TOP_10);
fc = fcc.get(idx);
fc.getStyle().setBackgroundColor(Color.fromArgb(195, 214, 155));
fc.getTop10().setRank(5);
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
ca = new CellArea();
ca = CellArea.createCellArea("C17", "AA17");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.TOP_10);
fc = fcc.get(idx);
fc.getStyle().setBackgroundColor(Color.fromArgb(217, 150, 148));
fc.getTop10().setRank(5);
fc.getTop10().setBottom(true);
}
Complete Application Code
Below you’ll find the complete source code of the application with all the design and conditional formatting methods used to create the sales Excel file. It is extensively commented to lead you through how the output file is created.
import com.aspose.cells.*;
public class ConditionalFormatting {
public static void main(String[] args) {
try {
Workbook workbook = new Workbook();
Style style = workbook.getDefaultStyle();
style.getFont().setName("Calibri");
style.getFont().setSize(11);
workbook.setDefaultStyle(style);
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);
Cells cells = sheet.getCells();
PopulateData(cells);
createDashboardDesign(workbook,cells);
workbook.calculateFormula();
ApplyConditionalFormattings(sheet);
sheet.autoFitColumns();
sheet.setName("Sales");
workbook.save("c:\\data\\Sales.xls");
}
catch(Exception exp)
{
System.out.print(exp.getMessage());
}
}
public static void createDashboardDesign(Workbook workbook, Cells cells)
{
cells.get("B2").setValue("Dashboard");
Range resultRange = cells.createRange("B2", "K8");
resultRange.setOutlineBorders(CellBorderType.MEDIUM,Color.getBlack());
Range detailRange = cells.createRange("B11", "AA19");
createRangeWithBorders(cells,"C4:D4",false);
cells.get("C4").setValue("This Week");
createRangeWithBorders(cells, "F4:G4", false);
cells.get("F4").setValue("Target");
createRangeWithBorders(cells, "I4:J4", false);
cells.get("I4").setValue("Projection");
cells.get("C5").setFormula("=SUM(C17:AA17)");
cells.get("B11").setValue("Sales Numbers");
cells.get("B14").setValue("Product");
cells.get("B15").setValue("Price");
cells.get("B16").setValue("Unit");
cells.get("B17").setValue("Sub Total");
cells.get("B18").setValue("Last Week");
cells.get("C5").setFormula("=SUM(C17:AA17)");
setStyleForCells(cells,false,14,"B11");
setStyleForCells(cells,true,11,"B14");
setStyleForCells(cells,true,11,"B15");
setStyleForCells(cells,true,11,"B16");
setStyleForCells(cells,true,11,"B17");
setStyleForCells(cells,false,11,"B18");
setStyleForCells(cells,false,16,"B2");
setStyleForCells(cells, false, 14, "C4");
setStyleForCells(cells, false, 14, "F4");
setStyleForCells(cells, false, 14, "I4");
Style NumStyle = workbook.createStyle();
FormatDataRows(cells, NumStyle);
createRangeWithBorders(cells,"F5:G6",true);
createRangeWithBorders(cells,"C5:D6",true);
createRangeWithBorders(cells,"I5:J6",true);
setStyleForCells(cells,false,14,"C5");
setStyleForCells(cells,false,14,"F5");
setStyleForCells(cells,false,14,"I5");
SetRangeStyle(resultRange, workbook.createStyle(),Color.getWhite());
SetRangeStyle(detailRange, workbook.createStyle(), Color.fromArgb(242, 242, 242));
}
public static void PopulateData(Cells cells)
{
double LastWeekValue = 8000.00;
double TargetValue = 9000.00;
double ProjectionValue = 7500.00;
String[] ProductNames=new String[]{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","Y","Z"};
double[] Price = { 12.50,12.50,9.50,7.50,3.50,25.00,10.00,9.33,12.89,8.00,12.50,12.50,9.50,7.50,3.50,25.00,10.00,9.33,12.89,8.00,12.50,12.50,9.50,7.50,3.50 };
int[] Unit = {24,34,9,50,28,45,98,11,2,11,47,32,68,5,98,43,15,3,1,23,45,27,21,43,25};
cells.importArray(ProductNames,13,2,false);
cells.importArray(Price,14,2,false);
cells.importArray(Unit,15,2,false);
cells.get("C17").setFormula("=C15*C16 ");
for (int i = 3; i <= 26; i++)
{
cells.get(16, i).copy(cells.get(16, 2));
}
cells.get("F5").setValue(TargetValue);
cells.get("I5").setValue(ProjectionValue);
cells.get("C18").setValue(LastWeekValue);
}
public static void createRangeWithBorders(Cells cells, String range, boolean border)
{
Range range1 = cells.createRange(range);
range1.merge();
if(border)
{
range1.setOutlineBorders(CellBorderType.MEDIUM,Color.getBlack());
}
}
public static void setStyleForCells(Cells cells, boolean fontBold, int size,String cell)
{
Style style = cells.get(cell).getStyle();
Font font = style.getFont();
font.setName("Calibri");
font.setSize(size);
font.setBold(fontBold);
cells.get(cell).setStyle(style);
}
private static void FormatDataRows(Cells cells, Style style)
{
Range Alignment = cells.createRange("C14", "AA17");
Range Price = cells.createRange("C15", "AA15");
Range Total = cells.createRange("C17", "AA17");
StyleFlag sf = new StyleFlag();
style.setHorizontalAlignment(TextAlignmentType.CENTER);
style.setVerticalAlignment(TextAlignmentType.CENTER);
sf.setHorizontalAlignment(true);
sf.setVerticalAlignment(true);
Alignment.applyStyle(style,sf);
style.setNumber(7);
sf.setNumberFormat(true);
Price.applyStyle(style, sf);
Total.applyStyle(style, sf);
cells.get("C5").setStyle(style);
cells.get("F5").setStyle(style);
cells.get("I5").setStyle(style);
cells.get("C18").setStyle(style);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN,
Color.getBlack());
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);
flag.setTopBorder(true);
Total.applyStyle(style, flag);
}
public static void ApplyConditionalFormattings(Worksheet worksheet)
{
int idx = worksheet.getConditionalFormattings().add();
FormatConditionCollection fcc = worksheet.getConditionalFormattings()
.get(idx);
CellArea ca = CellArea.createCellArea("C5", "C5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc = fcc.get(idx);
fc.setFormula1("=$C$18");
fc.setOperator(OperatorType.GREATER_THAN);
fc.getStyle().setBackgroundColor(Color.fromArgb(198, 239, 206));
fc.getStyle().getFont().setColor(Color.fromArgb(0, 97, 0));
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
ca = CellArea.createCellArea("C5", "C5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
fc = fcc.get(idx);
fc.setFormula1("=$C$18");
fc.setOperator(OperatorType.LESS_OR_EQUAL);
fc.getStyle().setBackgroundColor(Color.fromArgb(255, 199, 206));
fc.getStyle().getFont().setColor(Color.fromArgb(156, 0, 6));
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
ca = CellArea.createCellArea("F5", "F5");
fcc.addArea(ca);
ca = CellArea.createCellArea("I5", "I5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc2 = fcc.get(idx);
fc2.setFormula1("=$C$5");
fc2.setOperator(OperatorType.LESS_THAN);
fc2.getStyle().setBackgroundColor(Color.fromArgb(198, 239, 206));
fc2.getStyle().getFont().setColor(Color.fromArgb(0, 97, 0));
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
ca = CellArea.createCellArea("F5", "F5");
fcc.addArea(ca);
ca = CellArea.createCellArea("I5", "I5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
fc2 = fcc.get(idx);
fc2.setFormula1("=$C$5");
fc2.setOperator(OperatorType.GREATER_OR_EQUAL);
fc2.getStyle().setBackgroundColor(Color.fromArgb(255, 199, 206));
fc2.getStyle().getFont().setColor(Color.fromArgb(156, 0, 6));
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
ca = CellArea.createCellArea("C17", "AA17");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.TOP_10);
fc = fcc.get(idx);
fc.getStyle().setBackgroundColor(Color.fromArgb(195, 214, 155));
fc.getTop10().setRank(5);
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
ca = new CellArea();
ca = CellArea.createCellArea("C17", "AA17");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.TOP_10);
fc = fcc.get(idx);
fc.getStyle().setBackgroundColor(Color.fromArgb(217, 150, 148));
fc.getTop10().setRank(5);
fc.getTop10().setBottom(true);
}
private static void SetRangeStyle(Range range, Style style, Color color)
{
style.setForegroundColor(color);
style.setPattern(BackgroundType.SOLID);
StyleFlag flag = new StyleFlag();
flag.setCellShading(true);
range.applyStyle(style,flag);
}
}
Summary
For this example, we used Aspose.Cells for Java, an API specifically developed to make working with Microsoft Excel spreadsheets in Java applications as simple as possible. It provides the same conditional formatting features that are available in Microsoft Excel and works without Excel installed on the development machine.
Conditional formatting is one of the many tools that Microsoft Excel users depend on to make spreadsheets easier to read. The application above illustrates that applying conditional formatting programmatically doesn't have to be difficult. With the right tools, Java developers can create and format sophisticated spreadsheets that use conditional formatting to good effect.
Get a free Aspose.Cells trial.