News Category

Conditional Formatting

Conditional Formatting (6)

Java: Add Data Bars in Excel

2021-10-27 08:15:51 Written by support iceblue

Data bars are a type of conditional formatting Microsoft Excel offers for visualizing the values in Excel cells. They can help you compare the values quickly because a cell with a longer bar represents a larger value, while a cell with a shorter bar represents a smaller value. This article will introduce how to add data bars in a range of cells using Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Add Data Bars in Excel

The following are steps to add data bars in a range of Excel cells through conditional formatting:

  • Create a Workbook instance.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get the worksheets collection using Workbook.getWorksheets() method, and then get the first worksheet using WorksheetsCollection.get() method.
  • Get a specific cell range using Worksheet.getCellRange() method.
  • Add a new conditional formatting to the cell range using ConditionalFormats. addCondition(), and then set the type of the new conditional formatting to DataBar using ConditionalFormatWrapper.setFormatType() method.
  • Set the color of the data bar using DataBar.setBarColor() method.
  • Save the document to another file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;
import com.spire.xls.core.*;
import java.awt.*;

public class applyDataBars {
    public static void main(String[] args)  {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\test.xlsx");

        //Get the first worksheet.
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Get the specific cell range
        CellRange range = sheet.getCellRange("B2:B13");

        //Add the conditional formatting of data bars in the cell range
        IConditionalFormat format = range.getConditionalFormats().addCondition();
        format.setFormatType( ConditionalFormatType.DataBar);

        //Set color for the data bars
        format.getDataBar().setBarColor( Color.GREEN);

        //Save to file
        workbook.saveToFile("ApplyDataBars.xlsx", ExcelVersion.Version2013);
    }
}

Java: Add Data Bars in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

This article will demonstrate how to add the traffic lights icons in Java applications by using Spire.XLS for Java.

import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class setTrafficLightsIcons {
    public static void main(String[] args) {
        //Create a workbook
        Workbook workbook = new Workbook();

        //Add a worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add some data to the cell range and set the format for them
        sheet.getCellRange("A1").setText("Traffic Lights");
        sheet.getCellRange("A2").setNumberValue(0.95);
        sheet.getCellRange("A2").setNumberFormat("0%");
        sheet.getCellRange("A3").setNumberValue(0.5);
        sheet.getCellRange("A3").setNumberFormat("0%");
        sheet.getCellRange("A4").setNumberValue(0.1);
        sheet.getCellRange("A4").setNumberFormat("0%");
        sheet.getCellRange("A5").setNumberValue(0.9);
        sheet.getCellRange("A5").setNumberFormat("0%");
        sheet.getCellRange("A6").setNumberValue(0.7);
        sheet.getCellRange("A6").setNumberFormat("0%");
        sheet.getCellRange("A7").setNumberValue(0.6);
        sheet.getCellRange("A7").setNumberFormat("0%");

        //Set the height of row and width of column for Excel cell range
        sheet.getAllocatedRange().setRowHeight(20);
        sheet.getAllocatedRange().setColumnWidth(25);

        //Add a conditional formatting
        XlsConditionalFormats conditional = sheet.getConditionalFormats().add();
        conditional.addRange(sheet.getAllocatedRange());
        IConditionalFormat format1 = conditional.addCondition();

        //Add a conditional formatting of cell range and set its type to CellValue
        format1.setFormatType(ConditionalFormatType.CellValue);
        format1.setFirstFormula("300");
        format1.setOperator(ComparisonOperatorType.Less);
        format1.setFontColor(Color.black);
        format1.setBackColor(Color.lightGray);

        //Add a conditional formatting of cell range and set its type to IconSet
        conditional.addRange(sheet.getAllocatedRange());
        IConditionalFormat format = conditional.addCondition();
        format.setFormatType(ConditionalFormatType.IconSet);
        format.getIconSet().setIconSetType(IconSetType.ThreeTrafficLights1);

        //Save to file
        String result = "output/setTrafficLightsIcons_result.xlsx";
        workbook.saveToFile(result, ExcelVersion.Version2013);
    }
}

Effective screenshot of traffic lights icons on Excel worksheet:

Java add the traffic lights icons to Excel

If you have a lot of data in Excel, finding the special values can be challenging. In this situation, you can use the conditional formatting to automatically highlight the cells that contain the value that meets certain criteria. This article introduces how to highlight values that are above or below the average using conditional formatting in Java, using Spire.XLS for Java.

Install Spire.XLS for Java

First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Highlight Values Above or Below Average Excel in Java

Below are the steps to highlight values above or below average in Excel using Spire.XLS for Java.

  • Create a Workbook object.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get a specific worksheet from the workbook using Workbook.getWorksheets.get(index) method.
  • Add a conditional formatting to the worksheet using Worksheet.getConditionalFormats().add() method and return an object of XlsConditionalFormats class.
  • Set the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
  • Add an average condition using XlsConditionalFormats.addAverageCondition() method, specify the average type to above and change the background color of the cells that meet the condition to yellow.
  • Add another average condition to change the background color of the cells that contain the value below average to light gray.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.AverageType;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class HighlightValuesAboveAndBelowAverage {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add a conditional format to the worksheet
        XlsConditionalFormats format = sheet.getConditionalFormats().add();

        //Set the range where the conditional format will be applied
        format.addRange(sheet.getRange().get("F2:F14"));

        //Add a condition to highlight values above average with yellow
        IConditionalFormat condition1 = format.addAverageCondition(AverageType.Above);
        condition1.setBackColor(Color.yellow);

        //Add a condition to highlight values below average with light gray
        IConditionalFormat condition2 = format.addAverageCondition(AverageType.Below);
        condition2.setBackColor(Color.lightGray);

        //Get the count of values below average
        sheet.getRange().get("F17").setFormula("=COUNTIF(F2:F14,\"<\"&AVERAGE(F2:F14))");

        //Get the count of values above average
        sheet.getRange().get("F18").setFormula("=COUNTIF(F2:F14,\">\"&AVERAGE(F2:F14))");

        //Save the workbook to an Excel file
        workbook.saveToFile("output/HighlightValues.xlsx", ExcelVersion.Version2016);
    }
}

