Excel named range, that is, to name the specified cell range so as to reference the cell range. For example, in formula application, you can reference the specified named range for formula operation. When you create a named range, you can create it for the whole workbook, that is, workbook.getNameRanges().add(), which is valid for the whole workbook when it is referenced, or for the specified worksheet, that is, sheet.getNames().add(), which is valid only for the worksheet where the named range is referenced. In addition, you can rename the existing named range in Excel, reset the cell reference range, set hide or display (note that the name of the named range is set here to hide or display, rather than the named range cell, which can protect the reference data source when the name is set to hide) and delete. The following will introduce the operation method through the code example.

Program running environment: Java, IDEA, jdk1.8.0, no need to install Microsoft Excel

Tools: free flame.xls for Java (free version)

Jar access and import: through Download official website , and unzip the jar file under the lib folder into the java program.

Refer to the following jar import effect:

Java code example

1. Create a named area

1.1 global named area

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

public class NamedRange1 {
    public static void main(String[] args) {
        //Create instance, load test document
        Workbook wb = new Workbook();
        wb.loadFromFile("test.xlsx");

        //Get sheet 1
        Worksheet sheet1 = wb.getWorksheets().get(0);
        //Create global naming
        INamedRange namedRange1 = wb.getNameRanges().add("Range1");
        namedRange1.setRefersToRange(sheet1.getCellRange("C2:C3"));
        INamedRange namedRange2 = wb.getNameRanges().add("Range2");
        namedRange2.setRefersToRange(sheet1.getCellRange("C4:C5"));

        //Get sheet 2, reference named range in cell formula
        Worksheet sheet2 = wb.getWorksheets().get(1);
        sheet2.getCellRange("B1").setFormula("=SUM(Range1,Range2)");

       //Save file
        wb.saveToFile("NamadRange1.xlsx", ExcelVersion.Version2013);
    }
}

Global named region creation effect:

1.2 local named area

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

public class NamedRange2 {
    public static void main(String[] args) {
        //Create an instance and load the test document
        Workbook wb = new Workbook();
        wb.loadFromFile("test.xlsx");

        //Get sheet 1
        Worksheet sheet = wb.getWorksheets().get(0);

        //Create a local named area
        INamedRange namedRange1 = sheet.getNames().add("Range1");
        namedRange1.setRefersToRange(sheet.getCellRange("C2:C3"));
        INamedRange namedRange2 = sheet.getNames().add("Range2");
        namedRange2.setRefersToRange(sheet.getCellRange("C4:C5"));

        //Referencing named areas in formulas
        sheet.getCellRange("C6").setFormula("=SUM(Range1,Range2)");

        //Save file
        wb.saveToFile("NamedRange2.xlsx", ExcelVersion.Version2013);
    }
}

Local named area creation effect:

2. Edit the existing named area

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

public class ModifyNamedRange {
    public static void main(String[] args) {
        //Create instance, load test document
        Workbook wb = new Workbook();
        wb.loadFromFile("NamedRange1.xlsx");

        //Get worksheet
        Worksheet sheet = wb.getWorksheets().get(0);
        //Get global named area
        INamedRange namedRange = wb.getNameRanges().getByName("Range1");
        //Get local named area (perform relevant operations on local named area)
        //INamedRange namedRange1 = sheet.getNames().getByName("Range1");
        namedRange.setName("NewRange");//Modify area name
        namedRange.setRefersToRange(sheet.getCellRange("C2"));//Modify cell reference range
        namedRange.setVisible(false);//Hide named area names
        //wb.getNameRanges().remove("Range2"); / / delete the named range

        //Save document
        wb.saveToFile("ModifyNamedRange.xlsx");
    }
}

Editing effect:

(end of this paper)