com.extentech.ExtenXLS
Class CellRange

java.lang.Object
  com.extentech.ExtenXLS.CellRange

All Implemented Interfaces:

Serializable

Direct Known Subclasses:

DataBoundCellRange

public class CellRange
extends Object
implements Serializable

Cell Range is a handle to a range of Workbook Cells
Contains useful methods for working with Collections of Cells.

for example:

CellRange cr = new CellRange("Sheet1!A1:B10", workbk);
cr.addCellToRange("C10");
CellHandle mycells = cr.getCells();
for(int x=0;x < mycells.length;x++)
Logger.logInfo(mycells[x].getCellAddress() + mycells[x].toString());
}

Since:

1.3

Version:

4.1

Author:

John McMahon -- Copyright ©2010 Extentech Inc.

See Also:

DataBoundCellRange, XLSRecord, Serialized Form

Field Summary

CellHandle[]	cells
boolean	DEBUG
static boolean	REMOVE_MERGED_CELLS
static boolean	RETAIN_MERGED_CELLS
static String	xmlResponsePost
static String	xmlResponsePre

Constructor Summary

CellRange(CellHandle[] newcells)
Constructor which creates a new CellRange using an array of cells as it's constructor.

CellRange(CellHandle[] newcells, boolean createblanks)
create a new CellRange using an array of cells as it's constructor.

CellRange(String r)
Constructor to Create a new CellRange from a String range
The String range must be in the format Sheet!CR:CR
For Example, "Sheet1!C9:I19"
NOTE: You MUST Set the WorkBookHandle explicitly on this CellRange or it will generate NullPointerException when trying to access the Cells.

CellRange(String range, WorkBook bk)
Constructor which creates a new CellRange from a String range
The String range must be in the format Sheet!CR:CR
For Example, "Sheet1!C9:I19"

CellRange(String range, WorkBook bk, boolean createblanks)
Constructor which creates a new CellRange from a String range
The String range must be in the format Sheet!CR:CR
For Example, "Sheet1!C9:I19"

CellRange(String range, WorkBook bk, boolean createblanks, boolean initcells)
Constructor which creates a new CellRange from a String range
The String range must be in the format Sheet!CR:CR
For Example, "Sheet1!C9:I19"

CellRange(WorkSheetHandle sht, int[] coords)
Constructor to create a new CellRange from a WorkSheetHandle and a set of range coordinates:
coords[0] = first row
coords[1] = first col
coords[2] = last row
coords[3] = last col

CellRange(WorkSheetHandle sht, int[] coords, boolean cb)
Constructor to create a new CellRange from a WorkSheetHandle and a set of range coordinates:
coords[0] = first row
coords[1] = first col
coords[2] = last row
coords[3] = last col

Method Summary

boolean	addCellToRange(CellHandle ch) Increase the bounds of the CellRange by including the CellHandle.
void	clear()
void	clearContents()
void	clearFormats()
boolean	contains(CellHandle ch) returns whether this CellRange contains a particular Cell
boolean	contains(int[] rc) returns whether this CellRange contains the specified Row/Col coordinates
List	getCellList() Return a list of the cells in this cell range
com.extentech.formats.XLS.BiffRec[]	getCellRecs() get the underlying Cell Records in this range NOTE: Cell Records are not a part of the public API and are not intended for use in client applications.
CellHandle[]	getCells() get the Cells in this cell range
static CellHandle[]	getCells(String range, WorkBookHandle wbh) returns the cells for a given range static version
int[]	getColInts() returns an array of column numbers referenced by this CellRange
ColHandle[]	getCols() Returns an array of Columns (ColHandles) referenced by this CellRange
boolean	getCreateBlanks() gets whether this CellRange will add blank records to the WorkBook for any missing Cells contained within the range.
int[]	getEdgePositions(CellHandle ch, int sz) returns edge status of the desired CellHandle within this CellRange ie: top, left, bottom, right returns 0 or 1 for 4 sides 1,1,1,1 is a single cell in a range 1,1,0,0 is on the top left edge of the range
org.json.JSONArray	getJSON() returns a JSON representation of this CellRange
static org.json.JSONArray	getJSON(String range, WorkBook wbh) return a JSON array of cell values for the given range static version
String	getR1C1Range() Return the String cell address of this range in R1C1 format
String	getRange() Return the String representation of this range
int[]	getRangeCoords() Gets the coordinates of this cell range.
int[]	getRowInts() Returns an array of the row numbers referenced by this CellRange
RowHandle[]	getRows() Returns an array of Rows (RowHandles) referenced by this CellRange
WorkSheetHandle	getSheet() Returns the WorkSheet referenced in this CellRange.
WorkBook	getWorkBook()
String	getXML() Return the XML representation of this CellRange object
void	init() initializes this CellRange
void	initCells(boolean createBlanks) Initializes this CellRange's cell list if necessary.
boolean	intersects(CellRange cr) returns whether this CellRange intersects with another CellRange
boolean	isMerged() returns the merged state of the CellRange
void	mergeCells(boolean remove) Merge the Cells contained in this CellRange
void	setAsPrintArea() Set this CellRange to be the current Print Area
void	setBorder(int width, int linestyle, Color colr) sets a border around the range of cells
void	setCreateBlanks(boolean b) set whether this CellRange will add blank records to the WorkBook for any missing Cells contained within the range.
void	setFormatID(int fmtID) Set the format ID of all cells in this CellRange FormatID can be obtained through any CellHandle with the getFormatID() call
void	setRange(String rng) Sets the range of cells for this CellRange to a string range
void	setSheet(WorkSheetHandle aSheet) Sets the sheet reference for this CellRange.
void	setURL(String url) Set a hyperlink on all cells in this CellRange
void	setWorkBook(WorkBook bk) attach the workbook for this CellRange
String	toString() returns the String representation of this CellRange
void	unMergeCells() Un-Merge the Cells contained in this CellRange
boolean	update() update the CellRange when the underlying Cells change their location

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

