com.extentech.ExtenXLS
Interface WorkBook

All Superinterfaces:

Document, Handle

All Known Implementing Classes:

EncryptedWorkBookHandle, FileWebWorkBook, MemeWorkBook, WebWorkBook, WorkBookHandle

public interface WorkBook
extends Handle, Document

An interface representing an ExtenXLS WorkBook.

Version:

4.1

Author:

John McMahon -- Copyright ©2010 Extentech Inc.

See Also:

WorkBookHandle

Field Summary

static int	ALLOWDUPES
static int	CALCULATE_ALWAYS
static int	CALCULATE_AUTO
static int	CALCULATE_EXPLICIT
static int	DEBUG_HIGH
static int	DEBUG_LOW
static int	DEBUG_MEDIUM
static int	SHAREDUPES
static int	STRING_ENCODING_AUTO
static int	STRING_ENCODING_COMPRESSED
static int	STRING_ENCODING_UNICODE

Method Summary

void	addProperty(String name, Object val) add non-Excel property
boolean	addSheetFromWorkBook(WorkBookHandle sourceBook, String sourceSheetName, String destSheetName) Deprecated.
WorkSheetHandle	addWorkSheet(WorkSheetHandle sht, String NewSheetName) Inserts a new worksheet and places it at the end of the workbook
void	calculateFormulas() Iterate through the formulas in this WorkBook and call the calculate method on each.
void	copyChartToSheet(ChartHandle chart, WorkSheetHandle sheet) Copies an existing Chart to another WorkSheet
void	copyChartToSheet(String chartname, String sheetname) Copies an existing Chart to another WorkSheet
WorkSheetHandle	copyWorkSheet(String SourceSheetName, String NewSheetName) Copy (duplicate) a worksheet in the workbook and add it to the end of the workbook with a new name
WorkSheetHandle	createWorkSheet(String name) Creates a new worksheet and places it at the end of the workbook
byte[]	getBytes() Returns a byte Array containing the valid file containing this WorkBook and associated Storages (such as VB files and PivotTables.) This is the actual file data and that can be read from and written to FileOutputStreams and ServletOutputStreams.
CellHandle	getCell(String address) Returns the Cell at the specified Location
CellHandle[]	getCells() Returns all of the Cells contained in the WorkBook
ChartHandle	getChart(String chartname) Returns a Chart Handle
ChartHandle[]	getCharts() Returns all Chart Handles contained in the WorkBook
FormatHandle[]	getFormats() Returns an Array of all the FormatHandles present in the workbook
int	getFormulaCalculationMode()
String	getName() Returns the name of this WorkBook
NameHandle	getNamedRange(String rangename) Returns a Named Range Handle
NameHandle[]	getNamedRanges() Returns all Named Range Handles
WorkBookHandle	getNoSheetWorkBook() Returns a WorkBookHandle containing an empty version of this WorkBook.
int	getNumCells() Returns the number of Cells in this WorkBook
PivotTableHandle	getPivotTable(String ptname) get a handle to a PivotTable in the WorkBook
PivotTableHandle[]	getPivotTables() get an array of handles to all PivotTables in the WorkBook
Object	getProperty(String name) get a non-Excel property
com.extentech.formats.XLS.WorkBook	getWorkBook() Returns a low-level WorkBook.
String	getWorkingDirectory() Returns the directory containing the ExtenXLS.jar and extenxls.lic files.
WorkSheetHandle	getWorkSheet(int i) returns the handle to a WorkSheet by name.
WorkSheetHandle	getWorkSheet(String handstr) returns the handle to a WorkSheet by name.
WorkSheetHandle[]	getWorkSheets() Returns an array of handles to all of the WorkSheets in the Workbook.
void	removeAllWorkSheets() Removes all of the WorkSheets from this WorkBook.
void	reset() Clears all values in a template WorkBook.
void	setDebugLevel(int l) Set the Debugging level.
void	setDefaultColWidth(int t) set Default col width Note: only affects undefined Columns containing Cells
void	setDefaultRowHeight(int t) set Default row height Note: only affects undefined Rows containing Cells
void	setDupeStringMode(int mode) Set Duplicate String Handling Mode.
void	setFormulaCalculationMode(int mode) Explicit calcing of formulas
void	setName(String nm) Sets the internal name of this WorkBookHandle.
void	setProtected(boolean b) set the workbook to protected mode Note: the password cannot be decrypted or changed in Excel -- protection can only be set/removed using ExtenXLS
void	setStringEncodingMode(int mode) Set Encoding mode of new Strings added to file.
String	toString() Returns the name of this Sheet.
StringBuffer	writeBytes(OutputStream bbout)

