WorkSheetHandle (ExtenXLS v10.1-20110808)

Overview

Package

Class

Use

Tree

Deprecated

Index

Help

PREV CLASS NEXT CLASS

FRAMES NO FRAMES

SUMMARY: NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD

com.extentech.ExtenXLS
Class WorkSheetHandle

java.lang.Object
  com.extentech.ExtenXLS.WorkSheetHandle

All Implemented Interfaces:: Handle

public class WorkSheetHandle
extends Object
implements Handle
extends Object
implements Handle

The WorkSheetHandle provides a handle to a Worksheet within an XLS file
and includes convenience methods for working with the Cell values within the sheet.

for example:

WorkBookHandle book = new WorkBookHandle("testxls.xls");
WorkSheetHandle sheet1 = book.getWorkSheet("Sheet1");
CellHandle cell = sheet1.getCell("B22");

to add a cell:

CellHandle cell = sheet1.add("Hello World","C22");

to add a numeric cell:

CellHandle cell = sheet1.add(new Integer(120),"C23");

to add a formula cell:

CellHandle cell = sheet1.add("=PI()","C24");

Version:: 4.1
Author:: John McMahon -- Copyright ©2011Extentech Inc.
See Also:: WorkSheet, WorkBookHandle, CellHandle

Field Summary
`static short`	`ALLOWAUTOFILTER` enhanced protection settings: Use Autofilter
`static short`	`ALLOWDELETECOLUMNS` enhanced protection settings: Delete columns
`static short`	`ALLOWDELETEROWS` enhanced protection settings: Delete rows
`static short`	`ALLOWFORMATCELLS` enhanced protection settings: Format cells
`static short`	`ALLOWFORMATCOLUMNS` enhanced protection settings: Format columns
`static short`	`ALLOWFORMATROWS` enhanced protection settings: Format rows
`static short`	`ALLOWINSERTCOLUMNS` enhanced protection settings: Insert columns
`static short`	`ALLOWINSERTHYPERLINKS` enhanced protection settings: Insert hyperlinks
`static short`	`ALLOWINSERTROWS` enhanced protection settings: Insert rows
`static short`	`ALLOWOBJECTS` enhanced protection settings: Edit Object
`static short`	`ALLOWPIVOTTABLES` enhanced protection settings: Use PivotTable reports
`static short`	`ALLOWSCENARIOS` enhanced protection settings: Edit scenario
`static short`	`ALLOWSELLOCKEDCELLS` enhanced protection settings: Select locked cells
`static short`	`ALLOWSELUNLOCKEDCELLS` enhanced protection settings: Select unlocked cells
`static short`	`ALLOWSORT` enhanced protection settings: Sort
`static int`	`ROW_DELETE`
`static int`	`ROW_DELETE_NO_REFERENCE_UPDATE`
`static int`	`ROW_INSERT` Same as ROW_INSERT_ONCE
`static int`	`ROW_INSERT_MULTI` Insert row multiple times allowed
`static int`	`ROW_INSERT_NO_REFERENCE_UPDATE` Insert row but do not update any cell references affected by insert
`static int`	`ROW_INSERT_ONCE` Insert row one time, multiple calls ignored

Method Summary
`CellHandle`	`add(Date dt, int row, int col, String fmt)` Add a java.sql.Date Cell to a WorkSheet.
`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, int row, int col, int formatId)` 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.
`CellHandle`	`add(Timestamp dt, String address, String fmt)` Add a java.sql.Timestamp Cell to a WorkSheet.
`AutoFilterHandle`	`addAutoFilter(int column)` Adds a new AutoFilter for the specified column (0-based) in this sheet returns a handle to the new AutoFilter
`void`	`addChart(byte[] serialchart, String name, short[] coords)`
`ColHandle`	`addCol(int colNum)` adds the column (col1st, colLast) and returns the new ColHandle
`ColHandle`	`addCol(int c1st, int clast)` Deprecated. use addCol(int)
`CellHandle[]`	`addValidated(Date dt, int row, int col, String fmt)` Add a java.sql.Date Cell to a WorkSheet.
`CellHandle[]`	`addValidated(Date dt, String address, String fmt)` Add a java.sql.Date Cell to a WorkSheet.
`CellHandle[]`	`addValidated(Object obj, int row, int col)` Add a Cell with the specified value to a WorkSheet.
`CellHandle[]`	`addValidated(Object obj, int row, int col, int formatId)` Add a Cell with the specified value to a WorkSheet.
`CellHandle[]`	`addValidated(Object obj, String address)` Add a Cell with the specified value to a WorkSheet.
`CellHandle[]`	`addValidated(Timestamp dt, String address, String fmt)` Add a java.sql.Timestamp Cell to a WorkSheet.
`List`	`calculateAffectedCells(String CellAddress)` Calculates all formulas that reference the cell address passed in.
`boolean`	`checkProtectionPassword(String guess)` Checks whether the given password matches the protection password.
`ConditionalFormatHandle`	`createConditionalFormatHandle(String cellAddress, String operator, String value1, String value2, String format, String firstCondition, String secondCondition)` Create a Conditional Format handle for a cell/range
`CommentHandle`	`createNote(String address, String txt, String author)` Creates a new annotation (Note or Comment) to the worksheeet, attached to a specific cell
`CommentHandle`	`createNote(String address, com.extentech.formats.XLS.Unicodestring txt, String author)` Creates a new annotation (Note or Comment) to the worksheeet, attached to a specific cell The note or comment is a Unicode string, thus it can contain formatting information
`ValidationHandle`	`createValidationHandle(String cellAddress, byte valueType, byte condition, String errorBoxText, String errorBoxTitle, String promptBoxText, String promptBoxTitle, String firstCondition, String secondCondition)` Create a validation handle for a cell/range
`void`	`evaluateAutoFilters()` Updates the Row filter (hidden status) for each row on the sheet by evaluating all AutoFilter conditions
`void`	`extractChartToDirectory(String outdir)` retrieves all charts for this sheet and writes them (in SVG form) to outpdir Filename is in form of: _Chart<#>.svg
`void`	`extractImagesToDirectory(String outdir)` write out all of the images in the Sheet to a directory
`void`	`fastAdd(Object obj, int row, int col, int formatId)` Fast-adds a Cell with the specified value to a WorkSheet.
`void`	`freezeCol(int col)` freezes the cols starting at the specified column and creating a scrollable sheet to the right
`void`	`freezeRow(int row)` freezes the rows starting at the specified row and creating a scrollable sheet below this row
`com.extentech.toolkit.CompatibleVector`	`getAddedrows()`
`ValidationHandle[]`	`getAllValidationHandles()` Return all validation handles that refer to this worksheet
`AutoFilterHandle[]`	`getAutoFilterHandles()` Returns a list of all AutoFilterHandles on this sheet An AutoFilterHandle allows access and manipulation of AutoFilters on the sheet
`CellHandle`	`getCell(int row, int col)` Returns a CellHandle for working with the value of a Cell on a WorkSheet.
`CellHandle`	`getCell(int row, int col, boolean cache)` 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 ColHandle for the column at index position the column index is zero based ie: column A = 0
`ColHandle`	`getCol(String name)` returns the Column at the named position
`List`	`getColNames()` returns a List of Column names
`ColHandle[]`	`getColumns()` returns all of the Columns in this WorkSheet
`CommentHandle[]`	`getCommentHandles()` returns an array of all CommentHandles that exist in the sheet
`ConditionalFormatHandle[]`	`getConditionalFormatHandles()` returns an array of FormatHandles for the ConditionalFormats applied to this cell
`boolean`	`getEnhancedProtection(int protectionOption)` returns true if the indicated Enhanced Protection Setting is turned on
`boolean`	`getFastCellAdds()` Get the current fast add cell mode for 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`	`getHashedProtectionPassword()` Gets the hash of the sheet protection password.
`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.
`ImageHandle`	`getImage(String name)` Get a handle to all of the images in this worksheet
`ImageHandle[]`	`getImages()` Get a handle to all of the images in this worksheet
`int`	`getLastCol()` Get the last column on the Worksheet
`int`	`getLastRow()` Get the last row on the Worksheet
`boolean`	`getManualGridLineColor()` Get whether to use manual grid color in the output sheet.
`com.extentech.formats.XLS.Boundsheet`	`getMysheet()` FOR internal Use Only!
`NameHandle`	`getNamedRangeInScope(String rangename)` Returns a Named Range Handle if it exists in the specified scope.
`NameHandle[]`	`getNamedRangesInScope()` Returns all Named Range Handles scoped to this Worksheet.
`int`	`getNumCols()` Returns the number of Columns in this WorkSheet
`int`	`getNumImages()` returns the actual amount of images contained in the sheet and is determined by imageMap
`int`	`getNumRows()` Returns the number of rows in this WorkSheet
`String`	`getPrintArea()` Get the print area set for this WorkSheetHandle.
`PrinterSettingsHandle`	`getPrinterSettings()` Get the printer settings handle for this WorkSheetHandle.
`boolean`	`getProtected()` Returns whether the sheet is protected.
`RowHandle`	`getRow(int t)` returns the RowHandle for the row at index position the row index is zero based ie: Excel row 1 = 0
`Map`	`getRowMap()` get an array of BIFFREC Rows
`List`	`getRowNums()` returns a List 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.
`String`	`getSheetName()` Returns the name of the Sheet.
`int`	`getSheetNum()` Returns the index of the Sheet.
`boolean`	`getShowFormulaResults()` Set whether to show calculated formula results in the output sheet.
`boolean`	`getShowGridlines()` Get whether to show gridlines in the output sheet.
`boolean`	`getShowInNormalView()` Get whether to show normal view or page break preview view in the output sheet.
`boolean`	`getShowOutlineSymbols()` Get whether to show outline symbols in the output sheet.
`boolean`	`getShowSheetHeaders()` Get whether to show sheet headers in the output sheet.
`boolean`	`getShowZeroValues()` Get whether to show zero values in the output sheet.
`int`	`getSplitColLocation()` gets the column number (0-based)that the sheet split is locaated on; if the sheet is not split, returns -1
`int`	`getSplitLocation()` Gets the twips split location returns -1
`int`	`getSplitRowLocation()` Gets the row number (0 based) that the sheet split is located on.
`int`	`getTabIndex()` get the tab display order of this Worksheet this is a zero based index with zero representing the left-most WorkSheet tab.
`String`	`getTopLeftCell()` if this sheet has freeze panes, return the address of the top left cell otherwise, return null
`boolean`	`getUseCache()`
`Object`	`getVal(String address)` Get the Object value of a Cell.
`ValidationHandle`	`getValidationHandle(String cellAddress)` Get a validation handle for the cell address passed in.
`boolean`	`getVeryHidden()` return the 'veryhidden' state of the sheet
`WorkBookHandle`	`getWorkBook()` Returns the WorkBookHandle for this Sheet
`float`	`getZoom()` gets the zoom for the sheet
`boolean`	`hasDataValidations()` return true if sheet contains data validations
`boolean`	`hasFrozenPanes()` Get whether there are freeze panes in the output sheet.
`void`	`insertCol(int colnum)` Insert a blank column into the worksheet.
`void`	`insertCol(String colnum)` Insert a blank column into the worksheet.
`void`	`insertImage(ImageHandle im)` insert an image into this worksheet
`boolean`	`insertRow(int rownum)` Insert a blank row into the worksheet.
`boolean`	`insertRow(int rownum, boolean shiftrows)` Insert a blank row into the worksheet.
`void`	`insertRow(int rownum, int flag)` Insert a blank row into the worksheet.
`CellHandle[]`	`insertRow(int row1, Object[] data)` Insert a row of Objects into the worksheet.
`CellHandle[]`	`insertRow(int rownum, Object[] data, boolean shiftrows)` Insert a row of Objects into the worksheet.
`boolean`	`insertRow(int rownum, RowHandle copyRow, int flag, boolean shiftrows)` Insert a blank row into the worksheet.
`boolean`	`insertRowAt(int rownum, 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 NOTE: will throw a WorkBookException if the last sheet is removed.
`void`	`removeAutoFilters()` Removes all AutoFilters from this sheet As a consequence, all previously hidden rows are shown or unhidden
`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`	`removeImage(ImageHandle img)` removes an Image from the Spreadsheet Jan 22, 2010
`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`	`removeRow(int rownum, int flag)` 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`	`setEnhancedProtection(int protectionOption, boolean set)` Sets the worksheet enhanced protection option
`void`	`setFastCellAdds(boolean fastadds)` Toggle fast cell add mode.
`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`	`setHasFrozenPanes(boolean b)` Set whether there are freeze panes in the output sheet.
`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`	`setManualGridLineColor(boolean b)` Set whether to use manual grid color in the output sheet.
`void`	`setPrintArea(CellRange printarea)` Sets the print area for the worksheet sets the printarea as a CellRange
`void`	`setProtected(boolean protect)` Sets whether the worksheet is protected.
`void`	`setProtected(boolean protect, String password)` Sets whether the worksheet is protected.
`void`	`setProtectionPassword(String password)` Sets the password used to unlock the sheet when it is protected.
`void`	`setProtectionPasswordHashed(String hash)` Sets the password used to unlock the sheet when it is protected.
`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`	`setShowFormulaResults(boolean b)`
`void`	`setShowGridlines(boolean b)` Set whether to show gridlines in the output sheet.
`void`	`setShowInNormalView(boolean b)` Set whether to show normal view or page break preview view in the output sheet.
`void`	`setShowOutlineSymbols(boolean b)` Set whether to show outline symbols in the output sheet.
`void`	`setShowSheetHeaders(boolean b)` Set whether to show sheet headers in the output sheet.
`void`	`setShowZeroValues(boolean b)` Set whether to show zero values in the output sheet.
`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.
`void`	`setZoom(float zm)` sets the zoom for the sheet
`void`	`splitCol(int col, int splitpos)` splits the worksheet at column col for nCols Note: unfreezes panes if frozen
`void`	`splitRow(int row, int splitpos)` splits the worksheet at row for nRows Note: unfreezes panes if frozen
`String`	`toString()` Returns the name of this Sheet.

Methods inherited from class java.lang.Object
`equals, getClass, hashCode, notify, notifyAll, wait, wait, wait`

Field Detail

ROW_INSERT_MULTI

public static final int ROW_INSERT_MULTI

Insert row multiple times allowed

See Also:: Constant Field Values

ROW_INSERT

public static final int ROW_INSERT

Same as ROW_INSERT_ONCE

See Also:: Constant Field Values

ROW_INSERT_ONCE

public static final int ROW_INSERT_ONCE

Insert row one time, multiple calls ignored

See Also:: Constant Field Values

ROW_INSERT_NO_REFERENCE_UPDATE

public static final int ROW_INSERT_NO_REFERENCE_UPDATE

Insert row but do not update any cell references affected by insert

See Also:: Constant Field Values

ROW_DELETE

public static final int ROW_DELETE

See Also:: Constant Field Values

ROW_DELETE_NO_REFERENCE_UPDATE

public static final int ROW_DELETE_NO_REFERENCE_UPDATE

See Also:: Constant Field Values

ALLOWOBJECTS

public static final short ALLOWOBJECTS

enhanced protection settings: Edit Object

See Also:: Constant Field Values

ALLOWSCENARIOS

public static final short ALLOWSCENARIOS

enhanced protection settings: Edit scenario

See Also:: Constant Field Values

ALLOWFORMATCELLS

public static final short ALLOWFORMATCELLS

enhanced protection settings: Format cells

See Also:: Constant Field Values

ALLOWFORMATCOLUMNS

public static final short ALLOWFORMATCOLUMNS

enhanced protection settings: Format columns

See Also:: Constant Field Values

ALLOWFORMATROWS

public static final short ALLOWFORMATROWS

enhanced protection settings: Format rows

See Also:: Constant Field Values

ALLOWINSERTCOLUMNS

public static final short ALLOWINSERTCOLUMNS

enhanced protection settings: Insert columns

See Also:: Constant Field Values

ALLOWINSERTROWS

public static final short ALLOWINSERTROWS

enhanced protection settings: Insert rows

See Also:: Constant Field Values

ALLOWINSERTHYPERLINKS

public static final short ALLOWINSERTHYPERLINKS

enhanced protection settings: Insert hyperlinks

See Also:: Constant Field Values

ALLOWDELETECOLUMNS

public static final short ALLOWDELETECOLUMNS

enhanced protection settings: Delete columns

See Also:: Constant Field Values

ALLOWDELETEROWS

public static final short ALLOWDELETEROWS

enhanced protection settings: Delete rows

See Also:: Constant Field Values

ALLOWSELLOCKEDCELLS

public static final short ALLOWSELLOCKEDCELLS

enhanced protection settings: Select locked cells

See Also:: Constant Field Values

ALLOWSORT

public static final short ALLOWSORT

enhanced protection settings: Sort

See Also:: Constant Field Values

ALLOWAUTOFILTER

public static final short ALLOWAUTOFILTER

enhanced protection settings: Use Autofilter

See Also:: Constant Field Values

ALLOWPIVOTTABLES

public static final short ALLOWPIVOTTABLES

enhanced protection settings: Use PivotTable reports

See Also:: Constant Field Values

ALLOWSELUNLOCKEDCELLS

public static final short ALLOWSELUNLOCKEDCELLS

enhanced protection settings: Select unlocked cells

See Also:: Constant Field Values

Method Detail

addChart

public void addChart(byte[] serialchart,
                     String name,
                     short[] coords)

getFirstRow

public int getFirstRow()

Get the first row on the Worksheet

Returns:: the Minimum Row Number on the Worksheet

getFirstCol

public int getFirstCol()

Get the first column on the Worksheet

Returns:: the Minimum Column Number on the Worksheet

getLastRow

public int getLastRow()

Get the last row on the Worksheet

Returns:: the Maximum Row Number on the Worksheet

getLastCol

public int getLastCol()

Get the last column on the Worksheet

Returns:: the Maximum Column Number on the Worksheet

setProtected

public void setProtected(boolean protect,
                         String password)
                  throws WorkBookException

Sets whether the worksheet is protected. If protect is true, the worksheet will be protected and the password will be set to password. If it's false, the worksheet will be unprotected and the password will be removed.

Parameters:: protect - whether the worksheet should be protected; password - the password to set if protect is true. ignored when protect is false.
Throws:: WorkBookException - never. This used to be thrown when unprotecting if the password was incorrect.

setProtected

public void setProtected(boolean protect)

Sets whether the worksheet is protected.

Parameters:: protect - whether worksheet protection should be enabled

setProtectionPassword

public void setProtectionPassword(String password)

Sets the password used to unlock the sheet when it is protected.

Parameters:: password - the clear text of the password to be applied or null to remove the existing password

setProtectionPasswordHashed

public void setProtectionPasswordHashed(String hash)

Sets the password used to unlock the sheet when it is protected. This method is useful in combination with getHashedProtectionPassword() to copy the password from one worksheet to another.

Parameters:: hash - the hash of the protection password to be applied or null to remove the existing password

getHashedProtectionPassword

public String getHashedProtectionPassword()

Gets the hash of the sheet protection password. This method returns the hashed password as stored in the file. It has been passed through a one-way hash function. It is therefore not possible to recover the actual password. You can, however, use setProtectionPasswordHashed(java.lang.String) to apply the same password to another worksheet.

Returns:: the password hash or "0000" if the sheet doesn't have a password

getProtected

public boolean getProtected()

Returns whether the sheet is protected. Note that this is separate from whether the sheet has a protection password. It can be protected without a password or have a password but not be protected.

Returns:: whether protection is enabled for the sheet

checkProtectionPassword

public boolean checkProtectionPassword(String guess)

Checks whether the given password matches the protection password.

Parameters:: guess - the password to be checked against the stored hash
Returns:: whether the given password matches the stored hash

setEnhancedProtection

public void setEnhancedProtection(int protectionOption,
                                  boolean set)

Sets the worksheet enhanced protection option

Parameters:: int - protectionOption
See Also:: options

getEnhancedProtection

public boolean getEnhancedProtection(int protectionOption)

returns true if the indicated Enhanced Protection Setting is turned on

Parameters:: protectionOption -
Returns:: boolean true if the indicated Enhanced Protection Setting is turned on
See Also:: options

setVeryHidden

public 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

public boolean getSelected()

get whether this sheet is selected upon opening the file.

Returns:: boolean b selected state

getHidden

public boolean getHidden()

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

Returns:: boolean b hidden state

getVeryHidden

public boolean getVeryHidden()

return the 'veryhidden' state of the sheet

Returns:

setHidden

public 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

public void setFirstVisibleTab()

set this WorkSheet as the first visible tab on the left

getTabIndex

public 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

public 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

public void setSelected(boolean b)

set whether this sheet is selected upon opening the file.

Parameters:: boolean - b selected value

getCol

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

returns the ColHandle for the column at index position the column index is zero based ie: column A = 0

Returns:: ColHandle the Column
Throws:: com.extentech.formats.XLS.ColumnNotFoundException

addCol

public ColHandle addCol(int c1st,
                        int clast)

Deprecated. use addCol(int)

adds the column (col1st, colLast) and returns the new ColHandle

Parameters:: c1st -; clast -
Returns:

addCol

public ColHandle addCol(int colNum)

adds the column (col1st, colLast) and returns the new ColHandle

Parameters:: colNum, - zero based number of the column
Returns:: ColHandle

getCol

public 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

public ColHandle[] getColumns()

returns all of the Columns in this WorkSheet

Returns:: ColHandle[] Columns

getColNames

public List getColNames()

returns a List of Column names

Returns:: List column names

getRowNums

public List getRowNums()

returns a List of Row numbers

Returns:: List of row numbers

getRow

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

returns the RowHandle for the row at index position the row index is zero based ie: Excel row 1 = 0

Parameters:: int - row number to return
Returns:: RowHandle a Row on this WorkSheet
Throws:: com.extentech.formats.XLS.RowNotFoundException

getRows

public RowHandle[] getRows()

get an array of all RowHandles for this WorkSheet

Returns:: RowHandle[] all Rows on this WorkSheet

getRowMap

public Map getRowMap()

get an array of BIFFREC Rows

Returns:: RowHandle[] all Rows on this WorkSheet

getNumRows

public int getNumRows()

Returns the number of rows in this WorkSheet

Returns:: int Number of Rows on this WorkSheet

getNumCols

public int getNumCols()

Returns the number of Columns in this WorkSheet

Returns:: int Number of Cols on this WorkSheet

removeCell

public void removeCell(CellHandle celldel)

Remove a Cell from this WorkSheet.

Parameters:: CellHandle - to remove

removeImage

public void removeImage(ImageHandle img)

removes an Image from the Spreadsheet Jan 22, 2010

Parameters:: img -

removeCell

public void removeCell(String celladdr)

Remove a Cell from this WorkSheet.

Parameters:: String - celladdr - the Address of the Cell to remove

removeRow

public 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

removeRow

public void removeRow(int rownum)
               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 uses default row deletion rules regarding updating references
Throws:: com.extentech.formats.XLS.RowNotFoundException

removeRow

public void removeRow(int rownum,
                      int flag)
               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; int - flag - controls whether row deletions updates references as well ...
Throws:: com.extentech.formats.XLS.RowNotFoundException

removeCol

public 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

public 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

getSheetNum

public int getSheetNum()

Returns the index of the Sheet.

Returns:: String Sheet Name

getNamedRangesInScope

public NameHandle[] getNamedRangesInScope()

Returns all Named Range Handles scoped to this Worksheet. Note this will not include workbook scoped named ranges

Returns:: NameHandle[] all of the Named ranges that are scoped to the present worksheet

getNamedRangeInScope

public NameHandle getNamedRangeInScope(String rangename)
                                throws com.extentech.formats.XLS.CellNotFoundException

Returns a Named Range Handle if it exists in the specified scope. This can be used to distinguish between multiple named ranges with the same name but differing scopes

Returns:: NameHandle a Named range in the Worksheet that exists in the scope
Throws:: com.extentech.formats.XLS.CellNotFoundException

getSheetName

public String getSheetName()

Returns the name of the Sheet.

Returns:: String Sheet Name

getSerialBytes

public 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 WorkBookHandle.addWorkSheet(byte[] serialsheet, String NewSheetName) method.

Returns:: byte[] the WorkSheet's Serialized bytes
See Also:: WorkBookHandle.addWorkSheet(byte[] serialsheet, String NewSheetName)

getUseCache

public boolean getUseCache()

Returns:: setting on whether to use cache or not

setVal

public 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

public 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

public 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

public 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

public 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

public 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

public CellHandle[] insertRow(int row1,
                              Object[] data)

Insert a row of Objects into the worksheet. Automatically shifts all rows below the cell down one. Method takes an array of Objects to insert into the rows. Object array must match columns in number starting with column A. For emptly cells, put a null Object reference in your array. example: Object[] newCellHandles = { null, // col A "Hello", // col B new Integer(120), // col C "=sum(A1+B1)", // col D null, // col E null, // col F "World" // col G }; CellHandle ret = sheet.insertRow(newCellHandles, 1, true); if(ret !=null) Logger.log("It worked");

Parameters:: an - array of Objects to insert into the new row; rownum - the rownumber to insert; whether - to shift down existing Cells

insertRow

public boolean 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

public void insertRow(int rownum,
                      int flag)

Parameters:: rownum - the rownumber to insert; flag - row insertion rule

insertRowAt

public boolean insertRowAt(int rownum,
                           boolean shiftrows)

Insert a blank row into the worksheet. Shift all rows below the cell down one. This method differs from insertRow in that it can be used to repeatedly insert rows at the same row index. 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
Returns:: whether the insert was successful

insertRow

public boolean 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 (NOTE: rownum is 0-based); whether - to shift down existing Cells
Returns:: whether the insert was successful

insertRow

public boolean insertRow(int rownum,
                         RowHandle copyRow,
                         int flag,
                         boolean shiftrows)

Parameters:: rownum - the rownumber to insert; whether - to shift down existing Cells

insertRow

public CellHandle[] insertRow(int rownum,
                              Object[] data,
                              boolean shiftrows)

Insert a row of Objects into the worksheet. Shift all rows below the cell down one. Method takes an array of Objects to insert into the rows. Object array must match columns in number starting with column A. For emptly cells, put a null Object reference in your array. example: Object[] newCellHandles = { null, // col A "Hello", // col B new Integer(120), // col C "=sum(A1+B1)", // col D null, // col E null, // col F "World" // col G }; boolean okay = sheet.insertRow(newCellHandles, 1, true); if(okay)Logger.log("It worked");

Parameters:: an - array of Objects to insert into the new row; rownum - the rownumber to insert; whether - to shift down existing Cells

getConditionalFormatHandles

public ConditionalFormatHandle[] getConditionalFormatHandles()

returns an array of FormatHandles for the ConditionalFormats applied to this cell

Returns:: an array of FormatHandles, one for each of the Conditional Formatting rules

getWorkBook

public WorkBookHandle getWorkBook()

Returns the WorkBookHandle for this Sheet

Returns:

getImage

public ImageHandle getImage(String name)
                     throws com.extentech.formats.XLS.ImageNotFoundException

Get a handle to all of the images in this worksheet

Returns:
Throws:: com.extentech.formats.XLS.ImageNotFoundException

getImages

public ImageHandle[] getImages()

Get a handle to all of the images in this worksheet

Returns:

getNumImages

public int getNumImages()

returns the actual amount of images contained in the sheet and is determined by imageMap

Returns:

extractImagesToDirectory

public void extractImagesToDirectory(String outdir)

write out all of the images in the Sheet to a directory

Parameters:: imageoutput - directory

extractChartToDirectory

public void extractChartToDirectory(String outdir)

retrieves all charts for this sheet and writes them (in SVG form) to outpdir
Filename is in form of: _Chart<#>.svg

Parameters:: outdir - String output folder

insertImage

public void insertImage(ImageHandle im)

insert an image into this worksheet

Parameters:: im - -- the ImageHandle to insert
See Also:: ImageHandle

insertCol

public void insertCol(int 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:: colnum - the zero-based (0='A') Column to insert

insertCol

public void insertCol(String colnum)

Parameters:: colstr - the Column string to insert

setCopyFormatsFromPriorWhenAdding

public 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

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

Parameters:: obj - the value of the new Cell; int - row the row of the new Cell; int - col the column of the new Cell

addValidated

public CellHandle[] addValidated(Object obj,
                                 int row,
                                 int col)
                          throws com.extentech.formats.XLS.ValidationException

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. If a validation record for the cell exists the validation is checked for a correct value, if the value does not pass the validation a ValidationException will be thrown

Parameters:: obj - the value of the new Cell; int - row the row of the new Cell; int - col the column of the new Cell
Throws:: com.extentech.formats.XLS.ValidationException

addValidated

public CellHandle[] addValidated(Object obj,
                                 int row,
                                 int col,
                                 int formatId)
                          throws com.extentech.formats.XLS.ValidationException

Add a Cell with the specified value to a WorkSheet. This method determines the Cell type based on type-compatibility of the value. Further, this method allows passing in a format id 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. If a validation record for the cell exists the validation is checked for a correct value, if the value does not pass the validation a ValidationException will be thrown

Parameters:: obj - the value of the new Cell; int - row the row of the new Cell; int - col the column of the new Cell; int - the format id to apply to this cell
Throws:: com.extentech.formats.XLS.ValidationException

add

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

Parameters:: obj - the value of the new Cell; int - row the row of the new Cell; int - col the column of the new Cell; int - the format id to apply to this cell

fastAdd

public void fastAdd(Object obj,
                    int row,
                    int col,
                    int formatId)

Fast-adds a Cell with the specified value to a WorkSheet. This method determines the Cell type based on type-compatibility of the value. Further, this method allows passing in a format id 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; int - the format id to apply to this cell

setFastCellAdds

public void setFastCellAdds(boolean fastadds)

Toggle fast cell add mode. Set to true to turn off checking for existing cells, conditional formats and merged ranges in order to accelerate adding new cells

Parameters:: fastadds - whether to disable checking for existing cells and

getFastCellAdds

public boolean getFastCellAdds()

Get the current fast add cell mode for this worksheet

add

public CellHandle add(Object obj,
                      String address)

Parameters:: obj - the value of the new Cell; address - the address of the new Cell

addValidated

public CellHandle[] addValidated(Object obj,
                                 String address)
                          throws com.extentech.formats.XLS.ValidationException

Parameters:: obj - the value of the new Cell; address - the address of the new Cell
Throws:: com.extentech.formats.XLS.ValidationException

add

public CellHandle add(Timestamp dt,
                      String address,
                      String fmt)

Add a java.sql.Timestamp Cell to a WorkSheet. Will create a default format of: "m/d/yyyy h:mm:ss" if none is specified.

Parameters:: dt - the value of the new java.sql.Timestamp Cell; address - the address of the new java.sql.Date Cell; formatting - pattern the address of the new java.sql.Date Cell

addValidated

public CellHandle[] addValidated(Timestamp dt,
                                 String address,
                                 String fmt)
                          throws com.extentech.formats.XLS.ValidationException

Add a java.sql.Timestamp Cell to a WorkSheet. Will create a default format of: "m/d/yyyy h:mm:ss" if none is specified. If a validation record for the cell exists the validation is checked for a correct value, if the value does not pass the validation a ValidationException will be thrown

Parameters:: dt - the value of the new java.sql.Timestamp Cell; address - the address of the new java.sql.Date Cell; formatting - pattern the address of the new java.sql.Date Cell
Throws:: com.extentech.formats.XLS.ValidationException

add

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

Parameters:: dt - the value of the new java.sql.Date Cell; row - to add the date; col - to add the date; formatting - pattern the address of the new java.sql.Date Cell

addValidated

public CellHandle[] addValidated(Date dt,
                                 String address,
                                 String fmt)
                          throws com.extentech.formats.XLS.ValidationException

Parameters:: dt - the value of the new java.sql.Date Cell; row - to add the date; col - to add the date; formatting - pattern the address of the new java.sql.Date Cell
Throws:: com.extentech.formats.XLS.ValidationException

add

public CellHandle add(Date dt,
                      int row,
                      int col,
                      String fmt)

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

addValidated

public CellHandle[] addValidated(Date dt,
                                 int row,
                                 int col,
                                 String fmt)
                          throws com.extentech.formats.XLS.ValidationException

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" If a validation record for the cell exists the validation is checked for a correct value, if the value does not pass the validation a ValidationException will be thrown

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
Throws:: com.extentech.formats.XLS.ValidationException

remove

public void remove()

Remove this WorkSheet from the WorkBook NOTE: will throw a WorkBookException if the last sheet is removed. This results in an invalid output file.

getCells

public CellHandle[] getCells()

Returns all CellHandles defined on this WorkSheet.

Returns:: CellHandle[] - the array of Cells in the Sheet

getFormula

public 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

public 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

public 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. returns a new CellHandle with each call use caching method getCell(int row, int col, boolean cache) to control caching of CellHandles.

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.

getCell

public CellHandle getCell(int row,
                          int col,
                          boolean cache)
                   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; boolean - whether to cache or return a new CellHandle each call
Throws:: com.extentech.formats.XLS.CellNotFoundException - is thrown if there is no existing Cell at the specified address.

moveCell

public 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

public String getFooterText()

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

Returns:: String footer text

getHeaderText

public String getHeaderText()

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

Returns:: String header text

getPrintArea

public String getPrintArea()

Get the print area set for this WorkSheetHandle. If no print area is set return null;

getPrinterSettings

public PrinterSettingsHandle getPrinterSettings()

Get the printer settings handle for this WorkSheetHandle.

setPrintArea

public void setPrintArea(CellRange printarea)

Sets the print area for the worksheet sets the printarea as a CellRange

Parameters:: printarea -

setHeaderText

public void setHeaderText(String t)

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

Parameters:: String - header text

setFooterText

public void setFooterText(String t)

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

Parameters:: String - footer text

toString

public String toString()

Returns the name of this Sheet.

Overrides:: toString in class Object

See Also:: Object.toString()

getMysheet

public com.extentech.formats.XLS.Boundsheet getMysheet()

FOR internal Use Only!

Returns:: Returns the low-level sheet record.

calculateAffectedCells

public List calculateAffectedCells(String CellAddress)

Calculates all formulas that reference the cell address passed in. Please note that these cells have already been calculated, so in order to get their values without re-calculating them Extentech suggests setting the book level non-calculation flag, ie book.setFormulaCalculationMode(WorkBookHandle.CALCULATE_EXPLICIT) or FormulaHandle.getCachedVal()

Returns:: List of of calculated cells

getShowFormulaResults

public boolean getShowFormulaResults()

Set whether to show calculated formula results in the output sheet.

Returns:: boolean whether to show calculated formula results

setShowFormulaResults

public void setShowFormulaResults(boolean b)

getShowGridlines

public boolean getShowGridlines()

Get whether to show gridlines in the output sheet.

Parameters:: boolean - whether to show gridlines

setShowGridlines

public void setShowGridlines(boolean b)

Set whether to show gridlines in the output sheet.

getShowSheetHeaders

public boolean getShowSheetHeaders()

Get whether to show sheet headers in the output sheet.

Returns:: boolean whether to show sheet headers

setShowSheetHeaders

public void setShowSheetHeaders(boolean b)

Set whether to show sheet headers in the output sheet.

Parameters:: boolean - whether to show sheet headers

getShowZeroValues

public boolean getShowZeroValues()

Get whether to show zero values in the output sheet.

Returns:: boolean whether to show zero values

setShowZeroValues

public void setShowZeroValues(boolean b)

Set whether to show zero values in the output sheet.

getShowOutlineSymbols

public boolean getShowOutlineSymbols()

Get whether to show outline symbols in the output sheet.

Returns:: boolean whether to outline symbols

setShowOutlineSymbols

public void setShowOutlineSymbols(boolean b)

Set whether to show outline symbols in the output sheet.

Parameters:: boolean - whether to show outline symbols

getShowInNormalView

public boolean getShowInNormalView()

Get whether to show normal view or page break preview view in the output sheet.

Returns:: boolean whether to show normal view or page break preview view

setShowInNormalView

public void setShowInNormalView(boolean b)

Set whether to show normal view or page break preview view in the output sheet.

Parameters:: boolean - whether to show normal view or page break preview view

hasFrozenPanes

public boolean hasFrozenPanes()

Get whether there are freeze panes in the output sheet.

Returns:: boolean whether there are freeze panes

setHasFrozenPanes

public void setHasFrozenPanes(boolean b)

Set whether there are freeze panes in the output sheet.

Parameters:: boolean - whether there are freeze panes

setZoom

public void setZoom(float zm)

sets the zoom for the sheet

Parameters:: the - zoom as a float percentage (.25 = 25%)

getTopLeftCell

public String getTopLeftCell()

if this sheet has freeze panes, return the address of the top left cell otherwise, return null

Returns:

getZoom

public float getZoom()

gets the zoom for the sheet

Returns:: the zoom as a float percentage (.25 = 25%)

freezeRow

public void freezeRow(int row)

freezes the rows starting at the specified row and creating a scrollable sheet below this row

Parameters:: row - the row to start the freeze

freezeCol

public void freezeCol(int col)

freezes the cols starting at the specified column and creating a scrollable sheet to the right

Parameters:: col - the col to start the freeze

splitCol

public void splitCol(int col,
                     int splitpos)

splits the worksheet at column col for nCols Note: unfreezes panes if frozen

Parameters:: col - col start col to split; splitpos - position of the horizontal split

splitRow

public void splitRow(int row,
                     int splitpos)

splits the worksheet at row for nRows Note: unfreezes panes if frozen

Parameters:: row - start row to split; splitpos - position of the vertical split

getSplitRowLocation

public int getSplitRowLocation()

Gets the row number (0 based) that the sheet split is located on. If the sheet is not split returns -1

Returns:

getSplitColLocation

public int getSplitColLocation()

gets the column number (0-based)that the sheet split is locaated on; if the sheet is not split, returns -1

Returns:: 0-based index of split column, if any

getSplitLocation

public int getSplitLocation()

Gets the twips split location returns -1

Returns:

getManualGridLineColor

public boolean getManualGridLineColor()

Get whether to use manual grid color in the output sheet.

Returns:: boolean whether to use manual grid color

setManualGridLineColor

public void setManualGridLineColor(boolean b)

Set whether to use manual grid color in the output sheet.

Parameters:: boolean - whether to use manual grid color

getAddedrows

public com.extentech.toolkit.CompatibleVector getAddedrows()

createConditionalFormatHandle

public ConditionalFormatHandle createConditionalFormatHandle(String cellAddress,
                                                             String operator,
                                                             String value1,
                                                             String value2,
                                                             String format,
                                                             String firstCondition,
                                                             String secondCondition)

Create a Conditional Format handle for a cell/range

Parameters:: cellAddress - without sheetname. Can also be a range, such as A1:B5; qualifier - = maps to CONDITION_* bytes in ConditionalFormatHandle; value1 - = the error message; value2 - = the error title; format - = the initial format string to use with the condition; firstCondition - = formula string; secondCondition - = 2nd formula string (optional)
Returns:

createNote

public CommentHandle createNote(String address,
                                String txt,
                                String author)

Creates a new annotation (Note or Comment) to the worksheeet, attached to a specific cell

Parameters:: address - -- address to attach; txt - -- text of note; author - -- name of author
Returns:: NoteHandle - handle which allows access to the Note object
See Also:: CommentHandle

createNote

public CommentHandle createNote(String address,
                                com.extentech.formats.XLS.Unicodestring txt,
                                String author)

Creates a new annotation (Note or Comment) to the worksheeet, attached to a specific cell
The note or comment is a Unicode string, thus it can contain formatting information

Parameters:: address - -- address to attach; txt - -- Unicode string of note with Formatting; author - -- name of author
Returns:: NoteHandle - handle which allows access to the Note object
See Also:: CommentHandle

getCommentHandles

public CommentHandle[] getCommentHandles()

returns an array of all CommentHandles that exist in the sheet

Returns:

getValidationHandle

public ValidationHandle getValidationHandle(String cellAddress)

Get a validation handle for the cell address passed in. If the validation is for a range, the handle returned will modify the entire range, not just the cell address passed in. Returns null if a validation does not exist at the specified location

Parameters:: cell - address String

createValidationHandle

public ValidationHandle createValidationHandle(String cellAddress,
                                               byte valueType,
                                               byte condition,
                                               String errorBoxText,
                                               String errorBoxTitle,
                                               String promptBoxText,
                                               String promptBoxTitle,
                                               String firstCondition,
                                               String secondCondition)

Create a validation handle for a cell/range

Parameters:: cellAddress - without sheetname. Can also be a range, such as A1:B5; valueType - = maps to VALUE_* bytes in ValidationHandle; condition - = maps to CONDITION_* bytes in ValidationHandle; errorBoxText - = the error message; errorBoxTitle - = the error title; promptBoxText - = the prompt (hover) message; promptBoxTitle - = the prompt (hover) title; firstCondition - = formula string, seeValidationHandle.setFirstCondition; secondCondition - = seeValidationHandle.setSecondCondition, this can be left null for validations that do not require a second argument.
Returns:

getAllValidationHandles

public ValidationHandle[] getAllValidationHandles()

Return all validation handles that refer to this worksheet

Returns:: array of all validationhandles valid for this worksheet

hasDataValidations

public boolean hasDataValidations()

return true if sheet contains data validations

Returns:: boolean

getAutoFilterHandles

public AutoFilterHandle[] getAutoFilterHandles()

Returns a list of all AutoFilterHandles on this sheet
An AutoFilterHandle allows access and manipulation of AutoFilters on the sheet

Returns:: array of AutoFilterHandles if any exist on sheet, null otherwise

addAutoFilter

public AutoFilterHandle addAutoFilter(int column)

Adds a new AutoFilter for the specified column (0-based) in this sheet
returns a handle to the new AutoFilter

Parameters:: int - column - column number to add an AutoFilter to
Returns:: AutoFilterHandle

removeAutoFilters

public void removeAutoFilters()

Removes all AutoFilters from this sheet
As a consequence, all previously hidden rows are shown or unhidden

evaluateAutoFilters

public void evaluateAutoFilters()

Updates the Row filter (hidden status) for each row on the sheet by evaluating all AutoFilter conditions

NOTE: This method must be called after Autofilter updates or additions in order to see the results of the AutoFilter(s)

NOTE: this evaluation is NOT done automatically due to performance considerations, and is designed to be called after all additions and updating is completed (as evaluation may be time-consuming)

Overview

Package

Class

Use

Tree

Deprecated

Index

Help

PREV CLASS NEXT CLASS

FRAMES NO FRAMES

SUMMARY: NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD

com.extentech.ExtenXLS Class WorkSheetHandle

ROW_INSERT_MULTI

ROW_INSERT

ROW_INSERT_ONCE

ROW_INSERT_NO_REFERENCE_UPDATE

ROW_DELETE

ROW_DELETE_NO_REFERENCE_UPDATE

ALLOWOBJECTS

ALLOWSCENARIOS

ALLOWFORMATCELLS

ALLOWFORMATCOLUMNS

ALLOWFORMATROWS

ALLOWINSERTCOLUMNS

ALLOWINSERTROWS

ALLOWINSERTHYPERLINKS

ALLOWDELETECOLUMNS

ALLOWDELETEROWS

ALLOWSELLOCKEDCELLS

ALLOWSORT

ALLOWAUTOFILTER

ALLOWPIVOTTABLES

ALLOWSELUNLOCKEDCELLS

addChart

getFirstRow

getFirstCol

getLastRow

getLastCol

setProtected

setProtected

setProtectionPassword

setProtectionPasswordHashed

getHashedProtectionPassword

getProtected

checkProtectionPassword

setEnhancedProtection

getEnhancedProtection

setVeryHidden

getSelected

getHidden

getVeryHidden

setHidden

setFirstVisibleTab

getTabIndex

setTabIndex

setSelected

getCol

addCol

addCol

getCol

getColumns

getColNames

getRowNums

getRow

getRows

getRowMap

getNumRows

getNumCols

removeCell

removeImage

removeCell

removeRow

removeRow

removeRow

removeCol

removeCol

getSheetNum

getNamedRangesInScope

getNamedRangeInScope

getSheetName

getSerialBytes

getUseCache

setVal

setVal

setVal

setSheetName

setVal

getVal

insertRow

insertRow

insertRow

com.extentech.ExtenXLS
Class WorkSheetHandle