Class CellReference
- Direct Known Subclasses:
CellReference
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 ClassesModifier and TypeClassDescriptionstatic enum
Used to classify identifiers found in formulas as cell references or not. -
Constructor Summary
ConstructorsConstructorDescriptionCellReference
(int pRow, int pCol) CellReference
(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) CellReference
(int pRow, short pCol) CellReference
(String cellRef) Create an cell ref from a string representation.CellReference
(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) CellReference
(Cell cell) -
Method Summary
Modifier and TypeMethodDescriptionstatic 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.static CellReference.NameType
classifyCellReference
(String str, SpreadsheetVersion ssVersion) Classifies an identifier as either a simple (2D) cell reference or a named range namestatic int
takes in a column reference portion of a CellRef and converts it from ALPHA-26 number format to 0-based base 10.static String
convertNumToColString
(int col) Takes in a 0-based base-10 column and returns a ALPHA-26 representation.boolean
Checks whether this cell reference is equal to another object.Returns a text representation of this cell reference.String[]
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()
int
getRow()
int
hashCode()
boolean
static boolean
isColumnWithinRange
(String colStr, SpreadsheetVersion ssVersion) static boolean
isPartAbsolute
(String part) boolean
static boolean
isRowWithinRange
(int rowNum, SpreadsheetVersion ssVersion) Determines whetherrow
is a valid row number for a given SpreadsheetVersion.static boolean
isRowWithinRange
(String rowStr, SpreadsheetVersion ssVersion) Determines whetherrowStr
is a valid row number for a given SpreadsheetVersion.toString()
-
Constructor Details
-
CellReference
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
-
CellReference
public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) -
CellReference
-
-
Method Details
-
getRow
public int getRow() -
getCol
public short getCol() -
isRowAbsolute
public boolean isRowAbsolute() -
isColAbsolute
public boolean isColAbsolute() -
getSheetName
- Returns:
- possibly
null
if this is a 2D reference. Special characters are not escaped or delimited
-
isPartAbsolute
-
convertColStringToIndex
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:
POI currently targets BIFF8 (Excel 97-2003), so the following behaviour can be observed for this method:Version File Format Last Column Last Row 97-2003 BIFF8 "IV" (2^8) 65536 (2^14) 2007 BIFF12 "XFD" (2^14) 1048576 (2^20) 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 charactersrowStr
- a string of only digit characters- Returns:
true
if the row and col parameters are within range of a BIFF8 spreadsheet.
-
isColumnWithinRange
-
isRowWithinRange
Determines whetherrowStr
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
Determines whetherrow
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
Takes in a 0-based base-10 column and returns a ALPHA-26 representation. egconvertNumToColString(3)
returns"D"
-
formatAsString
Returns a text representation of this cell reference.Example return values:
Result Comment A1 Cell reference without sheet Sheet1!A1 Standard 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
-
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 useformatAsString()
to properly turn references into strings.- Returns:
- String array of { sheetName, rowString, colString }
-
equals
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. -
hashCode
public int hashCode()
-