Nested and Inner Type Summary

Modifier and Type	Class and Description
private static class	CellReference.CellRefParts
public static enum	CellReference.NameType Used to classify identifiers found in formulas as cell references or not.

Field Summary

Modifier and Type	Field and Description
private final int	_colIndex
private final boolean	_isColAbs
private final boolean	_isRowAbs
private final int	_rowIndex
private final String	_sheetName
private static final char	ABSOLUTE_REFERENCE_MARKER The character ($) that signifies a row or column value is absolute instead of relative
private static final Pattern	CELL_REF_PATTERN Matches a run of one or more letters followed by a run of one or more digits.
private static final Pattern	COLUMN_REF_PATTERN Matches a run of one or more letters.
private static final Pattern	NAMED_RANGE_NAME_PATTERN Named range names must start with a letter or underscore.
private static final Pattern	ROW_REF_PATTERN Matches a run of one or more letters.
private static final char	SHEET_NAME_DELIMITER The character (!) that separates sheet names from cell references
private static final char	SPECIAL_NAME_DELIMITER The character (') used to quote sheet names when they contain special characters
private static final Pattern	STRICTLY_CELL_REF_PATTERN Matches references only where row and column are included.

Constructor Summary

Access	Constructor and Description
public	CellReference(String cellRef) Create an cell ref from a string representation.
public	CellReference(int pRow, int pCol)
public	CellReference(int pRow, short pCol)
public	CellReference(Cell cell)
public	CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol)
public	CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol)

Method Summary

