Create Named Ranges in Excel in Java

#java #excel #api #free

A named range is a cell or a range of cells that have been given a name. After you have created named ranges in Excel, you can then use these names directly instead of the cell references. This article will share how to programmatically create a named range in an Excel worksheet using Free Spire.XLS for Java.

Installation

Free Spire.XLS for Java is a totally free Excel API for both commercial and personal use. Below are two methods to install it in your Java application.
1# Download the free API and unzip it, then add the Spire.Xls.jar file to your project as dependency.

2# Directly 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>https://repo.e iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>5.1.0</version>
    </dependency>
</dependencies>

Sample Code

Free Spire.XLS for Java offers the Workbook.getNameRanges().add() method to create a named range in Excel and the complete sample code is shown below.

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

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

        //Create a workbook and load the document from disk
        Workbook workbook = new Workbook();
        workbook.loadFromFile("E:\\Files\\input0.xlsx");

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

        //Create a named range
        INamedRange NamedRange = workbook.getNameRanges().add("Costs");

        //Set the range of the named range
        NamedRange.setRefersToRange(sheet.getCellRange("B2:C13"));

        //Save to document
        workbook.saveToFile("NamedRanges.xlsx", ExcelVersion.Version2013);
    }
}