Package org.apache.poi.ss.util

Class CellReference

  • All Implemented Interfaces:
    GenericRecord
    public class CellReference
extends 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.

    • Constructor Detail

      • CellReference

        public CellReference​(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:
        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​(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 String getSheetName()
        Returns:
        possibly null if this is a 2D reference. Special characters are not escaped or delimited

      • isPartAbsolute

        public static boolean isPartAbsolute​(String part)

      • convertColStringToIndex

        public static int convertColStringToIndex​(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​(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​(String colStr,
                                                 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.

      • isRowWithinRange

        public static boolean isRowWithinRange​(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:
        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 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 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 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 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 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()

      • getCellRefParts

        public 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​(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 Object

      • hashCode

        public int hashCode()
        Overrides:
        hashCode in class Object