Package org.apache.poi.hssf.model
Class InternalWorkbook
java.lang.Object
org.apache.poi.hssf.model.InternalWorkbook
Low level model implementation of a Workbook. Provides creational methods
for settings and objects contained in the workbook object.
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.
- See Also:
-
Field Summary
FieldsModifier and TypeFieldDescriptionstatic final String
Name of older (pre-Excel 97) Workbook streams, which aren't supported by HSSFWorkbook, only byOldExcelExtractor
protected SSTRecord
this contains a reference to the SSTRecord so that new stings can be added to it.static final String[]
Normally, the Workbook will be in a POIFS Stream called "Workbook". -
Method Summary
Modifier and TypeMethodDescriptionint
addName
(NameRecord name) adds a name recordint
addSSTString
(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)boolean
changeExternalReference
(String oldUrl, String newUrl) Changes an external referenced file to another file.short
checkExternSheet
(int sheetNumber) Returns the extern sheet number for specific sheet number.short
checkExternSheet
(int firstSheetNumber, int lastSheetNumber) Returns the extern sheet number for specific range of sheets.void
cloneDrawings
(InternalSheet sheet) Check if the cloned sheet has drawings.cloneFilter
(int filterDbNameIndex, int newSheetIndex) createBuiltInName
(byte builtInName, int sheetNumber) Generates a NameRecord to represent a built-in regioncreates a new Cell-type Extended Format Record and adds it to the end of ExtendedFormatRecords collectionvoid
Creates a primary drawing group record.int
createFormat
(String formatString) Creates a FormatRecord, inserts it, and returns the index code.creates new namecreates a new font record and adds it to the "font table".createStyleRecord
(int xfIndex) Creates a new StyleRecord, for the given Extended Format index, and adds it onto the end of the records collectionstatic InternalWorkbook
Creates an empty workbook object with three blank sheets and all the empty fields.static InternalWorkbook
createWorkbook
(List<Record> recs) read support for low level API.boolean
doesContainsSheetName
(String name, int excludeSheetIdx) Determines whether a workbook contains the provided sheet name.Finds the primary drawing group, if one already existsfindFirstRecordBySid
(short sid) Returns the first occurance of a record matching a particular sid.int
findFirstRecordLocBySid
(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.findSheetFirstNameFromExternSheet
(int externSheetIndex) Finds the first sheet name by his extern sheet indexfindSheetLastNameFromExternSheet
(int externSheetIndex) Returns 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 indexgetExternalName
(int externSheetIndex, int externNameIndex) getExternalSheet
(int externSheetIndex) int
getExternalSheetIndex
(String workbookName, String sheetName) int
getExternalSheetIndex
(String workbookName, String firstSheetName, String lastSheetName) int
getFirstSheetIndexFromExternSheetIndex
(int externSheetNumber) Finds the (first) sheet index for a particular external sheet number.int
getFontIndex
(FontRecord font) Retrieves the index of the given fontgetFontRecordAt
(int idx) gets the font record at the given index in the font table.short
Returns a format index that matches the passed in format.Returns the list of FormatRecords in the workbook.int
getLastSheetIndexFromExternSheetIndex
(int externSheetNumber) Finds the last sheet index for a particular external sheet number, which may be the same as the first (except for multi-sheet references)getNameCommentRecord
(NameRecord nameRecord) gets the name comment recordgetNameRecord
(int index) gets the name recordgetNameXPtg
(String name, int sheetRefIndex, UDFFinder udf) getNameXPtg
(String name, UDFFinder udf) int
gets the number of font recordsint
get the number of ExtendedFormat records contained in this workbook.int
gets the total number of namesint
int
returns the number of boundsheet objects contained in this workbook.Get or create RecalcIdRecordint
getSheetIndex
(String name) get the sheet's indexgetSheetName
(int sheetIndex) gets the name for a given sheet.getSheetVisibility
(int sheetnum) Gets the hidden flag for a given sheet.int
getSize()
getSpecificBuiltinRecord
(byte name, int sheetNumber) 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 valuegetStyleRecord
(int xfIndex) Returns the StyleRecord for the given xfIndex, or null if that ExtendedFormat doesn't have a Style set.Only for internal calls - code based on this is not supported ...void
use this function to add a Shared String Table to an existing sheet (say generated by a different java api) without an sst....boolean
isSheetHidden
(int sheetnum) Gets the hidden flag for a given sheet.boolean
isSheetVeryHidden
(int sheetnum) Gets the very hidden flag for a given sheet.boolean
Whether date windowing is based on 1/2/1904 or 1/1/1900.boolean
is the workbook protected with a password (not encrypted)?int
linkExternalWorkbook
(String name, Workbook externalWorkbook) void
Perform any work necessary before the workbook is about to be serialized.void
removeBuiltinRecord
(byte name, int sheetIndex) Removes the specified Builtin NameRecord that matches the name and indexvoid
removeExFormatRecord
(int index) Removes ExtendedFormatRecord record with given index from the file's list.void
Removes the given ExtendedFormatRecord record from the file's list.void
Removes the given font record from the file's list.void
removeName
(int nameIndex) removes the namevoid
removeSheet
(int sheetIndex) resolveNameXText
(int refIndex, int definedNameIndex) int
serialize
(int offset, byte[] data) Serializes all records int the worksheet section into a big byte array.void
setSheetBof
(int sheetIndex, int pos) Sets the BOF for a given sheetvoid
setSheetHidden
(int sheetnum, boolean hidden) Hide or unhide a sheetvoid
setSheetHidden
(int sheetnum, SheetVisibility visibility) Hide or unhide a sheet.void
setSheetName
(int sheetnum, String sheetname) sets the name for a given sheet.void
setSheetOrder
(String sheetname, int pos) sets the order of appearance for a given sheet.void
removes the write protect flagvoid
updateNameCommentRecordCache
(NameCommentRecord commentRecord) If aNameCommentRecord
is added or the name it references is renamed, then this will update the lookup cache for it.void
updateNamesAfterCellShift
(FormulaShifter shifter) Updates named ranges due to moving of cellsvoid
updateStyleRecord
(int oldXf, int newXf) Update the StyleRecord to point to the new given index.void
writeProtectWorkbook
(String password, String username) protect a workbook with a password (not encypted, just sets writeprotect flags and the password.
-
Field Details
-
WORKBOOK_DIR_ENTRY_NAMES
Normally, the Workbook will be in a POIFS Stream called "Workbook". However, some weird XLS generators use "WORKBOOK" or "BOOK". -
OLD_WORKBOOK_DIR_ENTRY_NAME
Name of older (pre-Excel 97) Workbook streams, which aren't supported by HSSFWorkbook, only byOldExcelExtractor
- See Also:
-
sst
this contains a reference to the SSTRecord so that new stings can be added to it.
-
-
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.- Returns:
- an empty workbook object
-
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 matchsheetNumber
- 1-based sheet number- 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
-
getFontIndex
Retrieves the index of the given font- Parameters:
font
- the font- Returns:
- the font index
- Throws:
IllegalArgumentException
- if the font index can't be determined
-
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
-
removeFontRecord
Removes the given font record from the file's list. This will make all subsequent font indicies drop by one, so you'll need to update those yourself!- Parameters:
rec
- the font record
-
getNumberOfFontRecords
public int getNumberOfFontRecords()gets the number of font records- Returns:
- number of font records in the "font table"
-
setSheetBof
public void setSheetBof(int sheetIndex, int pos) Sets the BOF for a given sheet- Parameters:
sheetIndex
- 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.- 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 it's > 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 provided sheet name. For the purpose of comparison, long names are truncated to 31 chars.- Parameters:
name
- the name to test (case insensitive match)excludeSheetIdx
- 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.
-
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:
sheetIndex
- the sheet number (0 based)- Returns:
- sheetname the name for the sheet
-
isSheetHidden
public boolean isSheetHidden(int sheetnum) Gets the hidden flag for a given sheet. Note that a sheet could instead be set to be very hidden, which is different (isSheetVeryHidden(int)
)- Parameters:
sheetnum
- the sheet number (0 based)- Returns:
- True if sheet is hidden
-
isSheetVeryHidden
public boolean isSheetVeryHidden(int sheetnum) Gets the very hidden flag for a given sheet. This is different from the normal hidden flag (isSheetHidden(int)
)- Parameters:
sheetnum
- the sheet number (0 based)- Returns:
- True if sheet is very hidden
-
getSheetVisibility
Gets the hidden flag for a given sheet. Note that a sheet could instead be set to be very hidden, which is different (isSheetVeryHidden(int)
)- Parameters:
sheetnum
- the sheet number (0 based)- Returns:
- True if sheet is hidden
- Since:
- 3.16 beta 2
-
setSheetHidden
public void setSheetHidden(int sheetnum, boolean hidden) Hide or unhide a sheet- Parameters:
sheetnum
- The sheet numberhidden
- True to mark the sheet as hidden, false otherwise
-
setSheetHidden
Hide or unhide a sheet.- Parameters:
sheetnum
- The sheet numbervisibility
- the sheet visibility to set (visible, hidden, very hidden)- Since:
- 3.16 beta 2
-
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 sheetIndex) - Parameters:
sheetIndex
- zero based sheet index
-
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
-
removeExFormatRecord
Removes the given ExtendedFormatRecord record from the file's list. This will make all subsequent font indicies drop by one, so you'll need to update those yourself!- Parameters:
rec
- the ExtendedFormatRecord
-
removeExFormatRecord
public void removeExFormatRecord(int index) Removes ExtendedFormatRecord record with given index from the file's list. This will make all subsequent font indicies drop by one, so you'll need to update those yourself!- Parameters:
index
- of the Extended format record (0-based)
-
createCellXF
creates a new Cell-type Extended Format Record and adds it to the end of ExtendedFormatRecords collection- Returns:
- ExtendedFormatRecord that was created
-
getStyleRecord
Returns the StyleRecord for the given xfIndex, or null if that ExtendedFormat doesn't have a Style set.- Parameters:
xfIndex
- the extended format index- Returns:
- the StyleRecord,
null
if it that ExtendedFormat doesn't have a Style set.
-
updateStyleRecord
public void updateStyleRecord(int oldXf, int newXf) Update the StyleRecord to point to the new given index.- Parameters:
oldXf
- the extended format index that was previously associated with this StyleRecordnewXf
- the extended format index that is now associated with this StyleRecord
-
createStyleRecord
Creates a new StyleRecord, for the given Extended Format index, and adds it onto the end of the records collection- Parameters:
xfIndex
- the extended format index- Returns:
- a new StyleRecord
-
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- Parameters:
str
- the index into the SST table- 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:
-
createExtendedSST()
SSTRecord
-
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- Returns:
- the length of serialized bytes
-
preSerialize
public void preSerialize()Perform any work necessary before the workbook is about to be serialized. Include in it ant code that modifies the workbook record stream and affects its size. -
getSize
public int getSize() -
linkExternalWorkbook
-
findSheetFirstNameFromExternSheet
Finds the first sheet name by his extern sheet index- Parameters:
externSheetIndex
- extern sheet index- Returns:
- first sheet name.
-
findSheetLastNameFromExternSheet
-
getExternalSheet
-
getExternalName
-
getFirstSheetIndexFromExternSheetIndex
public int getFirstSheetIndexFromExternSheetIndex(int externSheetNumber) Finds the (first) sheet index for a particular external sheet number.- Parameters:
externSheetNumber
- The external sheet number to convert- Returns:
- The index to the sheet found.
-
getLastSheetIndexFromExternSheetIndex
public int getLastSheetIndexFromExternSheetIndex(int externSheetNumber) Finds the last sheet index for a particular external sheet number, which may be the same as the first (except for multi-sheet references)- 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
- local sheet number- Returns:
- index to extern sheet
-
checkExternSheet
public short checkExternSheet(int firstSheetNumber, int lastSheetNumber) Returns the extern sheet number for specific range of sheets. If this sheet range doesn't exist in extern sheet, add it- Parameters:
firstSheetNumber
- first local sheet numberlastSheetNumber
- last local sheet number- Returns:
- index to extern sheet
-
getExternalSheetIndex
-
getExternalSheetIndex
-
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
-
getNameCommentRecord
gets the name comment record- Parameters:
nameRecord
- name record who's comment is required.- Returns:
- name comment record or
null
if there isn't one for the given name.
-
createName
creates new name- Returns:
- new name record
-
addName
adds a name record- Parameters:
name
- the name record to be added- Returns:
- the given name record
-
createBuiltInName
Generates a NameRecord to represent a built-in region- Parameters:
builtInName
- the built-in namesheetNumber
- the sheet number- Returns:
- a new NameRecord
-
removeName
public void removeName(int nameIndex) removes the name- Parameters:
nameIndex
- name index
-
updateNameCommentRecordCache
If aNameCommentRecord
is added or the name it references is renamed, then this will update the lookup cache for it.- Parameters:
commentRecord
- the comment 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:
formatString
- 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.- Parameters:
sid
- the sid- Returns:
- the matching record or
null
if it wasn't found
-
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.- Parameters:
sid
- the sidpos
- specifies the n-th matching sid- Returns:
- the matching record or
null
if it wasn't found
-
getHyperlinks
-
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.- Returns:
- the custom palette
-
findDrawingGroup
Finds the primary drawing group, if one already exists- Returns:
- the primary drawing group
-
createDrawingGroup
public void createDrawingGroup()Creates a primary drawing group record. If it already exists then it's modified. -
getWindowOne
-
getBSERecord
-
addBSERecord
-
getDrawingManager
-
getWriteProtect
-
getWriteAccess
-
getFileSharing
-
isWriteProtected
public boolean isWriteProtected()is the workbook protected with a password (not encrypted)?- Returns:
true
if the workbook is write protected
-
writeProtectWorkbook
protect a workbook with a password (not encypted, just sets writeprotect flags and the password.- Parameters:
password
- the passwordusername
- the username
-
unwriteProtectWorkbook
public void unwriteProtectWorkbook()removes the write protect flag -
resolveNameXText
- Parameters:
refIndex
- Index to REF entry in EXTERNSHEET record in the Link TabledefinedNameIndex
- zero-based to DEFINEDNAME or EXTERNALNAME record- Returns:
- the string representation of the defined or external name
-
getNameXPtg
- Parameters:
name
- the name of an external function, typically a name of a UDFsheetRefIndex
- the sheet ref index, or -1 if not knownudf
- locator of user-defiend functions to resolve names of VBA and Add-In functions- Returns:
- the external name or null
-
getNameXPtg
-
cloneDrawings
Check if the cloned sheet has drawings. If yes, then allocate a new drawing group ID and re-generate shape IDs- Parameters:
sheet
- the cloned sheet
-
cloneFilter
-
updateNamesAfterCellShift
Updates named ranges due to moving of cells- Parameters:
shifter
- the formula shifter
-
getRecalcId
Get or create RecalcIdRecord- Returns:
- a new RecalcIdRecord
- See Also:
-
changeExternalReference
Changes an external referenced file to another file. A formular in Excel which refers a cell in another file is saved in two parts: The referenced file is stored in an reference table. the row/cell information is saved separate. This method invokation will only change the reference in the lookup-table itself.- Parameters:
oldUrl
- The old URL to search for and which is to be replacednewUrl
- The URL replacement- Returns:
- true if the oldUrl was found and replaced with newUrl. Otherwise false
-
getWorkbookRecordList
Only for internal calls - code based on this is not supported ...- Returns:
- The list of records.
-