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);
}
}
No comments:
Post a Comment