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 ©2011Extentech Inc.

See Also:

DataBoundCellRange, XLSRecord, Serialized Form

Field Summary

CellHandle[]	cells
static int	COPY_CONTENTS Whether to copy the cell contents.
static int	COPY_FORMATS
static int	COPY_FORMULAS Whether formulas should be copied.
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(CellRangeRef source)
Initializes a CellRange from a CellRangeRef.

CellRange(CellRangeRef source, boolean init, boolean create)
Initializes a CellRange from a CellRangeRef.

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 c)
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() clears the contents and formats of the cells referenced by this range but does not remove the cells from the workbook.
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
CellRange	copy(WorkSheetHandle sheet, int row, int col, int what) Copies this range to another location.
List	getCellList() Return a list of the cells in this cell range
String	getCellRangeXML(boolean fragment) gets the array of Cells in this Name NOTE: this method variation also returns the Sheetname for the name record if not null.
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
int	getHeight() Gets the number of rows in 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.
int	getWidth() Gets the number of columns in the range.
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	removeCells() removes the cells referenced by this range from the sheet.
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

COPY_CONTENTS

public static final int COPY_CONTENTS

Whether to copy the cell contents.

See Also:

Constant Field Values

COPY_FORMULAS

public static final int COPY_FORMULAS

Whether formulas should be copied. If this bit is not set the formula result will be copied instead.

See Also:

Constant Field Values

COPY_FORMATS

public static final int COPY_FORMATS

See Also:

Constant Field Values

Constructor Detail

CellRange

public CellRange(CellRangeRef source,
                 boolean init,
                 boolean create)

Initializes a CellRange from a CellRangeRef. The source CellRangeRef instance must be qualified with a single resolved worksheet.

Parameters:

source - the CellRangeRef from which to initialize

init - whether to populate the cell array

create - whether to fill gaps in the range with blank cells

Throws:

IllegalArgumentException - if the source range does not have a resolved sheet or has more than one sheet

CellRange

public CellRange(CellRangeRef source)

Initializes a CellRange from a CellRangeRef. The source CellRangeRef instance must be qualified with a single resolved worksheet.

Parameters:

source - the CellRangeRef from which to initialize

Throws:

IllegalArgumentException - if the source range does not have a resolved sheet or has more than one sheet

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 c)

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()

clears the contents and formats of the cells referenced by this range but does not remove the cells from the workbook.

removeCells

public void removeCells()

removes the cells referenced by this range from the sheet. NOTE: method does not shift rows or cols.

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

getWidth

public int getWidth()

Gets the number of columns in the range.

getHeight

public int getHeight()

Gets the number of rows 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

getCellRangeXML

public String getCellRangeXML(boolean fragment)

gets the array of Cells in this Name NOTE: this method variation also returns the Sheetname for the name record if not null. Thus this method is limited to use with 2D ranges.

Parameters:

fragment - whether to enclose result in NameHandle tag

Returns:

Cell[] all Cells defined in this Name

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

copy

public CellRange copy(WorkSheetHandle sheet,
                      int row,
                      int col,
                      int what)

Copies this range to another location. At present only contents and complete formats may be copied.

Parameters:

row - the topmost row of the target area

col - the leftmost column of the target area

what - a set of flags determining what will be copied

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