Create, edit and delete excel named range in Java

Time:2020-3-31

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, the existing named range in Excel can be renamed, the cell reference range can be reset, hidden or displayed (note that the named range is set hereNameHide or display, not named range cells. When setting the name to hide, you can protect the referenced data source) 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)

JarGet and import:adoptDownload 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 named area

   1.1 Global named region

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 an 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 the region name
        Namedrange.setreferstorange (sheet. Getcellrange ("C2")); // modify cell reference range
        Namedrange. Setvisible (false); // hide named area name
        //WB. Getnameranges(). Remove ("range2"); // delete the named range

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

Editing effect:

 

(end of this paper)