Package org.apache.poi.ss.usermodel

Interface Name

All Known Implementing Classes:
HSSFName, XSSFName
public interface Name
Represents a defined name for a range of cells.

A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant or a formula.

Examples: 

  Sheet sheet = workbook.createSheet("Loan Calculator");
  Name name;

  name = workbook.createName();
  name.setNameName("Interest_Rate");
  name.setRefersToFormula("'Loan Calculator'!$E$5");

  name = wb.createName();
  name.setNameName("Loan_Amount");
  name.setRefersToFormula("'Loan Calculator'!$E$4");

  name = wb.createName();
  name.setNameName("Number_of_Payments");
  name.setRefersToFormula("'Loan Calculator'!$E$10");

  name = wb.createName();
  name.setNameName("Monthly_Payment");
  name.setRefersToFormula("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)");

  name = wb.createName();
  name.setNameName("Values_Entered");
  name.setRefersToFormula("IF(Loan_Amount*Interest_Rate > 0,1,0)");

  • Method Summary

    Modifier and Type
    Method
    Description
    String
    getComment()
    Returns the comment the user provided when the name was created.
    String
    getNameName()
    Gets the name of the named range
    String
    getRefersToFormula()
    Returns the formula that the name is defined to refer to.
    int
    getSheetIndex()
    Returns the sheet index this name applies to.
    String
    getSheetName()
    Get the sheets name which this named range is referenced to
    boolean
    isDeleted()
    Checks if this name points to a cell that no longer exists
    boolean
    isFunctionName()
    Checks if this name is a function name
    boolean
    isHidden()
    Checks if this name is hidden, eg one of the built-in Excel internal names
    void
    setComment(String comment)
    Sets the comment the user provided when the name was created.
    void
    setFunction(boolean value)
    Indicates that the defined name refers to a user-defined function.
    void
    setNameName(String name)
    Sets the name of the named range
    void
    setRefersToFormula(String formulaText)
    Sets the formula that the name is defined to refer to.
    void
    setSheetIndex(int sheetId)
    Tell Excel that this name applies to the worksheet with the specified index instead of the entire workbook.

  • Method Details

    • getSheetName

      String getSheetName()
      Get the sheets name which this named range is referenced to
      Returns:
      sheet name, which this named range referred to

    • getNameName

      String getNameName()
      Gets the name of the named range
      Returns:
      named range name

    • setNameName

      void setNameName(String name)
      Sets the name of the named range

      The following is a list of syntax rules that you need to be aware of when you create and edit names.

      • Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
      • Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.
      • Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
      • Name length A name can contain up to 255 characters.
      • Case sensitivity Names can contain uppercase and lowercase letters.

      A name must always be unique within its scope. POI prevents you from defining a name that is not unique within its scope. However you can use the same name in different scopes. Example: 

      
 //by default names are workbook-global
 Name name;
 name = workbook.createName();
 name.setNameName("sales_08");

 name = workbook.createName();
 name.setNameName("sales_08"); //will throw an exception: "The workbook already contains this name (case-insensitive)"

 //create sheet-level name
 name = workbook.createName();
 name.setSheetIndex(0); //the scope of the name is the first sheet
 name.setNameName("sales_08");  //ok

 name = workbook.createName();
 name.setSheetIndex(0);
 name.setNameName("sales_08");  //will throw an exception: "The sheet already contains this name (case-insensitive)"
      Parameters:
      name - named range name to set
      Throws:
      IllegalArgumentException - if the name is invalid or the already exists within its scope (case-insensitive)

    • getRefersToFormula

      String getRefersToFormula()
      Returns the formula that the name is defined to refer to.
      Returns:
      the reference for this name, null if it has not been set yet. Never empty string
      See Also:

    • setRefersToFormula

      void setRefersToFormula(String formulaText)
      Sets the formula that the name is defined to refer to. The following are representative examples:
      • 'My Sheet'!$A$3
      • 8.3
      • HR!$A$1:$Z$345
      • SUM(Sheet1!A1,Sheet2!B2)
      • -PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)
      Note: Using relative values like 'A1:B1' can lead to unexpected moving of the cell that the name points to when working with the workbook in Microsoft Excel, usually using absolute references like '$A$1:$B$1' avoids this, see also https://superuser.com/a/1031047/126954
      Parameters:
      formulaText - the reference for this name
      Throws:
      IllegalArgumentException - if the specified formulaText is unparsable

    • isFunctionName

      boolean isFunctionName()
      Checks if this name is a function name
      Returns:
      true if this name is a function name

    • isDeleted

      boolean isDeleted()
      Checks if this name points to a cell that no longer exists
      Returns:
      true if the name refers to a deleted cell, false otherwise

    • isHidden

      boolean isHidden()
      Checks if this name is hidden, eg one of the built-in Excel internal names
      Returns:
      true if the name is a hidden name, false otherwise

    • setSheetIndex

      void setSheetIndex(int sheetId)
      Tell Excel that this name applies to the worksheet with the specified index instead of the entire workbook.
      Parameters:
      sheetId - the sheet index this name applies to, -1 unsets this property making the name workbook-global
      Throws:
      IllegalArgumentException - if the sheet index is invalid.

    • getSheetIndex

      int getSheetIndex()
      Returns the sheet index this name applies to.
      Returns:
      the sheet index this name applies to, -1 if this name applies to the entire workbook

    • getComment

      String getComment()
      Returns the comment the user provided when the name was created.
      Returns:
      the user comment for this named range

    • setComment

      void setComment(String comment)
      Sets the comment the user provided when the name was created.
      Parameters:
      comment - the user comment for this named range

    • setFunction

      void setFunction(boolean value)
      Indicates that the defined name refers to a user-defined function. This attribute is used when there is an add-in or other code project associated with the file.
      Parameters:
      value - true indicates the name refers to a function.