Field Detail

CALCULATE_ALWAYS

static final int CALCULATE_ALWAYS

See Also:

Constant Field Values

CALCULATE_EXPLICIT

static final int CALCULATE_EXPLICIT

See Also:

Constant Field Values

CALCULATE_AUTO

static final int CALCULATE_AUTO

See Also:

Constant Field Values

STRING_ENCODING_AUTO

static final int STRING_ENCODING_AUTO

See Also:

Constant Field Values

STRING_ENCODING_UNICODE

static final int STRING_ENCODING_UNICODE

See Also:

Constant Field Values

STRING_ENCODING_COMPRESSED

static final int STRING_ENCODING_COMPRESSED

See Also:

Constant Field Values

ALLOWDUPES

static final int ALLOWDUPES

See Also:

Constant Field Values

SHAREDUPES

static final int SHAREDUPES

See Also:

Constant Field Values

DEBUG_LOW

static final int DEBUG_LOW

See Also:

Constant Field Values

DEBUG_MEDIUM

static final int DEBUG_MEDIUM

See Also:

Constant Field Values

DEBUG_HIGH

static final int DEBUG_HIGH

See Also:

Constant Field Values

Method Detail

setFormulaCalculationMode

void setFormulaCalculationMode(int mode)

Explicit calcing of formulas

Parameters:

mode -

getFormulaCalculationMode

int getFormulaCalculationMode()

getProperty

Object getProperty(String name)

get a non-Excel property

Specified by:

getProperty in interface Document

Returns:

Returns the properties.

addProperty

void addProperty(String name,
                 Object val)

add non-Excel property

Specified by:

addProperty in interface Document

Parameters:

properties - The properties to set.

getCells

CellHandle[] getCells()

Returns all of the Cells contained in the WorkBook

getCell

CellHandle getCell(String address)
                   throws com.extentech.formats.XLS.CellNotFoundException,
                          com.extentech.formats.XLS.WorkSheetNotFoundException

Returns the Cell at the specified Location

Parameters:

address -

Returns:

Throws:

com.extentech.formats.XLS.CellNotFoundException

com.extentech.formats.XLS.WorkSheetNotFoundException

getFormats

FormatHandle[] getFormats()

Returns an Array of all the FormatHandles present in the workbook

Specified by:

getFormats in interface Document

Returns:

all existing FormatHandles in the workbook

getPivotTable

PivotTableHandle getPivotTable(String ptname)
                               throws com.extentech.formats.XLS.PivotTableNotFoundException

get a handle to a PivotTable in the WorkBook

Parameters:

String - name of the PivotTable

Returns:

PivotTable the PivotTable

Throws:

com.extentech.formats.XLS.PivotTableNotFoundException

getPivotTables

PivotTableHandle[] getPivotTables()
                                  throws com.extentech.formats.XLS.PivotTableNotFoundException

get an array of handles to all PivotTables in the WorkBook

Returns:

PivotTable[] all of the WorkBooks PivotTables

Throws:

com.extentech.formats.XLS.PivotTableNotFoundException

setProtected

void setProtected(boolean b)

set the workbook to protected mode Note: the password cannot be decrypted or changed in Excel -- protection can only be set/removed using ExtenXLS

Specified by:

setProtected in interface Document

Parameters:

int - Default Column width

setDefaultRowHeight

void setDefaultRowHeight(int t)

set Default row height Note: only affects undefined Rows containing Cells

Parameters:

int - Default Row Height

setDefaultColWidth

void setDefaultColWidth(int t)

set Default col width Note: only affects undefined Columns containing Cells

Parameters:

int - Default Column width

setName

void setName(String nm)

Sets the internal name of this WorkBookHandle. Overrides the default for 'getName()' which returns the file name source of this WorkBook by default.

Specified by:

setName in interface Document

Parameters:

WorkBook - Name

setDebugLevel

void setDebugLevel(int l)

Set the Debugging level. Higher values output more debugging info during execution.

Specified by:

setDebugLevel in interface Document

getNamedRange

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

Returns a Named Range Handle

Returns:

NameHandle a Named range in the WorkBook

Throws:

com.extentech.formats.XLS.CellNotFoundException

getChart

ChartHandle getChart(String chartname)
                     throws com.extentech.formats.XLS.ChartNotFoundException

Returns a Chart Handle

Specified by:

getChart in interface Document

Returns:

ChartHandle a Chart in the WorkBook

Throws:

com.extentech.formats.XLS.ChartNotFoundException

getCharts

