Named ranges in Excel are valuable tools that empower you to assign meaningful names to specific cells or ranges within your spreadsheets. Instead of relying on traditional cell references like A1:B10, named ranges allow you to reference data by their logical names, making your formulas more intelligible and easier to understand and maintain. This article will demonstrate how to create, edit or delete named ranges in Excel in Java using Spire.XLS for Java.

• Create a Named Range in Excel in Java
• Edit an Existing Named Range in Excel in Java
• Delete a Named Range from Excel in 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.4.1</version>
    </dependency>
</dependencies>
    

Create a Named Range in Excel in Java

You can use the Workbook.getNameRanges().add(String name) method provided by Spire.XLS for Java to add a named range to an Excel workbook. Once the named range is added, you can define the cell or range of cells it refers to using the INamedRange.setRefersToRange(IXLSRange range) method. The detailed steps are as follows:

• Initialize an instance of the Workbook class.
• Load an Excel workbook using the Workbook.loadFromFile() method.
• Add a named range to the workbook using the Workbook.getNameRanges().add(String name) method.
• Get a specific worksheet in the workbook using the Workbook.getWorksheets().get(int index) method.
• Set the cell range that the named range refers to using the INamedRange.setRefersToRange(IXLSRange range) method.
• Save the result file using the Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.INamedRange;

public class CreateNamedRange {
    public static void main(String[] args) {
        //Initialize an instance of the Workbook class
        Workbook workbook = new Workbook();
        //Load an Excel workbook
        workbook.loadFromFile("Sample.xlsx");

        //Add a named range to the workbook
        INamedRange namedRange = workbook.getNameRanges().add("Amount");

        //Get a specific worksheet in the workbook
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Set the cell range that the named range references
        namedRange.setRefersToRange(sheet.getCellRange("D2:D5"));

        //Save the result file to a specific location
        String result = "CreateNamedRange.xlsx";
        workbook.saveToFile(result, ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Edit an Existing Named Range in Excel in Java

After you've created a named range, you may want to modify its name or adjust the cells it refers to. The following are the detailed steps:

• Initialize an instance of the Workbook class.
• Load an Excel workbook using the Workbook.loadFromFile() method.
• Get a specific named range in the workbook using the Workbook.getNameRanges().get(int index) method.
• Modify the name of the named range using the INamedRange.setName(String name) method.
• Modify the cells that the named range refers to using the INamedRange.setRefersToRange(IXLSRange range) method.
• Save the result file using the Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.core.INamedRange;

public class ModifyNamedRange {
    public static void main(String[] args) {
        //Initialize an instance of the Workbook class
        Workbook workbook = new Workbook();
        //Load an Excel workbook
        workbook.loadFromFile("CreateNamedRange.xlsx");

        //Get a specific named range in the workbook
        INamedRange namedRange = workbook.getNameRanges().get(0);

        //Change the name of the named range
        namedRange.setName("MonitorAmount");

        //Set the cell range that the named range references
        namedRange.setRefersToRange(workbook.getWorksheets().get(0).getCellRange("D2"));

        //Save the result file to a specific location
        String result = "ModifyNamedRange.xlsx";
        workbook.saveToFile(result, ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Delete a Named Range from Excel in Java

If you have made significant changes to the structure or layout of your spreadsheet, it might be necessary to delete a named range that is no longer relevant or accurate. The detailed steps are as follows:

• Initialize an instance of the Workbook class.
• Load an Excel workbook using the Workbook.loadFromFile() method.
• Remove a specific named range by its index or name using the Workbook.getNameRanges().removeAt(int index) or Workbook.getNameRanges().remove(string name) method.
• Save the result file using the Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;

public class DeleteNamedRange {
    public static void main(String[] args) {
        //Initialize an instance of the Workbook class
        Workbook workbook = new Workbook();
        //Load an Excel workbook
        workbook.loadFromFile("CreateNamedRange.xlsx");

        //Remove a specific named range by its index
        workbook.getNameRanges().removeAt(0);

        //Remove a specific named range by its name
        //workbook.getNameRanges().remove("Amount");

        //Save the result file to a specific location
        String result = "RemoveNamedRange.xlsx";
        workbook.saveToFile(result, ExcelVersion.Version2013);
        workbook.dispose();
    }
}

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 use Spire.XLS for Java to remove the formulas but keep the values on the Excel worksheet.

Firstly, view the original Excel:

import com.spire.xls.*;

public class Test {
    public static void main(String[] args) throws Exception {

        String inputFile = "Sample.xlsx";
        String outputFile="output/removeFormulasButKeepValues_result.xlsx";

        //Create a workbook.
        Workbook workbook = new Workbook();
        //Load the file from disk.
        workbook.loadFromFile(inputFile);
        //Loop through worksheets.
        for (Worksheet sheet : (Iterable) workbook.getWorksheets())
        {
            //Loop through cells.
            for (CellRange cell : (Iterable) sheet.getRange())
            {
                //If the cell contains formula, get the formula value, clear cell content, and then fill the formula value into the cell.
                if (cell.hasFormula())
                {
                    Object value = cell.getFormulaValue();
                    cell.clear(ExcelClearOptions.ClearContent);
                    cell.setValue(value.toString());
                }
            }
        }
        //Save to file
        workbook.saveToFile(outputFile, ExcelVersion.Version2013);
    }
}

Output: