Package org.apache.poi.ss.util

Class CellReference

  • All Implemented Interfaces:
    GenericRecord
    public class CellReference
extends java.lang.Object
implements GenericRecord

    Common conversion functions between Excel style A1, C27 style cell references, and POI usermodel style row=0, column=0 style references. Handles sheet-based and sheet-free references as well, eg "Sheet1!A1" and "$B$72"

    Use CellReference when the concept of relative/absolute does apply (such as a cell reference in a formula). Use CellAddress when you want to refer to the location of a cell in a sheet when the concept of relative/absolute does not apply (such as the anchor location of a cell comment). CellReferences have a concept of "sheet", while CellAddresses do not.

    • Nested Class Summary

      Nested Classes 
      Modifier and Type Class Description
      static class  CellReference.NameType
      Used to classify identifiers found in formulas as cell references or not.

    • Constructor Summary

      Constructors 
      Constructor Description
      CellReference​(int pRow, int pCol)  
      CellReference​(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol)  
      CellReference​(int pRow, short pCol)  
      CellReference​(java.lang.String cellRef)
      Create an cell ref from a string representation.
      CellReference​(java.lang.String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol)  
      CellReference​(Cell cell)  

    • Method Summary

      All Methods Static Methods Instance Methods Concrete Methods 
      Modifier and Type Method Description
      static boolean cellReferenceIsWithinRange​(java.lang.String colStr, java.lang.String rowStr, SpreadsheetVersion ssVersion)
      Used to decide whether a name of the form "[A-Z]*[0-9]*" that appears in a formula can be interpreted as a cell reference.
      static CellReference.NameType classifyCellReference​(java.lang.String str, SpreadsheetVersion ssVersion)
      Classifies an identifier as either a simple (2D) cell reference or a named range name
      static int convertColStringToIndex​(java.lang.String ref)
      takes in a column reference portion of a CellRef and converts it from ALPHA-26 number format to 0-based base 10.
      static java.lang.String convertNumToColString​(int col)
      Takes in a 0-based base-10 column and returns a ALPHA-26 representation.
      boolean equals​(java.lang.Object o)
      Checks whether this cell reference is equal to another object.
      java.lang.String formatAsR1C1String()
      Returns a text representation of this cell reference in R1C1 format.
      java.lang.String formatAsR1C1String​(boolean includeSheetName)
      Returns a text representation of this cell reference in R1C1 format and allows to control if the sheetname is included in the reference.
      java.lang.String formatAsString()
      Returns a text representation of this cell reference.
      java.lang.String formatAsString​(boolean includeSheetName)
      Returns a text representation of this cell reference and allows to control if the sheetname is included in the reference.
      java.lang.String[] getCellRefParts()
      Returns the three parts of the cell reference, the Sheet name (or null if none supplied), the 1 based row number, and the A based column letter.
      short getCol()  
      java.util.Map<java.lang.String,​java.util.function.Supplier<?>> getGenericProperties()  
      int getRow()  
      java.lang.String getSheetName()  
      int hashCode()  
      boolean isColAbsolute()  
      static boolean isColumnWithinRange​(java.lang.String colStr, SpreadsheetVersion ssVersion)  
      static boolean isPartAbsolute​(java.lang.String part)  
      boolean isRowAbsolute()  
      static boolean isRowWithinRange​(int rowNum, SpreadsheetVersion ssVersion)
      Determines whether row is a valid row number for a given SpreadsheetVersion.
      static boolean isRowWithinRange​(java.lang.String rowStr, SpreadsheetVersion ssVersion)
      Determines whether rowStr is a valid row number for a given SpreadsheetVersion.
      java.lang.String toString()  

      • Methods inherited from class java.lang.Object

        getClass, notify, notifyAll, wait, wait, wait

    • Constructor Detail

      • CellReference

        public CellReference​(java.lang.String cellRef)
        Create an cell ref from a string representation. Sheet names containing special characters should be delimited and escaped as per normal syntax rules for formulas.
        Throws:
        java.lang.IllegalArgumentException - if cellRef is not valid

      • CellReference

        public CellReference​(int pRow,
                     int pCol)

      • CellReference

        public CellReference​(int pRow,
                     short pCol)

      • CellReference

        public CellReference​(Cell cell)

      • CellReference

        public CellReference​(int pRow,
                     int pCol,
                     boolean pAbsRow,
                     boolean pAbsCol)

      • CellReference

        public CellReference​(java.lang.String pSheetName,
                     int pRow,
                     int pCol,
                     boolean pAbsRow,
                     boolean pAbsCol)

    • Method Detail

      • getRow

        public int getRow()

      • getCol

        public short getCol()

      • isRowAbsolute

        public boolean isRowAbsolute()

      • isColAbsolute

        public boolean isColAbsolute()

      • getSheetName

        public java.lang.String getSheetName()
        Returns:
        possibly null if this is a 2D reference. Special characters are not escaped or delimited

      • isPartAbsolute

        public static boolean isPartAbsolute​(java.lang.String part)

      • convertColStringToIndex

        public static int convertColStringToIndex​(java.lang.String ref)
        takes in a column reference portion of a CellRef and converts it from ALPHA-26 number format to 0-based base 10. 'A' -> 0 'Z' -> 25 'AA' -> 26 'IV' -> 255
        Returns:
        zero based column index

      • classifyCellReference

        public static CellReference.NameType classifyCellReference​(java.lang.String str,
                                                           SpreadsheetVersion ssVersion)
        Classifies an identifier as either a simple (2D) cell reference or a named range name
        Returns:
        one of the values from NameType

      • cellReferenceIsWithinRange

        public static boolean cellReferenceIsWithinRange​(java.lang.String colStr,
                                                 java.lang.String rowStr,
                                                 SpreadsheetVersion ssVersion)
        Used to decide whether a name of the form "[A-Z]*[0-9]*" that appears in a formula can be interpreted as a cell reference. Names of that form can be also used for sheets and/or named ranges, and in those circumstances, the question of whether the potential cell reference is valid (in range) becomes important.

        Note - that the maximum sheet size varies across Excel versions:

        Notable cases.
        Version  File Format   Last Column  Last Row
        97-2003BIFF8"IV" (2^8)65536 (2^14)
        2007BIFF12"XFD" (2^14)1048576 (2^20)
        POI currently targets BIFF8 (Excel 97-2003), so the following behaviour can be observed for this method:
        Notable cases
        Input            Result 
        "A", "1"true
        "a", "111"true
        "A", "65536"true
        "A", "65537"false
        "iv", "1"true
        "IW", "1"false
        "AAA", "1"false
        "a", "111"true
        "Sheet", "1"false
        Parameters:
        colStr - a string of only letter characters
        rowStr - a string of only digit characters
        Returns:
        true if the row and col parameters are within range of a BIFF8 spreadsheet.

      • isColumnWithinRange

        public static boolean isColumnWithinRange​(java.lang.String colStr,
                                          SpreadsheetVersion ssVersion)

      • isRowWithinRange

        public static boolean isRowWithinRange​(java.lang.String rowStr,
                                       SpreadsheetVersion ssVersion)
        Determines whether rowStr is a valid row number for a given SpreadsheetVersion.
        Parameters:
        rowStr - the numeric portion of an A1-style cell reference (1-based index)
        ssVersion - the spreadsheet version
        Throws:
        java.lang.NumberFormatException - if rowStr is not parseable as an integer

      • isRowWithinRange

        public static boolean isRowWithinRange​(int rowNum,
                                       SpreadsheetVersion ssVersion)
        Determines whether row is a valid row number for a given SpreadsheetVersion.
        Parameters:
        rowNum - the row number (0-based index)
        ssVersion - the spreadsheet version
        Since:
        3.17 beta 1

      • convertNumToColString

        public static java.lang.String convertNumToColString​(int col)
        Takes in a 0-based base-10 column and returns a ALPHA-26 representation. eg convertNumToColString(3) returns "D"

      • formatAsString

        public java.lang.String formatAsString()
        Returns a text representation of this cell reference.

        Example return values:

        Example return values
        ResultComment
        A1Cell reference without sheet
        Sheet1!A1Standard sheet name
        'O''Brien''s Sales'!A1' Sheet name with special characters
        Returns:
        the text representation of this cell reference as it would appear in a formula.
        See Also:
        formatAsString(boolean)

      • formatAsR1C1String

        public java.lang.String formatAsR1C1String()
        Returns a text representation of this cell reference in R1C1 format.

        Example return values:

        Example return values
        ResultComment
        R1C1Cell reference without sheet
        Sheet1!R1C1Standard sheet name
        'O''Brien''s Sales'!R1C1' Sheet name with special characters
        Returns:
        the text representation of this cell reference as it would appear in a formula.
        Since:
        POI 5.2.1
        See Also:
        formatAsString(), formatAsR1C1String(boolean)

      • formatAsString

        public java.lang.String formatAsString​(boolean includeSheetName)
        Returns a text representation of this cell reference and allows to control if the sheetname is included in the reference.

        Example return values:

        Example return values
        ResultComment
        A1Cell reference without sheet
        Sheet1!A1Standard sheet name
        'O''Brien''s Sales'!A1' Sheet name with special characters
        Parameters:
        includeSheetName - If true and there is a sheet name set for this cell reference, the reference is prefixed with the sheet name and '!'
        Returns:
        the text representation of this cell reference as it would appear in a formula.
        See Also:
        formatAsString()

      • formatAsR1C1String

        public java.lang.String formatAsR1C1String​(boolean includeSheetName)
        Returns a text representation of this cell reference in R1C1 format and allows to control if the sheetname is included in the reference.

        Example return values:

        Example return values
        ResultComment
        R1C1Cell reference without sheet
        Sheet1!R1C1Standard sheet name
        'O''Brien''s Sales'!R1C1' Sheet name with special characters
        Parameters:
        includeSheetName - If true and there is a sheet name set for this cell reference, the reference is prefixed with the sheet name and '!'
        Returns:
        the text representation of this cell reference as it would appear in a formula.
        Since:
        POI 5.2.1
        See Also:
        formatAsString(boolean), formatAsR1C1String()

      • toString

        public java.lang.String toString()
        Overrides:
        toString in class java.lang.Object

      • getCellRefParts

        public java.lang.String[] getCellRefParts()
        Returns the three parts of the cell reference, the Sheet name (or null if none supplied), the 1 based row number, and the A based column letter. This will not include any markers for absolute references, so use formatAsString() to properly turn references into strings.
        Returns:
        String array of { sheetName, rowString, colString }

      • equals

        public boolean equals​(java.lang.Object o)
        Checks whether this cell reference is equal to another object.

        Two cells references are assumed to be equal if their string representations (formatAsString() are equal.

        Overrides:
        equals in class java.lang.Object

      • hashCode

        public int hashCode()
        Overrides:
        hashCode in class java.lang.Object

      • getGenericProperties

        public java.util.Map<java.lang.String,​java.util.function.Supplier<?>> getGenericProperties()
        Specified by:
        getGenericProperties in interface GenericRecord