Applying Conditional Formatting to Spreadsheets with Aspose.Cells

• Download Conditional Formatting Project - 20 KB

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:

1. Apply conditional formatting to the sub-total row in order to highlight the five best and worst performing products.
2. 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.

    // Apply CellValue Conditional Formatting on cells C5, F5, and I5
    // If cell value is greater than C18, apply green color
    int idx = worksheet.getConditionalFormattings().add();
    FormatConditionCollection fcc = worksheet.getConditionalFormattings()
            .get(idx);

    // Add area C5
    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));

    // Apply CellValue Conditional Formatting on cells C5, F5 and I5
    // If cell value is lesser or equal than C18, apply red color
    idx = worksheet.getConditionalFormattings().add();
    fcc = worksheet.getConditionalFormattings().get(idx);

    // Add area C5
    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));

    // Apply CellValue Conditional Formatting on cells C5, F5 and I5
    // If cell value is greater than C18, apply green color
    idx = worksheet.getConditionalFormattings().add();
    fcc = worksheet.getConditionalFormattings().get(idx);


    // Add area F5
    ca = CellArea.createCellArea("F5", "F5");
    fcc.addArea(ca);

    // Add area I5
    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));

    // Apply CellValue Conditional Formatting on cells C5, F5 and I5
    // If cell value is lesser or equal than C18, apply red color
    idx = worksheet.getConditionalFormattings().add();
    fcc = worksheet.getConditionalFormattings().get(idx);

    // Add area F5
    ca = CellArea.createCellArea("F5", "F5");
    fcc.addArea(ca);

    // Add area I5
    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));

    // Apply Top10 conditional formatting on range C17:AA17
    // We need to show Top 5 products
    idx = worksheet.getConditionalFormattings().add();
    fcc = worksheet.getConditionalFormattings().get(idx);

    // Add area C17:AA17
    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);

    // Apply Top10 conditional formatting on range C17:AA17
    // We need to show Bottom 5 products
    idx = worksheet.getConditionalFormattings().add();
    fcc = worksheet.getConditionalFormattings().get(idx);

    // Add area C17:AA17
    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 {

            //Instantiate a new Workbook.
            Workbook workbook = new Workbook();

            // Set workbook default style
            Style style = workbook.getDefaultStyle();
            style.getFont().setName("Calibri");
            style.getFont().setSize(11);
            workbook.setDefaultStyle(style);

            //Get Collection of Worksheets
            WorksheetCollection worksheets = workbook.getWorksheets();

            //Accessing the first worksheet in the Excel file
            Worksheet sheet = worksheets.get(0);

            //Getting Cells collection
            Cells cells = sheet.getCells();

            //Populate Data
            PopulateData(cells);

            //Create Dashboard Design
            createDashboardDesign(workbook,cells);

            //Calculate all the formulas
            workbook.calculateFormula();

            //Apply Conditional formatting
            ApplyConditionalFormattings(sheet);

            //Set Autofit columns
            sheet.autoFitColumns();

            //Set Worksheet Name
            sheet.setName("Sales");

            //Save the excel file.
            workbook.save("c:\\data\\Sales.xls");
        }
        catch(Exception exp)
        {

           System.out.print(exp.getMessage());
        }
    }

    public static void createDashboardDesign(Workbook workbook, Cells cells)
    {
        //Dashboard Title
        cells.get("B2").setValue("Dashboard");

        //Range in Dashboard
        Range resultRange = cells.createRange("B2", "K8");
        resultRange.setOutlineBorders(CellBorderType.MEDIUM,Color.getBlack());

        //Range for Sales Details
        Range detailRange = cells.createRange("B11", "AA19");

        //Setting Titles
        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");

        //This Week’s Sales Formula
        cells.get("C5").setFormula("=SUM(C17:AA17)");

        //Settings Style for titles
        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");

        //Setting the Style of Sales Data Row
        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)
    {
        //Sales Data Parameters
        double LastWeekValue = 8000.00;
        double TargetValue = 9000.00;
        double ProjectionValue = 7500.00;


        //Sales Data
        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};

        //Import Sales Data to Worksheet
        cells.importArray(ProductNames,13,2,false);
        cells.importArray(Price,14,2,false);
        cells.importArray(Unit,15,2,false);

        //Create formula for first Subtotal field
        cells.get("C17").setFormula("=C15*C16 ");

        //Apply the formula in the to all subtotal fields
        for (int i = 3; i <= 26; i++)
        {
            cells.get(16, i).copy(cells.get(16, 2));

        }

        //Set Dashboard Values
        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)
    {
        //Create a Range
        Range range1 = cells.createRange(range);

        //Merge Cells in Range
        range1.merge();

        //Set Border for range
        if(border)
        {
            range1.setOutlineBorders(CellBorderType.MEDIUM,Color.getBlack());
        }
    }

    public static void setStyleForCells(Cells cells, boolean fontBold, int size,String cell)
    {
        //Create a Style object to fetch the Style of C6 Cell.
        Style style = cells.get(cell).getStyle();

        //Create a Font object
        Font font = style.getFont();

        //Set the name.
        font.setName("Calibri");

        //Set the font size.
        font.setSize(size);

        //Bold the text
        font.setBold(fontBold);

        //Apply the Style to Cell.
        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");

        //Create style flag, we only want to set number format
        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);

        //Create your desired range and apply style to it
        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)
         {
        // Apply CellValue Conditional Formatting on cells C5, F5 and I5
        // If cell value is greater than C18, apply green color
        int idx = worksheet.getConditionalFormattings().add();
        FormatConditionCollection fcc = worksheet.getConditionalFormattings()
                .get(idx);

        // Add area C5
        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));

        // Apply CellValue Conditional Formatting on cells C5, F5 and I5
        // If cell value is lesser or equal than C18, apply red color
        idx = worksheet.getConditionalFormattings().add();
        fcc = worksheet.getConditionalFormattings().get(idx);

        // Add area C5
        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));

        // Apply CellValue Conditional Formatting on cells C5, F5 and I5
        // If cell value is greater than C18, apply green color
        idx = worksheet.getConditionalFormattings().add();
        fcc = worksheet.getConditionalFormattings().get(idx);


        // Add area F5
        ca = CellArea.createCellArea("F5", "F5");
        fcc.addArea(ca);

        // Add area I5
        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));

        // Apply CellValue Conditional Formatting on cells C5, F5 and I5
        // If cell value is lesser or equal than C18, apply red color
        idx = worksheet.getConditionalFormattings().add();
        fcc = worksheet.getConditionalFormattings().get(idx);

        // Add area F5
        ca = CellArea.createCellArea("F5", "F5");
        fcc.addArea(ca);

        // Add area I5
        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));

        // Apply Top10 conditional formatting on range C17:AA17
        // We need to show Top 5 products
        idx = worksheet.getConditionalFormattings().add();
        fcc = worksheet.getConditionalFormattings().get(idx);

        // Add area C17:AA17
        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);

        // Apply Top10 conditional formatting on range C17:AA17
        // We need to show Bottom 5 products
        idx = worksheet.getConditionalFormattings().add();
        fcc = worksheet.getConditionalFormattings().get(idx);

        // Add area C17:AA17
        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.