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 enumUsed 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 booleancellReferenceIsWithinRange(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.NameTypeclassifyCellReference(String str, SpreadsheetVersion ssVersion) Classifies an identifier as either a simple (2D) cell reference or a named range namestatic inttakes in a column reference portion of a CellRef and converts it from ALPHA-26 number format to 0-based base 10.static StringconvertNumToColString(int col) Takes in a 0-based base-10 column and returns a ALPHA-26 representation.booleanChecks 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.shortgetCol()intgetRow()inthashCode()booleanstatic booleanisColumnWithinRange(String colStr, SpreadsheetVersion ssVersion) static booleanisPartAbsolute(String part) booleanstatic booleanisRowWithinRange(int rowNum, SpreadsheetVersion ssVersion) Determines whetherrowis a valid row number for a given SpreadsheetVersion.static booleanisRowWithinRange(String rowStr, SpreadsheetVersion ssVersion) Determines whetherrowStris 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
nullif 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:
trueif the row and col parameters are within range of a BIFF8 spreadsheet.
-
isColumnWithinRange
-
isRowWithinRange
Determines whetherrowStris 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 whetherrowis 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()
-