ChartHandle[] getCharts()

Returns all Chart Handles contained in the WorkBook

Specified by:

getCharts in interface Document

Returns:

ChartHandle[] an array of all Charts in the WorkBook

getNamedRanges

NameHandle[] getNamedRanges()

Returns all Named Range Handles

Returns:

NameHandle[] all of the Named ranges in the WorkBook

getName

String getName()

Returns the name of this WorkBook

Specified by:

getName in interface Document

Returns:

String name of WorkBook

getNumCells

int getNumCells()

Returns the number of Cells in this WorkBook

Returns:

int number of Cells

getBytes

byte[] getBytes()

Returns a byte Array containing the valid file containing this WorkBook and associated Storages (such as VB files and PivotTables.) This is the actual file data and that can be read from and written to FileOutputStreams and ServletOutputStreams.

Specified by:

getBytes in interface Document

Returns:

byte[] the XLS File's bytes

getWorkingDirectory

String getWorkingDirectory()

Returns the directory containing the ExtenXLS.jar and extenxls.lic files. This must be called BEFORE using the byte Array WorkBookHandle constructor.

Specified by:

getWorkingDirectory in interface Document

Returns:

String directory path containing ExtenXLS.jar and extenxls.lic

getWorkSheets

WorkSheetHandle[] getWorkSheets()

Returns an array of handles to all of the WorkSheets in the Workbook.

Returns:

WorkSheetHandle[] Array of all WorkSheets in WorkBook

getWorkSheet

WorkSheetHandle getWorkSheet(int i)
                             throws com.extentech.formats.XLS.WorkSheetNotFoundException

returns the handle to a WorkSheet by name.

Parameters:

index - of worksheet (ie: 0)

Returns:

WorkSheetHandle the WorkSheet

Throws:

com.extentech.formats.XLS.WorkSheetNotFoundException - if the specified WorkSheet is not found in the WorkBook.

getWorkSheet

WorkSheetHandle getWorkSheet(String handstr)
                             throws com.extentech.formats.XLS.WorkSheetNotFoundException

returns the handle to a WorkSheet by name.

Parameters:

String - name of worksheet (ie: "Sheet1")

Returns:

WorkSheetHandle the WorkSheet

Throws:

com.extentech.formats.XLS.WorkSheetNotFoundException - if the specified WorkSheet is not found in the WorkBook.

getWorkBook

com.extentech.formats.XLS.WorkBook getWorkBook()

Returns a low-level WorkBook. NOTE: The WorkBook class is NOT a part of the published API. Any of the methods and/or variables on a WorkBook object are subject to change without notice in new versions of ExtenXLS.

reset

void reset()

Clears all values in a template WorkBook. Use this method to 'reset' the values of your WorkBook in memory to defaults. For example, if you load a Servlet with a single WorkBookHandle instance, then modify values and stream to a Client system, yo should call 'clearAll()' when the request is completed to remove the modified values and set them back to a default.

Specified by:

reset in interface Document

setStringEncodingMode

void setStringEncodingMode(int mode)

Set Encoding mode of new Strings added to file. ExtenXLS has 3 modes for handling the internal encoding of String data that is added to the file. ExtenXLS can save space in the file if it knows that all characters in your String data can be represented with a single byte (Compressed.) If your String contains characters which need 2 bytes to represent (such as Eastern-language characters) then it needs to be stored in an uncompressed Unicode format. ExtenXLS can either automatically detect the mode for each String, or you can set it explicitly. The auto mode is the most flexible but requires processing overhead. Default mode is WorkBookHandle.STRING_ENCODING_AUTO. Valid Modes Are: WorkBookHandle.STRING_ENCODING_AUTO Use if you are adding mixed Unicode and non-unicode Strings and can accept the performance hit -slowest String adds -optimal file size for mixed Strings WorkBookHandle.STRING_ENCODING_UNICODE Use if all of your new Strings are Unicode - faster than AUTO -faster than AUTO -largest file size WorkBookHandle.STRING_ENCODING_COMPRESSED Use if all of your new Strings are non-Unicode and can have high-bytes compressed -faster than AUTO -smallest file size

Specified by:

setStringEncodingMode in interface Document

Parameters:

int - String Encoding Mode

setDupeStringMode

void setDupeStringMode(int mode)

