OpenOffice.org's Documentation of the Microsoft Excel File Format Excel Versions 2, 3, 4, 5, 95, 97, 2000, XP, 2003 Author Daniel Rentz ✉ mailto:[email protected]  http://sc.openoffice.org License Public Documentation License Contributors Yves Hiltpold, James J. Keene, Sami Kuhmonen, John Marmion, Alexander Mavrin, Josh Micich, Andrew C. Oliver, Mike Salter, Stefan Schmöcker, Charles Wyble Other sources Hyperlinks to Wikipedia ( http://www.wikipedia.org) for various extended information Mailing list ✉ mailto:[email protected] Subscription ✉ mailto:[email protected] Download PDF  http://sc.openoffice.org/excelfileformat.pdf OpenOffice.org 1.x XML  http://sc.openoffice.org/excelfileformat.sxw OpenOffice.org 2.x XML  http://sc.openoffice.org/excelfileformat.odt Project started 2001-Jun-29 Last change 2008-Apr-02 Revision 1.42 Contents 1 Introduction ......................................................................................................... 6 1.1 License Notices 6 1.2 Abstract 7 1.3 Byte Order 9 2 Document Structure ........................................................................................... 10 2.1 Document Types 10 2.2 The Binary Interchange File Format 13 2.3 File Structure 14 2.4 BIFF Record Structure 16 2.5 Common Record Substructures 17 3 Formulas ............................................................................................................ 28 3.1 Common Formula Structure 28 3.2 Token Classes 32 3.3 Cell Addresses in Tokens 36 3.4 Token Overview 40 3.5 Unary Operator Tokens 44 3.6 Binary Operator Tokens 45 3.7 Function Operator Tokens 49 3.8 Constant Operand Tokens 51 3.9 Operand Tokens 54 3.10 Control Tokens 65 3.11 Built-In Sheet Functions 74 4 Worksheet/Workbook Stream ............................................................................ 79 4.1 Worksheet/Workbook Stream 80 4.2 Record Order in Streams and Substreams 81 4.3 Calculation Settings Block 87 4.4 Page Settings Block 87 4.5 Sheet View Settings Block 88 4.6 Cell Formatting 89 4.7 Cell Table and Row Blocks 90 4.8 Array Formulas and Shared Formulas 96 4.9 Multiple Operation Tables 97 4.10 Internal and External References 98 4.11 Shared String Table (BIFF8) 110 2 4.12 Conditional Formatting Table (BIFF8) 112 4.13 Hyperlink Table (BIFF8) 112 4.14 Data Validity Table (BIFF8) 112 4.15 AutoFilter, Advanced Filter 113 4.16 Scenarios 113 4.17 Web Queries (BIFF8) 113 4.18 Worksheet/Workbook Protection 114 4.19 File Protection 117 5 Worksheet/Workbook Records ........................................................................ 120 5.1 Overview, Ordered by Record Identifier 120 5.2 Overview, Ordered by Record Names 124 5.3 Overview, Ordered by BIFF Versions 127 5.4 ARRAY 132 5.5 BACKUP 133 5.6 BITMAP 133 5.7 BLANK 134 5.8 BOF – Beginning of File 135 5.9 BOOKBOOL 137 5.10 BOOLERR 137 5.11 BOTTOMMARGIN 138 5.12 BUILTINFMTCOUNT 138 5.13 CALCCOUNT 139 5.14 CALCMODE 139 5.15 CFHEADER 139 5.16 CFRULE – Conditional Formatting Rule 140 5.17 CODEPAGE 145 5.18 COLINFO 146 5.19 COLUMNDEFAULT 147 5.20 COLWIDTH 147 5.21 CONTINUE 148 5.22 COUNTRY 149 5.23 CRN 150 5.24 DATATABLE – Multiple Operation Table 151 5.25 DATATABLE2 – Multiple Operation Table 152 5.26 DATAVALIDATION 153 5.27 DATAVALIDATIONS 154 5.28 DATEMODE 154 5.29 DBCELL 155 5.30 DCONREF – Data Consolidation Reference 155 5.31 DEFAULTROWHEIGHT 156 5.32 DEFCOLWIDTH 156 5.33 DEFINEDNAME 157 5.34 DELTA 160 5.35 DIMENSION 160 5.36 DSF – Double Stream File 161 5.37 EOF – End of File 161 5.38 EXTERNALBOOK – External Workbook 162 5.39 EXTERNALNAME 164 5.40 EXTERNCOUNT 166 5.41 EXTERNSHEET 166 5.42 EXTSST – Extended SST 167 5.43 FILEPASS 168 5.44 FILESHARING 170 5.45 FONT 171 5.46 FONT2 173 3 5.47 FONTCOLOR 173 5.48 FOOTER 173 5.49 FORMAT 174 5.50 FORMULA 176 5.51 GCW – Global Column Width 178 5.52 GRIDSET 179 5.53 GUTS 179 5.54 HCENTER 179 5.55 HEADER 180 5.56 HIDEOBJ 181 5.57 HORIZONTALPAGEBREAKS 181 5.58 HYPERLINK 182 5.59 INDEX 185 5.60 INTEGER 186 5.61 ITERATION 186 5.62 IXFE – Index to XF 186 5.63 LABEL 187 5.64 LABELRANGES 187 5.65 LABELSST 188 5.66 LEFTMARGIN 188 5.67 MERGEDCELLS 188 5.68 MULBLANK – Multiple BLANK 189 5.69 MULRK – Multiple RK 189 5.70 NOTE 190 5.71 NUMBER 191 5.72 OBJECTPROTECT 191 5.73 PAGESETUP 192 5.74 PALETTE 195 5.75 PANE 197 5.76 PASSWORD 197 5.77 PHONETICPR 198 5.78 PLS 198 5.79 PRECISION 198 5.80 PRINTGRIDLINES 199 5.81 PRINTHEADERS 199 5.82 PROTECT 200 5.83 QUICKTIP 200 5.84 RANGEPROTECTION 200 5.85 REFMODE 201 5.86 RIGHTMARGIN 201 5.87 RK 201 5.88 ROW 202 5.89 RSTRING 204 5.90 SAVERECALC 204 5.91 SCENPROTECT 205 5.92 SCL 205 5.93 SELECTION 205 5.94 SHAREDFMLA – Shared Formula 206 5.95 SHEET 206 5.96 SHEETLAYOUT 207 5.97 SHEETPR 207 5.98 SHEETPROTECTION 208 5.99 SORT 209 5.100SST – Shared String Table 210 5.101STANDARDWIDTH 210 5.102STRING 211 5.103STYLE 212 4 5.104TOPMARGIN 213 5.105UNCALCED 213 5.106USESELFS 213 5.107VCENTER 214 5.108VERTICALPAGEBREAKS 214 5.109WINDOW1 215 5.110WINDOW2 216 5.111WINDOWPROTECT 217 5.112WRITEACCESS 218 5.113WRITEPROT 218 5.114XCT – CRN Count 218 5.115XF – Extended Format 219 6 Drawing Objects .............................................................................................. 225 7 Charts ............................................................................................................... 226 7.1 Document Integration 226 7.2 General Chart Structure 229 7.3 Common Record Blocks 231 7.4 Record Overview, Ordered by Identifier 234 7.5 Record Overview, Ordered by Name 235 7.6 CHAREAFORMAT 236 7.7 CHAXISLINE 237 7.8 CHBEGIN – Begin of Block 238 7.9 CHEND – End of Block 239 7.10 CHFRAME 240 7.11 CHLINEFORMAT 241 7.12 CHSOURCELINK 242 7.13 CHSTRING 243 8 PivotTables ...................................................................................................... 244 9 Change Tracking ............................................................................................. 245 10 Workspace Documents .................................................................................... 246 10.1 Workspace Stream 246 10.2 Overview, Ordered by Record Identifier 248 10.3 INDEX 249 10.4 SHEETHDR 250 10.5 SHEETSOFFSET 250 5 1 Introduction 1 Introduction 1.1 License Notices 1.1.1 Public Documentation License Notice The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the "License"); you may only use this Documentation if you comply with the terms of this License. A copy of the License is available at  http://www.openoffice.org/licenses/PDL.html. The Original Documentation is "OpenOffice.org's Documentation of the Microsoft Excel File Format". The Initial Writer of the Original Documentation is Sun Microsystems, Inc., Copyright © 2003. All Rights Reserved. See title page for Author contact and Contributors. All Trademarks are properties of their respective owners. 1.1.2 Wikipedia Wikipedia Disclaimer:  http://en.wikipedia.org/wiki/Wikipedia:General_disclaimer 6 1.2 Abstract 1.2 Abstract This document contains a description of the binary file format of Microsoft Excel, including all available Excel versions (for Windows) from Excel 2.x to the current Excel 2003. This project has been started in June 2001 and is still in progress. At several places the remark “2do” indicates an incomplete section of the documentation. 1.2.1 Project Status Chapter Contents Status 1 Introduction Common information Done 2 Document Structure Document structure overview Done 3 Formulas - Structure of RPN token arrays Done - Detailed description of all tokens In progress 4 Worksheet/Workbook Stream Abstract description of complex features represented In progress by several records 5 Worksheet/Workbook Records Detailed description of all records of the In progress worksheet/workbook stream 6 Drawing Objects Not started 7 Charts Internals of chart sheets and chart objects In progress 8 PivotTables Not started 9 Change Tracking Not started 10 Workspace Documents In progress 7 1 Introduction 1.2.2 Used Terms, Symbols, and Formatting References • A reference to another chapter is symbolised by a little arrow: ➜1.1. Definitions • Definitions of important terms are shown in a box with light-grey background. Definition: This is an example of a definition box. Examples • An example is indented and marked with a light-grey border. This is an example. Important Passages • Text passages with important information contain a leading exclamation mark. ! This is an important passage. Numbers and Strings • Numerical values are shown in several number systems: Number system Marking Example Decimal None 1234 Hexadecimal Trailing “H” 1234 H Binary Trailing “2” 1001 2 Constant strings are enclosed in quotation marks. They may contain specific values (control characters, unprintable characters). These values are enclosed in angle brackets. Example of a string containing a control character: “abcdef<01 >ghij”. H Record Listings • A record listing shows a bundle of records in the required order. A dark grey rectangle stands for a single record, a light grey rectangle stands for a group of records. Either this is a group representing a specific feature and is referred with the Record Group Name, or it is a group of various unspecified records that do not matter in this context. RECORD NAME Comments Record Group Name Comments 8 1.2 Abstract Record Content Listings • • Data offsets enclosed in square brackets indicate record content that may be omitted (the remark “optional” may point out this). • The term “Not used” means: Ignore the data on import and write zero bytes on export. The same applies for unmen- tioned bits in bit fields. • The term “Unknown” describes data fields with fixed but unknown contents. On export these fields have to be written as shown. • At several places a variable is introduced, which represents the value of this field for later use. In most common cases this is a field containing a size value, which is used later in the “Size” column of the record content listing. An example can be found in ➜2.4. Algorithm Listings • Algorithms given in pseudo-code are shown in a box with light-grey background. ALGORITHM Example_Algorithm 1) Command 1 2) Command 2 Notation conventions used in algorithms: Notation Description command1 ; command2 Two commands in one line, first execute command1, then command2 var ← value The value value is assigned to the variable var JUMP x) Continue with line x) in the algorithm RETURN [value] Returns value value if specified, otherwise returns without a return value IF cond THEN command Execute command only, if condition cond evaluates to true AND Binary AND operation OR Binary OR operation XOR Binary XOR (exclusive or) operation = <> < > <= >= Comparison operators for conditional execution array[] An array consisting of equal typed elements array[0] The first element of the array array[] (arrays are used zero-based) 1.3 Byte Order All data items containing more than one byte are stored using the Little-Endian method1. That means the least significant byte is stored first and the most significant byte last. This applies for all data types like 16-bit integers, 32-bit integers, floating-point values and Unicode characters. Example: The 32-bit integer value 13579BDF is converted into the byte sequence DF 9B 57 13 . H H H H H 1 For more information see http://en.wikipedia.org/wiki/Endianness. 9 2 Document Structure 2 Document Structure 2.1 Document Types 2.1.1 Microsoft Excel Releases The following table shows the different Excel versions released for Microsoft Windows and Apple Macintosh2: Excel version MS Windows Release year Apple Macintosh Release year Excel 2.x Excel 2.0 1987 Excel 2.2 1989 Excel 3.0 Excel 3.0 1990 Excel 3.0 1990 Excel 4.0 Excel 4.0 1992 Excel 4.0 1992 Excel 5.0 Excel 5.0 1993 Excel 5.0 1993 Excel 7.0 Excel 95 1995 ─ Excel 8.0 Excel 97 1997 Excel 98 1998 Excel 9.0 Excel 2000 1999 Excel 2001 2000 Excel 10.0 Excel XP 2001 Excel v.X 2001 Excel 11.0 Excel 2003 2003 Excel 2004 2004 2.1.2 Worksheet Document Definition: Worksheet Document A worksheet document consists of a single sheet only. Various kinds of sheets are possible, for instance a regular sheet (containing values and formulas), a chart sheet (➜7.1.1), or a macro sheet. The default file extension of worksheet documents is “XLS”. General structure of a worksheet document: Worksheet document Sheet 2 Source: http://en.wikipedia.org/wiki/Microsoft_Excel. 10