WorkBookHandle (ExtenXLS v10.1-20110808)

Overview

Package

Class

Use

Tree

Deprecated

Index

Help

PREV CLASS NEXT CLASS

FRAMES NO FRAMES

SUMMARY: NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD

com.extentech.ExtenXLS
Class WorkBookHandle

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

All Implemented Interfaces:: Document, Handle, WorkBook, Closeable

Direct Known Subclasses:: EncryptedWorkBookHandle, WorkBookParser

public class WorkBookHandle
extends DocumentHandle
implements WorkBook, Handle
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`
`static int`	`FORMAT_XLS` Format constant for BIFF8 (Excel '97-2007).
`static int`	`FORMAT_XLSM` Format constant for macro-enabled OOXML (Excel 2007).
`static int`	`FORMAT_XLSX` Format constant for normal OOXML (Excel 2007).
`static int`	`FORMAT_XLTM` Format constant for macro-enabled OOXML template (Excel 2007).
`static int`	`FORMAT_XLTX` Format constant for OOXML template (Excel 2007).

Fields inherited from class com.extentech.ExtenXLS.DocumentHandle
`FORMAT_NATIVE`

Fields inherited from interface com.extentech.ExtenXLS.WorkBook
`ALLOWDUPES, CALC_MODE_PROP, CALCULATE_ALWAYS, CALCULATE_AUTO, CALCULATE_EXPLICIT, REFTRACK_PROP, SHAREDUPES, STRING_ENCODING_AUTO, STRING_ENCODING_COMPRESSED, STRING_ENCODING_UNICODE`

Fields inherited from interface com.extentech.ExtenXLS.Document
`DEBUG_HIGH, DEBUG_LOW, DEBUG_MEDIUM`

Constructor Summary
`WorkBookHandle()` Default constructor creates a new, empty Spreadsheet with 3 WorkSheets: "Sheet1","Sheet2",and "Sheet3".
`WorkBookHandle(boolean Excel2007)` Constructor creates a new, empty Spreadsheet with 3 worksheets: "Sheet1", "Sheet2" and "Sheet3" This version allows flagging the workbook as Excel 2007 format.
`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`	`close()` Closes the WorkBook and releases resources.
`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
`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
`boolean`	`eventMode()`
`String[]`	`getAllStrings()` Returns all strings that are in the SharedStringTable for this workbook.
`byte[]`	`getBytes()` Deprecated. Writing the spreadsheet to a byte array uses a great deal of memory and generally provides no benefit over streaming output. Use the `write(java.lang.String)` family of methods instead. If you need a byte array use `ByteArrayOutputStream`.
`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.
`com.extentech.formats.XLS.WorkBookFactory`	`getFactory()` Gets the internal Factory object.
`String`	`getFileExtension()` Gets the file name extension for this document's native format.
`int`	`getFormat()` Gets the constant representing this document's native format.
`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
`File`	`getImage(String fname, int thumbWidth, int thumbHeight, int frameWidth, int frameHeight)` Generate a thumbnail JPEG image of the first worksheet in the workbook Feb 8, 2011
`ImageHandle[]`	`getImages()` Returns all ImageHandles in the workbook
`boolean`	`getIsExcel2007()` Returns whether the underlying spreadsheet is in Excel 2007 format by default.
`com.extentech.formats.LEO.LEOFile`	`getLEOFile()` Gets the internal LEOFile object.
`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`	`markFormulasDirty()` Marks every formula in the workbook as needing a recalc.
`void`	`recalc()` Recalculates all dirty formulas in the workbook immediately.
`void`	`removeAllWorkSheets()` Removes all of the WorkSheets from this WorkBook.
`void`	`reset()` Resets the document state to what it was when it was loaded.
`static File`	`saveComponentAsJPEG(Component comp, Container cont, String filename, int[] szs)` Comment me, particularly comp and cont, what do these do?
`static File`	`saveImageAsJPEG(BufferedImage bi, String filename)` Utility method to save an image stream to a file location.
`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`	`setIsExcel2007(boolean isExcel2007)` Sets whether this Workbook is in Excel 2007 format.
`void`	`setProtected(boolean protect)` 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 dest, boolean ooxml)` Deprecated. The boolean format parameter is not flexible enough to represent all supported formats. Use `write(OutputStream,int)` instead.
`void`	`write(OutputStream dest, int format)` Writes the document to the given stream in the requested format.
`void`	`write(String path)` Deprecated. The filename-based format choosing is counter-intuitive and failure-prone. Use `write(OutputStream,int)` instead.
`void`	`write(String path, boolean ooxml)` Deprecated. The boolean format parameter is not flexible enough to represent all supported formats. Use `DocumentHandle.write(File,int)` instead.
`StringBuffer`	`writeBytes(OutputStream dest)` Deprecated. Use `write(OutputStream,int)` instead.
`void`	`writeXLSXBytes(OutputStream dest)` Deprecated. Use `#write(OutputStream,int`) instead.
`void`	`writeXLSXBytes(OutputStream dest, int format)` Deprecated. This method is like `write(OutputStream,int)` except it only supports OOXML formats. Use that instead.

