com.extentech.ExtenXLS
Interface WorkSheet

All Superinterfaces:

Handle

public interface WorkSheet
extends Handle

An interface representing an ExtenXLS WorkSheet

Version:

4.1

Author:

John McMahon -- Copyright ©2011Extentech Inc.

See Also:

WorkSheetHandle

Field Summary

static int	ROW_INSERT
static int	ROW_KEEP

Method Summary

CellHandle	add(Date dt, String address, String fmt) Add a java.sql.Date Cell to a WorkSheet.
CellHandle	add(Object obj, int row, int col) Add a Cell with the specified value to a WorkSheet.
CellHandle	add(Object obj, String address) Add a Cell with the specified value to a WorkSheet.
void	addChart(byte[] serialchart, String name)
CellHandle	getCell(int row, int col) Returns a CellHandle for working with the value of a Cell on a WorkSheet.
CellHandle	getCell(String addr) Returns a CellHandle for working with the value of a Cell on a WorkSheet.
CellHandle[]	getCells() Returns all CellHandles defined on this WorkSheet.
ColHandle	getCol(int clnum) returns the Column at the index position
ColHandle	getCol(String name) returns the Column at the named position
List	getColNames() returns a Vector of Column names
ColHandle[]	getColumns() returns all of the Columns in this WorkSheet
int	getFirstCol() Get the first column on the Worksheet
int	getFirstRow() Get the first row on the Worksheet
String	getFooterText() Get the text for the Footer printed at the bottom of the Worksheet
FormulaHandle	getFormula(String addr) Returns a FormulaHandle for working with the ranges of a formula on a WorkSheet.
String	getHeaderText() Get the text for the Header printed at the top of the Worksheet
boolean	getHidden() get whether this sheet is hidden from the user opening the file.
int	getLastCol() Get the last column on the Worksheet
int	getLastRow() Get the last row on the Worksheet
com.extentech.formats.XLS.Mergedcells	getMergedCellsRec()
int	getNumCols() Returns the number of Columns in this WorkSheet
int	getNumRows() Returns the number of rows in this WorkSheet
RowHandle	getRow(int t) get an a RowHandle for this WorkSheet by number
List	getRowNums() returns a Vector of Row numbers
RowHandle[]	getRows() get an array of all RowHandles for this WorkSheet
boolean	getSelected() get whether this sheet is selected upon opening the file.
byte[]	getSerialBytes() Returns the Serialized bytes for this WorkSheet.
com.extentech.formats.XLS.Sheet	getSheet() Returns the low-level ExtenXLS Sheet.
String	getSheetName() Returns the name of the Sheet.
int	getTabIndex() get the tab display order of this Worksheet this is a zero based index with zero representing the left-most WorkSheet tab.
Object	getVal(String address) Get the Object value of a Cell.
WorkBook	getWorkBook()
void	insertCol(String colnum) Insert a blank column into the worksheet.
void	insertRow(int rownum) Insert a blank row into the worksheet.
void	insertRow(int rownum, boolean shiftrows) Insert a blank row into the worksheet.
void	insertRow(int rownum, int copyrow, int flag, boolean shiftrows) Insert a blank row into the worksheet.
void	moveCell(CellHandle c, String addr) Move a cell on this WorkSheet.
void	remove() Remove this WorkSheet from the WorkBook
void	removeCell(CellHandle celldel) Remove a Cell from this WorkSheet.
void	removeCell(String celladdr) Remove a Cell from this WorkSheet.
void	removeCol(String colstr) Remove a Column and all associated Cells from this WorkSheet.
void	removeCol(String colstr, boolean shiftcols) Remove a Column and all associated Cells from this WorkSheet.
void	removeRow(int rownum) Remove a Row and all associated Cells from this WorkSheet.
void	removeRow(int rownum, boolean shiftrows) Remove a Row and all associated Cells from this WorkSheet.
void	setCopyFormatsFromPriorWhenAdding(boolean f) When adding a new Cell to the sheet, ExtenXLS can automatically copy the formatting from the Cell directly above the inserted Cell.
void	setFirstVisibleTab() set this WorkSheet as the first visible tab on the left
void	setFooterText(String t) Set the text for the Footer printed at the bottom of the Worksheet
void	setHeaderText(String t) Set the text for the Header printed at the top of the Worksheet
void	setHidden(boolean b) set whether this sheet is hidden from the user opening the file.
void	setProtected(boolean b)
void	setSelected(boolean b) set whether this sheet is selected upon opening the file.
void	setSheetName(String name) Set the name of the Worksheet.
void	setTabIndex(int idx) set the tab display order of this Worksheet this is a zero based index with zero representing the left-most WorkSheet tab.
void	setVal(String address, double d) Set the double value of the Cell at the given address
void	setVal(String address, int i) Set the int value of the Cell at the given address
void	setVal(String address, Object val) Set the Object value of the Cell at the given address.
void	setVal(String address, String s) Set the String value of the Cell at the given address
void	setVeryHidden(boolean b) set whether this sheet is VERY hidden opening the file.
String	toString() Returns the name of this Sheet.

