MICROSOFT EXCEL BINARY FILE FORMAT ---------------------------------- Mark O'Brien Microsoft Corporation 18-Feb-1988 Table of Contents ----------------- Introduction General BIFF Record Format Rows and Columns Within BIFF Cell Table - Concepts Cell Records Record Types Cell Attributes Order of Records Finding Values From BIFF Files Excel Formulas Expression Evaluation Unary Operators Binary Operators Operand Tokens - Constant Operand Tokens - Classes Operand Tokens - Base Control Tokens Function Operators Reserved Ptg's Scanning a Parsed Expression Excel Function Table Command Equivalent Function Table List of Ptg's Introduction ------------ BIFF (BInary File Format) is the file format in which Excel documents are saved on disk. A BIFF file is a complete description of an Excel document. BIFF was designed to satisfy the following goals: - Easy to understand and use - Easy to expand the file format for future needs - Files should save and load quickly BIFF files consist of sequences of variable-length records. There are many different types of BIFF records. For example, one record type describes a formula entered into a cell; one describes the size and location of a window into a document; another describes a picture format. General BIFF Record Format -------------------------- Although different BIFF record types contain different information, every record follows the same basic format: - Record type. This tells us what kind of data the record contains (e.g. a formula, a window, or a picture format). - Record length. This tells us how long the data contained in the record is. The length of a record depends on the type of data it contains. For example, a window record may always be the same length, containing just the size and location of a window, while a formula record varies in length, depending on the length of the formula itself. - Record data. This is the variable-length portion of the record containing the actual data. All BIFF records are in the following format: Offset Length Contents ------ ------ -------- 0 word record type 2 word length of data portion 4 varies data portion of record The data portion of a BIFF record must be no longer than 2080 bytes long. Thus, counting the record type and length fields, the maximum length of a BIFF record is 2084 bytes. Within this document, all numbers are decimal numbers unless they are preceded by "0x", in which case they are hexadecimal. Some portions of BIFF records are marked as RESERVED. These portions are unavailable for application use. If they are marked "RESERVED - must be zero", then a BIFF-related application should ensure that their contents are always filled with zeros. If they are marked simply RESERVED, then they do not need to be set to any particular value. Rows and Columns Within BIFF ---------------------------- Within BIFF files, rows and columns are always stored zero-based, rather than one-based as they appear on the screen. For example, cell A1 is stored as row 0, column 0; cell B3 is row 2, column 1. Cell Table - Concepts --------------------- Microsoft Excel uses a sparse cell table to reduce memory requirements as much as possible. Cells that don't have values or formulas in them, have default format attributes, and are not referenced in any other formulas, are undefined cells and do not have any memory allocated for them. For example, if a worksheet has a value in cell A3 and the formula =A3+A4 in cell B10, then the only defined cells on the worksheet are A3, A4, and B10. No other cells need to exist. Entire rows can be undefined, if they have no defined cells in them. In this case, only rows 3, 4, and 10 are defined. Cell Records ------------ The term "cell record" refers to a BIFF record that defines a cell on an Excel document. A cell record is one of the following types: BLANK INTEGER NUMBER LABEL BOOLERR FORMULA The following records can occur in conjunction with cell records: CONTINUE ARRAY TABLE TABLE2 Record Types ------------ Here are the record types defined in BIFF. The record type and record length fields have been omitted from the descriptions, but they are present in every record. BOF record - beginning of file (type = 9) Offset Name Size Contents ------ ---- ---- -------- 4 vers 2 version number 6 dt 2 document type 0x10 = worksheet 0x40 = macro sheet Description The BOF must be the first record in every BIFF file. The version number for Excel documents is currently 2; Microsoft may change this number in the future, as BIFF is modified for future needs. Currently defined version numbers are: Value Name Meaning ----- ---- ------- 2 versExcel Excel document 3 versMP Multiplan document All other version numbers are reserved for future use by Microsoft. The high byte of the version number contains flag bits. Current flag values are: Mask Name Meaning ---- ---- ------- 0x0100 bitFMP =1 if the BIFF file is a Multiplan document 0xFE00 RESERVED for future use - must be zeros The dt field specifies whether the document is a worksheet or a macro sheet. Chart BIFF files are different and are not described in this document. FILEPASS record - file password key (type = 47) Description The FILEPASS record is used for an Excel document which was saved with a password in the File Save As command. If this record appears, it must directly follow the BOF record. All subsequent BIFF records will be encrypted, so you cannot read a password-protected BIFF file. Note that this record specifies a file password, as opposed to the PASSWORD record, which specifies a document password. INDEX record - ROW record index (type = 11) Offset Name Size Contents ------ ---- ---- -------- 4 ibRtName 4 absolute file position of the first NAME record 8 rwMic 2 first row that exists on the document 10 rwMac 2 last row that exists on the document, plus 1 12 rgibRw var array of absolute file positions of the blocks of ROW records Description The INDEX record is used to optimize searching through a file for a particular cell or name. This record is optional; if it occurs, it must occur directly after the document's FILEPASS record. If the document has no FILEPASS record, then the INDEX record must occur directly after the BOF record. The ibRtName field gives the absolute file offset (0 = beginning of file) of the first NAME record. rwMic and rwMac are the range of defined rows in the document. The rgibRw field is an array of 4-byte absolute file offsets to the document's ROW records. Excel always writes an INDEX record when it saves a BIFF file. If you are writing a BIFF file, you should probably not attempt to write an INDEX record. The INDEX record is explained more fully in the section "Finding Values From BIFF Files." CALCCOUNT record - iteration count (type = 12) Offset Name Size Contents ------ ---- ---- -------- 4 cIter 2 iteration count Description The CALCCOUNT record specifies the iteration count as set in the Options Calculation command. CALCMODE record - calculation mode (type = 13) Offset Name Size Contents ------ ---- ---- -------- 4 fAutoRecalc 2 calculation mode =0 for manual =1 for automatic =-1 for automatic, no tables Description The CALCMODE record specifies the calculation mode as set in the Options Calculation command. PRECISION record - precision (type = 14) Offset Name Size Contents ------ ---- ---- -------- 4 fFullPrec 2 document precision =1 for full precision =0 for precision as displayed Description The PRECISION record specifies the precision as set in the Options Calculation command. REFMODE record - reference mode (type = 15) Offset Name Size Contents ------ ---- ---- -------- 4 fRefA1 2 reference mode =1 for A1 mode =0 for R1C1 mode Description The REFMODE record specifies the reference mode as set in the Options Desktop command. DELTA record - maximum iteration change (type = 16) Offset Name Size Contents ------ ---- ---- -------- 4 numDelta 8 maximum change for iteration Description The DELTA record specifies the maximum change for an iterative model, as set in the Options Calculation command. The number is in 8-byte IEEE floating point format. ITERATION record - iteration flag (type = 17) Offset Name Size Contents ------ ---- ---- -------- 4 fIter 2 iteration flag =1 for iteration on =0 for iteration off Description The ITERATION record specifes the state of iteration as set in the Options Calculation command. 1904 record - date system (type = 34) Offset Name Size Contents ------ ---- ---- -------- 4 f1904 2 =1 if the document uses the 1904 date system =0 otherwise Description The 1904 record specifies the date system used in an Excel document, as specified in the Options Calculation command. BACKUP record - file backup option (type = 64) Offset Name Size Contents ------ ---- ---- -------- 4 fBackupFile 2 =1 if Excel should save a backup version of the file when it is saved =0 otherwise Description The BACKUP record specifies whether or not Excel should save backup versions of a BIFF file, as specified in the "Create Backup File" checkbox in the Save As dialog box. PRINT ROW HEADERS record - print row headers flag (type = 42) Offset Name Size Contents ------ ---- ---- -------- 4 fPrintRwCol 2 =1 if we should print row and column headers when printing the document =0 otherwise Description The PRINT ROW HEADERS record controls whether or not Excel prints row and column headers when printing the document. PRINT GRIDLINES record - print gridlines flag (type = 43) Offset Name Size Contents ------ ---- ---- -------- 4 fPrintGrid 2 =1 if we should print gridlines when printing the document =0 otherwise Description The PRINT GRIDLINES record controls whether or not Excel prints gridlines when printing the document. HORIZONTAL PAGE BREAKS record - row page breaks (type = 27) Offset Name Size Contents ------ ---- ---- -------- 4 cbrk 2 number of page breaks 6 rgrw var array of rows Description The HORIZONTAL PAGE BREAKS record contains a list of explicit row page breaks. The cbrk field contains the number of page breaks. rgrw is an array of 2-byte integers specifying rows. Excel sets a page break before each row in the list. The rows must be sorted in increasing order. VERTICAL PAGE BREAKS record - column page breaks (type = 26) Offset Name Size Contents ------ ---- ---- -------- 4 cbrk 2 number of page breaks 6 rgcol var array of columns Description The VERTICAL PAGE BREAKS record contains a list of explicit column page breaks. The cbrk field contains the number of page breaks. rgcol is an array of 2-byte integers specifying columns. Excel sets a page break before each column in the list. The columns must be sorted in increasing order. DEFAULT ROW HEIGHT record - default row height (type = 37) Offset Name Size Contents ------ ---- ---- -------- 4 miyRwGhost 2 default row height Description The DEFAULT ROW HEIGHT record specifies the height of all undefined rows in the document. The miyRwGhost field contains the row height in units of 1/20 of a point. This record does not affect the row heights of any rows that are explicitly defined. FONT record - document font (type = 49) Offset Name Size Contents ------ ---- ---- -------- 4 dy 2 height of the font 6 grbit 2 font attributes 8 cch 1 length of font name 9 rgch var the font name Description The FONT record describes an entry in the Excel document's font table. There are up to four different fonts on an Excel document, numbered 0 to 3. FONT records are read into the font table in the order in which they are encountered in the BIFF file. The dy field gives the height of the font in units of 1/20 of a point. grbit contains the font attributes as follows: Offset Bits Mask Name Contents ------ ---- ---- ---- -------- 0 7-0 0xFF RESERVED - must be zeros 1 7-4 0xF0 RESERVED - must be zeros 3 0x08 fStrikeout =1 if the font is struck out 2 0x04 fUnderline =1 if the font is underlined 1 0x02 fItalic =1 if the font is italic 0 0x01 fBold =1 if the font is bold cch and rgch contain the font's face name. FONT2 record - more font information (type = 50) Description The FONT2 record contains system-specific information about the font defined in the previous FONT record. This record is optional. If you are writing a BIFF file, do not write a FONT2 record. HEADER record - print header string (type = 20) Offset Name Size Contents ------ ---- ---- -------- 4 cch 1 length of string 5 rgch var the string Description The HEADER record specifies a print header string for a document. This string appears at the top of every page when the document is printed. FOOTER record - print footer string (type = 21) Offset Name Size Contents ------ ---- ---- -------- 4 cch 1 length of string 5 rgch var the string Description The FOOTER record specifies a print footer string for a document. This string appears at the bottom of every page when the document is printed. LEFT MARGIN record - left print margin (type = 38) Offset Name Size Contents ------ ---- ---- -------- 4 num 8 left margin Description The LEFT MARGIN record specifies the left margin in inches when a document is printed. The num field is in 8-byte IEEE floating point format. RIGHT MARGIN record - (type = 39) Offset Name Size Contents ------ ---- ---- -------- 4 num 8 right margin Description The RIGHT MARGIN record specifies the right margin in inches when a document is printed. The num field is in 8-byte IEEE floating point format. TOP MARGIN record - (type = 40) Offset Name Size Contents ------ ---- ---- -------- 4 num 8 top margin Description The TOP MARGIN record specifies the top margin in inches when a document is printed. The num field is in 8-byte IEEE floating point format. BOTTOM MARGIN record - (type = 41) Offset Name Size Contents ------ ---- ---- -------- 4 num 8 bottom margin Description The BOTTOM MARGIN record specifies the bottom margin in inches when a document is printed. The num field is in 8- byte IEEE floating point format. COLWIDTH record - column width (type = 36) Offset Name Size Contents ------ ---- ---- -------- 4 colFirst 1 first column in the range 5 colLast 1 last column in the range 6 dx 2 column width Description The COLWIDTH record sets the column width for a range of columns specified by colFirst and colLast. The dx field is an unsigned integer specifying the column width in units of 1/256 of a character. EXTERNCOUNT record - count of externally referenced documents (type = 22) Offset Name Size Contents ------ ---- ---- -------- 4 cxals 2 number of externally referenced documents Description The EXTERNCOUNT record specifies the number of documents that are referenced externally from an Excel document. Both external references and Dynamic Data Exchange (DDE) references are counted here. For external references, only the supporting sheet name counts. For DDE references, the application-topic pair counts. For example, suppose a worksheet contains the following formulas: =SALES.XLS!Gross-SALES.XLS!Profits =Signal|System!Formats =Signal|StockInfo!IBM This worksheet would have an EXTERNCOUNT of three: SALES.XLS, Signal|System, and Signal|StockInfo. EXTERNSHEET record - externally referenced document (type = 23) Offset Name Size Contents ------ ---- ---- -------- 4 cch 1 length of document name 5 rgch var document name Description The EXTERNSHEET record specifes a document which is referenced externally from an Excel document. There must be as many EXTERNSHEET records in a BIFF file as were specified in the EXTERNCOUNT record. The order of EXTERNSHEET records in a BIFF file is important and should not be changed. The document that is externally referenced is called the supporting document. The document which refers to it is called the dependent document. The cch field gives the length of the supporting document name, which is contained in the rgch field. Whenever possible, document names are encoded to make BIFF files compatible with file systems other than DOS. Encoded document names are identified by the first character of the rgch field. The following special characters are recognized: Name Value Meaning ---- ----- ------- chEmpty 0 empty sheetname chEncode 1 encoded pathname chSelf 2 self-referential external reference chEmpty is used to store an external reference to the empty sheet, as in the formula =!$A$1. chSelf is used to store an external reference where the dependent and supporting documents are the same, for example a worksheet SALES.XLS which contains the formula =SALES.XLS!$A$1. chEncode is used when the DOS file name of the supporting document has been translated to a less system-dependent name. The following special characters are recognized in an encoded document name: Name Value Related DOS keys ---- ----- ---------------- chVolume 1 : chSameVolume 2 none chDownDir 3 .\ chUpDir 4 ..\ The chVolume key is used to specify a DOS drive letter in a document name. It is followed by the drive letter. This replaces the DOS-specific ':' character, as in =C:SALES.XLS!Gross. The chSameVolume key is used when the drive letter was omitted, to indicate that the supporting document is on the same DOS drive as the dependent document, as in =SALES.XLS!Gross. The chDownDir key is used to go down a directory level. It is followed by the subdirectory name. This replaces the implicit DOS-specific sequence ".\", meaning subdirectory of the current directory. An example of such an external reference is =AUGUST\SALES.XLS!Gross. The chUpDir key is used to go up a directory level. It replaces the DOS-specific sequence "..\", meaning the parent directory of the current directory. DDE references are encoded differently. Only one translation is ever performed on a DDE reference, on the '|' character: Name Value Related DOS keys ---- ----- ---------------- chDde 3 | EXTERNNAME record - externally referenced name (type = 35) Offset Name Size Contents ------ ---- ---- -------- 4 cch 1 length of the name 5 rgch var the name Description The EXTERNNAME record specifes a name which is referenced externally from an Excel document. All EXTERNNAME records associated with a supporting document must directly follow the EXTERNSHEET record for the document. The order of EXTERNNAME records in a BIFF file is important and should not be changed. An externally referenced name is one of the following: - A worksheet or macro sheet name in an external reference. In the formula =SALES.XLS!Gross, the name "Gross" is an externally referenced name. - A DDE topic. In the formula =Signal|StockInfo!IBM, the topic "IBM" is an externally referenced name. When the externally referenced name is a DDE topic, Excel may append the most recent values for the topic to the EXTERNNAME record. The values are written in the same format as array constant values in parsed expressions. See the explanation of "ptgArray" in the "Operand Tokens - Base" section for a full description of this format. If there are many values, the EXTERNNAME record may become so long that it must be split into multiple records. In this case, the EXTERNNAME record will be followed by one or more CONTINUE records. FORMAT record - cell format (type = 30) Offset Name Size Contents ------ ---- ---- -------- 4 cch 1 length of format string 5 rgch var picture format string Description The FORMAT record describes a picture format on the document. All the FORMAT records should appear together in a BIFF file. The order of FORMAT records in an existing BIFF file is important and should not be changed. You can add new formats to a file, but they should be added at the end of the FORMAT list. NAME record - user-defined name (type = 24) Offset Name Size Contents ------ ---- ---- -------- 4 grbit 1 name attributes 5 grbitPli 1 name attributes 6 chKey 1 keyboard shortcut 7 cch 1 length of the name text 8 cce 1 length of the name's definition 9 rgch var text of the name var rgce var parsed expression for the name's definition var cceDup 1 length of the name's definition (this is a duplicate of the cce field) Description The NAME record describes a user-defined name on the document. The cch field contains the length the name text; the text itself is in rgch. cce is the length of the name definition, and rgce contains the definition. The location of rgce within the record depends on the length of the name text. Following rgce, the length of the name definition appears again. The name definition is stored in Excel's internal compressed format. See the section "Excel Formulas" for an explanation. The grbit field contains bit attributes of the name: Bits Mask Name Contents ---- ---- ---- -------- 7-3 0xF8 RESERVED - must be zeros 2 0x04 fCalcExp =1 if the name contains a complex function =0 otherwise 1 0x02 fProc =1 for a Function or Command name =0 otherwise 0 0x01 RESERVED - must be zero The fCalcExp bit is set if the name definition contains one or more of the following: - A function that returns an array (e.g. TREND, MINVERSE) - The ROW or COLUMN function - A user-defined function The fProc bit is set if the name is a Function or Command name on a macro sheet. grbitPli and chKey are meaningful only when the fProc bit is set in the grbit field. grbitPli contains bit attributes for Function or Command names: Bits Mask Name Contents ---- ---- ---- -------- 7-2 0xFC RESERVED - must be zeros 1 0x02 fRun =1 for Command names 0 0x01 fFunc =1 for Function names chKey is the keyboard shortcut for a Command name. If the name is not a command name or has no keyboard shortcut, then chKey will be 0. All the NAME records should appear together in a BIFF file. The order of NAME records in an existing BIFF file is important and should not be changed. You can add new names to a file, but they should be added at the end of the NAME list. Excel saves out the names in alphabetical order, but this is not a requirement; Excel will sort the name list, if necessary, when it loads a BIFF file. DIMENSIONS record - cell table size (type = 0) Offset Name Size Contents ------ ---- ---- -------- 4 rwMic 2 first defined row on the document 6 rwMac 2 last defined row on the document, plus 1 8 colMic 2 first defined column on the document 10 colMac 2 last defined column on the document, plus 1 Description The DIMENSIONS record contains the minimum and maximum bounds of the document. It tells us very quickly the approximate size of the document. Note that both the rwMac and colMac fields are 1 greater than the actual last row and column. For example, for a worksheet that exists between cells B3 and D6, we would have rwMic = 2, colMic = 1, rwMac = 6, colMac = 4. COLUMN DEFAULT record - default cell attributes (type = 32) Offset Name Size Contents ------ ---- ---- -------- 4 colMic 2 first column that has a default cell 6 colMac 2 last column that has a default cell, plus 1 8 rgrgbAttr var array of default cell attributes Description The COLUMN DEFAULT record is an optional record that controls the formats of cells that aren't defined on the worksheet. This is a space-saving technique. By specifying a default cell for a particular column, you are telling Excel that all undefined cells in the column should have the specified cell attributes. Default cells do not affect the formats of cells that are explicitly defined. For example, if you want all of column C to be left-aligned, then you could define all 16,384 cells in the column and specify that each one be left-aligned. This would require a large amount of storage to represent all 16,384 cells. Or, you could simply set the default cell for column C to be left-aligned, and not define any cells at all in column C. The rgrgbAttr field is an array of rgbAttr fields, with the range of the array being colMic to colMac-1, inclusive. Each rgbAttr field is 3 bytes long. See the "Cell Attributes" section for a description of the rgbAttr field. If the COLUMN DEFAULT record is present, it must appear in the file before any ROW records or cell records. ROW record - row descriptor (type = 8) Offset Name Size Contents ------ ---- ---- -------- 4 rw 2 row number 6 colMic 2 first defined column in the row 8 colMac 2 last defined column in the row, plus 1 10 miyRw 2 row height 12 irwMac 2 Microsoft internal use 14 fDefault 1 =1 if the row has default cell attributes =0 otherwise 15 dbRtcell 2 relative file offset to the cell records for this row 17 rgbAttr 3 default cell attributes Description A ROW record describes a single row on an Excel document. colMic and colMac give the range of defined columns in the row. miyRw is the row height in units of 1/20 of a point. irwMac is used by Microsoft Excel to optimize loading the file; if you are creating a BIFF file, set this field to 0. The miyRw field may have the 0x8000 bit set, indicating that the row is standard height. The low 15 bits must still contain the row height. Each row can have default cell attributes which control the format of all undefined cells in the row. This is a space- saving technique. By specifying default cell attributes for a particular row, you are effectively formatting all the undefined cells in the row, but without using up memory for those cells. Default cell attributes do not affect the formats of cells that are explicitly defined. For example, if you want all of row 3 to be left-aligned, then you could define all 256 cells in the row and specify that each one be left-aligned. This would require storage for each of the 256 cells. Or, you could simply set the default cell for row 3 to be left-aligned, and not define any cells at all in row 3. The fDefault field indicates whether a default cell is present or not. If it is, then rgbAttr contains the default cell attributes. See the "Cell Attributes" section for a description of the rbgAttr field. dbRtcell is a relative file offset to the cell records for the row. This is described in the section "Finding Values From BIFF Files." BLANK record - blank cell (type = 1) Offset Name Size Contents ------ ---- ---- -------- 4 rw 2 row 6 col 2 column 8 rgbAttr 3 cell attributes Description A BLANK record describes a cell with no formula or value. See the "Cell Attributes" section for a description of the rgbAttr field. INTEGER record - cell with constant integer (type = 2) Offset Name Size Contents ------ ---- ---- -------- 4 rw 2 row 6 col 2 column 8 rgbAttr 3 cell attributes 11 w 2 unsigned integer value Description An INTEGER record describes a cell containing a constant unsigned integer in the range 0 - 65535. Negative numbers and numbers outside this range must be stored as NUMBER records. See the "Cell Attributes" section for a description of the rgbAttr field. NUMBER record - cell with constant floating point number (type = 3) Offset Name Size Contents ------ ---- ---- -------- 4 rw 2 row 6 col 2 column 8 rgbAttr 3 cell attributes 11 num 8 floating point number value Description A NUMBER record describes a cell containing a constant floating point number. The number is in 8-byte IEEE floating point format. See the "Cell Attributes" section for a description of the rgbAttr field. LABEL record - cell with constant string (type = 4) Offset Name Size Contents ------ ---- ---- -------- 4 rw 2 row 6 col 2 column 8 rgbAttr 3 cell attributes 11 cch 1 length of the string 12 rgch var the string Description A LABEL record describes a cell with a constant string. The string length is in the range 0 - 255. See the "Cell Attributes" section for a description of the rgbAttr field. BOOLERR record - cell with constant boolean or error (type = 5) Offset Name Size Contents ------ ---- ---- -------- 4 rw 2 row 6 col 2 column 8 rgbAttr 3 cell attributes 11 bBoolErr 1 boolean or error value 12 fError 1 specifies boolean or error =1 for error =0 for boolean Description A BOOLERR record describes a cell containing a constant boolean or error value. Boolean values are 1 for TRUE and 0 or FALSE. Error values are as follows: 0 #NULL! 7 #DIV/0! 15 #VALUE! 23 #REF! 29 #NAME? 36 #NUM! 42 #N/A See the "Cell Attributes" section for a description of the rgbAttr field. FORMULA record - cell with a formula (type = 6) Offset Name Size Contents ------ ---- ---- -------- 4 rw 2 row 6 col 2 column 8 rgbAttr 3 cell attributes 11 num 8 current value of formula 19 sbRecalc 1 recalc flag =0 if the formula is calculated =nonzero if the formula needs to be calculated =3 if the formula is part of a matrix that needs to be calculated 20 cce 1 length of parsed expression 21 rgce var parsed expression Description A FORMULA record describes a cell with a formula. The sbRecalc field tells us whether the formula needs to be recalculated upon loading the file. Normally, when formulas are saved in BIFF files, they are fully calculated. In some cases, however, this is not possible. If the formula contains a circular reference or a "volatile" function which can never be considered truly calculated, like RAND() or NOW(), then we indicate that the formula needs to be calculated upon loading. Any nonzero value for sbRecalc indicates that the formula needs to be calculated. The special value of 3 is reserved for FORMULA records belonging to cells which are part of matrices, when the entire matrix itself needs to be calculated. The num field contains the current value of the formula in 8- byte IEEE format. For formulas that evaluate not to numbers but to strings, booleans, or error values, the last two bytes of the num field will be 0xFFFF. This covers the sign bit, the exponent, and four bits of the fraction. A boolean is stored in the num field as follows: Offset Name Size Contents ------ ---- ---- -------- 0 otBool 1 =1 always 1 1 RESERVED - must be zero 2 f 1 boolean value 3 3 RESERVED - must be zero 6 fExprO 2 =0xFFFF always An error is stored in the num field as follows: Offset Name Size Contents ------ ---- ---- -------- 0 otErr 1 =2 always 1 1 RESERVED - must be zero 2 err 1 error value 3 3 RESERVED - must be zero 6 fExprO 2 =0xFFFF always See the BOOLERR record for a description of boolean and error values. A string is stored in the num field as follows: Offset Name Size Contents ------ ---- ---- -------- 0 otString 1 =0 always 1 5 RESERVED - must be zero 6 fExprO 2 =0xFFFF always The string value itself is not stored in the num field; instead, it is stored in a separate BIFF record, the STRING record. The parsed expression is the cell's formula, stored in Excel's internal compressed format. See the section "Excel Formulas" for an explanation. See the "Cell Attributes" section for a description of the rgbAttr field. ARRAY record - array formula (type = 33) Offset Name Size Contents ------ ---- ---- -------- 4 rwFirst 2 first row of the array 6 rwLast 2 last row of the array 8 colFirst 1 first column of the array 9 colLast 1 last column of the array 10 sbRecalc 1 recalc flag =0 if the array is calculated =nonzero if the array needs to be calculated 11 cce 1 length of parsed expression 12 rgce var parsed expression Description An ARRAY record describes a formula which was array-entered into a range of cells. The range in which the array is entered is given by rwFirst, rwLast, colFirst, and colLast. The ARRAY record occurs directly after the FORMULA record for the upper left corner cell of the array, i.e. cell (rwFirst, colFirst). The sbRecalc field tells whether the array needs to be recalculated upon loading or not. See the FORMULA record for a description of this field. Note that in an ARRAY record, unlike a FORMULA record, sbRecalc will never have the value 3. The parsed expression is the array formula, stored in Excel's internal compressed format. See the section "Excel Formulas" for an explanation. CONTINUE record - (type = 60) Offset Name Size Contents ------ ---- ---- -------- 4 rgce var parsed expression Description Some parsed formulas are so long that they are split up into sections and written out as separate records. The first section appears in the FORMULA or ARRAY record; subsequent sections appear in CONTINUE records. Parsed expressions will be discussed in detail in future documents. Some EXTERNNAME records are also long enough to need CONTINUE records. STRING record - string value of a formula (type = 7) Offset Name Size Contents ------ ---- ---- -------- 4 cch 1 length of the string 5 rgch var the string Description A STRING record appears after a FORMULA record whose formula currently evaluates to a string. If the formula is part of an array, then the STRING record occurs after the ARRAY record. TABLE record - one-input table definition (type = 54) Offset Name Size Contents ------ ---- ---- -------- 4 rwFirst 2 first row of the table 6 rwLast 2 last row of the table 8 colFirst 1 first column of the table 9 colLast 1 last column of the table 10 sbRecalc 1 recalc flag =0 if the table is calculated =nonzero if the table needs to be calculated 11 fRw 1 =1 if this is a row input table =0 if this is a column input table 12 rwInp 2 row of the input cell 14 colInp 2 column of the input cell Description A TABLE record describes a one-input row or column table created through the Data Table command. The area in which the table is entered is given by rwFirst, rwLast, colFirst, and colLast. This is the interior of the table; it does not include the outer row or column, which contains table formulas or input values. The sbRecalc field tells whether the array needs to be recalculated upon loading or not. See the FORMULA record for a description of this field. Note that in an TABLE record, unlike a FORMULA record, sbRecalc will never have the value 3. fRw tells us whether the input cell is a row input cell or a column input cell. In either case, the input cell is given by (rwInp, colInp). rwInp is -1 in the case where the input cell is a deleted reference, i.e. displays as #REF!. colInp is unused in this case. TABLE2 record - two-input table definition (type = 55) Offset Name Size Contents ------ ---- ---- -------- 4 rwFirst 2 first row of the table 6 rwLast 2 last row of the table 8 colFirst 1 first column of the table 9 colLast 1 last column of the table 10 sbRecalc 1 recalc flag =0 if the table is calculated =nonzero if the table needs to be calculated 11 1 RESERVED - must be zero 12 rwInpRw 2 row of the row input cell 14 colInpRw 2 column of the row input cell 16 rwInpCol 2 row of the column input cell 18 colInpCol 2 column of the column input cell Description A TABLE2 record describes a two-input table created through the Data Table command. This record is the same as the TABLE record, with the following exceptions: - There is no fRw field. The byte is unused. - There are two input cells, a row input cell and a column input cell. - Either input cell, or both input cells, may have a row of -1 to indicate that the corresponding input cell is a deleted reference, i.e. displays as #REF!. PROTECT record - worksheet protection (type = 18) Offset Name Size Contents ------ ---- ---- -------- 4 fLock 2 =1 if the document is protected =0 if the document is not protected Description The PROTECT record specifies whether or not an Excel document has been protected through the Options Protect Document command. Note that this record specifies a document password, as opposed to the FILEPASS record, which specifies a file password. WINDOW PROTECT record - window protection (type = 25) Offset Name Size Contents ------ ---- ---- -------- 4 fLockWn 2 =1 if the windows of the document are protected =0 otherwise Description The WINDOW PROTECT record specifies whether or not the document's windows are protected, as specified in the Protect Document command. PASSWORD record - worksheet password (type = 19) Offset Name Size Contents ------ ---- ---- -------- 4 wPassword 2 encrypted password for a protected document Description The PASSWORD record contains the encrypted password for a document protected through the Options Protect Document command. NOTE record - notes (type = 28) Offset Name Size Contents ------ ---- ---- -------- 4 rw 2 row of the note 6 col 2 column of the note 8 cch 2 length of the note 10 rgch var the note Description The NOTE record specifies a note associated with a cell. The cell is given by the rw and col fields. cch is the length of the note; rgch contains the text of the note. Notes longer than 2048 characters must be spread across multiple NOTE records, each one containing at most 2048 characters. The first NOTE record contains the following fields: Offset Name Size Contents ------ ---- ---- -------- 4 rw 2 row of the note 6 col 2 column of the note 8 cch 2 total length of the note (>2048) 10 rgch 2048 the first 2048 characters of the note Each subsequent NOTE record for the note contains the following fields: Offset Name Size Contents ------ ---- ---- -------- 4 rw 2 =-1 always 6 2 RESERVED - must be zero 8 cch 2 length of this section of the note (<=2048) 10 rgch var section of the note WINDOW1 record - basic window information (type = 61) Offset Name Size Contents ------ ---- ---- -------- 4 x 2 horizontal position of the window 6 y 2 vertical position of the window 8 dx 2 width of the window 10 dy 2 height of the window 12 fHidden 1 =1 if the window is hidden =0 otherwise Description The WINDOW1 record provides basic information about an Excel window. The x and y fields give the location of the window in units of 1/20 of a point, relative to the upper left corner of the desktop. dx and dy give the window size, also in units of 1/20 of a point. fHidden is used to specify a hidden window. If you are creating a BIFF file, you can omit the WINDOW1 record, and Excel will create a default window into your document. WINDOW2 record - advanced window information (type = 62) Offset Name Size Contents ------ ---- ---- -------- 4 fDspFmla 1 =1 if the window should display formulas =0 if the window should display values 5 fDspGrid 1 =1 if the window should display gridlines =0 otherwise 6 fDspRwCol 1 =1 if the window should display row and column headers =0 otherwise 7 fFrozen 1 =1 if the panes in the window should be frozen =0 otherwise 8 fDspZeros 1 =1 if the window should display zero values =0 if the window should suppress display of zero values 9 rwTop 2 top row visible in the window 11 colLeft 2 leftmost column visible in the window 13 fDefaultHdr 1 =1 if the row/column headers and gridlines should be drawn in the default foreground color =0 otherwise 14 rgbHdr 4 row/column headers and gridline color Description The WINDOW2 record contains a fuller description of an Excel window. This record is optional. If it appears, it must directly follow the WINDOW1 record for the window it describes. The fDspFmla, fDspGrid, fDspRwCol, and fDspZeros fields are window properties as set in the Options Display command. fFrozen is as set through the Options Freeze/Unfreeze Panes commands. fDefaultHdr is 1 if the window's row and column headers and gridlines should be drawn in the window's default foreground color. If this field is 0, then the RGB color in rgbHdr is used instead. PANE Record - window split information (type = 65) Offset Name Size Contents ------ ---- ---- -------- 4 x 2 horizontal position of the split, or zero if none 6 y 2 vertical position of the split, or zero if none 8 rwTop 2 top row visible in the bottom pane 10 colLeft 2 leftmost column visible in the right pane 12 pnnAct 1 pane number of the active pane Description The PANE record describes the number and position of panes in a window. The x and y fields give the position of the vertical and horizontal splits, respectively, in units of 1/20 of a point. Either of these fields may be zero, indicating that the window is not split in the corresponding direction. For a window with a horizontal split, rwTop is the topmost row visible in the bottom pane or panes. For a window with a vertical split, colLeft gives the leftmost column visible in the right pane or panes. The pnnAct field tells which pane is the active pane. It contains one of the following values: 0 Bottom right 1 Top right 2 Bottom left 3 Top left If the document window associated with a pane has frozen panes, as specified in the WINDOW2 record, then x and y have special meaning. If there is a vertical split, then x contains the number of columns visible in the top pane. If there is a horizontal split, then y contains the number of rows visible in the left pane. Both types of splits can be present in a window, as in unfrozen panes. SELECTION record - selection within a pane (type = 29) Offset Name Size Contents ------ ---- ---- -------- 4 pnn 1 pane number 5 rwAct 2 row number of the active cell 7 colAct 2 column number of the active cell 9 irefAct 2 reference number of the active cell 11 cref 2 number of references in the selection 13 rgref var array of references Description The SELECTION record specifies which cells are selected in a pane of a split window. This record may also be used to specify selected cells in a window which does not have any splits. The pnn field tells which pane we are describing. It contains one of the following values: 0 Bottom right 1 Top right 2 Bottom left 3 Top left For a window which has no splits, use pnn = 3. rwAct and colAct specify which cell in the selection is the active cell. The selection itself consists of rgref, a variable length array of references. The number of references in the record is given by the cref field. Each reference is six bytes long and contains the following fields: Offset Name Size Contents ------ ---- ---- -------- 0 rwFirst 2 first row in the reference 2 rwLast 2 last row in the reference 4 colFirst 1 first column in the reference 5 colLast 1 last column in the reference irefAct is a zero-based index into the array of references, specifying which reference contains the active cell. If a selection is so large that it won't fit in the maximum size BIFF record, 2084 bytes, then it is broken down into multiple consecutive SELECTION records. Each record contains a portion of the larger selection. Only the cref and rgref fields vary in the multiple records; the pnn, rwAct, colAct, and irefAct fields are the same over all records in the group. On each record, the cref field contains the number of references found on that record alone. EOF record - end of file (type = 10) Description The EOF record must be the last record in the file. It has no data associated with it. Cell Attributes --------------- This section describes the cell attribute field found in the ROW, BLANK, INTEGER, NUMBER, LABEL, BOOLERR, FORMULA, and COLUMN DEFAULT records. The field is three bytes long and consists of bit fields: Offset Bits Mask Name Contents ------ ---- ---- ---- -------- 0 7 0x80 fHidden =1 if the cell is hidden 6 0x40 fLocked =1 if the cell is locked 5-0 RESERVED - must be zeros 1 7-6 0xC0 ifnt font number 5-0 0x3F ifmt the cell's format code 2 7 0x80 fShade =1 if the cell is shaded 6 0x40 fBottom =1 if the cell has a bottom border 5 0x20 fTop =1 if the cell has a top border 4 0x10 fRight =1 if the cell has a right border 3 0x08 fLeft =1 if the cell has a left border 2-0 0x07 alc the cell's alignment code The ifnt field is a zero-based index into the document's table of fonts. The ifmt field is a zero-based index into the document's table of picture formats. See the FONT and FORMAT records for details. The alc field has one of the following values: 0 General 1 Left 2 Center 3 Right 4 Fill 7 (Multiplan only) Default alignment Order of Records ---------------- Here is the order in which records are written in a BIFF file: BOF FILEPASS INDEX CALCCOUNT CALCMODE PRECISION REFMODE DELTA ITERATION 1904 BACKUP PRINT ROW HEADERS PRINT GRIDLINES HORIZONTAL PAGE BREAKS VERTICAL PAGE BREAKS DEFAULT ROW HEIGHT FONT FONT2 HEADER FOOTER LEFT MARGIN RIGHT MARGIN TOP MARGIN BOTTOM MARGIN COLWIDTH EXTERNCOUNT EXTERNSHEET EXTERNNAME FORMAT NAME DIMENSIONS COLUMN DEFAULT Cell table ROW, BLANK, INTEGER, NUMBER, LABEL, BOOLERR, FORMULA, ARRAY, STRING, TABLE, TABLE2 PROTECT WINDOW PROTECT PASSWORD NOTE WINDOW1 WINDOW2 PANE SELECTION EOF Finding Values From BIFF Files ------------------------------ This section explains how to look up a cell value in a BIFF file, without having to load the file into Excel. You can look up values only in BIFF files that are not password-protected; protected BIFF files are encrypted and cannot be read. One way to find the value of a particular cell in a BIFF file is to read every BIFF record, until we find a cell record for the cell. If we find one, we return its value. If we reach the EOF record without finding a cell record for the cell, then we return zero. Fortunately, we don't have to go through such an exhaustive search. We can narrow down the area that we have to search by using BIFF's INDEX and ROW records. If a non-protected BIFF file has an INDEX record, it will be the second record in the file (immediately after the BOF record). If the second record is not an INDEX record, then we must resort to the exhaustive record search described above. Or, alternatively, we could simply fail the search and return some sort of error code. Having located the INDEX record, we fetch the rwMic and rwMac fields, which tell us the range of defined rows on the document. If the row we are searching for is outside of that range, then we know right away that the desired cell doesn't exist, so we can return zero. The next step is to locate the ROW record for the row of the desired cell. To do this, we need to understand how Excel saves ROW records and cell records. When Excel saves a document in BIFF format, it divides the document into blocks of 32 rows, starting at the first defined row on the document. Since rwMic is by definition the first defined row, the first block consists of rows rwMic through rwMic+31; the second, from rwMic+32 through rwMic+63; and in general, the i-th block, assuming that i is zero-based, consists of rows (rwMic+i*32) through (rwMic+i*32+31). Excel writes a block of ROW records to the file, then follows this with all the cell records for cells in those rows. This process is repeated until all ROW and cell records have been written. The INDEX record contains an array of file pointers to the blocks of ROW records. Working backwards from our rule above for ROW blocks, we see that to locate the block for row 'rw', we fetch array element (rw-rwMic)/32. Here, the '/' operator is integer division that truncates. Having found the proper array element, we position the BIFF file at that location. The file pointer that we fetched from the array is an absolute byte offset from the beginning of the file, which is byte 0. For example, if the file pointer were 17,540, then we would position the file at byte 17,540. The file is now positioned at the correct block of ROW records. The next step is to search for the correct ROW record. Since Excel documents have sparse cell tables, blocks of ROW records contain only the defined rows within the block range. This means that if the row we are searching for doesn't exist, then it won't have a ROW record in the BIFF file. We must read at most 32 records at this point. If we do not find a ROW record for the desired row, then we know that the row doesn't exist, so we can return zero. We know that the row doesn't exist as soon as we find a non-ROW record, or a ROW record for a row beyond the one we are searching for. Having found the correct ROW record, we fetch the colMic and colMac fields, which tell us the range of defined columns in the row. If the column we are searching for lies outside of the defined range, then we know that the desired cell doesn't exist, and we can return zero. From the ROW record, we can now determine the position within the file of the cell records for the desired row. The next step is to position the file at that point and search for the cell record for the desired cell. The dbRtcell field contains the offset to cells for the ROW record. This field is limited to 16 bits to save space in BIFF files; thus the largest offset that will fit is 65,535. In a large Excel document, however, it is possible for cells to be located farther than 65,535 bytes from their ROW record. Therefore we encode the offset to get more value from it. The first ROW record in a block contains an offset to cells relative to the second ROW record. This is because after reading the first ROW record, you are positioned at the second ROW record, so finding the cells is just a matter of skipping some number of bytes relative to the current file position. The second and all subsequent ROW records in a block contain offsets to cells relative to the previous ROW record's cells. This iterative approach works like this: after reading the first ROW record, you get its offset and add it to the current file position to get the absolute file position of the first ROW's cell records. When you read the second ROW record, you add the offset contained therein to your computed position of the first ROW's cells, and you get the position of the second ROW's cells. Continuing in this manner, you find that by the time you find the proper ROW record, you have already computed the absolute file position of its cells, so you position there and continue your search. Having computed the file position of our row's cell records, we set the file there and start sequentially searching for the desired cell. If we find the cell, we fetch its value and return it. Our search fails as soon as we encounter a cell record for a cell beyond ours, or we encounter a record which is not a cell record. If a ROW has no defined cells, we will set its dbRtcell offset to zero. If a ROW's cells are more than 64K from the previous ROW's cells (which is rare but possible), we will write out a zero offset for that ROW and ALL subsequent ROW records in the same block. All this means is that we have to search a little harder for the correct cell record: instead of being able to start our search at cells in the desired row, we will have to start searching at cells in some previous row. Excel Formulas -------------- This section describes how Excel stores formulas within BIFF files. Formulas appear in FORMULA, ARRAY, and NAME records. In this section, the term "formula" is a synonym for "parsed expression"; it is the internal tokenized representation of an Excel formula. Formulas are stored in a reverse Polish scheme. A formula consists of a sequence of parse tokens, each of which is either an operand, operator, or a control token. Operand tokens provide values; operator tokens perform arithmetic operations upon the operands; and control tokens assist in formula evaluation by describing properties of the formula. A token consists of two parts: a token type and a token value. Token types are called "ptg's" in Excel; they are one byte long, ranging in value from 1 to 0x7F. Ptg's above 0x7F are reserved for internal Excel use. The ptg specifies only what kind of information is contained in a token. The information itself is stored in the token value, immediately following the ptg in the parsed expression. Some tokens consist only of a ptg, without an accompanying token value; for example, to specify an addition operation, only the token type, ptgAdd, is required. But to specify an integer operand, both the ptg, ptgInt, and the token value, an integer, must be specified. As an illustration, consider the parsed expression for =5+6. This parsed expression consists of three tokens: two integer operands and an operator. ptgInt 0x0005 ptgInt 0x0006 ptgAdd < token 1 > < token 2 > Notice that each ptgInt is immediately followed by the integer token value. In many cases, the token value consists of a structure of two or more fields. In describing structures for these cases, offset zero is assumed to be the first byte of the token value, i.e. the first byte immediately following the token type. Unless otherwise noted, all tokens can occur in FORMULA, ARRAY, and NAME records. Some tokens do not appear in one or more of these record types; they are explained as encountered. Expression Evaluation --------------------- The evaluation of Excel formulas is a straightforward process. One LIFO stack, the operand stack, is maintained during evaluation. When an operand is encountered, it is pushed onto the stack. When an operator is encountered, it operates on the topmost operand or operands. Operator precedence is irrelevant at evaluation time; operators are handled as soon as they are encountered. There are three kinds of operators: unary, binary, and function. Unary operators, like the minus sign which negates a number, operate only on the topmost operand. Binary operators, like the addition operator, operate on the top two operands. Function operators, which implement Excel functions, operate on a variable number of operands, depending on how many arguments the function accepts. All operators work by popping the required operands from the stack, performing calculations, and pushing the result back onto the operand stack. Unary Operators --------------- Here are the unary operator tokens. All of these operators pop the top argument from the operand stack, perform a calculation, and push the result back onto the operand stack. ptgUplus - unary plus (ptg = 0x12) This operator has no effect. ptgUminus - unary minus (ptg = 0x13) Negates the top operand. ptgPercent - percent sign (ptg = 0x14) Divides the top operand by 100 Binary Operators ---------------- Here are the binary operator ptg's. All of these operators pop the top two arguments from the operand stack, perform a calculation, and push the result back onto the operand stack. ptgAdd - addition (ptg = 0x03) Adds the top two operands together. ptgSub - subtraction (ptg = 0x04) Subtracts the top operand from the second-to-top. ptgMul - multiplication (ptg = 0x05) Multiplies the top two operands. ptgDiv - division (ptg = 0x06) Divides the top operand by the second-to-top. ptgPower - exponentiation (ptg = 0x07) Raises the second-to-top operand to the power of the top operand. ptgConcat - concatenation (ptg = 0x08) Appends the top operand to the second-to-top operand. ptgLT - less than (ptg = 0x09) Evaluates to TRUE if the second-to-top operand is less than the top operand; FALSE otherwise. ptgLE - less than or equal (ptg = 0x0A) Evaluates to TRUE if the second-to-top operand is less than or equal to the top operand; FALSE otherwise. ptgEQ - equal (ptg = 0x0B) Evaluates to TRUE if the top two operands are equal; FALSE otherwise. ptgGE - greater than or equal (ptg = 0x0C) Evaluates to TRUE if the second-to-top operand is greater than or equal to the top operand; FALSE otherwise. ptgGT - greater than (ptg = 0x0D) Evaluates to TRUE if the second-to-top operand is greater than the top operand; FALSE otherwise. ptgNE - not equal (ptg = 0x0E) Evaluates to TRUE if the top two operands are not equal; FALSE otherwise. ptgIsect - intersection (ptg = 0x0F) This is the Excel space operator. It computes the intersection of the top two operands. ptgUnion - union (ptg = 0x10) This is the Excel comma operator. It computes the union of the top two operands. ptgRange - range (ptg = 0x11) This is the Excel colon operator. It computes the minimal bounding rectangle of the top two operands. Operand Tokens - Constant ------------------------- The following operand tokens push a single constant operand onto the operand stack. ptgMissArg - missing argument (operand, ptg = 0x16) Missing argument to an Excel function. For example, the second argument to DCOUNT(Database,,Criteria) would be stored as a ptgMissArg. ptgStr - string constant (operand, ptg = 0x17) String constant. Followed by the string. Offset Name Size Contents ------ ---- ---- -------- 0 cch 1 length of the string 1 rgch var the string ptgStr requires special handling when parsed expressions are scanned. See the section "Scanning a Parsed Expression" for an explanation. ptgErr - error value (operand, ptg = 0x1C) Error constant. Followed by the error value. See the BOOLERR record for a list of error values. Offset Name Size Contents ------ ---- ---- -------- 0 err 1 Excel error value ptgBool - boolean (operand, ptg = 0x1D) Boolean constant. Followed by a byte value. Offset Name Size Contents ------ ---- ---- -------- 0 f 1 =1 for TRUE =0 for FALSE ptgInt - integer (operand, ptg = 0x1E) Integer constant. Followed by a word value. Offset Name Size Contents ------ ---- ---- -------- 0 w 2 unsigned integer value ptgNum - number (operand, ptg = 0x1F) Numeric constant. Followed by an 8-byte IEEE floating point number. Offset Name Size Contents ------ ---- ---- -------- 0 num 8 IEEE floating point number Operand Tokens - Classes ------------------------ As described above, operand tokens push operand values onto the operand stack. These values are divided into three different classes, depending on what type of value the formula expects from the operand. The type of value is determined at parse time by the context of the operand. REFERENCE CLASS. Some operands are required by context to be references. In this case, the term "reference" is a general term meaning the specification of one or more areas on an Excel document, without regard for the underlying cell values in those areas. When the Excel expression evaluator encounters a reference type operand, it pushes only the reference itself onto the operand stack; it does not dereference it to find the underlying cell values. For example, consider the formula CELL("width",B5), which returns the column width of cell B5. Clearly, only the reference to cell B5 is important here; the value stored at cell B5 is irrelevant to the cell' s width. VALUE CLASS. This is the most common type of operand; it pushes a single dereferenced value onto the operand stack. For example, consider the formula A1+1. Here, we are interested in the value stored in cell A1, so we dereference the A1 reference. ARRAY CLASS. This operand pushes an array of values onto the operand stack. The values may be specified either in an array constant or in a reference to cells. For example, consider the formula SUM({1,2,3;4,5,6}). Here, to evaluate the SUM function, the expression evaluator must push an entire array of values onto the operand stack. The three classes of operand tokens are numerically divided as follows: Operand Class Ptg's ------------- ----- Reference 0x20 - 0x3F Value 0x40 - 0x5F Array 0x60 - 0x7F Notice that the numerical difference between ptg classes is 0x20. This is the basis for forming the class variants of ptg's. Class variants of ptg's are formed from the reference class ptg, also known as the "base" ptg. To form the value class ptg from the base ptg, you add 0x20 to the ptg and append "V" (for "value") to the ptg name. To form the array class ptg from the base ptg, you add 0x40 to the ptg and append "A" (for "array") to the ptg name. These rules are summarized below for a hypothetical ptg called ptgFoo: Class Name Ptg ----- ---- --- Reference ptgFoo ptgFoo Value ptgFooV ptgFoo + 0x20 Array ptgFooA ptgFoo + 0x40 For example, the base ptg which specifies a cell reference is ptgRef, which is equal to 0x24. Thus the reference class ptg is ptgRef, which is 0x24; the value class ptg is ptgRefV, which is 0x44; and the array class ptg is ptgRefA, which is 0x64. Here is a useful method for computing the base ptg from any class variant: if (ptg & 0x40) { /* We have a value class ptg. We need to set the 0x20 bit to make it reference class, then strip off the high order bits. */ ptgBase = (ptg | 0x20) & 0x3F; } else { /* We have a reference or array class ptg. The 0x20 bit is already set, so we just have to strip off the high order bits. */ ptgBase = ptg & 0x3F; } A more efficient implementation in C is to define a macro which computes the base ptg: #define PtgBase(ptg) (((ptg & 0x40) ? (ptg | 0x20) : ptg) & 0x3F) This macro is safe, i.e. it can be used on any ptg without damage. Operand Tokens - Base --------------------- This section lists the operand tokens in their base form (also known as reference class). ptgArray - array constant (operand, ptg = 0x20) Array constant. Followed by six bytes. Offset Name Size Contents ------ ---- ---- -------- 0 6 RESERVED The token value for ptgArray consists of the array dimensions and the array values. ptgArray differs from most other operand tokens in that the token value does not follow the token type. Instead, the token value is appended to the saved parsed expression, immediately following the last token. The format of the token value is as follows: Offset Name Size Contents ------ ---- ---- -------- 0 ccol 1 number of columns in the array constant 1 crw 2 number of rows in the array constant 3 rgval var the array values 256-column arrays are stored with a ccol of zero, since the true number of columns does not fit into a byte field. This is acceptable since there are no zero-column array constants. The number of values in the array constant is equal to the product of the array dimensions, crw*ccol. Each value is either an 8-byte IEEE floating point number, or a string. The two formats of these values are as follows: Offset Name Size Contents ------ ---- ---- -------- 0 grbit 1 =0x01 for a number 1 num 8 IEEE floating point number Offset Name Size Contents ------ ---- ---- -------- 0 grbit 1 =0x02 for a string 1 cch 1 length of the string 2 rgch var the string If a formula contains more than one array constant, then the token values for the array constants are appended to the saved parsed expression in order: first, the values for the first array constant, then the values for the second, and so on. If a formula contains very long array constants, then the BIFF record containing the parsed expression may overflow into CONTINUE records to accomodate all of the array values. An individual array value is never split between records; record boundaries occur between successive array values. In practice, the reference class ptgArray never appears in an Excel formula; only the value and array classes are used. ptgName - name (operand, ptg = 0x23) This ptg specifies the usage of an Excel name. The token value specifies which name is referenced. Offset Name Size Contents ------ ---- ---- -------- 0 ilbl 2 index of the referenced name 2 5 RESERVED - must be zeros For local (i.e. non-external) name references, the ilbl field specifies a 1-based index into the document's own name table. The order of this name table is the order of NAME records in the BIFF file. For external name references, the ilbl field specifies a 1-based index into the table of externally referenced names defined on the supporting document. The order of this name table is the order of EXTERNNAME records which are associated with the supporting document. ptgRef - cell reference (operand, ptg = 0x24) This ptg specifies a reference to a single cell. It is followed by the row and column of the reference. The row is encoded as bit fields. Offset Name Size Contents ------ ---- ---- -------- 0 grbitRw 2 row bit fields (see below) 2 col 1 column of the reference Only the low 14 bits of the grbitRw field store the row number of the reference. The high 2 bits specify whether the row or column portion of the reference is a relative reference. Here is the bit field structure of the grbitRw field: Bits Mask Name Contents ---- ---- ---- -------- 15 0x8000 fRwRel =1 if the row portion of the reference is relative =0 otherwise 14 0x4000 fColRel =1 if the column portion of the reference is relative =0 otherwise 13-0 0x3FFF rw the row number of the reference For example, cell C5 is row number 4, column number 2 (since Excel stores cell references zero-based). Therefore the absolute reference $C$5 is stored as ptgRef 0x0004 0x02. The relative reference C5 is stored as ptgRef 0xC004 0x02. The mixed reference $C5 (absolute row, relative column) is stored as ptgRef 0x4004 0x02. ptgArea - area reference (operand, ptg = 0x25) This ptg specifies a reference to a rectangle of cells. It is followed by the first row of the rectangle, last row, first column, and last column. Both the first row and last row are stored as bit fields. Offset Name Size Contents ------ ---- ---- -------- 0 grbitRwFirst 2 first row bit fields (see below) 2 grbitRwLast 2 last row bit fields (see below) 4 colFirst 1 first column of the reference 5 colLast 1 last column of the reference The high order 2 bits of grbitRwFirst specify whether the first row or first column are relative references. The high order 2 bits of grbitRwLast specify whether the last row or last column are relative references. See the ptgRef token for a fuller explanation of these bit fields. For example, consider references to the area C5:D8. C5 is row 4, column 2; D8 is row 7, column 3 (since Excel stores cell references zero-based). Therefore the absolute reference $C$5:$D$8 is stored as ptgArea 0x0004 0x0007 0x02 0x03. The relative reference C5:D8 is stored as ptgArea 0xC004 0xC007 0x02 0x03. The mixed reference C$5:$D8 (absolute first row, relative first column, relative last row, absolute last column) is stored as ptgArea 0x4004 0x8007 0x02 0x03. ptgMemArea - constant reference subexpression (operand, ptg = 0x26) This ptg is used to optimize reference expressions. A reference expression consists of operands, usually references to cells or areas, joined by reference operators (intersection, union, and range). Here are three examples of reference expressions: - A1,C3,D3:D5. This evaluates to two single cells and a 3x1 area. - (A1:C3) (B2:D4). This evaluates to a 2x2 area. - Name:C3. This evaluates to the smallest area which contains both C3 and all the cells referenced in "Name". Many reference expressions evaluate to constant references. In the examples above, the first two expressions always evaluate to the same reference. The third example does not evaluate to a constant reference, since it depends on the name "Name", and Name's definition might change, which would cause the reference expression to evaluate differently. When a reference expression does evaluate to a constant reference, Excel stores the constant reference in the parsed formula through a ptgMemArea token. This saves time during expression evaluation, since part of the expression being evaluated will have been pre-evaluated. This part of the expression is known as a reference subexpression. ptgMemArea only occurs in FORMULA and ARRAY records, never in NAME records. The token value for ptgMemArea consists of two parts: the length of the reference subexpression, and the value of the reference subexpression. The length is stored immediately following the ptgMemArea, but the value is appended to the saved parsed expression, immediately following the last token. The format of the length is as follows: Offset Name Size Contents ------ ---- ---- -------- 0 3 RESERVED 3 cce 1 length of the reference subexpression Immediately following this part of the token value is the reference subexpression itself, whose length is given by the cce field. The rest of the token value, i.e. the value of the reference subexpression, is appended to the parsed expression in the following format: Offset Name Size Contents ------ ---- ---- -------- 0 cref 2 number of rectangles to follow 2 rgref var array of rectangles Each rectangle is six bytes long and contains the following fields: Offset Name Size Contents ------ ---- ---- -------- 0 rwFirst 2 first row 2 rwLast 2 last row 4 colFirst 1 first column 5 colLast 1 last column If a formula contains more than one ptgMemArea, then the token values are appended to the saved parsed expression in order: first, the values for the first ptgMemArea, then the values for the second, and so on. If a formula contains very long reference expressions, then the BIFF record containing the parsed expression may overflow into CONTINUE records to accomodate all of the array values. An individual rectangle is never split between records; record boundaries occur between successive rectangles. ptgMemErr - bad constant reference subexpression (operand, ptg = 0x27) This ptg is closely related to ptgMemArea. It is used for pre-evaluating reference subexpressions which do not evaluate to references. For example, consider the formula SUM(C:C 3:3). The argument to the SUM function is a valid reference subexpression, which generates a ptgMemArea for pre-evaluation. If the user deletes column C, then the formula adjusts to SUM(#REF! 3:3). In this case, the argument to SUM is still a constant reference subexpression, but it does not evaluate to a reference. Therefore a ptgMemErr is used for pre-evaluation. The token value consists of the error value and length of the reference subexpression. Its format is as follows: Offset Name Size Contents ------ ---- ---- -------- 0 2 RESERVED 2 err 1 error value 3 cce 1 length of the reference subexpression See the BOOLERR record for a list of error values. ptgRefErr - deleted cell reference (operand, ptg = 0x2A) This ptg specifies a cell reference that was adjusted to #REF! as a result of spreadsheet editing (e.g. cut and paste, delete). It is followed by three unused bytes. Offset Name Size Contents ------ ---- ---- -------- 0 3 RESERVED The original base type of the adjusted ptg is always ptgRef or ptgRefN. ptgAreaErr - deleted area reference (operand, ptg = 0x2C) This ptg specifies an area reference that was adjusted to #REF! as a result of spreadsheet editing (e.g. cut and paste, delete). It is followed by six unused bytes. Offset Name Size Contents ------ ---- ---- -------- 0 6 RESERVED The original base type of the adjusted ptg is always ptgArea or ptgAreaN. ptgRefN - cell reference within a name (operand, ptg = 0x2C) This ptg only occurs in the parsed expression of a NAME record, never in a FORMULA or ARRAY record. It specifies a reference to a single cell. It is followed by the row and column of the reference. The row is encoded as bit fields. The only difference between ptgRefN and ptgRef is the way relative references are stored. Relative references within name definitions are stored as offsets, not as row and column numbers. For example, if a name "Prev_cell" is defined to the relative reference =R[-2]C[-3] (assuming R1C1 mode), then the parsed expression for Prev_cell is ptgRefN 0xFFFE 0xFD. The row offset, -2, in hexadecimal is 0xFFFE; the column offset, -3, is 0xFFFD. The row portion of the token value consists of the low 14 bits of the row offset, plus two high-order '1' bits to indicate that both the row and column portions are relative references. The column portion of the token value is simply the low byte of 0xFFFD. If instead the name is =R[-2]C3, i.e. with an absolute column reference, then the parsed expression is ptgRefN 0xBFFE 0x02. ptgAreaN - area reference within a name (operand, ptg = 0x2D) This ptg only occurs in the parsed expression of a NAME record, never in a FORMULA or ARRAY record. It specifies a reference to a rectangle of cells. It is followed by the first row of the rectangle, last row, first column, and last column. Both the first row and last row are stored as bit fields. The only difference between ptgAreaN and ptgArea is the way relative references are stored. See ptgRefN for an explanation of this. Control Tokens -------------- ptgExp - array formula (ptg = 0x01) This ptg indicates an array formula. It only occurs in a FORMULA record, never in an ARRAY or NAME record. When ptgExp occurs in a formula, it is the only token in the formula. This indicates that the cell containing the formula is part of an array; the array formula is found in an ARRAY record. The token value for ptgExp consists of the row and column of the upper left corner of the array formula. Offset Name Size Contents ------ ---- ---- -------- 0 rwFirst 2 row number of upper left corner 2 colFirst 1 column number of upper left corner ptgTbl - data table (ptg = 0x02) This ptg indicates a data table. It only occurs in a FORMULA record, never in an ARRAY or NAME record. When ptgTbl occurs in a formula, it is the only token in the formula. This indicates that the cell containing the formula is an interior cell in a data table; the table description is found in a TABLE record. Rows and columns which contain input values to be substituted in the table do not contain ptgTbl. The token value for ptgTbl consists of the row and column of the upper left corner of the table's interior. Offset Name Size Contents ------ ---- ---- -------- 0 rwFirst 2 row number of upper left corner 2 colFirst 1 column number of upper left corner ptgParen - parenthesis (ptg = 0x15) This ptg is used only in unparsing a parsed expression, not in evaluating it. It indicates that the previous token in the parsed expression should be parenthesized. If the previous token is an operand, then only that operand is parenthesized. If the previous token is an operator, then the operator and all of its operands are parenthesized. For example, the formula 1+(2) is stored as ptgInt 0x0001 ptgInt 0x0002 ptgParen ptgAdd, and only the operand 2 is parenthesized. But the formula (1+2) is stored as ptgInt 0x0001 ptgInt 0x0002 ptgAdd ptgParen, so the parenthesized quantity consists of the ptgAdd operator and both of its operands. ptgAttr - special attribute (ptg = 0x19) This ptg is used for a variety of purposes. In all cases, the token value consists of a byte of flags and a byte dependent on the flags. Offset Name Size Contents ------ ---- ---- -------- 0 grbit 1 bit flags 1 b 1 data The grbit field contains the following flags: Bit Mask Name Contents --- ---- ---- -------- 0 0x01 bitFAttrSemi =1 if the formula contains a "volatile" function 1 0x02 bitFAttrIf =1 to implement an optimized IF function 2 0x04 bitFAttrChoose =1 to implement an optimized CHOOSE function 3 0x08 bitFAttrGoto =1 to jump to another location within the parsed expression 4 0x10 bitFAttrSum =1 to implement an optimized SUM function 5 0x20 bitFAttrBaxcel =1 if the formula is a BASIC-style assignment statement bitFAttrSemi is set to 1 if the formula contains a volatile function, i.e. a function which is calculated in every recalculation. The volatile functions in Excel are: INDEX RAND NOW AREAS ROWS COLUMNS CELL INDIRECT If ptgAttr is used to indicate a volatile function, then it must be the first token in the parsed expression. The b field is unused. bitFAttrGoto instructs the expression evaluator to skip part of the parsed expression during evaluation. The b field specifies the number of bytes to skip, minus one. bitFAttrIf indicates an optimized IF function. An IF function contains 3 parts: a condition, a TRUE subexpression, and a FALSE subexpression. The syntax of an associated Excel formula would be IF(condition, TRUE subexpression, FALSE subexpression). bitFAttrIf immediately follows the condition portion of the parsed expression. The b field specifies the offset to the FALSE subexpression; the TRUE subexpression is found immediately following the ptgAttr token. At the end of the TRUE subexpression, there is a bitFAttrGoto token which causes a jump to beyond the FALSE subexpression. In this way, Excel only evaluates the correct subexpression, instead of evaluating both of them and discarding the wrong one. The FALSE subexpression is optional in Excel. If it is missing, then the b field of the bitFAttrIf token specifies an offset to beyond the TRUE subexpression. bitFAttrChoose indicates an optimized CHOOSE function. The b field specifies the number of cases in the CHOOSE function, and is followed by a sequence of byte offsets to those cases. The number of byte offsets in the sequence is one more than the number of cases in the CHOOSE function. Here is the format of this complex token value: Offset Name Size Contents ------ ---- ---- -------- 0 grbit 1 bitFAttrChoose 1 cCases 1 the number of cases in the CHOOSE function 2 rgb var a sequence of byte offsets to the CHOOSE cases. The number of bytes in this field is equal to the cCases field, plus one. bitFAttrChoose requires special handling when parsed expressions are scanned. See the section "Scanning a Parsed Expression" for an explanation. bitFAttrSum indicates an optimized SUM function. This is only used to optimize SUM functions with a single argument. The b field is unused. ptgSheet - external reference (ptg = 0x1A) This ptg indicates the start of an external reference. The token value indicates which sheet is being externally referenced. When this token is encountered during evaluation, it indicates that any following references to cells or names are external references, not local references, until the matching ptgEndSheet token is encountered. Offset Name Size Contents ------ ---- ---- -------- 0 4 RESERVED 4 ixals 2 index of the supporting sheet 6 1 RESERVED - must be zero The ixals field specifies a 1-based index into the table of externally referenced documents. The order of this table is the order of EXTERNSHEET records. ptgEndSheet - end external reference (ptg = 0x1B) This ptg indicates the end of an external reference. It is followed by three bytes. Offset Name Size Contents ------ ---- ---- -------- 0 3 RESERVED ptgMemNoMem - incomplete constant reference subexpression (ptg = 0x28) This ptg is closely related to ptgMemArea. It is used to indicate a constant reference subexpression which could not be pre-evaluated because of low memory conditions. It only occurs in FORMULA and ARRAY records, never in NAME records. The token value consists of the length of the reference subexpression. Offset Name Size Contents ------ ---- ---- -------- 0 3 RESERVED 3 cce 1 length of the reference subexpression ptgMemFunc - variable reference subexpression (ptg = 0x29) This ptg indicates a reference subexpression which does not evaluate to a constant reference. Any reference subexpression containing one or more of the following will generate a ptgMemFunc: - an Excel function - a usage of a name - an external reference. Here are examples of the three kinds of ptgMemFunc's: - INDEX(ref,row_num,column_num,area_num):C3 - Name:$B$2 - SALES.XLS!$A$1:SALES.XLS!$C$3 The token value consists of the length of the reference subexpression. Offset Name Size Contents ------ ---- ---- -------- 0 cce 1 length of the reference subexpression ptgMemAreaN - reference subexpression within a name (ptg = 0x2E) This ptg only occurs in the parsed expression of a NAME record, never in a FORMULA or ARRAY record. It indicates a constant reference subexpression within a name definition. Unlike ptgMemArea, ptgMemAreaN is not used to pre-evaluate the reference subexpression. The token value consists of the length of the reference subexpression. Offset Name Size Contents ------ ---- ---- -------- 0 cce 1 length of the reference subexpression ptgMemNoMemN - incomplete reference subexpression within a name (control, ptg = 0x2F) This ptg is closely related to ptgMemAreaN. It is used to indicate a constant reference subexpression within a name which could not be evaluated because of low memory conditions. It only occurs in NAME records, never in FORMULA or ARRAY records. The token value consists of the length of the reference subexpression. Offset Name Size Contents ------ ---- ---- -------- 0 cce 1 length of the reference subexpression Function Operators ------------------ Here are the function operator ptg's. All of these operators pop arguments from the operand stack, compute a function, and push the result back onto the operand stack. The number of operands popped from the stack is equal to the number of arguments passed to the Excel function. Some Excel functions always require a fixed number of arguments, while others may accept a variable number of arguments. The SUM function, for example, accepts from 1 to 14 arguments. Although they are operators, function tokens also behave like operands in that they can occur in any of the three ptg classes (reference, value, and array). ptgFunc - Excel function (operator, ptg = 0x21) Indicates an Excel function with a fixed number of arguments. Followed by the index of the function within the function table. See the section "Excel Function Table" for a list of Excel functions. Offset Name Size Contents ------ ---- ---- -------- 0 iftab 1 index of the function ptgFuncVar - Excel function (operator, ptg = 0x22) Indicates an Excel function with a variable number of arguments. Followed by the number of arguments and the index of the function within the function table. See the section "Excel Function Table" for a list of Excel functions. Offset Name Size Contents ------ ---- ---- -------- 0 carg 1 number of arguments to the function 1 iftab 1 index of the function ptgFuncCE - command-equivalent function (operator, ptg = 0x38) Indicates an Excel command-equivalent function. Followed by the number of arguments and the index of the function within the command- equivalent function table. See the section "Command Equivalent Function Table" for a list of Excel command-equivalent functions. Offset Name Size Contents ------ ---- ---- -------- 0 carg 1 number of arguments to the function 1 icetab 1 index of the function Reserved Ptg's -------------- All ptg's between 0 and 0xFF not explicitly mentioned in this document are reserved by Microsoft for future use. Scanning a Parsed Expression ---------------------------- One fairly common operation on parsed expressions is to scan them, taking appropriate actions at each ptg. This is accomplished with a loop using a pointer variable, which points to the next ptg to scan. However, this pointer must be incremented carefully, since different ptg's may have token values of different lengths. One good solution to this problem is to maintain a array, with one element per ptg, containing the size of the token value. To increment the pointer, you simply add the array element corresponding to the current ptg. A possible space optimization here is to limit the array indices to the range 0 - 0x3F, and then index it using the base ptg instead of the fully classed ptg. This works because the token value is the same for all classes of a particular ptg. There are two tokens which are variable length, and so do not fit this framework. These tokens must be handled as special cases in any formula scanning loop. The first exception is ptgStr, which is followed by a variable length string. The token value specifies the length of the string, so the pointer is incrementing by fetching and adding the string length from the token value. The other exception is the bitFAttrChoose token of ptgAttr. The token value contains a variable number of bytes in sequence. The number of bytes in the sequence is specified in the token value, so the proper method of incrementing is to fetch and add the sequence length. Here is sample C code which scans a parsed expression: Scan(rgb, cb) char rgb[]; /* The parsed expression */ int cb; /* The length of the parsed expression */ { char *pb; /* Pointer to the current token */ char *pbMac; /* Pointer to the end of the p.e. */ int ptg; /* Raw ptg */ int ptgBase; /* Base ptg */ extern char token_size[]; /* Array of token value sizes */ #define bitFAttrChoose 0x04 /* CHOOSE type of ptgAttr */ pb = rgb; pbMac = &rgb[cb]; while (pb < pbMac) { /* Fetch the next token and determine its base type. Note that the postincrement conveniently leaves pb pointing to the token value. */ ptg = *pb++; ptgBase = PtgBase(ptg); switch (ptgBase) { ... case ptgAttr: /* Check for a CHOOSE ptgAttr and skip over the table of offsets if found. */ if (*pb & bitFAttrChoose) pb += *(pb + 1) + 1; break; case ptgStr: /* String constant. Skip over the size byte and the string itself. */ pb += *pb + 1; break; default: /* Look up the token value size and add it to the pointer. The token_size array is only indexed by the base ptg as a space optimization, since the token sizes of the value and array classes are the same as the base class'. */ pb += token_size[ptgBase]; break; ... } } } Excel Function Table -------------------- Here is a list of Excel functions sorted by index. The Excel name for a function index is "iftab". iftab's appear in ptgFunc and ptgFuncVar tokens. Unused iftab's are reserved for future use. Function iftab -------- ----- COUNT 0x00 IF 0x01 ISNA 0x02 ISERROR 0x03 SUM 0x04 AVERAGE 0x05 MIN 0x06 MAX 0x07 ROW 0x08 COLUMN 0x09 NA 0x0A NPV 0x0B STDEV 0x0C DOLLAR 0x0D FIXED 0x0E SIN 0x0F COS 0x10 TAN 0x11 ATAN 0x12 PI 0x13 SQRT 0x14 EXP 0x15 LN 0x16 LOG10 0x17 ABS 0x18 INT 0x19 SIGN 0x1A ROUND 0x1B LOOKUP 0x1C INDEX 0x1D REPT 0x1E MID 0x1F LEN 0x20 VALUE 0x21 TRUE 0x22 FALSE 0x23 AND 0x24 OR 0x25 NOT 0x26 MOD 0x27 DCOUNT 0x28 DSUM 0x29 DAVERAGE 0x2A DMIN 0x2B DMAX 0x2C DSTDEV 0x2D VAR 0x2E DVAR 0x2F TEXT 0x30 LINEST 0x31 TREND 0x32 LOGEST 0x33 GROWTH 0x34 GOTO 0x35 HALT 0x36 RETURN 0x37 PV 0x38 FV 0x39 NPER 0x3A PMT 0x3B RATE 0x3C MIRR 0x3D IRR 0x3E RAND 0x3F MATCH 0x40 DATE 0x41 TIME 0x42 DAY 0x43 MONTH 0x44 YEAR 0x45 WEEKDAY 0x46 HOUR 0x47 MINUTE 0x48 SECOND 0x49 NOW 0x4A AREAS 0x4B ROWS 0x4C COLUMNS 0x4D OFFSET 0x4E ABSREF 0x4F RELREF 0x50 ARGUMENT 0x51 SEARCH 0x52 TRANSPOSE 0x53 ERROR 0x54 STEP 0x55 TYPE 0x56 ECHO 0x57 SET.NAME 0x58 CALLER 0x59 DEREF 0x5A WINDOWS 0x5B SERIES 0x5C DOCUMENTS 0x5D ACTIVE.CELL 0x5E SELECTION 0x5F RESULT 0x60 ATAN2 0x61 ASIN 0x62 ACOS 0x63 CHOOSE 0x64 HLOOKUP 0x65 VLOOKUP 0x66 LINKS 0x67 INPUT 0x68 ISREF 0x69 GET.FORMULA 0x6A GET.NAME 0x6B SET.VALUE 0x6C LOG 0x6D EXEC 0x6E CHAR 0x6F LOWER 0x70 UPPER 0x71 PROPER 0x72 LEFT 0x73 RIGHT 0x74 EXACT 0x75 TRIM 0x76 REPLACE 0x77 SUBSTITUTE 0x78 CODE 0x79 NAMES 0x7A DIRECTORY 0x7B FIND 0x7C CELL 0x7D ISERR 0x7E ISTEXT 0x7F ISNUMBER 0x80 ISBLANK 0x81 T 0x82 N 0x83 FOPEN 0x84 FCLOSE 0x85 FSIZE 0x86 FREADLN 0x87 FREAD 0x88 FWRITELN 0x89 FWRITE 0x8A FPOS 0x8B DATEVALUE 0x8C TIMEVALUE 0x8D SLN 0x8E SYD 0x8F DDB 0x90 GET.DEF 0x91 REFTEXT 0x92 TEXTREF 0x93 INDIRECT 0x94 REGISTER 0x95 CALL 0x96 ADD.BAR 0x97 ADD.MENU 0x98 ADD.COMMAND 0x99 ENABLE.COMMAND 0x9A CHECK.COMMAND 0x9B RENAME.COMMAND 0x9C SHOW.BAR 0x9D DELETE.MENU 0x9E DELETE.COMMAND 0x9F GET.CHART.ITEM 0xA0 DIALOG.BOX 0xA1 CLEAN 0xA2 MDETERM 0xA3 MINVERSE 0xA4 MMULT 0xA5 FILES 0xA6 IPMT 0xA7 PPMT 0xA8 COUNTA 0xA9 CANCEL.KEY 0xAA FOR 0xAB WHILE 0xAC BREAK 0xAD NEXT 0xAE INITIATE 0xAF REQUEST 0xB0 POKE 0xB1 EXECUTE 0xB2 TERMINATE 0xB3 RESTART 0xB4 HELP 0xB5 GET.BAR 0xB6 PRODUCT 0xB7 FACT 0xB8 GET.CELL 0xB9 GET.WORKSPACE 0xBA GET.WINDOW 0xBB GET.DOCUMENT 0xBC DPRODUCT 0xBD ISNONTEXT 0xBE GET.NOTE 0xBF NOTE 0xC0 STDEVP 0xC1 VARP 0xC2 DSTDEVP 0xC3 DVARP 0xC4 TRUNC 0xC5 ISLOGICAL 0xC6 DCOUNTA 0xC7 DELETE.BAR 0xC8 Here is a list of Excel functions sorted alphabetically by function: Function iftab -------- ----- ABS 0x18 ABSREF 0x4F ACOS 0x63 ACTIVE.CELL 0x5E ADD.BAR 0x97 ADD.COMMAND 0x99 ADD.MENU 0x98 AND 0x24 AREAS 0x4B ARGUMENT 0x51 ASIN 0x62 ATAN 0x12 ATAN2 0x61 AVERAGE 0x05 BREAK 0xAD CALL 0x96 CALLER 0x59 CANCEL.KEY 0xAA CELL 0x7D CHAR 0x6F CHECK.COMMAND 0x9B CHOOSE 0x64 CLEAN 0xA2 CODE 0x79 COLUMN 0x09 COLUMNS 0x4D COS 0x10 COUNT 0x00 COUNTA 0xA9 DATE 0x41 DATEVALUE 0x8C DAVERAGE 0x2A DAY 0x43 DCOUNT 0x28 DCOUNTA 0xC7 DDB 0x90 DELETE.BAR 0xC8 DELETE.COMMAND 0x9F DELETE.MENU 0x9E DEREF 0x5A DIALOG.BOX 0xA1 DIRECTORY 0x7B DMAX 0x2C DMIN 0x2B DOCUMENTS 0x5D DOLLAR 0x0D DPRODUCT 0xBD DSTDEV 0x2D DSTDEVP 0xC3 DSUM 0x29 DVAR 0x2F DVARP 0xC4 ECHO 0x57 ENABLE.COMMAND 0x9A ERROR 0x54 EXACT 0x75 EXEC 0x6E EXECUTE 0xB2 EXP 0x15 FACT 0xB8 FALSE 0x23 FCLOSE 0x85 FILES 0xA6 FIND 0x7C FIXED 0x0E FOPEN 0x84 FOR 0xAB FPOS 0x8B FREAD 0x88 FREADLN 0x87 FSIZE 0x86 FV 0x39 FWRITE 0x8A FWRITELN 0x89 GET.BAR 0xB6 GET.CELL 0xB9 GET.CHART.ITEM 0xA0 GET.DEF 0x91 GET.DOCUMENT 0xBC GET.FORMULA 0x6A GET.NAME 0x6B GET.NOTE 0xBF GET.WINDOW 0xBB GET.WORKSPACE 0xBA GOTO 0x35 GROWTH 0x34 HALT 0x36 HELP 0xB5 HLOOKUP 0x65 HOUR 0x47 IF 0x01 INDEX 0x1D INDIRECT 0x94 INITIATE 0xAF INPUT 0x68 INT 0x19 IPMT 0xA7 IRR 0x3E ISBLANK 0x81 ISERR 0x7E ISERROR 0x03 ISLOGICAL 0xC6 ISNA 0x02 ISNONTEXT 0xBE ISNUMBER 0x80 ISREF 0x69 ISTEXT 0x7F LEFT 0x73 LEN 0x20 LINEST 0x31 LINKS 0x67 LN 0x16 LOG 0x6D LOG10 0x17 LOGEST 0x33 LOOKUP 0x1C LOWER 0x70 MATCH 0x40 MAX 0x07 MDETERM 0xA3 MID 0x1F MIN 0x06 MINUTE 0x48 MINVERSE 0xA4 MIRR 0x3D MMULT 0xA5 MOD 0x27 MONTH 0x44 N 0x83 NA 0x0A NAMES 0x7A NEXT 0xAE NOT 0x26 NOTE 0xC0 NOW 0x4A NPER 0x3A NPV 0x0B OFFSET 0x4E OR 0x25 PI 0x13 PMT 0x3B POKE 0xB1 PPMT 0xA8 PRODUCT 0xB7 PROPER 0x72 PV 0x38 RAND 0x3F RATE 0x3C REFTEXT 0x92 REGISTER 0x95 RELREF 0x50 RENAME.COMMAND 0x9C REPLACE 0x77 REPT 0x1E REQUEST 0xB0 RESTART 0xB4 RESULT 0x60 RETURN 0x37 RIGHT 0x74 ROUND 0x1B ROW 0x08 ROWS 0x4C SEARCH 0x52 SECOND 0x49 SELECTION 0x5F SERIES 0x5C SET.NAME 0x58 SET.VALUE 0x6C SHOW.BAR 0x9D SIGN 0x1A SIN 0x0F SLN 0x8E SQRT 0x14 STDEV 0x0C STDEVP 0xC1 STEP 0x55 SUBSTITUTE 0x78 SUM 0x04 SYD 0x8F T 0x82 TAN 0x11 TERMINATE 0xB3 TEXT 0x30 TEXTREF 0x93 TIME 0x42 TIMEVALUE 0x8D TRANSPOSE 0x53 TREND 0x32 TRIM 0x76 TRUE 0x22 TRUNC 0xC5 TYPE 0x56 UPPER 0x71 VALUE 0x21 VAR 0x2E VARP 0xC2 VLOOKUP 0x66 WEEKDAY 0x46 WHILE 0xAC WINDOWS 0x5B YEAR 0x45 Command Equivalent Function Table --------------------------------- Here is a list of command equivalent functions sorted by index. The Excel name for a comand equivalent function index is "icetab". icetab's appear in ptgFuncCE tokens. Unused icetab's are reserved for future use. Command Equivalent icetab ------------------ ------ BEEP 0x00 OPEN 0x01 OPEN.LINKS 0x02 CLOSE.ALL 0x03 SAVE 0x04 SAVE.AS 0x05 FILE.DELETE 0x06 PAGE.SETUP 0x07 PRINT 0x08 PRINTER.SETUP 0x09 QUIT 0x0A NEW.WINDOW 0x0B ARRANGE.ALL 0x0C SIZE 0x0D MOVE 0x0E FULL 0x0F CLOSE 0x10 RUN 0x11 SET.PRINT.AREA 0x16 SET.PRINT.TITLES 0x17 SET.PAGE.BREAK 0x18 REMOVE.PAGE.BREAK 0x19 FONT 0x1A DISPLAY 0x1B PROTECT.DOCUMENT 0x1C PRECISION 0x1D A1.R1C1 0x1E CALCULATE.NOW 0x1F CALCULATION 0x20 DATA.FIND 0x22 EXTRACT 0x23 DATA.DELETE 0x24 SET.DATABASE 0x25 SET.CRITERIA 0x26 SORT 0x27 DATA.SERIES 0x28 TABLE 0x29 FORMAT.NUMBER 0x2A ALIGNMENT 0x2B STYLE 0x2C BORDER 0x2D CELL.PROTECTION 0x2E COLUMN.WIDTH 0x2F UNDO 0x30 CUT 0x31 COPY 0x32 PASTE 0x33 CLEAR 0x34 PASTE.SPECIAL 0x35 EDIT.DELETE 0x36 INSERT 0x37 FILL.RIGHT 0x38 FILL.DOWN 0x39 DEFINE.NAME 0x3D CREATE.NAMES 0x3E FORMULA.GOTO 0x3F FORMULA.FIND 0x40 SELECT.LAST.CELL 0x41 SHOW.ACTIVE.CELL 0x42 GALLERY.AREA 0x43 GALLERY.BAR 0x44 GALLERY.COLUMN 0x45 GALLERY.LINE 0x46 GALLERY.PIE 0x47 GALLERY.SCATTER 0x48 COMBINATION 0x49 PREFERRED 0x4A ADD.OVERLAY 0x4B GRIDLINES 0x4C SET.PREFERRED 0x4D AXES 0x4E LEGEND 0x4F ATTACH.TEXT 0x50 ADD.ARROW 0x51 SELECT.CHART 0x52 SELECT.PLOT.AREA 0x53 PATTERNS 0x54 MAIN.CHART 0x55 OVERLAY 0x56 SCALE 0x57 FORMAT.LEGEND 0x58 FORMAT.TEXT 0x59 PARSE 0x5B JUSTIFY 0x5C HIDE 0x5D UNHIDE 0x5E WORKSPACE 0x5F FORMULA 0x60 FORMULA.FILL 0x61 FORMULA.ARRAY 0x62 DATA.FIND.NEXT 0x63 DATA.FIND.PREV 0x64 FORMULA.FIND.NEXT 0x65 FORMULA.FIND.PREV 0x66 ACTIVATE 0x67 ACTIVATE.NEXT 0x68 ACTIVATE.PREV 0x69 UNLOCKED.NEXT 0x6A UNLOCKED.PREV 0x6B COPY.PICTURE 0x6C SELECT 0x6D DELETE.NAME 0x6E DELETE.FORMAT 0x6F VLINE 0x70 HLINE 0x71 VPAGE 0x72 HPAGE 0x73 VSCROLL 0x74 HSCROLL 0x75 ALERT 0x76 NEW 0x77 CANCEL.COPY 0x78 SHOW.CLIPBOARD 0x79 MESSAGE 0x7A PASTE.LINK 0x7C APP.ACTIVATE 0x7D DELETE.ARROW 0x7E ROW.HEIGHT 0x7F FORMAT.MOVE 0x80 FORMAT.SIZE 0x81 FORMULA.REPLACE 0x82 SEND.KEYS 0x83 SELECT.SPECIAL 0x84 APPLY.NAMES 0x85 REPLACE.FONT 0x86 FREEZE.PANES 0x87 SHOW.INFO 0x88 SPLIT 0x89 ON.WINDOW 0x8A ON.DATA 0x8B DISABLE.INPUT 0x8C LIST.NAMES 0x8F FILE.CLOSE 0x90 SAVE.WORKSPACE 0x91 DATA.FORM 0x92 COPY.CHART 0x93 ON.TIME 0x94 WAIT 0x95 FORMAT.FONT 0x96 FILL.UP 0x97 FILL.LEFT 0x98 DELETE.OVERLAY 0x99 SHORT.MENUS 0x9B CHANGE.LINK 0xA6 CALCULATE.DOCUMENT 0xA7 ON.KEY 0xA8 APP.RESTORE 0xA9 APP.MOVE 0xAA APP.SIZE 0xAB APP.MINIMIZE 0xAC APP.MAXIMIZE 0xAD MAIN.CHART.TYPE 0xB9 OVERLAY.CHART.TYPE 0xBA SELECT.END 0xBB Here is a list of command equivalent functions sorted alphabetically by function name. Command Equivalent icetab ------------------ ------ A1.R1C1 0x1E ACTIVATE 0x67 ACTIVATE.NEXT 0x68 ACTIVATE.PREV 0x69 ADD.ARROW 0x51 ADD.OVERLAY 0x4B ALERT 0x76 ALIGNMENT 0x2B APPLY.NAMES 0x85 APP.ACTIVATE 0x7D APP.MAXIMIZE 0xAD APP.MINIMIZE 0xAC APP.MOVE 0xAA APP.RESTORE 0xA9 APP.SIZE 0xAB ARRANGE.ALL 0x0C ATTACH.TEXT 0x50 AXES 0x4E BEEP 0x00 BORDER 0x2D CALCULATE.DOCUMENT 0xA7 CALCULATE.NOW 0x1F CALCULATION 0x20 CANCEL.COPY 0x78 CELL.PROTECTION 0x2E CHANGE.LINK 0xA6 CLEAR 0x34 CLOSE 0x10 CLOSE.ALL 0x03 COLUMN.WIDTH 0x2F COMBINATION 0x49 COPY 0x32 COPY.CHART 0x93 COPY.PICTURE 0x6C CREATE.NAMES 0x3E CUT 0x31 DATA.DELETE 0x24 DATA.FIND 0x22 DATA.FIND.NEXT 0x63 DATA.FIND.PREV 0x64 DATA.FORM 0x92 DATA.SERIES 0x28 DEFINE.NAME 0x3D DELETE.ARROW 0x7E DELETE.FORMAT 0x6F DELETE.NAME 0x6E DELETE.OVERLAY 0x99 DISABLE.INPUT 0x8C DISPLAY 0x1B EDIT.DELETE 0x36 EXTRACT 0x23 FILE.CLOSE 0x90 FILE.DELETE 0x06 FILL.DOWN 0x39 FILL.LEFT 0x98 FILL.RIGHT 0x38 FILL.UP 0x97 FONT 0x1A FORMAT.FONT 0x96 FORMAT.LEGEND 0x58 FORMAT.MOVE 0x80 FORMAT.NUMBER 0x2A FORMAT.SIZE 0x81 FORMAT.TEXT 0x59 FORMULA 0x60 FORMULA.ARRAY 0x62 FORMULA.FILL 0x61 FORMULA.FIND 0x40 FORMULA.FIND.NEXT 0x65 FORMULA.FIND.PREV 0x66 FORMULA.GOTO 0x3F FORMULA.REPLACE 0x82 FREEZE.PANES 0x87 FULL 0x0F GALLERY.AREA 0x43 GALLERY.BAR 0x44 GALLERY.COLUMN 0x45 GALLERY.LINE 0x46 GALLERY.PIE 0x47 GALLERY.SCATTER 0x48 GRIDLINES 0x4C HIDE 0x5D HLINE 0x71 HPAGE 0x73 HSCROLL 0x75 INSERT 0x37 JUSTIFY 0x5C LEGEND 0x4F LIST.NAMES 0x8F MAIN.CHART 0x55 MAIN.CHART.TYPE 0xB9 MESSAGE 0x7A MOVE 0x0E NEW 0x77 NEW.WINDOW 0x0B ON.DATA 0x8B ON.KEY 0xA8 ON.TIME 0x94 ON.WINDOW 0x8A OPEN 0x01 OPEN.LINKS 0x02 OVERLAY 0x56 OVERLAY.CHART.TYPE 0xBA PAGE.SETUP 0x07 PARSE 0x5B PASTE 0x33 PASTE.LINK 0x7C PASTE.SPECIAL 0x35 PATTERNS 0x54 PRECISION 0x1D PREFERRED 0x4A PRINT 0x08 PRINTER.SETUP 0x09 PROTECT.DOCUMENT 0x1C QUIT 0x0A REMOVE.PAGE.BREAK 0x19 REPLACE.FONT 0x86 ROW.HEIGHT 0x7F RUN 0x11 SAVE 0x04 SAVE.AS 0x05 SAVE.WORKSPACE 0x91 SCALE 0x57 SELECT 0x6D SELECT.CHART 0x52 SELECT.END 0xBB SELECT.LAST.CELL 0x41 SELECT.PLOT.AREA 0x53 SELECT.SPECIAL 0x84 SEND.KEYS 0x83 SET.CRITERIA 0x26 SET.DATABASE 0x25 SET.PAGE.BREAK 0x18 SET.PREFERRED 0x4D SET.PRINT.AREA 0x16 SET.PRINT.TITLES 0x17 SHORT.MENUS 0x9B SHOW.ACTIVE.CELL 0x42 SHOW.CLIPBOARD 0x79 SHOW.INFO 0x88 SIZE 0x0D SORT 0x27 SPLIT 0x89 STYLE 0x2C TABLE 0x29 UNDO 0x30 UNHIDE 0x5E UNLOCKED.NEXT 0x6A UNLOCKED.PREV 0x6B VLINE 0x70 VPAGE 0x72 VSCROLL 0x74 WAIT 0x95 WORKSPACE 0x5F List of Ptg's ------------- Here is a list of all ptg's that appear in BIFF files. All other ptg's are reserved for future use. Name Ptg Type ---- --- ---- ptgExp 0x01 control ptgTbl 0x02 control ptgAdd 0x03 operator ptgSub 0x04 operator ptgMul 0x05 operator ptgDiv 0x06 operator ptgPower 0x07 operator ptgConcat 0x08 operator ptgLT 0x09 operator ptgLE 0x0A operator ptgEQ 0x0B operator ptgGE 0x0C operator ptgGT 0x0D operator ptgNE 0x0E operator ptgIsect 0x0F operator ptgUnion 0x10 operator ptgRange 0x11 operator ptgUplus 0x12 operator ptgUminus 0x13 operator ptgPercent 0x14 operator ptgParen 0x15 control ptgMissArg 0x16 operand ptgStr 0x17 operand ptgAttr 0x19 control ptgSheet 0x1A control ptgEndSheet 0x1B control ptgErr 0x1C operand ptgBool 0x1D operand ptgInt 0x1E operand ptgNum 0x1F operand ptgArray 0x20 operand, reference class ptgFunc 0x21 operator ptgFuncVar 0x22 operator ptgName 0x23 operand, reference class ptgRef 0x24 operand, reference class ptgArea 0x25 operand, reference class ptgMemArea 0x26 operand, reference class ptgMemErr 0x27 operand, reference class ptgMemNoMem 0x28 control ptgMemFunc 0x29 control ptgRefErr 0x2A operand, reference class ptgAreaErr 0x2B operand, reference class ptgRefN 0x2C operand, reference class ptgAreaN 0x2D operand, reference class ptgMemAreaN 0x2E control ptgMemNoMemN 0x2F control ptgFuncCE 0x38 operator ptgArrayV 0x40 operand, value class ptgFuncV 0x41 operator ptgFuncVarV 0x42 operator ptgNameV 0x43 operand, value class ptgRefV 0x44 operand, value class ptgAreaV 0x45 operand, value class ptgMemAreaV 0x46 operand, value class ptgMemErrV 0x47 operand, value class ptgMemNoMemV 0x48 control ptgMemFuncV 0x49 control ptgRefErrV 0x4A operand, value class ptgAreaErrV 0x4B operand, value class ptgRefNV 0x4C operand, value class ptgAreaNV 0x4D operand, value class ptgMemAreaNV 0x4E control ptgMemNoMemNV 0x4F control ptgFuncCEV 0x58 operator ptgArrayA 0x60 operand, array class ptgFuncA 0x61 operator ptgFuncVarA 0x62 operator ptgNameA 0x63 operand, array class ptgRefA 0x64 operand, array class ptgAreaA 0x65 operand, array class ptgMemAreaA 0x66 operand, array class ptgMemErrA 0x67 operand, array class ptgMemNoMemA 0x68 control ptgMemFuncA 0x69 control ptgRefErrA 0x6A operand, array class ptgAreaErrA 0x6B operand, array class ptgRefNA 0x6C operand, array class ptgAreaNA 0x6D operand, array class ptgMemAreaNA 0x6E control ptgMemNoMemNA 0x6F control ptgFuncCEA 0x78 operator Data writing object - (c) J.G. Ferreira, CIS 100326,1361. This upload allows a program to save records from TPW (and BP7 with minor changes) to ascii space-delimited files, ascii tab-delimited files, and MS-EXCEL vers 2.1, 3, 4 and 5. I use it to save data to file from a database in TPW and paradox engine, and results from mathematical modelling programs. A sample app. BIFFALL.PAS is provided which creates a file called test.xls in the root directory of C:\ and saves different types of data to it. You may then use Excel to open the file (or notepad for ASCII files). Parts of this are adapted from: BIFFLib 1.00 object: Object for reading and writing BIFF-files Copyright (C) Marcus Hettlage 1993 uploaded to the Pascal forum on CIS, and MS-SDK data. I found the object rather difficult to work with, so here a different approach is used. Some of the BIFF code is translated from C from an example by Todd Lucas from Microsoft. The classes were built by me, but the approach for writing the record header separately and then the data is from him. It is however made much simpler by using objects. If you want to further develop the classes to read excel files, save formatting info, etc. refer to the Excel SDK books from Microsoft Press or to Marcus Hettlage's object. Feel free to use these files as is, extend them, change the code etc. Let me know of problems/comments. With thanks to all (on this forum an otherwise) for help in so many different ways, specially to Marcus Hettlage, Todd Lucas and Frank Plas. ----- WINSAVE.PAS ------- {$I-,N+} {General purpose save library - (c) J. Gomes Ferreira 1994 Writes records in Excel v.2.1, v.3, v.4, ASCII comma separated text, and tab-delimited excel text files Excel BIFF: parts translated from C by Todd Lucas - Microsoft corp.} Unit WinSave; Interface uses strings, winfdlg, winprocs, wintypes; Const Space : char = chr(32); Tab : char = chr(9); CR : char = chr(13); LF : char = chr(10); {BOF} BOF = $0009; BIT_BIFF5 = $0800; BIT_BIFF4 = $0400; BIT_BIFF3 = $0200; BOF_BIFF5 = BOF or BIT_BIFF5; BOF_BIFF4 = BOF or BIT_BIFF4; BOF_BIFF3 = BOF or BIT_BIFF3; {EOF} BIFF_EOF = $000a; {Dimensions} DIMENSIONS = $0000; DIMENSIONS_BIFF4 = DIMENSIONS or BIT_BIFF3; DIMENSIONS_BIFF3 = DIMENSIONS or BIT_BIFF3; {Document types} DOCTYPE_XLS = $0010; DOCTYPE_XLC = $0020; DOCTYPE_XLM = $0040; DOCTYPE_XLW = $0100; {Use with output functions} VER_BIFF4 = $04; VER_BIFF3 = $03; VER_BIFF2 = $02; {Structures} LEN_RECORDHEADER = 4; {Data types } CellBlank = 1; CellInteger = 2; CellDouble = 4; CellLabel = 8; CellBoolean = 16; { or error } Type string10 = String[10]; String255 = string[255]; chartype = array[0..255] of char; PBaseSave = ^TBaseSave; TBaseSave = object Charfile : file of char; DataString : String255; Separator : char; MinSaveRecs, MaxSaveRecs, MinSaveCols, MaxSaveCols : word; CellType, Row, Col : integer; Data : pointer; EndOfLine : boolean; Constructor Init(SaveFileName : TFileName ); procedure WriteBlank; virtual; procedure WriteInteger; virtual; procedure WriteDouble; virtual; procedure WriteLabel (var w : word); virtual; procedure WriteData(AType, ARow, ACol: Integer; AData: Pointer); virtual; Destructor Done; virtual; end; PASCII = ^TASCII; TASCII = object(TBaseSave) Constructor Init( SaveFileName : TFileName ); Destructor Done; virtual; end; PExcelTab = ^TExcelTab; TExcelTab = object(TBaseSave) Constructor Init(SaveFileName : TFileName ); Destructor Done; virtual; end; PBIFF2 = ^TBIFF2; TBIFF2 = object(TBaseSave) {BIFFtime, BIFFdata : double;} BIFFColumn : byte; ExcelFile : File; VerBIFF, TypeDOC : word; typerec, lendata : word; constructor Init(AFileName : TFileName); destructor Done; virtual; procedure BIFFBOF; virtual; procedure BIFFDIM; virtual; procedure WriteBOF; virtual; procedure WriteRecordHeader; virtual; procedure WriteDimensions; virtual; procedure WriteEOF; virtual; procedure WriteData(AType, ARow, ACol: Integer; AData: Pointer); virtual; procedure WriteBlank; virtual; procedure WriteInteger; virtual; procedure WriteDouble; virtual; procedure WriteLabel (var w : word); virtual; procedure WriteBoolean; virtual; end; PBIFF3 = ^TBIFF3; TBIFF3 = object(TBIFF2) procedure BIFFBOF; virtual; procedure BIFFDIM; virtual; end; PBIFF4 = ^TBIFF4; TBIFF4 = object(TBIFF3) procedure BIFFBOF; virtual; end; PBIFF5 = ^TBIFF5; TBIFF5 = object(TBIFF4) procedure BIFFBOF; virtual; end; var PSaveFile : PBaseSave; Implementation {Generic save object} Constructor TBaseSave.Init; begin MinSaveRecs := 0; MaxSaveRecs := 100; MinSaveCols := 0; MaxSaveCols := 100; EndOfLine := false; end; Procedure TBaseSave.WriteBlank; begin write( CharFile, separator ); end; Procedure TBaseSave.WriteInteger; var AIntegerP : ^integer; AInteger : integer; begin AIntegerP := Data; AInteger := AIntegerP^; str(AInteger, DataString ); end; Procedure TBaseSave.WriteDouble; var ADoubleP : ^double; ADouble : double; begin ADoubleP := Data; ADouble := ADoubleP^; str(ADouble, DataString ); end; Procedure TBaseSave.WriteLabel; var ALabelP : ^CharType; ALabel : CharType; begin ALabelP := Data; ALabel := ALabelP^; DataString := StrPas( ALabel ); w := length(DataString); {unused by calling method} end; Procedure TBaseSave.WriteData; var i : integer; AWordLength : word; begin CellType := AType; if Row <> -1 then if Row <> ARow then EndOfLine := true else EndOfLine := false; Row := ARow; Col := ACol; Data := AData; case CellType of CellBlank : WriteBlank; CellInteger : WriteInteger; CellDouble : WriteDouble; CellLabel : WriteLabel(AWordLength); CellBoolean : exit; {No boolean types in text files} else exit; end; if EndOfLine then begin write ( CharFile, CR ); write ( CharFile, LF ) end; for i := 1 to length(DataString) do write( CharFile, DataString[i] ); write( CharFile, separator ); end; Destructor TBaseSave.Done; begin end; {ASCII files object} Constructor TASCII.Init; begin TBaseSave.Init( SaveFileName ); Separator := Space; assign( CharFile, SaveFileName ); Row := -1; col := -1; rewrite ( CharFile ); end; Destructor TASCII.Done; begin TBaseSave.Done; close( CharFile ); end; {Excel tab-delimited files object} Constructor TExcelTab.Init; begin TBaseSave.Init( SaveFileName ); Separator := tab; assign( CharFile, SaveFileName ); Row := -1; col := -1; rewrite ( CharFile ); end; Destructor TExcelTab.Done; begin TBaseSave.Done; close( CharFile ); end; {Excel BIFF2 object} Constructor TBIFF2.Init; begin TBaseSave.Init( AFileName ); Assign( ExcelFile, AFileName); Rewrite( ExcelFile, 1 ); WriteBOF; WriteDimensions; end; Destructor TBIFF2.Done; begin TBaseSave.Done; WriteEOF; Close (ExcelFile); end; procedure TBIFF2.BIFFBOF; begin typerec := BOF; lendata := 4; end; procedure TBIFF2.BIFFDIM; begin typerec := DIMENSIONS; lendata := 8; end; procedure TBIFF2.WriteBOF; var awBuf : array[0..2] of word; begin awBuf[0] := 0; awBuf[1] := DOCTYPE_XLS; awBuf[2] := 0; BIFFBOF; WriteRecordHeader; Blockwrite(Excelfile, awbuf, lendata); end; procedure TBIFF2.WriteRecordHeader; var awBuf : array[0..1] of word; begin awBuf[0] := typerec; awBuf[1] := lendata; Blockwrite(Excelfile, awbuf, LEN_RECORDHEADER); end; procedure TBIFF2.WriteDimensions; var awBuf : array[0..4] of word; begin awBuf[0] := MinSaveRecs; awBuf[1] := MaxSaveRecs; awBuf[2] := MinSaveCols; awBuf[3] := MaxSaveCols; awBuf[4] := 0; BIFFDIM; WriteRecordHeader; Blockwrite(Excelfile, awbuf, lendata); end; procedure TBIFF2.WriteEOF; begin typerec := BIFF_EOF; lendata := 0; WriteRecordHeader; end; Procedure TBIFF2.WriteBlank; begin typerec := 1; lendata := 7; WriteRecordHeader; lendata := 0; end; Procedure TBIFF2.WriteInteger; begin typerec := 2; lendata := 9; WriteRecordHeader; lendata := 2; end; Procedure TBIFF2.WriteDouble; begin typerec := 3; lendata := 15; WriteRecordHeader; lendata := 8; end; Procedure TBIFF2.WriteLabel(var w : word); begin w := strlen(Data); typerec := 4; lendata := 8+w; WriteRecordHeader; lendata := w; end; Procedure TBIFF2.WriteBoolean; begin typerec := 5; lendata := 9; WriteRecordHeader; lendata := 0; end; Procedure TBIFF2.WriteData; const Attribute: Array[0..2] Of Byte = (0, 0, 0); { 24 bit bitfield } var awBuf : array[0..1] of word; AWordLength : word; ABoolByte : byte; begin CellType := AType; Row := ARow; Col := ACol; Data := AData; case CellType of CellBlank : WriteBlank; CellInteger : WriteInteger; CellDouble : WriteDouble; CellLabel : WriteLabel(AWordLength); CellBoolean : WriteBoolean; { or error } else exit; end; awBuf[0] := Row; awBuf[1] := Col; Blockwrite(Excelfile, awbuf, sizeof(awBuf)); BlockWrite(Excelfile, Attribute, SizeOf(Attribute)); if CellType = CellLabel then begin ABoolByte := AWordLength; BlockWrite(Excelfile, ABoolByte, SizeOf(ABoolByte)) end else if CellType = CellBoolean then begin if byte(Data^) <> 0 then ABoolByte := 1 else ABoolByte := 0; BlockWrite(Excelfile, ABoolByte, SizeOf(ABoolByte)); ABoolByte := 0; BlockWrite(Excelfile, ABoolByte, SizeOf(ABoolByte)); end; if lendata <> 0 then BlockWrite(Excelfile, Data^, lendata); end; {Excel BIFF3 object} procedure TBIFF3.BIFFBOF; begin typerec := BOF_BIFF3; lendata := 6; end; procedure TBIFF3.BIFFDIM; begin typerec := DIMENSIONS_BIFF3; lendata := 10; end; {Excel BIFF4 object} procedure TBIFF4.BIFFBOF; begin typerec := BOF_BIFF4; lendata := 6; end; {Excel BIFF5 object} procedure TBIFF5.BIFFBOF; begin typerec := BOF_BIFF5; lendata := 6; end; end. ---------End of WINPAS.PAS ------------ ------ BIFFALL.PAS (Hauptprogramm) ----- {General purpose save library - (c) J. Gomes Ferreira 1994 Writes records in Excel v.2.1, v.3, v.4, ASCII comma separated text, and tab-delimited excel text files {You have a royalty-free right to use, modify, reproduce, and distribute this file (and/or any modified version) in any way you find useful, provided that you agree that I offer no warranty, and have no obligations or liability for anything whatsoever relating to the use of the files contained herein.} Program TestBiff; {N+} Uses Winsave,WinFdlg,strings,wintypes,winprocs; var i,j : integer; ADouble : Double; AInteger : Integer; ALabel : array[0..10] of char; ABoolean : boolean; FullFileName : TFileName; begin StrCopy(FullFileName,'c:\test.xls'); {substitute the appropriate BIFF in the line below} PSavefile := New(PBIFF5,Init(FullFileName)); ADouble := 1234.5678; AInteger := 25; StrCopy(ALabel,'10/11/94'); ABoolean := True; with PSaveFile^ do begin for i := 0 to 9 do for j := 0 to 9 do begin case j of 0: PSaveFile^.WriteData(CellLabel,i,j,@ALabel); 1: PSaveFile^.WriteData(CellInteger,i,j,@AInteger); 2: PSaveFile^.WriteData(CellBoolean,i,j,@ABoolean); else PSaveFile^.WriteData(CellDouble,i,j,@ADouble); end; end; end; dispose(PSaveFile,done); messagebox(0,'Job complete','BIFF any version',mb_ok); end.