com.extentech.ExtenXLS
Class WorkBookHandle

java.lang.Object
  com.extentech.ExtenXLS.WorkBookHandle

All Implemented Interfaces:

Handle, WorkBook

Direct Known Subclasses:

MemeWorkBook

public class WorkBookHandle
extends java.lang.Object
implements WorkBook

The WorkBookHandle provides a handle to the XLS file
and includes convenience methods for working with the WorkSheets
and Cell values within the XLS file.

for example:

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

For usability reasons, ExtenXLS will convert the internal Excel 'Mulblank' record type to the 'Blank' record type when parsing the file. The Blank records are used by Excel when there are empty Cells which have formatting information.
The conversion allows setting of values on Blank cells without deleting/adding values.
For performance reasons, you may wish to disable this feature. To do so, use the following to disable conversion of Mulblanks to 'real' records:
System.getProperties().put(WorkBookHandle.CONVERTMULBLANKS, "false");
By default, ExtenXLS will lock open WorkBook files, to close the file after parsing and work with a temporary file instead, use the following setting:

System.getProperties().put(WorkBookHandle.USETEMPFILE, "true");

IMPORTANT NOTE: You will need to clean up temp files occasionally in your user directory (temp filenames will begin with "ExtenXLS_".)


!! Important Notes on required resources and Application Server VMS:
Due to issues on a number of Application Server Platforms, ExtenXLS may
be unable to find required resources automatically. These include the location
of the license file, and default templates for new WorkBookHandles and WorkSheetHandles.

These issues are easily resolved by setting parameters using the
System Properties mechanism.

If you are running an Oracle or WebSphere Application Server, and are having
difficulty using ExtenXLS in your Server environment, you should try using
the server control panel to add these properties to your Server VM instances.

Programatically, you can set these values like so:

// your license key is set thus
String licensekey = "1255087426532DDD35268256220ABTRRW";
System.setProperty("com.extentech.extenxls.licensekey", licensekey);


// path to the file containing prototype.ser and other required ExtenXLS files - this could be a .war or .ear
String jarloc = "/home/user/workspace/lib/ExtenXLSFilesNamedSomethingElse.jar";
System.setProperty("com.extentech.extenxls.jarloc",jarloc);

By default, ExtenXLS will lock open WorkBook files, to close the file after parsing and work with a temporary file instead, use the following setting:

System.getProperties().put("com.extentech.formats.LEO.usetempfile", "true");

Since:

1.0

Version:

5.1

Author:

John McMahon -- Copyright ©2007 Extentech Inc.

See Also:

WorkBook, WorkSheetHandle, CellHandle

Field Summary

static java.lang.String

CONVERTMULBLANKS

Fields inherited from interface com.extentech.ExtenXLS.WorkBook

ALLOWDUPES, CALCULATE_ALWAYS, CALCULATE_AUTO, CALCULATE_EXPLICIT, DEBUG_HIGH, DEBUG_LOW, DEBUG_MEDIUM, SHAREDUPES, STRING_ENCODING_AUTO, STRING_ENCODING_COMPRESSED, STRING_ENCODING_UNICODE

Constructor Summary

WorkBookHandle()
Default constructor creates a new, empty WorkBookHandle with 3 WorkSheets: "Sheet1","Sheet2",and "Sheet3".

WorkBookHandle(byte[] barray)
constructor which takes a byte Array containing the bytes of a valid XLS file.

WorkBookHandle(java.io.File fx)
constructor which takes the XLS file

WorkBookHandle(java.io.InputStream inx)
constructor which takes an InputStream containing the bytes of a valid XLS file.

WorkBookHandle(com.extentech.toolkit.ProgressListener pn)
Constructor which takes a ProgressListener which monitors the progress of creating a new Excel file.

WorkBookHandle(java.lang.String fname)
constructor which takes the XLS file name(

WorkBookHandle(java.lang.String fname, int debug)
constructor which takes the XLS file name

WorkBookHandle(java.lang.String fname, com.extentech.toolkit.ProgressListener pn)
Constructor which takes the XLS file name and a ProgressListener which monitors the progress of reading the Excel file.

WorkBookHandle(java.net.URL u, com.extentech.security.User user)
constructor which reads spreadsheet bytes from a URL

Method Summary

void	addProperty(java.lang.String name, java.lang.Object val) add non-Excel property
boolean	addSheetFromWorkBook(WorkBookHandle sourceBook, java.lang.String sourceSheetName, java.lang.String destSheetName) Inserts a worksheet from a Source WorkBook.
boolean	addSheetFromWorkBookWithFormatting(WorkBookHandle sourceBook, java.lang.String sourceSheetName, java.lang.String destSheetName) Inserts a worksheet from a Source WorkBook.
void	addWorkSheet(byte[] serialsheet, java.lang.String NewSheetName, java.lang.String origWorkBookName) Inserts a new worksheet and places it at the end of the workbook The source bytes from the WorkSheet are from a serialized WorkSheet created by the WorkSheetHandle getSerialBytes() method.
void	addWorkSheet(WorkSheetHandle sht, java.lang.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	close() Closes the WorkBook and releases resources.
void	copyChartToSheet(ChartHandle chart, WorkSheetHandle sheet) Copies an existing Chart to another WorkSheet
void	copyChartToSheet(java.lang.String chartname, java.lang.String sheetname) Copies an existing Chart to another WorkSheet
WorkSheetHandle	copyWorkSheet(java.lang.String SourceSheetName, java.lang.String NewSheetName) Copy (duplicate) a worksheet in the workbook and add it to the end of the workbook with a new name
ChartHandle	createChart(java.lang.String name, WorkSheetHandle wsh) Creates a new Chart and places it at the end of the workbook Under development and unsupported!
NameHandle	createNamedRange(java.lang.String name, java.lang.String rangeDef) Create a named range in the workbook
WorkSheetHandle	createWorkSheet(java.lang.String name) Creates a new worksheet and places it at the end of the workbook
WorkSheetHandle	createWorkSheet(java.lang.String name, int sheetpos) Creates a new worksheet and places it at the end of the workbook
void	deleteChart(java.lang.String chartname, WorkSheetHandle wsh) delete an existing chart of the workbook
void	finalize() Closes the WorkBook and releases resources.
byte[]	getBytes() Deprecated.
CellHandle	getCell(java.lang.String address) Returns the Cell at the specified Location
CellRange[]	getCellRanges() Returns an Array of the CellRanges existing in this WorkBook specifically the Ranges referenced in Formulas, Charts, and Named Ranges.
CellHandle[]	getCells() Returns an array containing all cells in the WorkBook
ChartHandle	getChart(java.lang.String chartname) Returns a Chart Handle
ChartHandle	getChartById(int id) retrieve a ChartHandle via id
ChartHandle[]	getCharts() Returns all Chart Handles contained in the WorkBook
com.extentech.formats.XLS.WorkBookFactory	getFactory() returns a low-level WorkBookFactory NOTE: The WorkBook class is NOT a part of the published API.
FormatHandle[]	getFormats() Returns an array of all FormatHandles in the workbook
int	getFormulaCalculationMode() Get the calculation mode for the workbook.
FormulaHandle	getFormulaHandle(java.lang.String celladdress) Returns a Formula Handle
ImageHandle	getImage(java.lang.String imagename) Returns an ImageHandle ------------------------------------------------------------
ImageHandle[]	getImages() Returns all ImageHandles in the workbook ------------------------------------------------------------
boolean	getIsExcel2007() Returns whether the underlying spreadsheet is in Excel 2007 format by default.
java.lang.String	getName() Returns the name of this WorkBook
NameHandle	getNamedRange(java.lang.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
int	getNumWorkSheets() Returns the number of Sheets in this WorkBook
PivotTableHandle	getPivotTable(java.lang.String ptname) get a handle to a PivotTable in the WorkBook
PivotTableHandle[]	getPivotTables() get an array of handles to all PivotTables in the WorkBook
java.util.Map	getProperties()
java.lang.Object	getProperty(java.lang.String name) get a non-Excel property
java.lang.String	getStats() Return useful statistics about this workbook ------------------------------------------------------------
static java.lang.String	getVersion() Return the version of ExtenXLS
com.extentech.formats.XLS.WorkBook	getWorkBook() Returns a low-level WorkBook.
java.lang.String	getWorkingDirectory() Returns the directory containing the ExtenXLS.jar and extenxls.lic files.
WorkSheetHandle	getWorkSheet(int sheetnum) returns the handle to a WorkSheet by number.
WorkSheetHandle	getWorkSheet(java.lang.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.
java.lang.String	getXLSVersionString() Returns the lowest version of Excel compatible with the input file.
boolean	is1904() Returns whether this WorkBook is using the '1904' date system.
void	removeAllWorkSheets() Removes all of the WorkSheets from this WorkBook.
void	reset() Resets the values of this WorkBookHandle to defaults.
int	searchAndReplace(java.lang.String searchfor, java.lang.String replacewith) Searches all Cells in the workbook for the string occurrence and replaces with the replacement text.
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 CalcMode) Set the calculation mode for the workbook.
void	setName(java.lang.String nm) Sets the internal name of this WorkBookHandle.
void	setProperties(java.util.Map properties)
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.
java.lang.String	toString() Returns the name of this Sheet.
void	write(java.io.OutputStream out) Saves the WorkBookHandle to this opened OutputStream If original file is Excel2007 Format, the template will be written in Excel 2007 (XLSX) Format.
void	write(java.io.OutputStream out, boolean Excel2007Format) Saves the WorkBookHandle to this opened OutputStream if Excel2007Format, the template will be written in Excel 2007 (XLSX) Format.
void	write(java.lang.String fname) Saves this WorkBookHandle to filename and path fname if the filename ends with ".xlsx" or ".xlsm", the template will be written in Excel 2007 (XLSX) Format.
void	write(java.lang.String fname, boolean Excel2007Format) saves this WorkBookHandle to filename and path fname if Excel2007 Format, the template will be written in Excel 2007 (XLSX) Format.
java.lang.StringBuffer	writeBytes(java.io.OutputStream bout) Write the Spreadsheet bytes to an OutputStream.
void	writeXLSXBytes(java.io.OutputStream bout) Explicitly write the Spreadsheet bytes to an OutputStream in Excel 2007 (XLSX) format.

Methods inherited from class java.lang.Object

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

Field Detail

CONVERTMULBLANKS

public static java.lang.String CONVERTMULBLANKS

Constructor Detail

WorkBookHandle

public WorkBookHandle()

Default constructor creates a new, empty WorkBookHandle with 3 WorkSheets: "Sheet1","Sheet2",and "Sheet3".

WorkBookHandle

public WorkBookHandle(java.io.InputStream inx)

constructor which takes an InputStream containing the bytes of a valid XLS file.

Parameters:

InputStream - contains the valid BIFF8 bytes for reading

WorkBookHandle

public WorkBookHandle(byte[] barray)

constructor which takes a byte Array containing the bytes of a valid XLS file.

Parameters:

byte[] - byte array containing the valid XLS file for reading

WorkBookHandle

public WorkBookHandle(java.net.URL u,
                      com.extentech.security.User user)

constructor which reads spreadsheet bytes from a URL

WorkBookHandle

public WorkBookHandle(java.lang.String fname)

constructor which takes the XLS file name(

Parameters:

String - fname the name of the XLS file to read

WorkBookHandle

public WorkBookHandle(java.lang.String fname,
                      int debug)

constructor which takes the XLS file name

Parameters:

String - fname the name of the XLS file to read

Debug - level

WorkBookHandle

public WorkBookHandle(java.io.File fx)

constructor which takes the XLS file

Parameters:

File - the XLS file to read

Debug - level

WorkBookHandle

public WorkBookHandle(com.extentech.toolkit.ProgressListener pn)

Constructor which takes a ProgressListener which monitors the progress of creating a new Excel file.

Parameters:

ProgressListener - object which is monitoring progress of WorkBook read

WorkBookHandle

public WorkBookHandle(java.lang.String fname,
                      com.extentech.toolkit.ProgressListener pn)

Constructor which takes the XLS file name and a ProgressListener which monitors the progress of reading the Excel file.

Parameters:

String - fname the name of the XLS file to read

ProgressListener - object which is monitoring progress of WorkBook read

Method Detail

searchAndReplace

public int searchAndReplace(java.lang.String searchfor,
                            java.lang.String replacewith)

Searches all Cells in the workbook for the string occurrence and replaces with the replacement text. ------------------------------------------------------------

Returns:

int the number of replacements if the string was found and replaced

is1904

public boolean is1904()

Returns whether this WorkBook is using the '1904' date system. Useful on Macs. ------------------------------------------------------------

Returns:

true if the WorkBook is using the 1904 date system.

getXLSVersionString

public java.lang.String getXLSVersionString()

Returns the lowest version of Excel compatible with the input file. ------------------------------------------------------------

Returns:

getStats

public java.lang.String getStats()

Return useful statistics about this workbook ------------------------------------------------------------

Returns:

getProperty

public java.lang.Object getProperty(java.lang.String name)

Description copied from interface: WorkBook

get a non-Excel property

Specified by:

getProperty in interface WorkBook

Returns:

Returns the properties.

addProperty

public void addProperty(java.lang.String name,
                        java.lang.Object val)

Description copied from interface: WorkBook

add non-Excel property

Specified by:

addProperty in interface WorkBook

Parameters:

properties - The properties to set.

getProperties

public java.util.Map getProperties()

Returns:

Returns the properties.

setProperties

public void setProperties(java.util.Map properties)

Parameters:

properties - The properties to set.

close

public void close()

Closes the WorkBook and releases resources.

See Also:

Object.finalize()

finalize

public void finalize()

Closes the WorkBook and releases resources.

Overrides:

finalize in class java.lang.Object

See Also:

Object.finalize()

getCell

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

Returns the Cell at the specified Location

Specified by:

getCell in interface WorkBook

Parameters:

address -

Returns:

Throws:

com.extentech.formats.XLS.CellNotFoundException

com.extentech.formats.XLS.WorkSheetNotFoundException

getCellRanges

public CellRange[] getCellRanges()

Returns an Array of the CellRanges existing in this WorkBook specifically the Ranges referenced in Formulas, Charts, and Named Ranges. This is necessary to allow for automatic updating of references when adding/removing/moving Cells within these ranges, as well as shifting references to Cells in Formulas when Formula records are moved.

Returns:

all existing Cell Range references used in Formulas, Charts, and Names

getPivotTable

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

get a handle to a PivotTable in the WorkBook

Specified by:

getPivotTable in interface WorkBook

Parameters:

String - name of the PivotTable

Returns:

PivotTable the PivotTable

Throws:

com.extentech.formats.XLS.PivotTableNotFoundException

getPivotTables

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

get an array of handles to all PivotTables in the WorkBook

Specified by:

getPivotTables in interface WorkBook

Returns:

PivotTable[] all of the WorkBooks PivotTables

Throws:

com.extentech.formats.XLS.PivotTableNotFoundException

setFormulaCalculationMode

public void setFormulaCalculationMode(int CalcMode)

Set the calculation mode for the workbook. CALCULATE_AUTO is the default for new workbooks. Calling Cell.getVal() will calculate formulas if they exist within the cell. CALCULATE_EXPLICIT will return present, cached value of the cell. Formula calculation will ONLY occur when explicitly called through the Formula Handle.calculate() method. CALCULATE_ALWAYS will ignore the cache and force a recalc every time a cell value is requested. WorkBookHandle.CALCULATE_AUTO WorkBookHandle.CALCULATE_ALWAYS WorkBookHandle.CALCULATE_EXPLICIT

Specified by:

setFormulaCalculationMode in interface WorkBook

Parameters:

CalcMode - Calculation mode to use in workbook.

getFormulaCalculationMode

public int getFormulaCalculationMode()

Get the calculation mode for the workbook. CALCULATE_ALWAYS is the default for new workbooks. Calling Cell.getVal() will calculate formulas if they exist within the cell. CALCULATE_EXPLICIT will return present value of the cell. Formula calculation will only occur when explicitly called through the Formula Handle WorkBookHandle.CALCULATE_ALWAYS -- recalc every time the cell value is requested (no cacheing) WorkBookHandle.CALCULATE_EXPLICIT -- recalc only when FormulaHandle.calculate() called WorkBookHandle.CALCULATE_AUTO -- only recac when changes

Specified by:

getFormulaCalculationMode in interface WorkBook

Parameters:

CalcMode - Calculation mode to use in workbook.

setProtected

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

Parameters:

boolean - whether to protect the book

setDefaultRowHeight

public void setDefaultRowHeight(int t)

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

Specified by:

setDefaultRowHeight in interface WorkBook

Parameters:

int - Default Row Height

setDefaultColWidth

public void setDefaultColWidth(int t)

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

Specified by:

setDefaultColWidth in interface WorkBook

Parameters:

int - Default Column width

getVersion

public static java.lang.String getVersion()

Return the version of ExtenXLS

Returns:

String version of ExtenXLS

setName

public void setName(java.lang.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 WorkBook

Parameters:

WorkBook - Name

setDebugLevel

public void setDebugLevel(int l)

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

Specified by:

setDebugLevel in interface WorkBook

getFormulaHandle

public FormulaHandle getFormulaHandle(java.lang.String celladdress)
                               throws com.extentech.formats.XLS.FormulaNotFoundException

Returns a Formula Handle

Returns:

FormulaHandle a formula handle in the WorkBook

Throws:

com.extentech.formats.XLS.FormulaNotFoundException

getImages

public ImageHandle[] getImages()

Returns all ImageHandles in the workbook ------------------------------------------------------------

Returns:

getImage

public ImageHandle getImage(java.lang.String imagename)
                     throws com.extentech.formats.XLS.ImageNotFoundException

Returns an ImageHandle ------------------------------------------------------------

Parameters:

imagename -

Returns:

Throws:

com.extentech.formats.XLS.ImageNotFoundException

getNamedRange

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

Returns a Named Range Handle

Specified by:

getNamedRange in interface WorkBook

Returns:

NameHandle a Named range in the WorkBook

Throws:

com.extentech.formats.XLS.CellNotFoundException

createNamedRange

public NameHandle createNamedRange(java.lang.String name,
                                   java.lang.String rangeDef)

Create a named range in the workbook

Parameters:

name - The name that should be used to reference this named range

rangeDef - Range of the cells for this named range, in excel syntax including sheet name, ie "Sheet1!A1:D1"

Returns:

NameHandle for modifying the named range

getChart

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

Returns a Chart Handle

Specified by:

getChart in interface WorkBook

Returns:

ChartHandle a Chart in the WorkBook

Throws:

com.extentech.formats.XLS.ChartNotFoundException

getCharts

public ChartHandle[] getCharts()

Returns all Chart Handles contained in the WorkBook

Specified by:

getCharts in interface WorkBook

Returns:

ChartHandle[] an array of all Charts in the WorkBook

getChartById

public ChartHandle getChartById(int id)
                         throws com.extentech.formats.XLS.ChartNotFoundException

retrieve a ChartHandle via id

Parameters:

id -

Returns:

Throws:

com.extentech.formats.XLS.ChartNotFoundException

getNamedRanges

public NameHandle[] getNamedRanges()

Returns all Named Range Handles

Specified by:

getNamedRanges in interface WorkBook

Returns:

NameHandle[] all of the Named ranges in the WorkBook

getName

public java.lang.String getName()

Returns the name of this WorkBook

Specified by:

getName in interface WorkBook

Returns:

String name of WorkBook

getCells

public CellHandle[] getCells()

Returns an array containing all cells in the WorkBook

Specified by:

getCells in interface WorkBook

Returns:

CellHandle array of all book cells

getNumCells

public int getNumCells()

Returns the number of Cells in this WorkBook

Specified by:

getNumCells in interface WorkBook

Returns:

int number of Cells

getBytes

public byte[] getBytes()

Deprecated.

Create a byte array containing the binary spreadsheet bytes ------------------------------------------------------------ Outputs a Excel 97-2003 (BIFF8) compatible Spreadsheet This method creates a potentially large byte array and is not suitable for use with large output due to memory usage. Please use writeBytes() with an appropriate OutputStream for best memory performance.

Specified by:

getBytes in interface WorkBook

Returns:

byte[] the XLS File's bytes

See Also:

WorkBook.getBytes()

write

public void write(java.lang.String fname)

Saves this WorkBookHandle to filename and path fname if the filename ends with ".xlsx" or ".xlsm", the template will be written in Excel 2007 (XLSX) Format. Otherwise, it will be written in Excel 2003 Format. NOTE: for Excel2007 Format, if the file has an OOXML vba project, the file extension must be ".xlsm". It will be changed from ".xlsx" if necessary.

Parameters:

fname -

write

public void write(java.lang.String fname,
                  boolean Excel2007Format)

saves this WorkBookHandle to filename and path fname if Excel2007 Format, the template will be written in Excel 2007 (XLSX) Format. Otherwise, it will be written in Excel 2003 Format. NOTE: for Excel2007Format, if the file has an OOXML vba project, the file extension must be ".xlsm". It will be changed from ".xlsx" if necessary.

Parameters:

fname -

Excel2007Format -

write

public void write(java.io.OutputStream out,
                  boolean Excel2007Format)

Saves the WorkBookHandle to this opened OutputStream if Excel2007Format, the template will be written in Excel 2007 (XLSX) Format. Otherwise, it will be written in Excel 2003 Format.

Parameters:

out -

Excel2007Format -

write

public void write(java.io.OutputStream out)

Saves the WorkBookHandle to this opened OutputStream If original file is Excel2007 Format, the template will be written in Excel 2007 (XLSX) Format. Otherwise, it will be written in Excel 2003 Format.

Parameters:

out -

writeXLSXBytes

public void writeXLSXBytes(java.io.OutputStream bout)

Explicitly write the Spreadsheet bytes to an OutputStream in Excel 2007 (XLSX) format. ------------------------------------------------------------ Be sure to use a BufferedOutputStream when possible to ensure best performance. ie: FileOutputStream fileOutputStream = new FileOutputStream(fn); BufferedOutputStream bout = new BufferedOutputStream(fileOutputStream); bk.writeXLSXBytes(bout); bout.flush(); bout.close();

Parameters:

bout -

getIsExcel2007

public boolean getIsExcel2007()

Returns whether the underlying spreadsheet is in Excel 2007 format by default. Even if this method returns true, it is still possible to write out the file as a BIFF8 (Excel 97-2003) file, but unsupported features will be dropped, and some files could experience corruption. ------------------------------------------------------------

Returns:

whether the underlying spreadsheet is Excel 2007 format

writeBytes

public java.lang.StringBuffer writeBytes(java.io.OutputStream bout)

Write the Spreadsheet bytes to an OutputStream. ------------------------------------------------------------ Be sure to use a BufferedOutputStream when possible to ensure best performance. ie: FileOutputStream fileOutputStream = new FileOutputStream(fn); BufferedOutputStream bout = new BufferedOutputStream(fileOutputStream); bk.writeBytes(bout); bout.flush(); bout.close(); to output a debugging stringbuffer, you must first set the autolockdown setting: props.put("com.extentech.ExtenXLS.autocreatelockdown","true");

Specified by:

writeBytes in interface WorkBook

Parameters:

bout -

Returns:

for debugging: a StringBuffer containing an output of the record bytes streamed

getWorkingDirectory

public java.lang.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 WorkBook

Returns:

String directory path containing ExtenXLS.jar and extenxls.lic

getWorkSheets

public WorkSheetHandle[] getWorkSheets()

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

Specified by:

getWorkSheets in interface WorkBook

Returns:

WorkSheetHandle[] Array of all WorkSheets in WorkBook

getWorkSheet

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

returns the handle to a WorkSheet by number. Sheet 0 is the first Sheet.

Specified by:

getWorkSheet in interface WorkBook

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

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

returns the handle to a WorkSheet by name.

Specified by:

getWorkSheet in interface WorkBook

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

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

Specified by:

getWorkBook in interface WorkBook

getFactory

public com.extentech.formats.XLS.WorkBookFactory getFactory()

returns a low-level WorkBookFactory 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.

Returns:

WorkBookFactory - the factory for this WorkBook

reset

public void reset()

Resets the values of this WorkBookHandle to defaults.

Specified by:

reset in interface WorkBook

setStringEncodingMode

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

Parameters:

int - String Encoding Mode

setDupeStringMode

public 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 String inserts, larger file sizes, changing Cells has no effect on dupe Cells WorkBookHandle.SHAREDUPES - slower inserts, dupe smaller file sizes, Cells share changes

Specified by:

setDupeStringMode in interface WorkBook

Parameters:

int - Duplicate String Handling Mode

copyChartToSheet

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

Copies an existing Chart to another WorkSheet

Specified by:

copyChartToSheet in interface WorkBook

Parameters:

chartname -

sheetname -

Throws:

com.extentech.formats.XLS.ChartNotFoundException

com.extentech.formats.XLS.WorkSheetNotFoundException

copyChartToSheet

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

Copies an existing Chart to another WorkSheet

Specified by:

copyChartToSheet in interface WorkBook

Parameters:

chart -

sheet -

Throws:

com.extentech.formats.XLS.ChartNotFoundException

com.extentech.formats.XLS.WorkSheetNotFoundException

copyWorkSheet

public WorkSheetHandle copyWorkSheet(java.lang.String SourceSheetName,
                                     java.lang.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

Specified by:

copyWorkSheet in interface WorkBook

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

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

Specified by:

calculateFormulas in interface WorkBook

Throws:

com.extentech.formats.XLS.FunctionNotSupportedException

addWorkSheet

public void addWorkSheet(byte[] serialsheet,
                         java.lang.String NewSheetName,
                         java.lang.String origWorkBookName)

Inserts a new worksheet and places it at the end of the workbook The source bytes from the WorkSheet are from a serialized WorkSheet created by the WorkSheetHandle getSerialBytes() method.

Specified by:

addWorkSheet in interface WorkBook

Parameters:

byte[] - the source WorkSheetHandle as a serialized byte Array;

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

String - of the original WorkBookName

removeAllWorkSheets

public 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. NOTE: A WorkBook with no sheets is *invalid* and will not open in Excel. You must add sheets to this WorkBook for it to be valid.

Specified by:

removeAllWorkSheets in interface WorkBook

getNoSheetWorkBook

public 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");

Specified by:

getNoSheetWorkBook in interface WorkBook

Returns:

WorkBookHandle - the empty WorkBookHandle duplicate

See Also:

addSheetFromWorkBook

addSheetFromWorkBook

public boolean addSheetFromWorkBook(WorkBookHandle sourceBook,
                                    java.lang.String sourceSheetName,
                                    java.lang.String destSheetName)

Inserts a worksheet from a Source WorkBook.

Specified by:

addSheetFromWorkBook in interface 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

Returns:

addSheetFromWorkBookWithFormatting

public boolean addSheetFromWorkBookWithFormatting(WorkBookHandle sourceBook,
                                                  java.lang.String sourceSheetName,
                                                  java.lang.String destSheetName)

Inserts a worksheet from a Source WorkBook. Brings all string data and formatting information from the source workbook. Be aware this is programmatically creating a large amount of new formatting information in the destination workbook. A higher performance option will usually be using getNoSheetWorkbook and addSheetFromWorkBook. CURRENTLY UNDER DEVELOPMENT AND TESTING. UNSUPPORTED!

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

Returns:

addWorkSheet

public void addWorkSheet(WorkSheetHandle sht,
                         java.lang.String NewSheetName)

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

Specified by:

addWorkSheet in interface WorkBook

Parameters:

WorkSheetHandle - the source WorkSheetHandle;

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

createChart

public ChartHandle createChart(java.lang.String name,
                               WorkSheetHandle wsh)

Creates a new Chart and places it at the end of the workbook Under development and unsupported!

Parameters:

String - the Name of the newly created Chart

Returns:

the new ChartHandle

deleteChart

public void deleteChart(java.lang.String chartname,
                        WorkSheetHandle wsh)
                 throws com.extentech.formats.XLS.ChartNotFoundException

delete an existing chart of the workbook

Parameters:

chartname -

Throws:

com.extentech.formats.XLS.ChartNotFoundException

getNumWorkSheets

public int getNumWorkSheets()

Returns the number of Sheets in this WorkBook

Returns:

int number of Sheets

createWorkSheet

public WorkSheetHandle createWorkSheet(java.lang.String name,
                                       int sheetpos)

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

Parameters:

String - the Name of the newly created worksheet

int - the position of the Sheet

Returns:

the new WorkSheetHandle

createWorkSheet

public WorkSheetHandle createWorkSheet(java.lang.String name)

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

Specified by:

createWorkSheet in interface WorkBook

Parameters:

String - the Name of the newly created worksheet

Returns:

the new WorkSheetHandle

toString

public java.lang.String toString()

Returns the name of this Sheet.

Specified by:

toString in interface WorkBook

Overrides:

toString in class java.lang.Object

See Also:

Object.toString()

getFormats

public FormatHandle[] getFormats()

Returns an array of all FormatHandles in the workbook

Specified by:

getFormats in interface WorkBook

Returns:

all existing FormatHandles in the workbook