Modifier and Type	Method and Description
pack-priv void	appendCellReference(StringBuilder sb) Appends cell reference with '$' markers for absolute values as required.
pack-priv void	appendR1C1CellReference(StringBuilder sb) Appends R1C1 cell reference with '$' markers for absolute values as required.
public static boolean	Returns: `true` if the row and col parameters are within range of a BIFF8 spreadsheet. cellReferenceIsWithinRange(String a string of only letter characters colStr, String a string of only digit characters 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.
public static CellReference.NameType	Returns: one of the values from `NameType` classifyCellReference(String str, SpreadsheetVersion ssVersion) Classifies an identifier as either a simple (2D) cell reference or a named range name
public static int	Returns: zero based column index 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.
public static String	convertNumToColString(int col) Takes in a 0-based base-10 column and returns a ALPHA-26 representation.
public boolean	equals(Object the reference object with which to compare. o) Overrides java.lang.Object.equals. Checks whether this cell reference is equal to another object.
public String	Returns: the text representation of this cell reference as it would appear in a formula. formatAsR1C1String() Returns a text representation of this cell reference in R1C1 format.
public String	Returns: the text representation of this cell reference as it would appear in a formula. formatAsR1C1String(boolean If true and there is a sheet name set for this cell reference, the reference is prefixed with the sheet name and '!' includeSheetName) Returns a text representation of this cell reference in R1C1 format and allows to control if the sheetname is included in the reference.
public String	Returns: the text representation of this cell reference as it would appear in a formula. formatAsString() Returns a text representation of this cell reference.
public String	Returns: the text representation of this cell reference as it would appear in a formula. formatAsString(boolean If true and there is a sheet name set for this cell reference, the reference is prefixed with the sheet name and '!' includeSheetName) Returns a text representation of this cell reference and allows to control if the sheetname is included in the reference.
public String[]	Returns: String array of { sheetName, rowString, colString } 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.
public short	getCol()
public Map<String, Supplier<?>>	getGenericProperties() Implements org.apache.poi.common.usermodel.GenericRecord.getGenericProperties.
public int	getRow()
public String	Returns: possibly `null` if this is a 2D reference. Special characters are not escaped or delimited getSheetName()
public int	hashCode() Overrides java.lang.Object.hashCode. Returns a hash code value for this object.
public boolean	isColAbsolute()
public static boolean	isColumnWithinRange(String colStr, SpreadsheetVersion ssVersion)
public static boolean	isPartAbsolute(String part)
public boolean	isRowAbsolute()
public static boolean	isRowWithinRange(String the numeric portion of an A1-style cell reference (1-based index) rowStr, SpreadsheetVersion the spreadsheet version ssVersion) Determines whether `rowStr` is a valid row number for a given SpreadsheetVersion.
public static boolean	isRowWithinRange(int the row number (0-based index) rowNum, SpreadsheetVersion the spreadsheet version ssVersion) Determines whether `row` is a valid row number for a given SpreadsheetVersion.
private static String	parseSheetName(String reference, int indexOfSheetNameDelimiter)
private static CellReference.CellRefParts	Returns: String array of sheetName, column (in ALPHA-26 format), and row output column or row elements will contain absolute reference markers if they existed in the input reference. separateRefParts(String is a string that identifies a cell within the sheet or workbook reference may not refer to a cell in an external workbook reference may be absolute or relative. reference) Separates the sheet name, row, and columns from a cell reference string.
public String	toString() Overrides java.lang.Object.toString. Returns a string representation of the object.
private static CellReference.NameType	validateNamedRangeName(String str, SpreadsheetVersion ssVersion)

Inherited from java.lang.Object:: clone finalize getClass notify notifyAll wait wait wait

Field Detail

_colIndex	back to summary
private final int _colIndex

_isColAbs	back to summary
private final boolean _isColAbs

_isRowAbs	back to summary
private final boolean _isRowAbs

_rowIndex	back to summary
private final int _rowIndex

_sheetName	back to summary
private final String _sheetName

ABSOLUTE_REFERENCE_MARKER	back to summary
private static final char ABSOLUTE_REFERENCE_MARKER The character ($) that signifies a row or column value is absolute instead of relative

CELL_REF_PATTERN	back to summary
private static final Pattern CELL_REF_PATTERN Matches a run of one or more letters followed by a run of one or more digits. Both the letter and number groups are optional. The run of letters is group 1 and the run of digits is group 2. Each group may optionally be prefixed with a single '$'.

COLUMN_REF_PATTERN	back to summary
private static final Pattern COLUMN_REF_PATTERN Matches a run of one or more letters. The run of letters is group 1. References may optionally include a single '$' before the group, but these are excluded from the Matcher.group(int).

NAMED_RANGE_NAME_PATTERN	back to summary
private static final Pattern NAMED_RANGE_NAME_PATTERN Named range names must start with a letter or underscore. Subsequent characters may include digits or dot. (They can even end in dot).

ROW_REF_PATTERN	back to summary
private static final Pattern ROW_REF_PATTERN Matches a run of one or more letters. The run of numbers is group 1. References may optionally include a single '$' before the group, but these are excluded from the Matcher.group(int).

SHEET_NAME_DELIMITER	back to summary
private static final char SHEET_NAME_DELIMITER The character (!) that separates sheet names from cell references

SPECIAL_NAME_DELIMITER	back to summary
private static final char SPECIAL_NAME_DELIMITER The character (') used to quote sheet names when they contain special characters

STRICTLY_CELL_REF_PATTERN	back to summary
private static final Pattern STRICTLY_CELL_REF_PATTERN Matches references only where row and column are included. Matches a run of one or more letters followed by a run of one or more digits. If a reference does not match this pattern, it might match COLUMN_REF_PATTERN or ROW_REF_PATTERN References may optionally include a single '$' before each group, but these are excluded from the Matcher.group(int).

STRICTLY_CELL_REF_PATTERN

back to summary

private static final Pattern STRICTLY_CELL_REF_PATTERN

Matches references only where row and column are included. Matches a run of one or more letters followed by a run of one or more digits. If a reference does not match this pattern, it might match COLUMN_REF_PATTERN or ROW_REF_PATTERN References may optionally include a single '$' before each group, but these are excluded from the Matcher.group(int).

Constructor Detail

CellReference	back to summary
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. Exceptions IllegalArgumentException: if cellRef is not valid

CellReference

back to summary

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.

Exceptions

IllegalArgumentException:: if cellRef is not valid

CellReference	back to summary
public CellReference(int pRow, int pCol)

CellReference	back to summary
public CellReference(int pRow, short pCol)

CellReference	back to summary
public CellReference(Cell cell)

CellReference	back to summary
public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol)

CellReference	back to summary
public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol)

Method Detail

appendCellReference	back to summary
pack-priv void appendCellReference(StringBuilder sb) Appends cell reference with '$' markers for absolute values as required. Sheet name is not included.

appendR1C1CellReference	back to summary
pack-priv void appendR1C1CellReference(StringBuilder sb) Appends R1C1 cell reference with '$' markers for absolute values as required. Sheet name is not included.

cellReferenceIsWithinRange back to summary

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

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:String: a string of only letter characters
rowStr:String: a string of only digit characters

Returns:boolean

true if the row and col parameters are within range of a BIFF8 spreadsheet.

classifyCellReference back to summary

classifyCellReference	back to summary
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:CellReference.NameType one of the values from `NameType`

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:CellReference.NameType: one of the values from NameType

convertColStringToIndex	back to summary
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:int zero based column index

convertColStringToIndex

back to summary

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:int: zero based column index

convertNumToColString	back to summary
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"`

equals back to summary

