org.jxls.util

Class CellRefUtil

java.lang.Object

org.jxls.util.CellRefUtil

public class CellRefUtil
extends Object

This is a class to convert Excel cell names to (sheet,row,col) representations and vice versa The current code is taken from Apache POI CellReference class (@link http://poi.apache.org/apidocs/org/apache/poi/ss/util/CellReference.html)

Author:

Leonid Vysochyn Date: 2/6/12

Field Summary

Fields

Modifier and Type	Field and Description
static char	ABSOLUTE_REFERENCE_MARKER The character ($) that signifies a row or column value is absolute instead of relative
static char	SHEET_NAME_DELIMITER The character (!) that separates sheet names from cell references

Constructor Summary

Constructors

Constructor and Description
CellRefUtil()

Method Summary

Modifier and Type	Method and Description
static void	appendFormat(StringBuilder out, String rawSheetName)
static boolean	cellReferenceIsWithinRange(String colStr, String rowStr, int lastColumnIndex, int lastRowIndex) 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 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.
static String	convertNumToColString(int col) Takes in a 0-based base-10 column and returns a ALPHA-26 representation.
static boolean	isColumnWithnRange(String colStr, int lastColumnIndex)
static boolean	isPlainColumn(String refPart)
static boolean	isRowWithnRange(String rowStr, int lastRowIndex)
static String	parseSheetName(String reference, int indexOfSheetNameDelimiter)
static String[]	separateAreaRefs(String reference) Separates Area refs in two parts and returns them as separate elements in a String array, each qualified with the sheet name (if present)
static String[]	separateRefParts(String reference)

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

SHEET_NAME_DELIMITER

public static final char SHEET_NAME_DELIMITER

The character (!) that separates sheet names from cell references

See Also:

Constant Field Values

ABSOLUTE_REFERENCE_MARKER

public static final char ABSOLUTE_REFERENCE_MARKER

The character ($) that signifies a row or column value is absolute instead of relative

See Also:

Constant Field Values

Constructor Detail

CellRefUtil

public CellRefUtil()

Method Detail

convertNumToColString

public static String convertNumToColString(int col)

Takes in a 0-based base-10 column and returns a ALPHA-26 representation. eg column #3 -> D

appendFormat

public static void appendFormat(StringBuilder out,
                                String rawSheetName)

cellReferenceIsWithinRange

public static boolean cellReferenceIsWithinRange(String colStr,
                                                 String rowStr,
                                                 int lastColumnIndex,
                                                 int lastRowIndex)

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.

isColumnWithnRange

public static boolean isColumnWithnRange(String colStr,
                                         int lastColumnIndex)

isRowWithnRange

public static boolean isRowWithnRange(String rowStr,
                                      int lastRowIndex)

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

separateRefParts

public static String[] separateRefParts(String reference)

parseSheetName

public static String parseSheetName(String reference,
                                    int indexOfSheetNameDelimiter)

separateAreaRefs

public static String[] separateAreaRefs(String reference)

Separates Area refs in two parts and returns them as separate elements in a String array, each qualified with the sheet name (if present)

Returns:

array with one or two elements. never null

isPlainColumn

public static boolean isPlainColumn(String refPart)