Field Detail

ROW_KEEP

static final int ROW_KEEP

See Also:

Constant Field Values

ROW_INSERT

static final int ROW_INSERT

See Also:

Constant Field Values

Method Detail

getWorkBook

WorkBook getWorkBook()

getMergedCellsRec

com.extentech.formats.XLS.Mergedcells getMergedCellsRec()

addChart

void addChart(byte[] serialchart,
              String name)

getFirstRow

int getFirstRow()

Get the first row on the Worksheet

Returns:

the Minimum Row Number on the Worksheet

getFirstCol

int getFirstCol()

Get the first column on the Worksheet

Returns:

the Minimum Column Number on the Worksheet

getLastRow

int getLastRow()

Get the last row on the Worksheet

Returns:

the Maximum Row Number on the Worksheet

getLastCol

int getLastCol()

Get the last column on the Worksheet

Returns:

the Maximum Column Number on the Worksheet

setProtected

void setProtected(boolean b)

setVeryHidden

void setVeryHidden(boolean b)

set whether this sheet is VERY hidden opening the file. VERY hidden means users will not be able to unhide the sheet without using VB code.

Parameters:

boolean - b hidden state

getSelected

boolean getSelected()

get whether this sheet is selected upon opening the file.

Returns:

boolean b selected state

getHidden

boolean getHidden()

get whether this sheet is hidden from the user opening the file.

Returns:

boolean b hidden state

setHidden

void setHidden(boolean b)

set whether this sheet is hidden from the user opening the file. if the sheet is selected, the API will set the first visible sheet to selected as you cannot have your selected sheet be hidden. to override this behavior, set your desired sheet to selected after calling this method.

Parameters:

boolean - b hidden state

setFirstVisibleTab

void setFirstVisibleTab()

set this WorkSheet as the first visible tab on the left

getTabIndex

int getTabIndex()

get the tab display order of this Worksheet this is a zero based index with zero representing the left-most WorkSheet tab.

Returns:

int idx the index of the sheet tab

setTabIndex

void setTabIndex(int idx)

set the tab display order of this Worksheet this is a zero based index with zero representing the left-most WorkSheet tab.

Parameters:

int - idx the new index of the sheet tab

setSelected

void setSelected(boolean b)

set whether this sheet is selected upon opening the file.

Parameters:

boolean - b selected value

getCol

ColHandle getCol(int clnum)
                 throws com.extentech.formats.XLS.ColumnNotFoundException

returns the Column at the index position

Returns:

ColHandle the Column

Throws:

com.extentech.formats.XLS.ColumnNotFoundException

getCol

ColHandle getCol(String name)
                 throws com.extentech.formats.XLS.ColumnNotFoundException

returns the Column at the named position