equals	back to summary
public boolean equals(Object o) Overrides java.lang.Object.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. Parameters o:Object Doc from java.lang.Object.equals. the reference object with which to compare. Returns:boolean Doc from java.lang.Object.equals. `true` if this object is the same as the obj argument; `false` otherwise. Annotations @Override

public boolean equals(Object o)

Overrides java.lang.Object.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.

Parameters

o:Object

Doc from java.lang.Object.equals.

the reference object with which to compare.

Returns:boolean

Doc from java.lang.Object.equals.

true if this object is the same as the obj argument; false otherwise.

Annotations

@Override

formatAsR1C1String back to summary

public String formatAsR1C1String()

Returns a text representation of this cell reference in R1C1 format.

Example return values:

Example return values
Result	Comment
R1C1	Cell reference without sheet
Sheet1!R1C1	Standard sheet name
'O''Brien''s Sales'!R1C1'	Sheet name with special characters

Returns:String: the text representation of this cell reference as it would appear in a formula.
Since: POI 5.2.1
See Also: formatAsString(), formatAsR1C1String(boolean)

formatAsR1C1String back to summary

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
Result	Comment
R1C1	Cell reference without sheet
Sheet1!R1C1	Standard sheet name
'O''Brien''s Sales'!R1C1'	Sheet name with special characters

Parameters

includeSheetName:boolean: If true and there is a sheet name set for this cell reference, the reference is prefixed with the sheet name and '!'

Returns:String

the text representation of this cell reference as it would appear in a formula.

Since

POI 5.2.1

formatAsString back to summary

public String formatAsString()

Returns a text representation of this cell reference.

Example return values:

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:String: the text representation of this cell reference as it would appear in a formula.
See Also: formatAsString(boolean)

formatAsString back to summary

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
Result	Comment
A1	Cell reference without sheet
Sheet1!A1	Standard sheet name
'O''Brien''s Sales'!A1'	Sheet name with special characters

Parameters

includeSheetName:boolean: If true and there is a sheet name set for this cell reference, the reference is prefixed with the sheet name and '!'

Returns:String

the text representation of this cell reference as it would appear in a formula.

See Also

formatAsString()

getCellRefParts back to summary

getCellRefParts	back to summary
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[] String array of { sheetName, rowString, colString }

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[]: String array of { sheetName, rowString, colString }

getCol	back to summary
public short getCol()

getGenericProperties	back to summary
public Map<String, Supplier<?>> getGenericProperties() Implements org.apache.poi.common.usermodel.GenericRecord.getGenericProperties. Annotations @Override

getGenericProperties

back to summary

public Map<String, Supplier<?>> getGenericProperties()

Implements org.apache.poi.common.usermodel.GenericRecord.getGenericProperties.

Annotations

@Override

getRow	back to summary
public int getRow()

getSheetName	back to summary
public String getSheetName() Returns:String possibly `null` if this is a 2D reference. Special characters are not escaped or delimited

hashCode back to summary

hashCode	back to summary
public int hashCode() Overrides java.lang.Object.hashCode. Doc from java.lang.Object.hashCode. Returns a hash code value for this object. This method is supported for the benefit of hash tables such as those provided by `java.util.HashMap`. The general contract of `hashCode` is: Whenever it is invoked on the same object more than once during an execution of a Java application, the `hashCode` method must consistently return the same integer, provided no information used in `equals` comparisons on the object is modified. This integer need not remain consistent from one execution of an application to another execution of the same application. If two objects are equal according to the `equals` method, then calling the `hashCode` method on each of the two objects must produce the same integer result. It is not required that if two objects are unequal according to the `equals` method, then calling the `hashCode` method on each of the two objects must produce distinct integer results. However, the programmer should be aware that producing distinct integer results for unequal objects may improve the performance of hash tables. Returns:int a hash code value for this object Annotations @Override

public int hashCode()

Overrides java.lang.Object.hashCode.

Doc from java.lang.Object.hashCode.

Returns a hash code value for this object. This method is supported for the benefit of hash tables such as those provided by java.util.HashMap.

The general contract of hashCode is:

Whenever it is invoked on the same object more than once during an execution of a Java application, the hashCode method must consistently return the same integer, provided no information used in equals comparisons on the object is modified. This integer need not remain consistent from one execution of an application to another execution of the same application.
If two objects are equal according to the equals method, then calling the hashCode method on each of the two objects must produce the same integer result.
It is not required that if two objects are unequal according to the equals method, then calling the hashCode method on each of the two objects must produce distinct integer results. However, the programmer should be aware that producing distinct integer results for unequal objects may improve the performance of hash tables.

Returns:int

a hash code value for this object

Annotations

@Override

isColAbsolute	back to summary
public boolean isColAbsolute()

isColumnWithinRange	back to summary
public static boolean isColumnWithinRange(String colStr, SpreadsheetVersion ssVersion)

isPartAbsolute	back to summary
public static boolean isPartAbsolute(String part)

isRowAbsolute	back to summary
public boolean isRowAbsolute()

isRowWithinRange back to summary

public static boolean isRowWithinRange(String rowStr, SpreadsheetVersion ssVersion)

Determines whether rowStr is a valid row number for a given SpreadsheetVersion.

Parameters

rowStr:String: the numeric portion of an A1-style cell reference (1-based index)
ssVersion:SpreadsheetVersion: the spreadsheet version

Exceptions

NumberFormatException:: if rowStr is not parseable as an integer

isRowWithinRange back to summary

public static boolean isRowWithinRange(int rowNum, SpreadsheetVersion ssVersion)

Determines whether row is a valid row number for a given SpreadsheetVersion.

Parameters

rowNum:int: the row number (0-based index)
ssVersion:SpreadsheetVersion: the spreadsheet version

Since

3.17 beta 1

parseSheetName	back to summary
private static String parseSheetName(String reference, int indexOfSheetNameDelimiter)

separateRefParts

back to summary

private static CellReference.CellRefParts separateRefParts(String reference)

Separates the sheet name, row, and columns from a cell reference string.

Parameters

reference:String: is a string that identifies a cell within the sheet or workbook reference may not refer to a cell in an external workbook reference may be absolute or relative.

Returns:CellReference.CellRefParts

String array of sheetName, column (in ALPHA-26 format), and row output column or row elements will contain absolute reference markers if they existed in the input reference.

toString back to summary

public String toString()

Overrides java.lang.Object.toString.

Doc from java.lang.Object.toString.

Returns a string representation of the object. Satisfying this method's contract implies a non-null result must be returned.

Returns:String

a string representation of the object

Annotations

@Override

validateNamedRangeName	back to summary
private static CellReference.NameType validateNamedRangeName(String str, SpreadsheetVersion ssVersion)

org.apache.poi.ss.util back to summary

private final Class CellReference.CellRefParts

Class Inheritance

extends Object

java.lang.Object
org.apache.poi.ss.util.CellReference.CellRefParts

Field Summary

Modifier and Type	Field and Description
pack-priv final String	colRef
pack-priv final String	rowRef
pack-priv final String	sheetName

Constructor Summary

Access	Constructor and Description
private	CellRefParts(String sheetName, String rowRef, String colRef)

Method Summary

Inherited from java.lang.Object:: clone equals finalize getClass hashCode notify notifyAll toString wait wait wait

Field Detail

colRef	back to summary
pack-priv final String colRef

rowRef	back to summary
pack-priv final String rowRef

sheetName	back to summary
pack-priv final String sheetName

Constructor Detail

CellRefParts	back to summary
private CellRefParts(String sheetName, String rowRef, String colRef)

org.apache.poi.ss.util back to summary

public final Enum CellReference.NameType

Class Inheritance

extends Enum<CellReference.NameType>

java.lang.Object
java.lang.Enum
org.apache.poi.ss.util.CellReference.NameType

Used to classify identifiers found in formulas as cell references or not.

Field Summary

Modifier and Type	Field and Description
public static final CellReference.NameType	BAD_CELL_OR_NAMED_RANGE
public static final CellReference.NameType	CELL
public static final CellReference.NameType	COLUMN
public static final CellReference.NameType	NAMED_RANGE
public static final CellReference.NameType	ROW

Constructor Summary

Access	Constructor and Description
private	NameType()

Method Summary

Modifier and Type	Method and Description
public static CellReference.NameType	valueOf(String name)
public static CellReference.NameType[]	values()

Inherited from java.lang.Enum:: clone compareTo describeConstable equals finalize getDeclaringClass hashCode name ordinal toString valueOf

Field Detail

BAD_CELL_OR_NAMED_RANGE	back to summary
public static final CellReference.NameType BAD_CELL_OR_NAMED_RANGE

CELL	back to summary
public static final CellReference.NameType CELL

COLUMN	back to summary
public static final CellReference.NameType COLUMN

NAMED_RANGE	back to summary
public static final CellReference.NameType NAMED_RANGE

ROW	back to summary
public static final CellReference.NameType ROW

Constructor Detail

NameType	back to summary
private NameType()

Method Detail

valueOf	back to summary
public static CellReference.NameType valueOf(String name)

values	back to summary
public static CellReference.NameType[] values()

public Class CellReference

Nested and Inner Type Summary

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail

private final Class CellReference.CellRefParts

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

public final Enum CellReference.NameType

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail