Apply Data Bars in Excel using Java

In Excel, Data Bars is the combination of Data and a Bar Chart inside the cell, which shows the percentage of selected data or the position of the selected value on the bars inside the cell. This article will show you how to programmatically apply Data Bars in a specific range of cells in Excel using Java codes.

DEPENDENCY

In order to finish the operation above, you need to use a third-party library called Free Spire.XLS for Java. First of all, you need to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be gotten from this link, or you can easily import the JAR file by adding the following code to the 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.free</artifactId>
<version>3.9.1</version>
</dependency>
</dependencies>

USING THE CODE

Actually Data Bars is a type of Conditional Formatting offered by Microsoft Excel. Free Spire.XLS for Java provides ConditionalFormats.addCondition() method to add a new conditional formatting and ConditionalFormatWrapper.setFormatType() method to set the type of the new conditional formatting as DataBar. Here are detailed steps to follow.

l  Create a Workbook instance.

l  Load a sample Excel file using Workbook.loadFromFile() method.

l  Get a specific worksheet in Excel using Workbook.getWorksheets().get() method.

l  Get a specific cell range using Worksheet.getCellRange() method.

l  Add a new conditional formatting to the cell range using ConditionalFormats. addCondition(), and then set the type of the new conditional formatting as DataBar using ConditionalFormatWrapper.setFormatType() method.

l  Set the color of the Data Bar using DataBar.setBarColor() method.

l  Save the document using Workbook.saveToFile() method.

import com.spire.xls.*;
import com.spire.xls.core.*;
import java.awt.*;

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

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

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

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

        //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.PINK);

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