Returns:

ColHandle the Column

Throws:

com.extentech.formats.XLS.ColumnNotFoundException

getColumns

ColHandle[] getColumns()

returns all of the Columns in this WorkSheet

Returns:

ColHandle[] Columns

getColNames

List getColNames()

returns a Vector of Column names

Returns:

CompatibleVector column names

getRowNums

List getRowNums()

returns a Vector of Row numbers

Returns:

CompatibleVector row numbers

getRow

RowHandle getRow(int t)
                 throws com.extentech.formats.XLS.RowNotFoundException

get an a RowHandle for this WorkSheet by number

Parameters:

int - row number to return

Returns:

RowHandle a Row on this WorkSheet

Throws:

com.extentech.formats.XLS.RowNotFoundException

getRows

RowHandle[] getRows()

get an array of all RowHandles for this WorkSheet

Returns:

RowHandle[] all Rows on this WorkSheet

getNumRows

int getNumRows()

Returns the number of rows in this WorkSheet

Returns:

int Number of Rows on this WorkSheet

getNumCols

int getNumCols()

Returns the number of Columns in this WorkSheet

Returns:

int Number of Cols on this WorkSheet

removeCell

void removeCell(CellHandle celldel)

Remove a Cell from this WorkSheet.

Parameters:

CellHandle - to remove

removeCell

void removeCell(String celladdr)

Remove a Cell from this WorkSheet.

Parameters:

String - celladdr - the Address of the Cell to remove

removeRow

void removeRow(int rownum)
               throws com.extentech.formats.XLS.RowNotFoundException

Remove a Row and all associated Cells from this WorkSheet.

Parameters:

int - rownum - the number of the row to remove

Throws:

com.extentech.formats.XLS.RowNotFoundException

removeRow

void removeRow(int rownum,
               boolean shiftrows)
               throws com.extentech.formats.XLS.RowNotFoundException

Remove a Row and all associated Cells from this WorkSheet. Optionally shift all rows below target row up one.

Parameters:

int - rownum - the number of the row to remove

boolean - shiftrows - true will shift all lower rows up one.

Throws:

com.extentech.formats.XLS.RowNotFoundException

removeCol

void removeCol(String colstr)
               throws com.extentech.formats.XLS.ColumnNotFoundException

Remove a Column and all associated Cells from this WorkSheet.

Parameters:

String - colstr - the name of the column to remove

Throws:

com.extentech.formats.XLS.ColumnNotFoundException

removeCol

void removeCol(String colstr,
               boolean shiftcols)
               throws com.extentech.formats.XLS.ColumnNotFoundException

Remove a Column and all associated Cells from this WorkSheet. Optionally shift all cols to the right of this column to the left by one.

Parameters:

String - colstr - the name of the column to remove

boolean - shiftcols - true will shift following cols

Throws:

com.extentech.formats.XLS.ColumnNotFoundException

getSheet

com.extentech.formats.XLS.Sheet getSheet()

Returns the low-level ExtenXLS Sheet.

Returns:

com.extentech.formats.XLS.Sheet

getSheetName

String getSheetName()

Returns the name of the Sheet.

Returns:

String Sheet Name

getSerialBytes

byte[] getSerialBytes()

Returns the Serialized bytes for this WorkSheet. The output of this method can be used to insert a copy of this WorkSheet into another WorkBook using the WorkBook.addWorkSheet(byte[] serialsheet, String NewSheetName) method.

Returns:

byte[] the WorkSheet's Serialized bytes

See Also:

WorkBook.addWorkSheet(byte[] serialsheet, String NewSheetName)

setVal

void setVal(String address,
            Object val)
            throws com.extentech.formats.XLS.CellNotFoundException,
                   com.extentech.formats.XLS.CellTypeMismatchException

Set the Object value of the Cell at the given address.

Parameters:

String - Cell Address (ie: "D14")

Object - new Cell Object value

Throws:

com.extentech.formats.XLS.CellNotFoundException - is thrown if there is no existing Cell at the specified address.

com.extentech.formats.XLS.CellTypeMismatchException

setVal

void setVal(String address,
            double d)
            throws com.extentech.formats.XLS.CellNotFoundException,
                   com.extentech.formats.XLS.CellTypeMismatchException

Set the double value of the Cell at the given address

Parameters:

String - Cell Address (ie: "D14")

double - new Cell double value

address -

d -

Throws:

com.extentech.formats.XLS.CellNotFoundException - is thrown if there is no existing Cell at the specified address.

com.extentech.formats.XLS.CellTypeMismatchException

setVal

void setVal(String address,
            String s)
            throws com.extentech.formats.XLS.CellNotFoundException,
                   com.extentech.formats.XLS.CellTypeMismatchException

Set the String value of the Cell at the given address

Parameters:

String - Cell Address (ie: "D14")

String - new Cell String value

Throws:

com.extentech.formats.XLS.CellNotFoundException - is thrown if there is no existing Cell at the specified address.

com.extentech.formats.XLS.CellTypeMismatchException

setSheetName

void setSheetName(String name)

Set the name of the Worksheet. This method will change the name on the Worksheet's tab as displayed in the WorkBook, as well as all programmatic and internal references to the name. This change takes effect immediately, so all attempts to reference the Worksheet by its previous name will fail.

Parameters:

String - the new name for the Worksheet

setVal

void setVal(String address,
            int i)
            throws com.extentech.formats.XLS.CellNotFoundException,
                   com.extentech.formats.XLS.CellTypeMismatchException

Set the int value of the Cell at the given address

Parameters:

String - Cell Address (ie: "D14")

int - new Cell int value

Throws:

com.extentech.formats.XLS.CellNotFoundException - is thrown if there is no existing Cell at the specified address.

com.extentech.formats.XLS.CellTypeMismatchException

getVal

Object getVal(String address)
              throws com.extentech.formats.XLS.CellNotFoundException

Get the Object value of a Cell. Numeric Cell values will return as type Long, Integer, or Double. String Cell values will return as type String.

Returns:

the value of the Cell as an Object.

Throws:

com.extentech.formats.XLS.CellNotFoundException - is thrown if there is no existing Cell at the specified address.

insertRow

void insertRow(int rownum)

Insert a blank row into the worksheet. Shift all rows below the cell down one. Adding new cells to non-existent rows will automatically create new rows in the file, This method is only necessary to "move" existing cells by inserting empty rows.

Parameters:

rownum - the rownumber to insert

insertRow

void insertRow(int rownum,
               boolean shiftrows)

Insert a blank row into the worksheet. Shift all rows below the cell down one. Adding new cells to non-existent rows will automatically create new rows in the file, After calling this method, setVal() can be used on the newly created cells to update with new values.

Parameters:

rownum - the rownumber to insert

whether - to shift down existing Cells

insertRow

void insertRow(int rownum,
               int copyrow,
               int flag,
               boolean shiftrows)

Insert a blank row into the worksheet. Shift all rows below the cell down one. Adding new cells to non-existent rows will automatically create new rows in the file, After calling this method, setVal() can be used on the newly created cells to update with new values.

Parameters:

rownum - the rownumber to insert

whether - to shift down existing Cells

insertCol

void insertCol(String colnum)

Insert a blank column into the worksheet. Shift all columns to the right of the cell over one. Adding new cells to non-existent columns will automatically create new Columns in the file, This method is only necessary to "move" existing cells by inserting empty columns.

Parameters:

colstr - the Column string to insert

setCopyFormatsFromPriorWhenAdding

void setCopyFormatsFromPriorWhenAdding(boolean f)

When adding a new Cell to the sheet, ExtenXLS can automatically copy the formatting from the Cell directly above the inserted Cell. ie: if set to true, newly added Cell D19 would take its formatting from Cell D18. Default is false

add

CellHandle add(Object obj,
               int row,
               int col)

Add a Cell with the specified value to a WorkSheet. This method determines the Cell type based on type-compatibility of the value. In other words, if the Object cannot be converted safely to a Numeric Object type, then it is treated as a String and a new String value is added to the WorkSheet at the Cell address specified.

Parameters:

obj - the value of the new Cell

int - row the row of the new Cell

int - col the column of the new Cell

add

CellHandle add(Object obj,
               String address)

Add a Cell with the specified value to a WorkSheet. This method determines the Cell type based on type-compatibility of the value. In other words, if the Object cannot be converted safely to a Numeric Object type, then it is treated as a String and a new String value is added to the WorkSheet at the Cell address specified.

Parameters:

obj - the value of the new Cell

address - the address of the new Cell

add

CellHandle add(Date dt,
               String address,
               String fmt)

Add a java.sql.Date Cell to a WorkSheet. You must specify a formatting pattern for the new date, or null for the default ("m/d/yy h:mm".) valid date format patterns "m/d/y" "d-mmm-yy" "d-mmm" "mmm-yy" "h:mm AM/PM" "h:mm:ss AM/PM" "h:mm" "h:mm:ss" "m/d/yy h:mm" "mm:ss" "[h]:mm:ss" "mm:ss.0"

Parameters:

dt - the value of the new java.sql.Date Cell

address - the address of the new java.sql.Date Cell

formatting - pattern the address of the new java.sql.Date Cell

remove

void remove()

Remove this WorkSheet from the WorkBook

getCells

CellHandle[] getCells()

Returns all CellHandles defined on this WorkSheet.

Returns:

Cell[] - the array of Cells in the Sheet

getFormula

FormulaHandle getFormula(String addr)
                         throws com.extentech.formats.XLS.FormulaNotFoundException,
                                com.extentech.formats.XLS.CellNotFoundException

Returns a FormulaHandle for working with the ranges of a formula on a WorkSheet.

Parameters:

addr - the address of the Cell

Throws:

com.extentech.formats.XLS.FormulaNotFoundException - is thrown if there is no existing formula at the specified address.

com.extentech.formats.XLS.CellNotFoundException

getCell

CellHandle getCell(String addr)
                   throws com.extentech.formats.XLS.CellNotFoundException

Returns a CellHandle for working with the value of a Cell on a WorkSheet.

Parameters:

addr - the address of the Cell

Throws:

com.extentech.formats.XLS.CellNotFoundException - is thrown if there is no existing Cell at the specified address.

getCell

CellHandle getCell(int row,
                   int col)
                   throws com.extentech.formats.XLS.CellNotFoundException

Returns a CellHandle for working with the value of a Cell on a WorkSheet.

Parameters:

int - Row the integer row of the Cell

int - Col the integer col of the Cell

Throws:

com.extentech.formats.XLS.CellNotFoundException - is thrown if there is no existing Cell at the specified address.

moveCell

void moveCell(CellHandle c,
              String addr)
              throws com.extentech.formats.XLS.CellPositionConflictException

Move a cell on this WorkSheet.

Parameters:

CellHandle - c - the cell to be moved

String - celladdr - the destination address of the cell

Throws:

com.extentech.formats.XLS.CellPositionConflictException

getFooterText

String getFooterText()

Get the text for the Footer printed at the bottom of the Worksheet

Returns:

String footer text

getHeaderText

String getHeaderText()

Get the text for the Header printed at the top of the Worksheet

Returns:

String header text

setHeaderText

void setHeaderText(String t)

Set the text for the Header printed at the top of the Worksheet

Parameters:

String - header text

setFooterText

void setFooterText(String t)

Set the text for the Footer printed at the bottom of the Worksheet

Parameters:

String - footer text

toString

String toString()

Returns the name of this Sheet.

Overrides:

toString in class Object

See Also:

Object.toString()