Class CellReference

java.lang.Object
org.apache.poi.ss.util.CellReference
Direct Known Subclasses:
CellReference

public class CellReference extends Object

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 Details

    • 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.
    • 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 Details

    • 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:

      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:
      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(String colStr, SpreadsheetVersion ssVersion)
    • 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:

      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.
    • toString

      public String toString()
      Overrides:
      toString in class Object
    • 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