REMOVE_MERGED_CELLS

public static final boolean REMOVE_MERGED_CELLS

See Also:

Constant Field Values

RETAIN_MERGED_CELLS

public static final boolean RETAIN_MERGED_CELLS

See Also:

Constant Field Values

DEBUG

public boolean DEBUG

cells

public transient CellHandle[] cells

xmlResponsePre

public static String xmlResponsePre

xmlResponsePost

public static String xmlResponsePost

Constructor Detail

CellRange

public CellRange(WorkSheetHandle sht,
                 int[] coords,
                 boolean cb)
          throws Exception

Constructor to create a new CellRange from a WorkSheetHandle and a set of range coordinates:
coords[0] = first row
coords[1] = first col
coords[2] = last row
coords[3] = last col

Parameters:

WorkSheetHandle - sht - handle to the WorkSheet containing the Range's Cells

int[] - coords - the cell coordinates

boolean - cb - true if should create blank cells

Throws:

Exception

CellRange

public CellRange(WorkSheetHandle sht,
                 int[] coords)
          throws Exception

Constructor to create a new CellRange from a WorkSheetHandle and a set of range coordinates:
coords[0] = first row
coords[1] = first col
coords[2] = last row
coords[3] = last col

Parameters:

WorkSheetHandle - sht - handle to the WorkSheet containing the Range's Cells

int[] - coords - the cell coordinates

Throws:

Exception

CellRange

public CellRange(String r)

Constructor to Create a new CellRange from a String range
The String range must be in the format Sheet!CR:CR
For Example, "Sheet1!C9:I19"
NOTE: You MUST Set the WorkBookHandle explicitly on this CellRange or it will generate NullPointerException when trying to access the Cells.

Parameters:

String - r - range String

See Also:

CellRange.setWorkBook

CellRange

public CellRange(String range,
                 WorkBook bk,
                 boolean createblanks,
                 boolean initcells)

Constructor which creates a new CellRange from a String range
The String range must be in the format Sheet!CR:CR
For Example, "Sheet1!C9:I19"

Parameters:

String - range - the range string

WorkBook - bk

boolean - createblanks - true if blank cells should be created if necessary

boolean - initcells - true if cells should (be initialized)

CellRange

public CellRange(String range,
                 WorkBook bk,
                 boolean createblanks)

Constructor which creates a new CellRange from a String range
The String range must be in the format Sheet!CR:CR
For Example, "Sheet1!C9:I19"

Parameters:

String - range - the range string

WorkBook - bk

boolean - createblanks - true if blank cells should be created (if necessary)

CellRange

public CellRange(CellHandle[] newcells)
          throws com.extentech.formats.XLS.CellNotFoundException

Constructor which creates a new CellRange using an array of cells as it's constructor.
NOTE that if the array of cells you are adding is not a rectangle of data (ie [A1][B1][C1]) that you will have null cells in your cell range and operations on it may cause errors.
If you wish to populate a cell range that is not contiguous, consider the constructor CellRange(CellHandle[] newcells, boolean createblanks), which will populate null cells with blank records, allowing normal operations such as formatting, merging, etc.

