org.apache.poi.ss.util

Class CellReference

java.lang.Object

org.apache.poi.ss.util.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 and Description
static class	CellReference.NameType Used to classify identifiers found in formulas as cell references or not.

Constructor Summary

Constructors

Constructor and Description
CellReference(Cell cell)
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)

Method Summary

Modifier and Type	Method and 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	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

clone, finalize, getClass, notify, notifyAll, wait, wait, wait

Methods inherited from interface org.apache.poi.common.usermodel.GenericRecord

getGenericChildren, getGenericRecordType

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.

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:

Version	File Format	Last Column	Last Row
97-2003	BIFF8	"IV" (2^8)	65536 (2^14)
2007	BIFF12	"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(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:

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.

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:

Result	Comment
A1	Cell reference without sheet
Sheet1!A1	Standard 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.

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