com.extentech.ExtenXLS
Class WorkBookHandle

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

All Implemented Interfaces:

Document, Handle, WorkBook

Direct Known Subclasses:

EncryptedWorkBookHandle

public class WorkBookHandle
extends DocumentHandle
implements WorkBook, Handle

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 sheet = book.getWorkSheet("Sheet1");
CellHandle cell = sheet.getCell("B22");


In order to improve usability 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 adding or deleting values. You may wish to disable this feature to improve performance. To do so, use the following: 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");
If you enable this mode you will need to periodically clean up the generated temporary files in your working directory. All ExtenXLS temporary file names begin with "ExtenXLS_".

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 the default templates for new WorkBookHandles and WorkSheetHandles.

These issues are easily resolved by setting the system properties "com.extentech.ExtenXLS.licensekey" and "com.extentech.ExtenXLS.jarloc". These should contain the license key and the fully-qualified path to the ExtenXLS JAR, respectively.

If your environment provides a mechanism for setting Java system properties, you may use it to set them. If not, you may set them programatically as in the following example. Note that you will need to replace the example values below with the ones appropriate to your environment.
String licensekey = "1255087426532DDD35268256220ABTRRW";
System.setProperty("com.extentech.extenxls.licensekey", licensekey);

String jarloc = "/opt/ExtenXLS/lib/ExtenXLS.jar";
System.setProperty("com.extentech.extenxls.jarloc", jarloc);


Author:

John McMahon

See Also:

WorkBook, WorkSheetHandle, CellHandle

Field Summary

static 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 Spreadsheet with 3 WorkSheets: "Sheet1","Sheet2",and "Sheet3".

WorkBookHandle(byte[] barray)
Create a new WorkBookHandle from the byte array passed in.

WorkBookHandle(File fx)
constructor which takes the XLS file