Parameters:

CellHandle[] - newcells - the array of cells from which to create the new CellRange

Throws:

com.extentech.formats.XLS.CellNotFoundException

CellRange

public CellRange(CellHandle[] newcells,
                 boolean createblanks)
          throws com.extentech.formats.XLS.CellNotFoundException

create a new CellRange using an array of cells as it's constructor.
If you wish to populate a cell range that is not contiguous, set createblanks to true, which will populate null cells with blank records, allowing normal operations such as formatting, merging, etc.

Parameters:

CellHandle[] - newcells - the array of cells from which to create the new CellRange

boolean - createblanks - true if should create blank cells if necesary

Throws:

com.extentech.formats.XLS.CellNotFoundException

CellRange

public CellRange(String range,
                 WorkBook bk)
          throws com.extentech.formats.XLS.CellNotFoundException

Constructor which creates a new CellRange from a String range
The String range must be in the format Sheet!CR:CR
For Example, "Sheet1!C9:I19"

Parameters:

String - range - the range string

WorkBook - bk

Throws:

com.extentech.formats.XLS.CellNotFoundException

Method Detail

clearFormats

public void clearFormats()

clearContents

public void clearContents()

clear

public void clear()

unMergeCells

public void unMergeCells()
                  throws Exception

Un-Merge the Cells contained in this CellRange

Throws:

Exception

setFormatID

public void setFormatID(int fmtID)
                 throws Exception

Set the format ID of all cells in this CellRange
FormatID can be obtained through any CellHandle with the getFormatID() call

Parameters:

int - fmtID - the format ID to set the cells within the range to

Throws:

Exception

setURL

public void setURL(String url)
            throws Exception

Set a hyperlink on all cells in this CellRange

Parameters:

String - url - the URL String to set

Throws:

Exception

mergeCells

public void mergeCells(boolean remove)

Merge the Cells contained in this CellRange

Parameters:

boolean - remove - true to delete the Cells following the first in the range

getRowInts

public int[] getRowInts()

Returns an array of the row numbers referenced by this CellRange

Returns:

int[] array of row ints

getColInts

public int[] getColInts()

returns an array of column numbers referenced by this CellRange

Returns:

int[] array of col ints

getRows

public RowHandle[] getRows()
                    throws com.extentech.formats.XLS.RowNotFoundException

Returns an array of Rows (RowHandles) referenced by this CellRange

Returns:

RowHandle[] array of row handles

Throws:

com.extentech.formats.XLS.RowNotFoundException

getCols

public ColHandle[] getCols()
                    throws com.extentech.formats.XLS.ColumnNotFoundException

Returns an array of Columns (ColHandles) referenced by this CellRange

Returns:

ColHandle[] array of columns handles

Throws:

com.extentech.formats.XLS.ColumnNotFoundException

getEdgePositions

public int[] getEdgePositions(CellHandle ch,
                              int sz)

returns edge status of the desired CellHandle within this CellRange ie: top, left, bottom, right
returns 0 or 1 for 4 sides
1,1,1,1 is a single cell in a range 1,1,0,0 is on the top left edge of the range

Parameters:

CellHandle - ch -

int - sz -

Returns:

int[] array representing the edge positions

intersects

public boolean intersects(CellRange cr)

returns whether this CellRange intersects with another CellRange

Parameters:

CellRange - cr - CellRange to test

Returns:

boolean true if CellRange cr intersects with this CellRange

contains

public boolean contains(CellHandle ch)

returns whether this CellRange contains a particular Cell

Parameters:

CellHandle - ch - the Cell to check

Returns:

true if CellHandle ch is contained within this CellRange

contains

public boolean contains(int[] rc)

returns whether this CellRange contains the specified Row/Col coordinates

Parameters:

int[] - rc - row/col coordinates to test

Returns:

true if the coordinates are contained with this CellRange

toString

public String toString()

returns the String representation of this CellRange

Overrides:

toString in class Object

setAsPrintArea

public void setAsPrintArea()

Set this CellRange to be the current Print Area

addCellToRange

public boolean addCellToRange(CellHandle ch)

Increase the bounds of the CellRange by including the CellHandle.
These are the limitations and side-effects of this method:
- the Cell must be contiguous with the existing Range, ie: you can add a Cell which either increments the row or the column of the existing range by one.
- the Cell must be on the same sheet as the existing range.
- as a Cell Range is a 2 dimensional rectangle, expanding a multiple column range by adding a Cell to the end will include the logical Cells on the row in the range.
Some Examples:

// simple one dimensional range expansion:
existing Range = A1:A20
addCellToRange(A21) new Range = A1:A21

existing Range = A1:B20
addCellToRange(A21)
new Range = A1:B21 // note B20 is included automatically

existing Range = A1:A20
addCellToRange(B1)
new Range = A1:B20 //note entire B column of cells are included automatically

Parameters:

CellHandle - ch - the Cell to add to the CellRange

getCells

public CellHandle[] getCells()

get the Cells in this cell range

Returns:

CellHandle[] all the Cells in this range

getCellList

public List getCellList()

Return a list of the cells in this cell range

Returns:

List of CellHandles

getCellRecs

public com.extentech.formats.XLS.BiffRec[] getCellRecs()

get the underlying Cell Records in this range
NOTE: Cell Records are not a part of the public API and are not intended for use in client applications.

Returns:

BiffRec[] array of Cell Records

getXML

public String getXML()

Return the XML representation of this CellRange object

Returns:

String of XML

setWorkBook

public void setWorkBook(WorkBook bk)

attach the workbook for this CellRange

Parameters:

WorkBook - bk

getRangeCoords

public int[] getRangeCoords()
                     throws com.extentech.formats.XLS.CellNotFoundException

Gets the coordinates of this cell range.

Returns:

int[5]: [0] first row, [1] first column, [2] last row, [3] last column, [4] number of cells in range

Throws:

com.extentech.formats.XLS.CellNotFoundException

getSheet

public WorkSheetHandle getSheet()

Returns the WorkSheet referenced in this CellRange.

Returns:

WorkSheetHandle sheet referenced in this CellRange.

init

public void init()
          throws com.extentech.formats.XLS.CellNotFoundException

initializes this CellRange

Throws:

com.extentech.formats.XLS.CellNotFoundException

initCells

public void initCells(boolean createBlanks)

Initializes this CellRange's cell list if necessary. This method is useful if this CellRange was created with initCells set to false and it is later necessary to retrieve the cell list.

Parameters:

createBlanks - whether missing cells should be created as blanks. If this is false they will appear in the cell list as nulls.

getWorkBook

public WorkBook getWorkBook()

Returns:

the workbook object attached to this CellRange

getCreateBlanks

public boolean getCreateBlanks()

gets whether this CellRange will add blank records to the WorkBook for any missing Cells contained within the range.

Returns:

true if should create blank records for missing Cells

setCreateBlanks

public void setCreateBlanks(boolean b)

set whether this CellRange will add blank records to the WorkBook for any missing Cells contained within the range.

Parameters:

boolean - b - true if should create blank records for missing Cells

getRange

public String getRange()

Return the String representation of this range

Returns:

the String range

getR1C1Range

public String getR1C1Range()

Return the String cell address of this range in R1C1 format

Returns:

String range in R1C1 format

setRange

public void setRange(String rng)

Sets the range of cells for this CellRange to a string range

Parameters:

String - rng - Range string

setBorder

public void setBorder(int width,
                      int linestyle,
                      Color colr)

sets a border around the range of cells

Parameters:

int - width - line width

int - linestyle - line style

java.awt.Color - colr - color of border line

update

public boolean update()

update the CellRange when the underlying Cells change their location

Returns:

boolean true if the CellRange could be updated, false if there are no cells represented by this range

isMerged

public boolean isMerged()

returns the merged state of the CellRange

Returns:

true if this CellRange is merged

setSheet

public void setSheet(WorkSheetHandle aSheet)

Sets the sheet reference for this CellRange.

Parameters:

WorkSheetHandle - aSheet

getJSON

public org.json.JSONArray getJSON()

returns a JSON representation of this CellRange

Returns:

JSONArray - a JSON representation of this CellRange

getJSON

public static org.json.JSONArray getJSON(String range,
                                         WorkBook wbh)

return a JSON array of cell values for the given range
static version

Parameters:

String - range - a string representation of the desired range of cells

WorkBook - wbh - the source WorkBook for the cell range

Returns:

JSONArray - a JSON representation of the desired cell range

getCells

public static CellHandle[] getCells(String range,
                                    WorkBookHandle wbh)

returns the cells for a given range
static version

Parameters:

String - range - a string representation of the desired range of cells

WorkBook - wbh - the source WorkBook for the cell range

Returns:

CellHandle[] array of cells represented by the desired cell range