Java: Highlight Values Above or Below Average in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

This article demonstrates how to highlight the highest and lowest value in a cell rang through conditional formatting. You can also highlight the top 5 or bottom 5 values by passing 5 to setRank() method in the code snippet below.

import com.spire.xls.*;

import java.awt.*;

public class HighlightTopBottom {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load the sample Excel file
        workbook.loadFromFile("G:\\360MoveData\\Users\\Administrator\\Desktop\\sales report.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Apply conditional formatting to range "B2:E5" to highlight the highest value
        ConditionalFormatWrapper format1 = sheet.getCellRange("B2:E5").getConditionalFormats().addCondition();
        format1.setFormatType(ConditionalFormatType.TopBottom);
        format1.getTopBottom().setType(TopBottomType.Top);
        format1.getTopBottom().setRank(1);
        format1.setBackColor(Color.red);

        //Apply conditional formatting to range "B2:E5" to highlight the lowest value
        ConditionalFormatWrapper format2 = sheet.getCellRange("B2:E5").getConditionalFormats().addCondition();
        format2.setFormatType(ConditionalFormatType.TopBottom);
        format2.getTopBottom().setType(TopBottomType.Bottom);
        format2.getTopBottom().setRank(1);
        format2.setBackColor(Color.yellow);

        //Save the document
        workbook.saveToFile("output/HighestLowestValue.xlsx", ExcelVersion.Version2016);
    }
}

Highlight Highest and Lowest Value in Excel in Java

This article demonstrates how to highlight the duplicate and unique values in a selected range through conditional formatting using Spire.XLS for Java.

import com.spire.xls.*;

import java.awt.*;

public class HighlightDuplicates {
    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load a sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Use conditional formatting to highlight duplicate values in the range "A2:A11" with red 
        ConditionalFormatWrapper format1 = sheet.getCellRange("A2:A11").getConditionalFormats().addCondition();
        format1.setFormatType(ConditionalFormatType.DuplicateValues);
        format1.setBackColor(Color.red);
        
        //Use conditional formatting to highlight unique values in the range "A2:A11" with yellow
        ConditionalFormatWrapper format2 = sheet.getCellRange("A2:A11").getConditionalFormats().addCondition();
        format2.setFormatType(ConditionalFormatType.UniqueValues);
        format2.setBackColor(Color.yellow);

        //Save the document
        workbook.saveToFile("HighlightDuplicates.xlsx", ExcelVersion.Version2016);
    }
}

Highlight Dulicate and Unique Values in Excel in Java

Applying different background colors to alternate rows of Excel can improve the readability of data and make the spreadsheet appear more professional. There many ways to set row color, among which using conditional formatting is a good choice. It can not only set colors in batches, but also define more flexible rules, such as alternating every three rows. In this article, you will learn how to alternate row color in Excel using conditional formatting using Spire.XLS for Java.

Install Spire.XLS for Java

First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.3.2</version>
    </dependency>
</dependencies>
    

Apply Color to Alternate Rows in Excel Using Conditional Formatting

The following are the steps to add color to alternative rows in Excel using Spire.XLS for Java.

  • Create a Workbook object.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get a specific worksheet from the workbook using Workbook.getWorsheets().get(index) method.
  • Add a conditional formatting to the worksheet using Worksheet.getConditionalFormats().add() method and return an object of XlsConditionalFormats class.
  • Set the cell range where the conditional formatting will be applied using XlsConditionalFormats.addRange() method.
  • Add a condition using XlsConditionalFormats.addCondition() method, then set the conditional formula and the cell color of even rows.
  • Add another condition to change the format of the cells of odd rows.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ConditionalFormatType;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

public class AlternateRowColors {
    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add a conditional format to the worksheet
        XlsConditionalFormats format = sheet.getConditionalFormats().add();

        //Set the range where the conditional format will be applied
        format.addRange(sheet.getRange().get(2,1,sheet.getLastRow(),sheet.getLastColumn()));

        //Add a condition to change the format of the cells based on formula
        IConditionalFormat condition1 = format.addCondition();
        condition1.setFirstFormula("=MOD(ROW(),2)=0");
        condition1.setFormatType(ConditionalFormatType.Formula);
        condition1.setBackColor(Color.YELLOW);

        //Add another condition to change the format of the cells based on formula
        IConditionalFormat condition2 = format.addCondition();
        condition2.setFirstFormula("=MOD(ROW(),2)=1");
        condition2.setFormatType(ConditionalFormatType.Formula);
        condition2.setBackColor(new Color(32,178, 170));

        //Save the workbook to an Excel file
        workbook.saveToFile("output/AlternateRowColors.xlsx", ExcelVersion.Version2016);
    }
}

Java: Apply Color to Alternate Rows in Excel Using Conditional Formatting

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.