Add Data Bars in Excel using Java

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 Free Spire.XLS for Java.

Import JAR Dependency (2 Method)

1# Download the free API and unzip it, then add the Spire.Xls.jar file to your project as dependency.
2# You can also add the jar dependency to maven project by adding the following configurations to the pom.xml.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>http://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>

Sample Code

Free Spire.XLS for Java offers Worksheet.getCellRange() method to get a specific cell range, and you can add a new conditional formatting to the cell range using ConditionalFormats. addCondition() method. To add data bars in the selected cell range, you just need to set the type of the new conditional formatting to DataBar using ConditionalFormatWrapper.setFormatType() method.

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 condotional format of data bar to the cell range
        IConditionalFormat format = range.getConditionalFormats().addCondition();
        format.setFormatType( ConditionalFormatType.DataBar);

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

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