Class Workbook
- All Implemented Interfaces:
Model
This file contains the low level binary records starting at the workbook's BOF and ending with the workbook's EOF. Use HSSFWorkbook for a high level representation.
The structures of the highlevel API use references to this to perform most of their operations. Its probably unwise to use these low level structures directly unless you really know what you're doing. I recommend you read the Microsoft Excel 97 Developer's Kit (Microsoft Press) and the documentation at http://sc.openoffice.org/excelfileformat.pdf before even attempting to use this.
- Version:
- 1.0-pre
- Author:
- Luc Girardin (luc dot girardin at macrofocus dot com), Sergei Kozello (sergeikozello at mail.ru), Shawn Laubach (slaubach at apache dot org) (Data Formats), Andrew C. Oliver (acoliver at apache dot org), Brian Sanders (bsanders at risklabs dot com) - custom palette, Dan Sherman (dsherman at isisph.com), Glen Stampoultzis (glens at apache.org)
- See Also:
-
Field Summary
FieldsModifier and TypeFieldDescriptionprotected ArrayListholds the "boundsheet" records (aka bundlesheet) so that they can have their reference to their "BOF" markerprotected ExternSheetRecordHolds the Extern Sheet with references to bound sheetsprotected ArrayListprotected ArrayListprotected intprotected intprotected WorkbookRecordListthis contains the Worksheet record objectsprotected SSTRecordthis contains a reference to the SSTRecord so that new stings can be added to it. -
Constructor Summary
Constructors -
Method Summary
Modifier and TypeMethodDescriptionintaddName(NameRecord name) creates new nameintaddSSTString(UnicodeString string) Adds a string to the SST table and returns its index (if its a duplicate just returns its index and update the counts) ASSUMES compressed unicode (meaning 8bit)shortcheckExternSheet(int sheetNumber) returns the extern sheet number for specific sheet number , if this sheet doesn't exist in extern sheet , add itprotected Recordcreates the Backup record with backup set to 0.protected Recordcreates the BOF recordprotected Recordcreates the BookBool record with saveLinkValues set to 0.protected RecordcreateBoundSheet(int id) create a "bound sheet" or "bundlesheet" (depending who you ask) record Always sets the sheet's bof to 0.createBuiltInName(byte builtInName, int index) Generates a NameRecord to represent a built-in regioncreates a new Cell-type Extneded Format Record and adds it to the end of ExtendedFormatRecords collectionprotected Recordcreates the Codepage record containing the constant stored in CODEPAGEprotected RecordCreates the Country record with the default country set to 1 and current country set to 7 in case of russian locale ("ru_RU") and 1 otherwiseprotected Recordcreates the DateWindow1904 record with windowing set to 0.voidCreates a drawing group record.protected Recordcreates the DSF record containing a 0 since HSSF can't even create Dual Stream Filesprotected Recordcreates the EOF recordprotected ExtendedFormatRecordcreates an default cell type ExtendedFormatRecord object.protected RecordcreateExtendedFormat(int id) Creates an ExtendedFormatRecord objectprotected RecordCreates the ExtendedSST record with numstrings per bucket set to 0x8.protected ExternSheetRecordcreates a new extern sheet recordprotected Recordcreates the FnGroupCount record containing the Magic number constant of 14.protected Recordcreates a Font record with the following magic values:protected RecordcreateFormat(int id) Creates a FormatRecord objectshortcreateFormat(String format) Creates a FormatRecord, inserts it, and returns the index code.protected Recordcreates the HideObj record with hide object set to 0.protected Recordcreates the InterfaceEnd recordprotected Recordcreates the InterfaceHdr recordprotected Recordcreates an MMS recordcreates new namecreates a new font record and adds it to the "font table".protected PaletteRecordCreates a palette record initialized to the default paletteprotected Recordcreates the Password record with password set to 0.protected Recordcreates the PasswordRev4 record with password set to 0.protected Recordcreates the Precision record with precision set to true.protected Recordcreates the Protect record with protect set to false.protected Recordcreates the ProtectionRev4 record with protect set to false.protected Recordcreates the RefreshAll record with refreshAll set to true.protected RecordCreates the SST record with no strings and the unique/num string set to 0protected RecordcreateStyle(int id) Creates a StyleRecord objectprotected Recordcreates the TabId record containing an array of 0,1,2.protected RecordCreates the UseSelFS object with the use natural language flag set to 0 (false)protected Recordcreates the WindowOne record with the following magic values:protected Recordcreates the WindowProtect record with protect set to false.static WorkbookCreates an empty workbook object with three blank sheets and all the empty fields.static WorkbookcreateWorkbook(List recs) read support for low level API.protected Recordcreates the WriteAccess record containing the logged in user's namebooleandoesContainsSheetName(String name, int excludeSheetIdx) Determines whether a workbook contains the privided sheet name.findFirstRecordBySid(short sid) Returns the first occurance of a record matching a particular sid.intfindFirstRecordLocBySid(short sid) Returns the index of a record matching a particular sid.findNextRecordBySid(short sid, int pos) Returns the next occurance of a record matching a particular sid.findSheetNameFromExternSheet(short num) finds the sheet name by his extern sheet indexReturns the position of the backup record.getBSERecord(int pictureIndex) Returns the custom palette in use for this workbook; if a custom palette record does not exist, then it is created.getExFormatAt(int index) gets the ExtendedFormatRecord at the given 0-based indexgetFontRecordAt(int idx) gets the font record at the given index in the font table.shortReturns a format index that matches the passed in format.Returns the list of FormatRecords in the workbook.getNameRecord(int index) gets the name recordintgets the number of font recordsintget the number of ExtendedFormat records contained in this workbook.intgets the total number of namesintintreturns the number of boundsheet objects contained in this workbook.intgetSheetIndex(String name) get the sheet's indexintgetSheetIndexFromExternSheetIndex(int externSheetNumber) Finds the sheet index for a particular external sheet number.getSheetName(int sheetnum) gets the name for a given sheet.intgetSize()getSpecificBuiltinRecord(byte name, int sheetIndex) Retrieves the Builtin NameRecord that matches the name and index There shouldn't be too many names to make the sequential search too slowgetSSTString(int str) given an index into the SST table, this function returns the corresponding String valuevoiduse this function to add a Shared String Table to an existing sheet (say generated by a different java api) without an sst....booleanWhether date windowing is based on 1/2/1904 or 1/1/1900.voidremoveBuiltinRecord(byte name, int sheetIndex) Removes the specified Builtin NameRecord that matches the name and indexvoidremoveName(int namenum) removes the namevoidremoveSheet(int sheetnum) intserialize(int offset, byte[] data) Serializes all records int the worksheet section into a big byte array.voidsetSheetBof(int sheetnum, int pos) Sets the BOF for a given sheetvoidsetSheetName(int sheetnum, String sheetname) sets the name for a given sheet.voidsetSheetName(int sheetnum, String sheetname, short encoding) Deprecated.3-Jan-06 Simply use setSheetNam e(int sheetnum, String sheetname)voidsetSheetOrder(String sheetname, int pos) sets the order of appearance for a given sheet.voidremoves the write protect flagvoidwriteProtectWorkbook(String password, String username) protect a workbook with a password (not encypted, just sets writeprotect flags and the password.
-
Field Details
-
records
this contains the Worksheet record objects -
sst
this contains a reference to the SSTRecord so that new stings can be added to it. -
externSheet
Holds the Extern Sheet with references to bound sheets -
boundsheets
holds the "boundsheet" records (aka bundlesheet) so that they can have their reference to their "BOF" marker -
formats
-
names
-
numxfs
protected int numxfs -
numfonts
protected int numfonts
-
-
Constructor Details
-
Workbook
public Workbook()Creates new Workbook with no intitialization --useless right now- See Also:
-
-
Method Details
-
createWorkbook
read support for low level API. Pass in an array of Record objects, A Workbook object is constructed and passed back with all of its initialization set to the passed in records and references to those records held. Unlike Sheet workbook does not use an offset (its assumed to be 0) since its first in a file. If you need an offset then construct a new array with a 0 offset or write your own ;-p.- Parameters:
recs- an array of Record objects- Returns:
- Workbook object
-
createWorkbook
Creates an empty workbook object with three blank sheets and all the empty fields. Use this to create a workbook from scratch. -
getSpecificBuiltinRecord
Retrieves the Builtin NameRecord that matches the name and index There shouldn't be too many names to make the sequential search too slow- Parameters:
name- byte representation of the builtin name to matchsheetIndex- Index to match- Returns:
- null if no builtin NameRecord matches
-
removeBuiltinRecord
public void removeBuiltinRecord(byte name, int sheetIndex) Removes the specified Builtin NameRecord that matches the name and index- Parameters:
name- byte representation of the builtin to matchsheetIndex- zero-based sheet reference
-
getNumRecords
public int getNumRecords() -
getFontRecordAt
gets the font record at the given index in the font table. Remember "There is No Four" (someone at M$ must have gone to Rocky Horror one too many times)- Parameters:
idx- the index to look at (0 or greater but NOT 4)- Returns:
- FontRecord located at the given index
-
createNewFont
creates a new font record and adds it to the "font table". This causes the boundsheets to move down one, extended formats to move down (so this function moves those pointers as well)- Returns:
- FontRecord that was just created
-
getNumberOfFontRecords
public int getNumberOfFontRecords()gets the number of font records- Returns:
- number of font records in the "font table"
-
setSheetBof
public void setSheetBof(int sheetnum, int pos) Sets the BOF for a given sheet- Parameters:
sheetnum- the number of the sheet to set the positing of the bof forpos- the actual bof position
-
getBackupRecord
Returns the position of the backup record. -
setSheetName
sets the name for a given sheet. If the boundsheet record doesn't exist and its only one more than we have, go ahead and create it. If its > 1 more than we have, except- Parameters:
sheetnum- the sheet number (0 based)sheetname- the name for the sheet
-
doesContainsSheetName
Determines whether a workbook contains the privided sheet name.- Parameters:
name- the name to testexcludeSheetIdx- the sheet to exclude from the check or -1 to include all sheets in the check.- Returns:
- true if the sheet contains the name, false otherwise.
-
setSheetName
Deprecated.3-Jan-06 Simply use setSheetNam e(int sheetnum, String sheetname)sets the name for a given sheet forcing the encoding. This is STILL A BAD IDEA. Poi now automatically detects unicode- Parameters:
sheetnum- the sheet number (0 based)sheetname- the name for the sheet
-
setSheetOrder
sets the order of appearance for a given sheet.- Parameters:
sheetname- the name of the sheet to reorderpos- the position that we want to insert the sheet into (0 based)
-
getSheetName
gets the name for a given sheet.- Parameters:
sheetnum- the sheet number (0 based)- Returns:
- sheetname the name for the sheet
-
getSheetIndex
get the sheet's index- Parameters:
name- sheet name- Returns:
- sheet index or -1 if it was not found.
-
removeSheet
public void removeSheet(int sheetnum) -
getNumSheets
public int getNumSheets()returns the number of boundsheet objects contained in this workbook.- Returns:
- number of BoundSheet records
-
getNumExFormats
public int getNumExFormats()get the number of ExtendedFormat records contained in this workbook.- Returns:
- int count of ExtendedFormat records
-
getExFormatAt
gets the ExtendedFormatRecord at the given 0-based index- Parameters:
index- of the Extended format record (0-based)- Returns:
- ExtendedFormatRecord at the given index
-
createCellXF
creates a new Cell-type Extneded Format Record and adds it to the end of ExtendedFormatRecords collection- Returns:
- ExtendedFormatRecord that was created
-
addSSTString
Adds a string to the SST table and returns its index (if its a duplicate just returns its index and update the counts) ASSUMES compressed unicode (meaning 8bit)- Parameters:
string- the string to be added to the SSTRecord- Returns:
- index of the string within the SSTRecord
-
getSSTString
given an index into the SST table, this function returns the corresponding String value- Returns:
- String containing the SST String
-
insertSST
public void insertSST()use this function to add a Shared String Table to an existing sheet (say generated by a different java api) without an sst....- See Also:
-
serialize
public int serialize(int offset, byte[] data) Serializes all records int the worksheet section into a big byte array. Use this to write the Workbook out.- Parameters:
offset- of the data to be writtendata- array of bytes to write this to
-
getSize
public int getSize() -
createBOF
creates the BOF record -
createInterfaceHdr
creates the InterfaceHdr record- Returns:
- record containing a InterfaceHdrRecord
- See Also:
-
createMMS
creates an MMS record -
createInterfaceEnd
creates the InterfaceEnd record- Returns:
- record containing a InterfaceEndRecord
- See Also:
-
createWriteAccess
creates the WriteAccess record containing the logged in user's name- Returns:
- record containing a WriteAccessRecord
- See Also:
-
createCodepage
creates the Codepage record containing the constant stored in CODEPAGE- Returns:
- record containing a CodepageRecord
- See Also:
-
createDSF
creates the DSF record containing a 0 since HSSF can't even create Dual Stream Files -
createTabId
creates the TabId record containing an array of 0,1,2. This release of HSSF always has the default three sheets, no less, no more.- Returns:
- record containing a TabIdRecord
- See Also:
-
createFnGroupCount
creates the FnGroupCount record containing the Magic number constant of 14.- Returns:
- record containing a FnGroupCountRecord
- See Also:
-
createWindowProtect
creates the WindowProtect record with protect set to false.- Returns:
- record containing a WindowProtectRecord
- See Also:
-
createProtect
creates the Protect record with protect set to false.- Returns:
- record containing a ProtectRecord
- See Also:
-
createPassword
creates the Password record with password set to 0.- Returns:
- record containing a PasswordRecord
- See Also:
-
createProtectionRev4
creates the ProtectionRev4 record with protect set to false.- Returns:
- record containing a ProtectionRev4Record
- See Also:
-
createPasswordRev4
creates the PasswordRev4 record with password set to 0.- Returns:
- record containing a PasswordRev4Record
- See Also:
-
createWindowOne
creates the WindowOne record with the following magic values:horizontal hold - 0x168
vertical hold - 0x10e
width - 0x3a5c
height - 0x23be
options - 0x38
selected tab - 0
displayed tab - 0
num selected tab- 0
tab width ratio - 0x258
- Returns:
- record containing a WindowOneRecord
- See Also:
-
createBackup
creates the Backup record with backup set to 0. (loose the data, who cares)- Returns:
- record containing a BackupRecord
- See Also:
-
createHideObj
creates the HideObj record with hide object set to 0. (don't hide)- Returns:
- record containing a HideObjRecord
- See Also:
-
createDateWindow1904
creates the DateWindow1904 record with windowing set to 0. (don't window)- Returns:
- record containing a DateWindow1904Record
- See Also:
-
createPrecision
creates the Precision record with precision set to true. (full precision)- Returns:
- record containing a PrecisionRecord
- See Also:
-
createRefreshAll
creates the RefreshAll record with refreshAll set to true. (refresh all calcs)- Returns:
- record containing a RefreshAllRecord
- See Also:
-
createBookBool
creates the BookBool record with saveLinkValues set to 0. (don't save link values)- Returns:
- record containing a BookBoolRecord
- See Also:
-
createFont
creates a Font record with the following magic values:fontheight = 0xc8
attributes = 0x0
color palette index = 0x7fff
bold weight = 0x190
Font Name Length = 5
Font Name = Arial
- Returns:
- record containing a FontRecord
- See Also:
-
createFormat
Creates a FormatRecord object- Parameters:
id- the number of the format record to create (meaning its position in a file as M$ Excel would create it.)- Returns:
- record containing a FormatRecord
- See Also:
-
createExtendedFormat
Creates an ExtendedFormatRecord object- Parameters:
id- the number of the extended format record to create (meaning its position in a file as MS Excel would create it.)- Returns:
- record containing an ExtendedFormatRecord
- See Also:
-
createExtendedFormat
creates an default cell type ExtendedFormatRecord object.- Returns:
- ExtendedFormatRecord with intial defaults (cell-type)
-
createStyle
Creates a StyleRecord object- Parameters:
id- the number of the style record to create (meaning its position in a file as MS Excel would create it.- Returns:
- record containing a StyleRecord
- See Also:
-
createPalette
Creates a palette record initialized to the default palette- Returns:
- a PaletteRecord instance populated with the default colors
- See Also:
-
createUseSelFS
Creates the UseSelFS object with the use natural language flag set to 0 (false)- Returns:
- record containing a UseSelFSRecord
- See Also:
-
createBoundSheet
create a "bound sheet" or "bundlesheet" (depending who you ask) record Always sets the sheet's bof to 0. You'll need to set that yourself.- Parameters:
id- either sheet 0,1 or 2.- Returns:
- record containing a BoundSheetRecord
- See Also:
-
createCountry
Creates the Country record with the default country set to 1 and current country set to 7 in case of russian locale ("ru_RU") and 1 otherwise- Returns:
- record containing a CountryRecord
- See Also:
-
createSST
Creates the SST record with no strings and the unique/num string set to 0 -
createExtendedSST
Creates the ExtendedSST record with numstrings per bucket set to 0x8. HSSF doesn't yet know what to do with this thing, but we create it with nothing in it hardly just to make Excel happy and our sheets look like Excel's- Returns:
- record containing an ExtSSTRecord
- See Also:
-
createEOF
creates the EOF record -
getSheetReferences
-
findSheetNameFromExternSheet
finds the sheet name by his extern sheet index- Parameters:
num- extern sheet index- Returns:
- sheet name
-
getSheetIndexFromExternSheetIndex
public int getSheetIndexFromExternSheetIndex(int externSheetNumber) Finds the sheet index for a particular external sheet number.- Parameters:
externSheetNumber- The external sheet number to convert- Returns:
- The index to the sheet found.
-
checkExternSheet
public short checkExternSheet(int sheetNumber) returns the extern sheet number for specific sheet number , if this sheet doesn't exist in extern sheet , add it- Parameters:
sheetNumber- sheet number- Returns:
- index to extern sheet
-
getNumNames
public int getNumNames()gets the total number of names- Returns:
- number of names
-
getNameRecord
gets the name record- Parameters:
index- name index- Returns:
- name record
-
createName
creates new name- Returns:
- new name record
-
addName
creates new name- Returns:
- new name record
-
createBuiltInName
Generates a NameRecord to represent a built-in region- Returns:
- a new NameRecord unless the index is invalid
-
removeName
public void removeName(int namenum) removes the name- Parameters:
namenum- name index
-
createExternSheet
creates a new extern sheet record- Returns:
- the new extern sheet record
-
getFormat
Returns a format index that matches the passed in format. It does not tie into HSSFDataFormat.- Parameters:
format- the format stringcreateIfNotFound- creates a new format if format not found- Returns:
- the format id of a format that matches or -1 if none found and createIfNotFound
-
getFormats
Returns the list of FormatRecords in the workbook.- Returns:
- ArrayList of FormatRecords in the notebook
-
createFormat
Creates a FormatRecord, inserts it, and returns the index code.- Parameters:
format- the format string- Returns:
- the index code of the format record.
- See Also:
-
findFirstRecordBySid
Returns the first occurance of a record matching a particular sid. -
findFirstRecordLocBySid
public int findFirstRecordLocBySid(short sid) Returns the index of a record matching a particular sid.- Parameters:
sid- The sid of the record to match- Returns:
- The index of -1 if no match made.
-
findNextRecordBySid
Returns the next occurance of a record matching a particular sid. -
getRecords
-
isUsing1904DateWindowing
public boolean isUsing1904DateWindowing()Whether date windowing is based on 1/2/1904 or 1/1/1900. Some versions of Excel (Mac) can save workbooks using 1904 date windowing.- Returns:
- true if using 1904 date windowing
-
getCustomPalette
Returns the custom palette in use for this workbook; if a custom palette record does not exist, then it is created. -
createDrawingGroup
public void createDrawingGroup()Creates a drawing group record. If it already exists then it's modified. -
getWindowOne
-
getBSERecord
-
addBSERecord
-
getDrawingManager
-
getWriteProtect
-
getWriteAccess
-
getFileSharing
-
writeProtectWorkbook
protect a workbook with a password (not encypted, just sets writeprotect flags and the password.- Parameters:
password- to set
-
unwriteProtectWorkbook
public void unwriteProtectWorkbook()removes the write protect flag
-