Methods inherited from class com.extentech.ExtenXLS.DocumentHandle
`addProperty, getFile, getFileName, getInstance, getProperties, getProperty, getVersion, setDebugLevel, setFile, setFileName, setName, setProperties, toString, write, write, write`

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

Methods inherited from interface com.extentech.ExtenXLS.WorkBook
`addProperty, getProperty, setDebugLevel, setName, toString`

Field Detail

CONVERTMULBLANKS

public static String CONVERTMULBLANKS

FORMAT_XLS

public static final int FORMAT_XLS

Format constant for BIFF8 (Excel '97-2007).

See Also:: Constant Field Values

FORMAT_XLSX

public static final int FORMAT_XLSX

Format constant for normal OOXML (Excel 2007).

See Also:: Constant Field Values

FORMAT_XLSM

public static final int FORMAT_XLSM

Format constant for macro-enabled OOXML (Excel 2007).

See Also:: Constant Field Values

FORMAT_XLTX

public static final int FORMAT_XLTX

Format constant for OOXML template (Excel 2007).

See Also:: Constant Field Values

FORMAT_XLTM

public static final int FORMAT_XLTM

Format constant for macro-enabled OOXML template (Excel 2007).

See Also:: Constant Field Values

Constructor Detail

WorkBookHandle

public WorkBookHandle()

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

WorkBookHandle

public WorkBookHandle(boolean Excel2007)

Constructor creates a new, empty Spreadsheet with 3 worksheets: "Sheet1", "Sheet2" and "Sheet3"
This version allows flagging the workbook as Excel 2007 format.
Excel 2007 format contains larger maximum column and row contraints, for example.
Even if the workbook is set to Excel 2007 format, 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.

Parameters:: boolean - Excel2007 - true if set to Excel 2007 version

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

eventMode

public boolean eventMode()

getFactory

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

Gets the internal Factory object.

WARNING: This method is not part of the public API. Its use is not supported and behavior is subject to change.

getLEOFile

public com.extentech.formats.LEO.LEOFile getLEOFile()

Gets the internal LEOFile object.

WARNING: This method is not part of the public API. Its use is not supported and behavior is subject to change.

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

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

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

@Deprecated
public byte[] getBytes()

Deprecated. Writing the spreadsheet to a byte array uses a great deal of memory and generally provides no benefit over streaming output. Use the write(java.lang.String) family of methods instead. If you need a byte array use ByteArrayOutputStream.

Gets the spreadsheet as a byte array in BIFF8 (Excel '97-2003) format.

Specified by:: getBytes in interface WorkBook

Returns:: byte[] the XLS File's bytes

write

@Deprecated
public void write(String path)

Deprecated. The filename-based format choosing is counter-intuitive and failure-prone. Use write(OutputStream,int) instead.

Writes the document to the given path. If the filename ends with ".xlsx" or ".xlsm", the workbook will be written as OOXML (XLSX). Otherwise it will be written as BIFF8 (XLS). For OOXML, if the file has a VBA project the file extension must be ".xlsm". It will be changed if necessary.

Parameters:: path - the path to which the document should be written

write

@Deprecated
public void write(String path,
                             boolean ooxml)

Deprecated. The boolean format parameter is not flexible enough to represent all supported formats. Use DocumentHandle.write(File,int) instead.

Writes the document to the given file in either XLS or XLSX. For OOXML, if the file has a VBA project the file extension must be ".xlsm". It will be changed if necessary.

Parameters:: path - the path to which the document should be written; ooxml - If true, write as OOXML (XLSX). Otherwise, write as BIFF8 (XLS).

write

@Deprecated
public void write(OutputStream dest,
                             boolean ooxml)

Deprecated. The boolean format parameter is not flexible enough to represent all supported formats. Use write(OutputStream,int) instead.

Writes the document to the given stream in either XLS or XLSX format.

Parameters:: dest - the stream to which the document should be written; ooxml - If true, write as OOXML (XLSX). Otherwise, write as BIFF8 (XLS).

getFormat

public int getFormat()

Gets the constant representing this document's native format.

Specified by:: getFormat in class DocumentHandle

getFileExtension

public String getFileExtension()

Description copied from class: DocumentHandle

Gets the file name extension for this document's native format.

Specified by:: getFileExtension in class DocumentHandle

write

public void write(OutputStream dest,
                  int format)
           throws IOException

Writes the document to the given stream in the requested format.

format choices:

WorkBookHandle.FORMAT_XLS for 2003 and previous versions
WorkBookHandle.FORMAT_XLSX for non-macro-enabled 2007 version
WorkBookHandle.FORMAT_XLSM for macro-enabled 2007 version
WorkBookHandle.FORMAT_XLTM for macro-enabled 2007 templates.
WorkBookHandle.FORMAT_XLTX for 2007 templates,

IMPORTANT NOTE: if the resulting filename contains the .XLSM extension
the WorkBook MUST be written in FORMAT_XLSM; otherwise open errors will occur

NOTE: If the format is FORMAT_XLSX and the filename contains macros
the file will be written as Macro-Enabled i.e. in FORMAT_XLSM. In these cases,
the filename must contain the .XLSM extension

Specified by:: write in class DocumentHandle

Parameters:: dest - the stream to which the document should be written; format - the constant representing the desired output format
Throws:: IllegalArgumentException - if the given type code is invalid; IOException - if an error occurs while writing to the stream

writeXLSXBytes

@Deprecated
public void writeXLSXBytes(OutputStream dest,
                                      int format)
                    throws Exception

Deprecated. This method is like write(OutputStream,int) except it only supports OOXML formats. Use that instead.

Writes the document to the given stream in the requested OOXML format.

Parameters:: dest - the stream to which the document should be written; format - the constant representing the desired output format
Throws:: IllegalArgumentException - if the given type code is invalid; IOException - if an error occurs while writing to the stream; Exception

writeXLSXBytes

@Deprecated
public void writeXLSXBytes(OutputStream dest)
                    throws Exception

Deprecated. Use #write(OutputStream,int) instead.

Writes the document to the given stream in the default OOXML format.

Parameters:: dest - the stream to which the document should be written
Throws:: IOException - if an error occurs while writing to the stream; 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

setIsExcel2007

public void setIsExcel2007(boolean isExcel2007)

Sets whether this Workbook is in Excel 2007 format. Excel 2007 format contains larger maximum column and row contraints, for example.
Even if the workbook is set to Excel 2007 format, 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.

Parameters:: isExcel2007 -

writeBytes

@Deprecated
public StringBuffer writeBytes(OutputStream dest)

Deprecated. Use write(OutputStream,int) instead.

Writes the document to the given stream in BIFF8 (XLS) format.

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:: dest - the stream to which the document should be written
Returns:: for debugging: a StringBuffer containing an output of the record bytes streamed

close

public void close()

Closes the WorkBook and releases resources.

Specified by:: close in interface Closeable

reset

public void reset()

Description copied from class: DocumentHandle

Resets the document state to what it was when it was loaded.

Specified by:: reset in interface Document
Specified by:: reset in interface WorkBook
Specified by:: reset in class DocumentHandle

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

markFormulasDirty

public void markFormulasDirty()

Marks every formula in the workbook as needing a recalc. This method does not actually calculate formulas, for that use 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:: markFormulasDirty()

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

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

getImage

public File getImage(String fname,
                     int thumbWidth,
                     int thumbHeight,
                     int frameWidth,
                     int frameHeight)
              throws Exception

Generate a thumbnail JPEG image of the first worksheet in the workbook Feb 8, 2011

Parameters:: fname -; thumbWidth -; thumbHeight -; frameWidth -; frameHeight -
Returns:
Throws:: Exception

saveComponentAsJPEG

public static File saveComponentAsJPEG(Component comp,
                                       Container cont,
                                       String filename,
                                       int[] szs)

Comment me, particularly comp and cont, what do these do?

saveImageAsJPEG

public static File saveImageAsJPEG(BufferedImage bi,
                                   String filename)

Utility method to save an image stream to a file location. Handles non-existent files.

Overview

Package

Class

Use

Tree

Deprecated

Index

Help

PREV CLASS NEXT CLASS

FRAMES NO FRAMES

SUMMARY: NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD

com.extentech.ExtenXLS Class WorkBookHandle

CONVERTMULBLANKS

FORMAT_XLS

FORMAT_XLSX

FORMAT_XLSM

FORMAT_XLTX

FORMAT_XLTM

WorkBookHandle

WorkBookHandle

WorkBookHandle

WorkBookHandle

WorkBookHandle

WorkBookHandle

WorkBookHandle

WorkBookHandle

WorkBookHandle

WorkBookHandle

eventMode

getFactory

getLEOFile

searchAndReplace

getAllStrings

is1904

getXLSVersionString

getStats

getStats

getCell

getCellRanges

getPivotTable

getPivotTables

setFormulaCalculationMode

getFormulaCalculationMode

setProtected

setDefaultRowHeight

setDefaultColWidth

getFormulaHandle

getImages

getImage

getNamedRange

getNamedRangeInScope

createNamedRange

getChart

getCharts

getChartById

getNamedRanges

getNamedRangesInScope

getName

getCells

getNumCells

showSheetTabs

setShowSheetTabs

getBytes

write

write

write

getFormat

getFileExtension

write

writeXLSXBytes

writeXLSXBytes

getIsExcel2007

setIsExcel2007

writeBytes

close

reset

getWorkSheets

getWorkSheet

getWorkSheet

getWorkBook

setStringEncodingMode

setDupeStringMode

copyChartToSheet

copyChartToSheet

copyWorkSheet

calculateFormulas

markFormulasDirty

recalc

removeAllWorkSheets

getNoSheetWorkBook

addSheetFromWorkBook

com.extentech.ExtenXLS
Class WorkBookHandle