WorkBookHandle(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(String filePath)
Constructor which takes the XLS file name(

WorkBookHandle(String filePath, int debug)
constructor which takes the XLS file name and has an optional debug setting to assist with output.

WorkBookHandle(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(URL url)
Fetches a workbook from a URL If you need to authenticate your URL connection first then use the InputStream constructor

Method Summary

boolean	addSheetFromWorkBook(WorkBookHandle sourceBook, String sourceSheetName, String destSheetName) Deprecated. - use addWorkSheet(WorkSheetHandle sht, String NewSheetName){
boolean	addSheetFromWorkBookWithFormatting(WorkBookHandle sourceBook, String sourceSheetName, String destSheetName) Deprecated. - use addWorkSheet(WorkSheetHandle sht, String NewSheetName){
WorkSheetHandle	addWorkSheet(WorkSheetHandle sourceSheet) Inserts a WorkSheetHandle from a separate WorkBookhandle into the current WorkBookHandle.
WorkSheetHandle	addWorkSheet(WorkSheetHandle sourceSheet, String NewSheetName) Inserts a WorkSheetHandle from a separate WorkBookhandle into the current WorkBookHandle.
void	calculateFormulas() Forces immediate recalculation of every formula in the workbook.
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
ChartHandle	createChart(String name, WorkSheetHandle wsh) Creates a new Chart and places it at the end of the workbook Under development and unsupported!
NameHandle	createNamedRange(String name, String rangeDef) Create a named range in the workbook Note that the named range designation can conform to excel specs, that is, boolean values, references, or string variables can be set.
WorkSheetHandle	createWorkSheet(String name) Creates a new worksheet and places it at the end of the workbook.
WorkSheetHandle	createWorkSheet(String name, int sheetpos) Creates a new worksheet and places it at the specified position.
void	deleteChart(String chartname, WorkSheetHandle wsh) delete an existing chart of the workbook
void	forceRecalc() Marks every formula in the workbook as dirty.
String[]	getAllStrings() Returns all strings that are in the SharedStringTable for this workbook.
byte[]	getBytes() Deprecated.
CellHandle	getCell(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(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
FormatHandle[]	getConditionalFormats() Returns an array of all Conditional Formats in the workbook these are formats referenced and used by the conditionally formatted ranges in the workbook.
FormatHandle[]	getFormats() Returns an array of all FormatHandles in the workbook
int	getFormulaCalculationMode() Get the calculation mode for the workbook.
FormulaHandle	getFormulaHandle(String celladdress) Returns a Formula Handle
ImageHandle	getImage(String imagename) Returns an ImageHandle for manipulating images in the WorkBook
ImageHandle[]	getImages() Returns all ImageHandles in the workbook
boolean	getIsExcel2007() Returns whether the underlying spreadsheet is in Excel 2007 format by default.
String	getName() Returns the name of this WorkBook
NameHandle	getNamedRange(String rangename) Returns a Named Range Handle
NameHandle	getNamedRangeInScope(String rangename) Returns a Named Range Handle if it exists in the specified scope.
NameHandle[]	getNamedRanges() Returns all Named Range Handles
NameHandle[]	getNamedRangesInScope() Returns all Named Range Handles scoped to WorkBook.
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(String ptname) get a handle to a PivotTable in the WorkBook
PivotTableHandle[]	getPivotTables() get an array of handles to all PivotTables in the WorkBook
String	getStats() Return useful statistics about this workbook.
String	getStats(boolean usehtml) Return useful statistics about this workbook.
com.extentech.formats.XLS.WorkBook	getWorkBook() Returns a low-level WorkBook.
WorkSheetHandle	getWorkSheet(int sheetnum) returns the handle to a WorkSheet by number.
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.
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	recalc() Recalculates all dirty formulas in the workbook immediately.
void	removeAllWorkSheets() Removes all of the WorkSheets from this WorkBook.
int	searchAndReplace(String searchfor, String replacewith) Searches all Cells in the workbook for the string occurrence and replaces with the replacement text.
void	setDefaultColWidth(int t) set Default col width Note: only affects undefined Columns containing Cells
void	setDefaultRowHeight(int t) set Default row height in twips (=1/20 of a point) 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	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	setShowSheetTabs(boolean show) Sets whether the sheet selection tabs should be shown.
void	setStringEncodingMode(int mode) Set Encoding mode of new Strings added to file.
boolean	showSheetTabs() Returns whether the sheet selection tabs should be shown.
void	write(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(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(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(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.
StringBuffer	writeBytes(OutputStream bout) Write the Spreadsheet bytes to an OutputStream.
void	writeXLSXBytes(OutputStream bout) Explicitly write the Spreadsheet bytes to an OutputStream in Excel 2007 (XLSX) format.

Methods inherited from class com.extentech.ExtenXLS.DocumentHandle

addProperty, close, finalize, getFactory, getProperties, getProperty, getVersion, getWorkingDirectory, reset, setDebugLevel, setName, setProperties, toString

Methods inherited from class java.lang.Object

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

Methods inherited from interface com.extentech.ExtenXLS.WorkBook

addProperty, getProperty, getWorkingDirectory, reset, setDebugLevel, setName, toString

Field Detail

CONVERTMULBLANKS

public static String CONVERTMULBLANKS

Constructor Detail

WorkBookHandle

public WorkBookHandle()

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

WorkBookHandle

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

Create a new WorkBookHandle from the byte array passed in. Byte array passed in must contain a valid xls or xlsx workbook file

Parameters:

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

WorkBookHandle

public WorkBookHandle(URL url)

Fetches a workbook from a URL If you need to authenticate your URL connection first then use the InputStream constructor

Parameters:

urlx -

Throws:

Exception

WorkBookHandle

public WorkBookHandle(String filePath)

Constructor which takes the XLS file name(

Parameters:

String - filePath the name of the XLS file to read

WorkBookHandle

public WorkBookHandle(String filePath,
                      int debug)

constructor which takes the XLS file name and has an optional debug setting to assist with output. Setting this value will cause verbose logging and is discouraged unless required for support.

Parameters:

String - filePath the name of the XLS file to read

Debug - level

WorkBookHandle

public WorkBookHandle(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(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(String searchfor,
                            String replacewith)

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

Returns:

the number of replacements that were made

getAllStrings

public String[] getAllStrings()

Returns all strings that are in the SharedStringTable for this workbook. The SST contains all standard string records in cells, but may not include such things as strings that are contained within formulas. This is useful for such things as full text indexing of workbooks

Returns:

Strings in the workbook.

is1904

public boolean is1904()

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

Returns:

whether the WorkBook is using the 1904 date system.

getXLSVersionString

public String getXLSVersionString()

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

Returns:

an Excel version string

getStats

public String getStats(boolean usehtml)

Return useful statistics about this workbook.

Parameters:

use - html line breaks

Returns:

a string contatining various statistics.

getStats

public String getStats()

Return useful statistics about this workbook.

Returns:

a string contatining various statistics.

getCell

public CellHandle getCell(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(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 Document

Specified by:

setProtected in interface WorkBook

Parameters:

boolean - whether to protect the book

setDefaultRowHeight

public void setDefaultRowHeight(int t)

set Default row height in twips (=1/20 of a point) 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

getFormulaHandle

public FormulaHandle getFormulaHandle(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(String imagename)
                     throws com.extentech.formats.XLS.ImageNotFoundException

Returns an ImageHandle for manipulating images in the WorkBook

Parameters:

imagename -

Returns:

Throws:

com.extentech.formats.XLS.ImageNotFoundException

getNamedRange

public NameHandle getNamedRange(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

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 WorkBook that exists in the scope

Throws:

com.extentech.formats.XLS.CellNotFoundException

createNamedRange

public NameHandle createNamedRange(String name,
                                   String rangeDef)

Create a named range in the workbook Note that the named range designation can conform to excel specs, that is, boolean values, references, or string variables can be set. Remember to utilize the sheet name when setting referential names. NameHandle nh = createNamedRange("cellRange", "Sheet1!A1:B3"); NameHandle nh = createNamedRange("trueRange", "=true");

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(String chartname)
                     throws com.extentech.formats.XLS.ChartNotFoundException

Returns a Chart Handle

Specified by:

getChart in interface Document

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 Document

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

getNamedRangesInScope

public NameHandle[] getNamedRangesInScope()

Returns all Named Range Handles scoped to WorkBook. Note this will not include worksheet scoped named ranges

Returns:

NameHandle[] all of the Named ranges that are scoped to WorkBook

getName

public String getName()

Returns the name of this WorkBook

Specified by:

getName in interface Document

Specified by:

getName in interface WorkBook

Overrides:

getName in class DocumentHandle

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

showSheetTabs

public boolean showSheetTabs()

Returns whether the sheet selection tabs should be shown.

setShowSheetTabs

public void setShowSheetTabs(boolean show)

Sets whether the sheet selection tabs should be shown.

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 Document

Specified by:

getBytes in interface WorkBook

Overrides:

getBytes in class DocumentHandle

Returns:

byte[] the XLS File's bytes

See Also:

WorkBook.getBytes()

write

public void write(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.

Overrides:

write in class DocumentHandle

Parameters:

fname -

write

public void write(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.

Overrides:

write in class DocumentHandle

Parameters:

fname -

Excel2007Format -

write

public void write(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(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.

Overrides:

write in class DocumentHandle

Parameters:

out -

writeXLSXBytes

public void writeXLSXBytes(OutputStream bout)
                    throws Exception

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 -

Throws:

Exception

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 StringBuffer writeBytes(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 Document

Specified by:

writeBytes in interface WorkBook

Parameters:

bout -

Returns:

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

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

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 Document

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 Document

Specified by:

setDupeStringMode in interface WorkBook

Parameters:

int - Duplicate String Handling Mode

copyChartToSheet

public void copyChartToSheet(String chartname,
                             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(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

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

Forces immediate recalculation of every formula in the workbook.

Specified by:

calculateFormulas in interface WorkBook

Throws:

com.extentech.formats.XLS.FunctionNotSupportedException - if an unsupported function is used by any formula in the workbook

See Also:

forceRecalc(), recalc()

forceRecalc

public void forceRecalc()

Marks every formula in the workbook as dirty.

See Also:

recalc()

recalc

public void recalc()

Recalculates all dirty formulas in the workbook immediately.

You generally need not call this method. Dirty formulas will automatically be recalculated when their values are queried. This method is only useful for forcing calculation to occur at a certain time. In the case of functions such as NOW() whose value is volatile the formula will still be recalculated every time it is queried.

Throws:

com.extentech.formats.XLS.FunctionNotSupportedException - if an unsupported function is used by any formula in the workbook

See Also:

forceRecalc()

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,
                                    String sourceSheetName,
                                    String destSheetName)

Deprecated. - use addWorkSheet(WorkSheetHandle sht, String NewSheetName){

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

addSheetFromWorkBookWithFormatting

public boolean addSheetFromWorkBookWithFormatting(WorkBookHandle sourceBook,
                                                  String sourceSheetName,
                                                  String destSheetName)

Deprecated. - use addWorkSheet(WorkSheetHandle sht, String NewSheetName){

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.

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

public WorkSheetHandle addWorkSheet(WorkSheetHandle sourceSheet)

Inserts a WorkSheetHandle from a separate WorkBookhandle into the current WorkBookHandle. copies charts, images, formats from source workbook Worksheet will be the same name as in the source workbook. To add a custom named worksheet use the addWorkSheet(WorkSheetHandle, String sheetname) method

Parameters:

WorkSheetHandle - the source WorkSheetHandle;

addWorkSheet

public WorkSheetHandle addWorkSheet(WorkSheetHandle sourceSheet,
                                    String NewSheetName)

Inserts a WorkSheetHandle from a separate WorkBookhandle into the current WorkBookHandle. copies charts, images, formats from source workbook

Specified by:

addWorkSheet in interface WorkBook

Parameters:

WorkSheetHandle - the source WorkSheetHandle;

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

createChart

public ChartHandle createChart(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(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(String name,
                                       int sheetpos)

Creates a new worksheet and places it at the specified position. The new sheet will be inserted before the sheet currently at the given index. If the given index is higher than the last index currently in use, the sheet will be added to the end of the workbook and will receive an index one higher than that of the current final sheet. If the given index is negative it will be interpreted as 0.

Parameters:

name - the name of the newly created worksheet

sheetpos - the index at which the sheet should be inserted

Returns:

the new WorkSheetHandle

createWorkSheet

public WorkSheetHandle createWorkSheet(String name)

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

Specified by:

createWorkSheet in interface WorkBook

Parameters:

name - the name of the newly created worksheet

Returns:

the new WorkSheetHandle

getFormats

public FormatHandle[] getFormats()

Returns an array of all FormatHandles in the workbook

Specified by:

getFormats in interface Document

Specified by:

getFormats in interface WorkBook

Returns:

all existing FormatHandles in the workbook

getConditionalFormats

public FormatHandle[] getConditionalFormats()

Returns an array of all Conditional Formats in the workbook these are formats referenced and used by the conditionally formatted ranges in the workbook.

Returns: