Class Workbook

  • All Implemented Interfaces:
    Model

    public class Workbook
    extends Object
    implements Model
    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.

    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:
    HSSFWorkbook
    • Field Detail

      • records

        protected WorkbookRecordList records
        this contains the Worksheet record objects
      • sst

        protected SSTRecord sst
        this contains a reference to the SSTRecord so that new stings can be added to it.
      • externSheet

        protected ExternSheetRecord externSheet
        Holds the Extern Sheet with references to bound sheets
      • boundsheets

        protected ArrayList boundsheets
        holds the "boundsheet" records (aka bundlesheet) so that they can have their reference to their "BOF" marker
      • numxfs

        protected int numxfs
      • numfonts

        protected int numfonts
    • Constructor Detail

      • Workbook

        public Workbook()
        Creates new Workbook with no intitialization --useless right now
        See Also:
        createWorkbook(List)
    • Method Detail

      • createWorkbook

        public static Workbook createWorkbook​(List recs)
        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

        public static Workbook createWorkbook()
        Creates an empty workbook object with three blank sheets and all the empty fields. Use this to create a workbook from scratch.
      • getSpecificBuiltinRecord

        public NameRecord 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 slow
        Parameters:
        name - byte representation of the builtin name to match
        sheetIndex - 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 match
        sheetIndex - zero-based sheet reference
      • getNumRecords

        public int getNumRecords()
      • getFontRecordAt

        public FontRecord getFontRecordAt​(int idx)
        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

        public FontRecord 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 for
        pos - the actual bof position
      • getBackupRecord

        public BackupRecord getBackupRecord()
        Returns the position of the backup record.
      • setSheetName

        public void setSheetName​(int sheetnum,
                                 String sheetname)
        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

        public boolean doesContainsSheetName​(String name,
                                             int excludeSheetIdx)
        Determines whether a workbook contains the privided sheet name.
        Parameters:
        name - the name to test
        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.
      • setSheetName

        public void setSheetName​(int sheetnum,
                                 String sheetname,
                                 short encoding)
        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

        public void setSheetOrder​(String sheetname,
                                  int pos)
        sets the order of appearance for a given sheet.
        Parameters:
        sheetname - the name of the sheet to reorder
        pos - the position that we want to insert the sheet into (0 based)
      • getSheetName

        public String getSheetName​(int sheetnum)
        gets the name for a given sheet.
        Parameters:
        sheetnum - the sheet number (0 based)
        Returns:
        sheetname the name for the sheet
      • getSheetIndex

        public int getSheetIndex​(String name)
        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

        public ExtendedFormatRecord getExFormatAt​(int index)
        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

        public ExtendedFormatRecord createCellXF()
        creates a new Cell-type Extneded Format Record and adds it to the end of ExtendedFormatRecords collection
        Returns:
        ExtendedFormatRecord that was created
      • addSSTString

        public int 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)
        Parameters:
        string - the string to be added to the SSTRecord
        Returns:
        index of the string within the SSTRecord
      • getSSTString

        public UnicodeString getSSTString​(int str)
        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:
        createSST(), 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 written
        data - array of bytes to write this to
      • getSize

        public int getSize()
      • createBOF

        protected Record createBOF()
        creates the BOF record
        Returns:
        record containing a BOFRecord
        See Also:
        BOFRecord, Record
      • createInterfaceHdr

        protected Record createInterfaceHdr()
        creates the InterfaceHdr record
        Returns:
        record containing a InterfaceHdrRecord
        See Also:
        InterfaceHdrRecord, Record
      • createMMS

        protected Record createMMS()
        creates an MMS record
        Returns:
        record containing a MMSRecord
        See Also:
        MMSRecord, Record
      • createInterfaceEnd

        protected Record createInterfaceEnd()
        creates the InterfaceEnd record
        Returns:
        record containing a InterfaceEndRecord
        See Also:
        InterfaceEndRecord, Record
      • createWriteAccess

        protected Record createWriteAccess()
        creates the WriteAccess record containing the logged in user's name
        Returns:
        record containing a WriteAccessRecord
        See Also:
        WriteAccessRecord, Record
      • createCodepage

        protected Record createCodepage()
        creates the Codepage record containing the constant stored in CODEPAGE
        Returns:
        record containing a CodepageRecord
        See Also:
        CodepageRecord, Record
      • createDSF

        protected Record createDSF()
        creates the DSF record containing a 0 since HSSF can't even create Dual Stream Files
        Returns:
        record containing a DSFRecord
        See Also:
        DSFRecord, Record
      • createTabId

        protected Record 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:
        TabIdRecord, Record
      • createFnGroupCount

        protected Record createFnGroupCount()
        creates the FnGroupCount record containing the Magic number constant of 14.
        Returns:
        record containing a FnGroupCountRecord
        See Also:
        FnGroupCountRecord, Record
      • createWindowProtect

        protected Record createWindowProtect()
        creates the WindowProtect record with protect set to false.
        Returns:
        record containing a WindowProtectRecord
        See Also:
        WindowProtectRecord, Record
      • createProtect

        protected Record createProtect()
        creates the Protect record with protect set to false.
        Returns:
        record containing a ProtectRecord
        See Also:
        ProtectRecord, Record
      • createPassword

        protected Record createPassword()
        creates the Password record with password set to 0.
        Returns:
        record containing a PasswordRecord
        See Also:
        PasswordRecord, Record
      • createProtectionRev4

        protected Record createProtectionRev4()
        creates the ProtectionRev4 record with protect set to false.
        Returns:
        record containing a ProtectionRev4Record
        See Also:
        ProtectionRev4Record, Record
      • createPasswordRev4

        protected Record createPasswordRev4()
        creates the PasswordRev4 record with password set to 0.
        Returns:
        record containing a PasswordRev4Record
        See Also:
        PasswordRev4Record, Record
      • createWindowOne

        protected Record 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:
        WindowOneRecord, Record
      • createBackup

        protected Record createBackup()
        creates the Backup record with backup set to 0. (loose the data, who cares)
        Returns:
        record containing a BackupRecord
        See Also:
        BackupRecord, Record
      • createHideObj

        protected Record createHideObj()
        creates the HideObj record with hide object set to 0. (don't hide)
        Returns:
        record containing a HideObjRecord
        See Also:
        HideObjRecord, Record
      • createDateWindow1904

        protected Record createDateWindow1904()
        creates the DateWindow1904 record with windowing set to 0. (don't window)
        Returns:
        record containing a DateWindow1904Record
        See Also:
        DateWindow1904Record, Record
      • createPrecision

        protected Record createPrecision()
        creates the Precision record with precision set to true. (full precision)
        Returns:
        record containing a PrecisionRecord
        See Also:
        PrecisionRecord, Record
      • createRefreshAll

        protected Record createRefreshAll()
        creates the RefreshAll record with refreshAll set to true. (refresh all calcs)
        Returns:
        record containing a RefreshAllRecord
        See Also:
        RefreshAllRecord, Record
      • createBookBool

        protected Record createBookBool()
        creates the BookBool record with saveLinkValues set to 0. (don't save link values)
        Returns:
        record containing a BookBoolRecord
        See Also:
        BookBoolRecord, Record
      • createFont

        protected Record 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:
        FontRecord, Record
      • createFormat

        protected Record createFormat​(int id)
        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:
        FormatRecord, Record
      • createExtendedFormat

        protected Record createExtendedFormat​(int id)
        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:
        ExtendedFormatRecord, Record
      • createExtendedFormat

        protected ExtendedFormatRecord createExtendedFormat()
        creates an default cell type ExtendedFormatRecord object.
        Returns:
        ExtendedFormatRecord with intial defaults (cell-type)
      • createStyle

        protected Record createStyle​(int id)
        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:
        StyleRecord, Record
      • createPalette

        protected PaletteRecord createPalette()
        Creates a palette record initialized to the default palette
        Returns:
        a PaletteRecord instance populated with the default colors
        See Also:
        PaletteRecord
      • createUseSelFS

        protected Record createUseSelFS()
        Creates the UseSelFS object with the use natural language flag set to 0 (false)
        Returns:
        record containing a UseSelFSRecord
        See Also:
        UseSelFSRecord, Record
      • createBoundSheet

        protected Record createBoundSheet​(int id)
        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:
        BoundSheetRecord, Record
      • createCountry

        protected Record 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:
        CountryRecord, Record
      • createSST

        protected Record createSST()
        Creates the SST record with no strings and the unique/num string set to 0
        Returns:
        record containing a SSTRecord
        See Also:
        SSTRecord, Record
      • createExtendedSST

        protected Record 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:
        ExtSSTRecord, Record
      • createEOF

        protected Record createEOF()
        creates the EOF record
        Returns:
        record containing a EOFRecord
        See Also:
        EOFRecord, Record
      • findSheetNameFromExternSheet

        public String findSheetNameFromExternSheet​(short num)
        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

        public NameRecord getNameRecord​(int index)
        gets the name record
        Parameters:
        index - name index
        Returns:
        name record
      • createName

        public NameRecord createName()
        creates new name
        Returns:
        new name record
      • addName

        public NameRecord addName​(NameRecord name)
        creates new name
        Returns:
        new name record
      • createBuiltInName

        public NameRecord createBuiltInName​(byte builtInName,
                                            int index)
        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

        protected ExternSheetRecord createExternSheet()
        creates a new extern sheet record
        Returns:
        the new extern sheet record
      • getFormat

        public short getFormat​(String format,
                               boolean createIfNotFound)
        Returns a format index that matches the passed in format. It does not tie into HSSFDataFormat.
        Parameters:
        format - the format string
        createIfNotFound - 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

        public ArrayList getFormats()
        Returns the list of FormatRecords in the workbook.
        Returns:
        ArrayList of FormatRecords in the notebook
      • createFormat

        public short createFormat​(String format)
        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:
        FormatRecord, Record
      • findFirstRecordBySid

        public Record findFirstRecordBySid​(short sid)
        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

        public Record findNextRecordBySid​(short sid,
                                          int pos)
        Returns the next occurance of a record matching a particular sid.
      • getRecords

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

        public PaletteRecord 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.
      • writeProtectWorkbook

        public void writeProtectWorkbook​(String password,
                                         String username)
        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