Set Duplicate String Handling Mode. The Duplicate String Mode determines the behavior of the String table when inserting new Strings. The String table shares a single entry for multiple Cells containing the same string. When multiple Cells have the same value, they share the same underlying string. Changing the value of any one of the Cells will change the value for any Cells sharing that reference. For this reason, you need to determine the handling of new strings added to the sheet that are duplicates of strings already in the table. If you will be changing the values of these new Cells, you will need to set the Duplicate String Mode to ALLOWDUPES. If the string table encounters a duplicate entry being added, it will insert a duplicate that can then be subsequently changed without affecting the other duplicate Cells. Valid Modes Are: WorkBookHandle.ALLOWDUPES - faster, smaller file sizes, dupe Cells share changes WorkBookHandle.SHAREDUPES - slower inserts, changing Cells has no effect on dupe Cells

Specified by:

setDupeStringMode in interface Document

Parameters:

int - Duplicate String Handling Mode

copyChartToSheet

void copyChartToSheet(String chartname,
                      String sheetname)
                      throws com.extentech.formats.XLS.ChartNotFoundException,
                             com.extentech.formats.XLS.WorkSheetNotFoundException

Copies an existing Chart to another WorkSheet

Parameters:

chartname -

sheetname -

Throws:

com.extentech.formats.XLS.ChartNotFoundException

com.extentech.formats.XLS.WorkSheetNotFoundException

copyChartToSheet

void copyChartToSheet(ChartHandle chart,
                      WorkSheetHandle sheet)
                      throws com.extentech.formats.XLS.ChartNotFoundException,
                             com.extentech.formats.XLS.WorkSheetNotFoundException

Copies an existing Chart to another WorkSheet

Parameters:

chart -

sheet -

Throws:

com.extentech.formats.XLS.ChartNotFoundException

com.extentech.formats.XLS.WorkSheetNotFoundException

copyWorkSheet

WorkSheetHandle copyWorkSheet(String SourceSheetName,
                              String NewSheetName)
                              throws com.extentech.formats.XLS.WorkSheetNotFoundException

Copy (duplicate) a worksheet in the workbook and add it to the end of the workbook with a new name

Parameters:

String - the Name of the source worksheet;

String - the Name of the new (destination) worksheet;

Returns:

the new WorkSheetHandle

Throws:

com.extentech.formats.XLS.WorkSheetNotFoundException

calculateFormulas

void calculateFormulas()
                       throws com.extentech.formats.XLS.FunctionNotSupportedException

Iterate through the formulas in this WorkBook and call the calculate method on each. May be more expensive than calling update on individual FormulaHandles depending on extent of data changes to your WorkBook, or calling update on only the 'top-level' formula in a calculation. When a formula references a Cell containing another formula, it will recursively calculate until it reaches non-formula Cells. Thus, calling this method may calculate formula Cells in a heirarchy more than once.

Throws:

com.extentech.formats.XLS.FunctionNotSupportedException

removeAllWorkSheets

void removeAllWorkSheets()

Removes all of the WorkSheets from this WorkBook. Bytes streamed from this WorkBook will create invalid Spreadsheet files unless a WorkSheet(s) are added to it.

getNoSheetWorkBook

WorkBookHandle getNoSheetWorkBook()

Returns a WorkBookHandle containing an empty version of this WorkBook. Use in conjunction with addSheetFromWorkBook() to create new output WorkBooks containing various sheets from a master template. ie: WorkBookHandle emptytemplate = this.getNoSheetWorkBook(); emptytemplate.addSheetFromWorkBook(this, "Sheet1", "TargetSheet");

Returns:

WorkBookHandle - the empty WorkBookHandle duplicate

See Also:

addSheetFromWorkBook

addSheetFromWorkBook

boolean addSheetFromWorkBook(WorkBookHandle sourceBook,
                             String sourceSheetName,
                             String destSheetName)

Deprecated.

Inserts a worksheet from a Source WorkBook.

Parameters:

sourceBook - - the WorkBook containing the sheet to copy

sourceSheetName - - the name of the sheet to copy

destSheetName - - the name of the new sheet in this workbook

addWorkSheet

WorkSheetHandle addWorkSheet(WorkSheetHandle sht,
                             String NewSheetName)

Inserts a new worksheet and places it at the end of the workbook

Parameters:

WorkSheetHandle - the source WorkSheetHandle;

String - the Name of the new (destination) worksheet;

createWorkSheet

WorkSheetHandle createWorkSheet(String name)

Creates a new worksheet and places it at the end of the workbook

Parameters:

String - the Name of the newly created worksheet

Returns:

the new WorkSheetHandle

toString

String toString()

Returns the name of this Sheet.

Specified by:

toString in interface Document

Overrides:

toString in class Object

See Also:

Object.toString()

writeBytes

StringBuffer writeBytes(OutputStream bbout)

Specified by:

writeBytes in interface Document