'sDocumentationofthe
MicrosoftExcelFileFormat
ExcelVersions2,3,4,5,95,97,2000,XP,2003
AuthorLicenseContributors
OthersourcesMailinglist
Download
ProjectstartedLastchangeRevision
DanielRentz✉mailto:dr@
PublicDocumentationLicense
YvesHiltpold,JamesJ.Keene,SamiKuhmonen,JohnMarmion,AlexanderMavrin,JoshMicich,AndrewC.Oliver,MikeSalter,StefanSchmöcker,CharlesWyble
HyperlinkstoWikipedia()forvariousextendedinformation
✉mailto:dev@Subscription✉mailto:dev-subscribe@
PDF/excelfileformat.pdf1.xXML/excelfileformat.sxw2.xXML/excelfileformat.odt
2001-Jun-29
2008-Apr-02
1.42
Contents
1Introduction
6 1.1LicenseNotices
6 1.2Abstract
7 1.3ByteOrder
9 2DocumentStructure
2 4.12ConditionalFormattingTable(BIFF8) 112 4.13HyperlinkTable(BIFF8) 112 4.14DataValidityTable(BIFF8) 112 4.15AutoFilter,AdvancedFilter 113 4.16Scenarios 113 4.17WebQueries(BIFF8) 113 4.18Worksheet/WorkbookProtection 114 4.19FileProtection 117 5Worksheet/WorkbookRecords........................................................................120 5.1Overview,OrderedbyRecordIdentifier 120 5.2Overview,OrderedbyRecordNames 124 5.3Overview,OrderedbyBIFFVersions 127 5.4ARRAY 132 5.5BACKUP 133 5.6BITMAP 133 5.7BLANK 134 5.8BOF–BeginningofFile 135 5.9BOOKBOOL 137 5.10BOOLERR 137 5.11BOTTOMMARGIN 138 5.12BUILTINFMTCOUNT 138 5.13CALCCOUNT 139 5.14CALCMODE 139 5.15CFHEADER 139 5.16CFRULE–ConditionalFormattingRule 140 5.17CODEPAGE 145 5.18COLINFO 146 5.19COLUMNDEFAULT 147 5.20COLWIDTH 147 5.21CONTINUE 148 5.22COUNTRY 149 5.23CRN 150 5.24DATATABLE–MultipleOperationTable 151 5.25DATATABLE2–MultipleOperationTable 152 5.26DATAVALIDATION 153 5.27DATAVALIDATIONS 154 5.28DATEMODE 154 5.29DBCELL 155 5.30DCONREF–DataConsolidationReference 155 5.31DEFAULTROWHEIGHT 156 5.32DEFCOLWIDTH 156 5.33DEFINEDNAME 157 5.34DELTA 160 5.35DIMENSION 160 5.36DSF–DoubleStreamFile 161 5.37EOF–EndofFile 161 5.38EXTERNALBOOK–ExternalWorkbook 162 5.39EXTERNALNAME 164 5.40EXTERNCOUNT 166 5.41EXTERNSHEET 166 5.42EXTSST–ExtendedSST 167 5.43FILEPASS 168 5.44FILESHARING 170 5.45FONT 171 5.46FONT2 173
3 5.47FONTCOLOR 173 5.48FOOTER 173 5.49FORMAT 174 5.50FORMULA 176 5.51GCW–GlobalColumnWidth 178 5.52GRIDSET 179 5.53GUTS 179 5.54HCENTER 179 5.55HEADER 180 5.56HIDEOBJ 181 5.57HORIZONTALPAGEBREAKS 181 5.58HYPERLINK 182 5.59INDEX 185 5.60INTEGER 186 5.61ITERATION 186 5.62IXFE–IndextoXF 186 5.63LABEL 187 5.64LABELRANGES 187 5.65LABELSST 188 5.66LEFTMARGIN 188 5.67MERGEDCELLS 188 5.68MULBLANK–MultipleBLANK 189 5.69MULRK–MultipleRK 189 5.70NOTE 190 5.71NUMBER 191 5.72OBJECTPROTECT 191 5.73PAGESETUP 192 5.74PALETTE 195 5.75PANE 197 5.76PASSWORD 197 5.77PHONETICPR 198 5.78PLS 198 5.79PRECISION 198 5.80PRINTGRIDLINES 199 5.81PRINTHEADERS 199 5.82PROTECT 200 5.83QUICKTIP 200 5.84RANGEPROTECTION 200 5.85REFMODE 201 5.86RIGHTMARGIN 201 5.87RK 201 5.88ROW 202 5.89RSTRING 204 5.90SAVERECALC 204 5.91SCENPROTECT 205 5.92SCL 205 5.93SELECTION 205 5.94SHAREDFMLA–SharedFormula 206 5.95SHEET 206 5.96SHEETLAYOUT 207 5.97SHEETPR 207 5.98SHEETPROTECTION 208 5.99SORT 209 5.100SST–SharedStringTable 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–CRNCount 218 5.115XF–ExtendedFormat 219 6DrawingObjects
5 1Introduction 1Introduction 1.1LicenseNotices 1.1.1PublicDocumentationLicenseNotice ThecontentsofthisDocumentationaresubjecttothePublicDocumentationLicenseVersion1.0(the"License");youmayonlyusethisDocumentationifplywiththetermsofthisLicense.AcopyoftheLicenseisavailableat.TheOriginalDocumentationis"'sDocumentationoftheMicrosoftExcelFileFormat".TheInitialWriteroftheOriginalDocumentationisSunMicrosystems,Inc.,Copyright©2003.AllRightsReserved.SeetitlepageforAuthorcontactandContributors.AllTrademarksarepropertiesoftheirrespectiveowners. 1.1.2Wikipedia WikipediaDisclaimer:/wiki/Wikipedia:General_disclaimer
6 1.2Abstract 1.2Abstract ThisdocumentcontainsadescriptionofthebinaryfileformatofMicrosoftExcel,includingallavailableExcelversions(forWindows)fromExcel2.xtothecurrentExcel2003.ThisprojecthasbeenstartedinJune2001andisstillinprogress.Atseveralplacestheremark“2do”indicatesanpletesectionofthedocumentation. 1.2.1ProjectStatus Chapter1Introduction2DocumentStructure3Formulas 4Worksheet/WorkbookStream 5Worksheet/WorkbookRecords 6DrawingObjects7Charts8PivotTables9ChangeTracking10WorkspaceDocuments ContentsCommoninformationDocumentstructureoverview-StructureofRPNtokenarrays-DetaileddescriptionofalltokensAbstractdescriptionplexfeaturesrepresentedbyseveralrecordsDetaileddescriptionofallrecordsoftheworksheet/workbookstream Internalsofchartsheetsandchartobjects StatusDoneDoneDoneInprogressInprogress Inprogress NotstartedInprogressNotstartedNotstartedInprogress
7 1Introduction 1.2.2UsedTerms,Symbols,andFormatting •References Areferencetoanotherchapterissymbolisedbyalittlearrow:➜1.1. •Definitions Definitionsofimportanttermsareshowninaboxwithlight-greybackground.Definition:Thisisanexampleofadefinitionbox. •Examples Anexampleisindentedandmarkedwithalight-greyborder.Thisisanexample. •ImportantPassages !
Textpassageswithimportantinformationcontainaleadingexclamationmark.Thisisanimportantpassage. •NumbersandStrings Numericalvaluesareshowninseveralnumbersystems: Numbersystem Marking Example Decimal None 1234 Hexadecimal Trailing“H” 1234H Binary Trailing“2” 10012 Constantstringsareenclosedinquotationmarks.Theymaycontainspecificvalues(controlcharacters,unprintablecharacters).Thesevaluesareenclosedinanglebrackets. Exampleofastringcontainingacontrolcharacter:“abcdef<01H>ghij”. •RecordListings Arecordlistingshowsabundleofrecordsintherequiredorder.Adarkgreyrectanglestandsforasinglerecord,alightgreyrectanglestandsforagroupofrecords.EitherthisisagrouprepresentingaspecificfeatureandisreferredwiththeRecordGroupName,oritisagroupofvariousunspecifiedrecordsthatdonotmatterinthiscontext. RECORDNAME Comments RecordGroupName Comments
8 1.2Abstract •RecordContentListings •Dataoffsetsenclosedinsquarebracketsindicaterecordcontentthatmaybeomitted(theremark“optional”maypointoutthis). •Theterm“Notused”means:Ignorethedataonimportandwritezerobytesonexport.Thesameappliesforunmentionedbitsinbitfields. •Theterm“Unknown”describesdatafieldswithfixedbutunknowncontents.Onexportthesefieldshavetobewrittenasshown. •Atseveralplacesavariableisintroduced,whichrepresentsthevalueofthisfieldforlateruse.Inmoncasesthisisafieldcontainingasizevalue,whichisusedlaterinthe“Size”columnoftherecordcontentlisting.Anexamplecanbefoundin➜2.4. •AlgorithmListings Algorithmsgiveninpseudo-codeareshowninaboxwithlight-greybackground. ALGORITHMExample_Algorithm1)Command12)Command2 Notationconventionsusedinalgorithms: mand1;mand2var←valueJUMPx)RETURN[value]IFcondmandANDORXOR=<><><=>=array[]array[0] Descriptionmandsinoneline,firstmand1,mand2ThevaluevalueisassignedtothevariablevarContinuewithlinex)inthealgorithmReturnsvaluevalueifspecified,otherwisereturnswithoutareturnvaluemandonly,ifconditioncondevaluatestotrueBinaryANDoperationBinaryORoperationBinaryXOR(exclusiveor)operationComparisonoperatorsforconditionalexecutionAnarrayconsistingofequaltypedelementsThefirstelementofthearrayarray[](arraysareusedzero-based) 1.3ByteOrder AlldataitemscontainingmorethanonebytearestoredusingtheLittle-Endianmethod1.Thatmeanstheleastsignificantbyteisstoredfirstandthemostsignificantbytelast.Thisappliesforalldatatypeslike16-bitintegers,32-bitintegers,floating-pointvaluesandUnicodecharacters. Example:The32-bitintegervalue13579BDFHisconvertedintothebytesequenceDFH9BH57H13H. 1Formoreinformationsee/wiki/Endianness.9 2DocumentStructure 2DocumentStructure 2.1DocumentTypes 2.1.1MicrosoftExcelReleases ThefollowingtableshowsthedifferentExcelversionsreleasedforMicrosoftWindowsandAppleMacintosh2: Excelversion MSWindows Releaseyear AppleMacintoshReleaseyear Excel2.x Excel2.0 1987 Excel2.2 1989 Excel3.0 Excel3.0 1990 Excel3.0 1990 Excel4.0 Excel4.0 1992 Excel4.0 1992 Excel5.0 Excel5.0 1993 Excel5.0 1993 Excel7.0 Excel95 1995 ─ Excel8.0 Excel97 1997 Excel98 1998 Excel9.0 Excel2000 1999 Excel2001 2000 Excel10.0 ExcelXP 2001 Excelv.X 2001 Excel11.0 Excel2003 2003 Excel2004 2004 2.1.2WorksheetDocument Definition:WorksheetDocumentAworksheetdocumentconsistsofasinglesheetonly.Variouskindsofsheetsarepossible,forinstancearegularsheet(containingvaluesandformulas),achartsheet(➜7.1.1),oramacrosheet.Thedefaultfileextensionofworksheetdocumentsis“XLS”.Generalstructureofaworksheetdocument: WorksheetdocumentSheet 2Source:/wiki/Microsoft_Excel.10 2.1DocumentTypes 2.1.3WorkbookDocument Definition:WorkbookdocumentAworkbookdocumentcontainsseveralsheets.Itispossiblebinesheetsofalltypesintotheworkbook,forinstanceregularsheets,chartsheets(➜7.1.2),macrosheets,orVisualBasicmodules.Eachworkbookdocumentcontainsglobalsettingsfortheworkbook,calledtheworkbookglobals.Thedefaultfileextensionofworkbookdocumentsis“XLS”. !
Notethedifference:Asheetispartofaworksheetdocumentaswellasaworkbookdocument.Aworkbookdocumentcontainingonlyonesheetispossible,butitisstillcalledaworkbookdocument.Generalstructureofaworkbookdocument: WorkbookdocumentSheet3Sheet2Sheet1Workbookglobals 2.1.4WorkspaceDocument Definition:WorkspacedocumentAworkspacedocumentcontainslinkstoseveralworksheetand/orworkbookdocuments.Itstoresthefilename,windowsize,andwindowpositionofeachdocumentthatispartoftheworkspace.Thedefaultfileextensionofworkspacedocumentsis“XLW”.Notethedifference:Aworkbookdocumentcontainsseveralsheets,butaworkspacedocumentcontainslinksto !
worksheetorworkbookdocumentsthatarestoredinseveralfiles.Norulewithoutexception:AworkspacedocumentwrittenbyExcel4.0isinfactbinationofaworkbook !
documentandaworkspacedocument:Itmaycontainlinkstoworksheetdocuments,andembeddedsheetsthatareloadedfromanexistingworksheetfileorcreatedfromscratch.Theworkspacedocumentcontainspletedataofallembeddedsheets.Generalstructureofaworkspacedocument: WorkspacedocumentLinktoworksheet/workbookdocument1 Linktoworksheet/workbookdocument2 Linktoworksheet/workbookdocument3 Worksheetorworkbookdocument1 Worksheetorworkbookdocument2 Worksheetorworkbookdocument3 Thedetailedstructureofworkspacedocumentsisdescribedinchapter➜10. 11 2DocumentStructure 2.1.5AvailabilityoftheDocumentTypes ThefollowingillustrationshowswhichdocumenttypesareavailableinthedifferentExcelversions:Excel2.xworksheet Excel3.0worksheet Excel3.0workspace Excel4.0worksheet Excelbinedworkbook/workspace Excel5.0/7.0workbook Excel5.0/7.0workspace Excel8.0+workbook Excel8.0+workspace 12 2.2TheBinaryInterchangeFileFormat 2.2TheBinaryInterchangeFileFormat TheExcelfileformatisnamedBIFF(BinaryInterchangeFileFormat).Itisusedtostorealltypesofdocuments:worksheetdocuments(➜2.1.2),workbookdocuments(➜2.1.3),andworkspacedocuments(➜2.1.4).Therearedifferentversionsofthisfileformat,dependingontheversionofExcelthathaswrittenthefile(➜2.1.1),anddependingonthedocumenttype. 2.2.1BIFFVersionsforWorksheet/WorkbookDocuments ThefollowingtableshowswhichExcelversionwriteswhichfileformatforworksheetandworkbookdocuments: Excelversion BIFFversion Documenttype Excel2.x BIFF2 Worksheet Excel3.0 BIFF3 Worksheet Excel4.0 BIFF4 Worksheet Excel5.0 BIFF5 Workbook Excel7.0 BIFF5 Workbook Excel8.0 BIFF8 Workbook Excel9.0 BIFF8 Workbook Excel10.0 BIFF8 Workbook Excel11.0 BIFF8 Workbook BIFF8containsmajorchangestowardsolderBIFFversions,forinstancethehandlingofUnicodestrings. 2.2.2BIFFVersionsforWorkspaceDocuments ThefollowingtableshowswhichExcelversionwriteswhichfileformatforworkspacedocuments: Excelversion BIFFversion Documenttype Excel2.x ─ ─ Excel3.0 BIFF3W Workspace Excel4.0 BIFF4W Combinedworkbook/workspace Excel5.0 BIFF5W Workspace Excel7.0 BIFF5W Workspace Excel8.0 BIFF8W Workspace Excel9.0 BIFF8W Workspace Excel10.0 BIFF8W Workspace Excel11.0 BIFF8W Workspace 13 2DocumentStructure 2.3FileStructure 2.3.1StreamFile AlldocumenttypesandBIFFversionscanbestoredinasimplestreamfile,mostofthemarealwaysstoredthisway.TheonlyexceptionareBIFF5-BIFF8workbookdocuments,whichareusuallystoredpounddocumentfiles(seebelow).Ifthesedocumentsarestoredasstreamfiles,theentirefileconsistsofthe“Book”stream(BIFF5)or“Workbook”stream(BIFF8)only. 2.3.2CompoundDocumentFile(BIFF5-BIFF8) Aworkbookdocumentwithseveralsheets(BIFF5-BIFF8)isusuallystoredusingpounddocumentfileformat(alsoknownas“OLE2storagefileformat”or“Microsoftpatiblestoragefileformat”).Itcontainsseveralstreamsfordifferenttypesofdata.pletedocumentationoftheformatpounddocumentfilescanbefoundatpdocfileformat.pdf. Thefollowingtablelistsnamesofpossiblestreams. Streamname Contents Book BIFF5WorkbookStream(➜2.3.3) Workbook BIFF8WorkbookStream(➜2.3.3) <05H>SummaryInformation<05H>DocumentSummaryInformationCtls DocumentsettingsDocumentsettingsFormattingofformcontrols UserNames Usernamesinsharedworkbooks(➜9) RevisionLog Changetrackinglogstream(➜9) Itispossibletocreatesubstorageslikesubdirectoriesinafilesystem,forinstanceforthePivotTablestreams.Thesestoragescontainsubstreamsitself. Storagename Contents LNKxxxxxxxx StorageforalinkedOLEobject(➜6) MBDxxxxxxxx StorageforanembeddedOLEobject(➜6) _SX_DB_CUR Pivotcachestorage.ThestreamscontaincachedvaluesforPivotTables(➜8). _VBA_PROJECT_CUR VisualBASICprojectstorage !
InBIFF8,theEscherstreamdescribingdrawingobjects(➜6)isnotstoredasseparatestreaminpounddocumentfile,butsplitandembeddedinseveralMSODRAWINGrecordsthatarepartoftheWorkbookStream. 14 2.3FileStructure 2.3.3Worksheet/Workbook/WorkspaceStream Dependingonthedocumenttype,differentnamesareusedforthestream(s)theycontain.Definition:WorksheetStreamBIFF2-BIFF4worksheetdocuments(➜2.1.2)arestoredasstreamfiles(➜2.3.1).TheentirestreamiscalledtheWorksheetStream. TheWorksheetStreamisdescribedindetailin➜4.1.1.Definition:WorkbookStreamBIFF5-BIFF8workbookdocuments(➜2.1.3)thatarestoredinpounddocumentfile(➜2.3.2)containastreamintherootstoragecalledtheWorkbookStream.Thenameofthisstreaminpounddocumentfileis“Book”forBIFF5workbooks,and“Workbook”forBIFF8workbooks.IfaBIFF5-BIFF8workbookdocumentisstoredasstreamfile(➜2.3.1),theentirestreamiscalledtheWorkbookStream. TheWorksheetStreamisdescribedindetailin➜4.1.2.Definition:WorkspaceStreamBIFF3W-BIFF8Wworkspacedocuments(➜2.1.4)arestoredasstreamfiles(➜2.3.1).TheentirestreamiscalledtheWorkspaceStream. TheWorkspaceStreamisdescribedindetailinchapter➜10. 2.3.4Substreams TheBIFF5-BIFF8WorkbookStream(➜2.3.3)isdividedintoseveralpartsthatdescribetheworkbookglobalsandthecontainedsheets(➜2.1.3).Eachofthispartsiscalledasubstream,definedbyastartingandendpositioninthestream. Definition:WorkbookGlobalsSubstreamThesubstreamthatcontainstheglobalinformationofaworkbookiscalledtheWorkbookGlobalsSubstream.ItispartoftheWorkbookStreamofBIFF5-BIFF8workbooks. Definition:SheetSubstreamAsubstreamthatdescribesasheetinaworkbookiscalledSheetSubstream.ItispartoftheBIFF5-BIFF8WorkbookStreamaswellastheBIFF4WWorkspaceStream.TheSheetSubstreaminaWorkbookStreamcanberegardedasaWorksheetStream,becausebothdescribeonesheetandareverysimilarintheirstructure.AspecialtypeofaSheetSubstreamistheChartSubstream(➜7.1.2). •SubstreamsintheBIFF5-BIFF8WorkbookStream InBIFF5-BIFF8WorkbookStreams,theWorkbookGlobalsSubstreamisttheleadingpartofthestream.ItisfollowedbyallSheetSubstreamsinorderofthesheetsthatareinthedocument.CommonstructureofaBIFF5-BIFF8WorkbookStream: WorkbookGlobalsSubstream(required) FirstSheetSubstream(required)SecondSheetSubstream(optional) ThirdSheetSubstream(optional)⋮ 15 2DocumentStructure •SubstreamsintheBIFF4WorkspaceStream InBIFF4WorkspaceStreams,theSheetSubstreamsareembeddedintheWorkspaceStream.TheyaresimilarintheirstructuretoBIFF4WorksheetStreams.CommonstructureofaBIFF4WWorkspaceStream: WorkspaceStream(required)FirstembeddedBIFF4SheetSubstream(optional)SecondembeddedBIFF4SheetSubstream(optional)ThirdembeddedBIFF4SheetSubstream(optional)⋮ 2.4BIFFRecordStructure Definition:BIFFRecord MostoftheExcelstreamsorsubstreams(includingallstreamsdescribedin➜2.3.3andsubstreamsdescribedin➜2.3.4)aredividedintorecords.Eachrecordcontainsspecificdataforthevariouscontentsorfeaturesinadocument.Itconsistsofaheaderspecifyingtherecordtypeandsize,followedbytherecorddata. CommonstructureofaBIFFrecord: Offset SizeContents
0 2 Identifier
2 2 Sizeofthefollowingdata(sz)
4 sz Recorddata }Recordheader ThemaximumsizeoftherecorddataislimitedanddependsontheBIFFversion.Ifthesizeoftherecorddataexceedsthecurrentlimit,oneormoreCONTINUErecords(➜5.21)willbeadded.InsideaCONTINUErecordthedataofthepreviousrecordcontinuesasusual. Inthisdocumentationonlytherecorddatawithouttheheadersisshown.Alloffsetsarerelativetothebeginningoftherecorddataandnottotheentirerecord.ThecontentsofmostoftherecordsdifferfromBIFFversiontoversion.Thiswillbedescribedinseparatetables.AfewolderrecordsarereplacedinnewerBIFFversions.ExceldoesnotwritetheseoldrecordsinnewBIFFversionsanymore. 16 2.5CommonRecordSubstructures 2.5CommonRecordSubstructures Thischaptercontainsinformationaboutbasicsubstructureswhichdonotbelongtospecificrecords,forinstancestrings,errorcodes,constantvalues,URLs,orlineandareaformatting. 2.5.1FormattingRuns Formattingrunsdescribethecharacterformattingofstrings.Aformattingruncontainstheindexofacharacterandtheindexofafontinthefontbuffer.Thefontisusedtoformattheindexedcharacterandthefollowingcharacters,untilthestringendsoranotherformattingrunfollows. Formattingrun,BIFF2-BIFF5: Offset SizeContents
0 1 Firstformattedcharacter(zero-based)
1 1 IndextoFONTrecord(➜5.45) Formattingrun,BIFF8: Offset Size
0 2
2 2 ContentsFirstformattedcharacter(zero-based)IndextoFONTrecord(➜5.45) 2.5.2ByteStrings(BIFF2-BIFF5) AllExcelfileformatsuptoBIFF5containsimplebytestrings.Thebytestringconsistsofthelengthofthestringfollowedbythecharacterarray.Thelengthisstoredeitheras8-bitvalueoras16-bitvalue,dependingonthecurrentrecord.Thestringisnotzero-terminated.Theencodingofthecharacterarrayisdependentonthecurrentrecord(forexampletakenfromtheCODEPAGErecord,➜5.17,orfromtheFONTrecord,➜5.45). Offset SizeContents 01or2 1or2ln Lengthofthestring(charactercount,ln)Characterarray(8-bitcharacters) 2.5.3UnicodeStrings(BIFF8) FromBIFF8on,stringsarealwaysstoredusingUTF-16LE3textencoding.Thecharacterarrayisasequenceof16-bitvalues4.Additionallyitispossibletousepressedformat,whichomitsthehighbytesofallcharacters,iftheyareallzero.Thefollowingtabledescribesthestandardformatoftheentirestring,butinmanyrecordsthestringsdifferfromthisformat.Thiswillbementionedseparately.Itispossible(butnotrequired)tostoreRich-TextformattinginformationandAsianicinformationinsideaUnicodestring.Thisresultsinfourdifferentwaystostoreastring.Thecharacterarrayisnotzero-terminated. 3Formoreinformationsee/wiki/UTF-16.4InmostcaseseachvaluecorrespondstoaUnicodecharacter.OnlytheUnicodecharactersaboveU+FFFFareencodedwitha “surrogatepair”,thataretwo16-bitcodevaluesinUTF-16(seefootnote3). 17 2DocumentStructure •ContentsofaUnicodeString Thestringconsistsofthecharactercount(asusualan8-bitvalueora16-bitvalue),optionflags,thecharacterarrayandoptionalformattinginformation.Ingeneral,theoptionflagsfieldursalsoforemptystrings.Butinafewrecords,thisfieldisomitted,ifthestringisempty.Thisismentionedattherespectiveplace. Offset SizeContents 01or2 1or21 Lengthofthestring(charactercount,ln)Optionflags: [2or3][var.]var.[var.][var.] 24lnor2∙ln4∙rtsz BitMaskContents
0 01Hpressionompr): 02=Compressed(8-bitcharacters) 12=pressed(16-bitcharacters)
2 04HAsianicsettings(ic): 02=DoesnotcontainAsianicsettings 12=ContainsAsianicsettings
3 08HRich-Textsettings(richtext): 02=DoesnotcontainRich-Textsettings 12=ContainsRich-Textsettings (optional,onlyifrichtext=1)NumberofRich-Textformattingruns(rt) (optional,onlyific=1)SizeofAsianicsettingsblock(inbytes,sz) Characterarray(8-bitcharactersor16-bitcharacters,dependentompr) (optional,onlyifrichtext=1)Listofrtformattingruns(➜2.5.1) (optional,onlyific=1)AsianicSettingsBlock(seebelow) 18 2.5CommonRecordSubstructures •AsianicSettingsBlock Asianictext5(Ruby)canbeusedtoprovideextendedicinformationforspecificcharactersorwords.Itappearsabovetheregulartext(ortotherightofverticaltext),andcanrefertosinglecharacters,groupsofcharacters,orentirewords. Offset SizeContents
0 2 Unknownidentifier0001H
2 2 Sizeofthefollowingdata(10+2∙ln+6∙np)
4 2 IndextoFONTrecord(➜5.45)usedfortheAsianictext
6 2 AdditionalsettingsfortheAsianictext: BitMaskContents 1-00003HTypeofJapaneseictext(type): 002=Katakana(narrow) 102=Hiragana 012=Katakana(wide) 3-2000CHAlignmentofallportionsoftheAsianictext(align): 002=Notspecified(Japaneseonly)102=Centered 012=Left(Topforverticaltext) 112=Distributed 5-40030H112(alwaysset)
8 2 NumberofportionstheAsianictextisbrokeninto(np). Ifnp=0,theAsianictextreferstotheentirecelltext. 10
2 TotallengthofthefollowingAsianictext(numberofcharacters,ln) 12
2 Repeatedtotallengthofthetext 1414+2∙ln 2∙lnor26∙np CharacterarrayofAsianictext,noUnicodestringheader,always16-bitcharacters.Note:Ifln=0,thisfieldisnotemptybutcontains0000H. Listofnpstructuresthatdescribethepositionofeachportioninthemaintext.Eachstructurecontainsthefollowingfields: OffsetSizeContents
0 2FirstcharacterintheAsianictextofthisportion(cpa)
2 2Firstcharacterofthemaintextbelongingtothisportion(cpm)
4 2Numberofcharactersinmaintextbelongingtothisportionm) Example:JapanesewordTokyo(東京)withaddedhiragana(とうきょう)
6.ThefollowingexamplesshowthecontentsoftheimportantfieldsoftheAsianicSettingsBlock. Example1:Hiraganacenteredovertheentireword: 東京とうきょう type=102(hiragana)align=102(centered)np=0(noportions,hiraganareferstoentiretext)ln=5(lengthofentirehiraganatext)Noportionstructures 5Formoreinformationsee/wiki/Ruby_characters.6Exampletakenfrom/wiki/Ruby_characters. 19 2DocumentStructure Example2:Hiraganaleft-alignedpercharacter: とう きょう 東京 type=102(hiragana)align=012(left-aligned)np=2(hiraganasplitinto2portions)ln=5(lengthofentirehiraganatext)Portion#1:cpa=0(startwith1sthiraganacharacter);cpm=0;m=1(attachto1stcharacterinmaintext)Portion#2:cpa=2(startwith3rdhiraganacharacter);cpm=1;m=1(attachto2ndcharacterinmaintext) 2.5.4RGBColours ColourvaluesarerepresentedinRGBmode(red/green/blue). Offset SizeContents
0 1 ponent
1 1 ponent
2 1 ponent
3 1 Notused Inthisdocumentation,constantcolourvaluesarewrittenas6-digithexadecimalvaluesinRGBnotation:RRGGBBH. Example:ThecolourvalueFF8000Hdescribesthecolourorange:redisFFH,greenis80H,andblueis00H. 2.5.5RKValues AnRKvalueisanencodedintegerorfloating-pointvalue.RKvalueshaveasizeof4bytesandareusedtodecreasefilesizeforfloating-pointvalues. StructureofanRKvalue(32-bitvalue),BIFF3-BIFF8: Bit Mask Contents
0 00000001H0=Valuenotchanged
1 00000002H0=Floating-pointvalue 31-
2 FFFFFFFCHEncodedvalue 1=Encodedvalueismultipliedby1001=Signedintegervalue Ifbit1iscleared,theencodedvaluerepresentsthe30mostsignificantbitsofanIEEE754floating-pointvalue(64-bitdoubleprecision).The34leastsignificantbitsmustbesettozero.Ifbit1isset,theencodedvaluerepresentsasigned30-bitintegervalue.Togetthecorrectinteger,theencodedvaluehastobeshiftedrightarithmeticallyby2bits.Ifbit0isset,thedecodedvalue(bothintegerandfloating-point)mustbedividedby100togetthefinalresult. Examples:RKvalue3FF00000H3FF00001H004B5646H004B5647H Typefloatfloatintegerinteger Div100noyesnoyes Encodedvalue3FF00000H3FF00000H004B5644H004B5644H Decodedvalue3FF0000000000000H=1.03FF0000000000000H=1.00012D591H=12343210012D591H=1234321 Result1.00.01123432112343.21 20 2.5CommonRecordSubstructures 2.5.6ErrorCodes Ifthecalculationofaformularesultsinanerrororanyotheractionfails,Excelsetsaspecificerrorcode.Theseerrorcodesareusedforinstanceincellrecordsandformulas. Errorcode00H Errorvalue#NULL!
DescriptionIntersectionoftwocellrangesisempty 07H #DIV/0!
Divisionbyzero 0FH #VALUE!
Wrongtypeofoperand 17H #REF!
Illegalordeletedcellreference 1DH #NAME?
Wrongfunctionorrangename 24H #NUM!
Valuerangeoverflow 2AH #N/A Argumentorfunctionnotavailable 2.5.7ConstantValues Sometimesitisneededtostoreconstantvaluesofdifferentdatatypes.Thesevaluesareusedtocreatelinearlists(forinstanceintheCRNrecord,➜5.23),ortwo-dimensionalarrays(➜2.5.8).Thischapterdescribestheformatoftheindividualconstantvalues. •EmptyValue Offset Size
0 1
1 8 Contents00H(Identifierforanemptyconstant)Notused •Number Offset01 SizeContents
1 01H(Identifierforanumericalconstant)
8 IEEE754floating-pointvalue(64-bitdoubleprecision) •StringValue Offset Size
0 1
1 var. Contents 02H(Identifierforastringconstant) BIFF2-BIFF5:Bytestring,8-bitstringlength(➜2.5.2) BIFF8: Unicodestring,16-bitstringlength,optionflagsuralways(➜2.5.3) •BooleanValue Offset Size
0 1
1 1
2 7 Contents04H(IdentifierforaBooleanconstant)0=FALSE,1=TRUENotused 21 2DocumentStructure •ErrorValue Offset Size
0 1
1 1
2 7 Contents10H(Identifierforanerrorconstant)Errorcode(➜2.5.6)Notused 2.5.8ConstantValueArray Two-dimensionalarraysofconstantvaluesareusedtostorecachedDDElinkresults(recordEXTERNALNAME,➜5.39),orforconstantarraysinformulas(tokentArray,➜3.8.7).Thearraystartswiththedimensions(widthandheight),followedbyalistofconstantvalues. Two-dimensionalconstantvaluearray,BIFF2-BIFF5: Offset SizeContents
0 1 Numberofcolumns(nc).Thevalue0represents256columns.
1 2 Numberofrows(nr)
3 var. Listofnc∙nrconstantvalues(➜2.5.7) Two-dimensionalconstantvaluearray,BIFF8: Offset SizeContents
0 1 Numberofcolumnsdecreasedby1(nc)
1 2 Numberofrowsdecreasedby1(nr)
3 var. Listof(nc+1)∙(nr+1)constantvalues(➜2.5.7) 2.5.9EncodedFileURLs TheintentionofencodingfileURLsistomakethemmoreplatformindependent.EncodedURLsurintherecordsEXTERNSHEET(BIFF2-BIFF5,➜5.41)orEXTERNALBOOK(BIFF8,➜5.38),andDCONREF(➜5.30). ThefirstcharacteroftheURLisusedtodeterminethetypeofencoding.InUnicodestrings(BIFF8)thiscouldbea16-bitvalue. Firstcharacter00H 01H02H03H04H BIFF2-BIFF4Notused EncodedURLfollowsReferencetothecurrentsheet(nothingwillfollow)Notused Notused BIFF5 Referencerelativetothecurrentsheet(nothingwillfollow),usede.g.indefinednames EncodedURLfollows Referencetothecurrentsheet(nothingwillfollow) Referencetoasheetintheowndocument(sheetnamefollows) Referencetotheownworkbook,sheetisunspecified(nothingwillfollow) BIFF8Referencerelativetothecurrentsheet(nothingwillfollow),usede.g.indefinednamesEncodedURLfollowsReferencetoasheetintheowndocument(sheetnamefollows)Notused Notused others Notencoded.ThisisalreadythefirstcharacteroftheURL. 22 2.5CommonRecordSubstructures InsideoftheencodedURLtherecanurseveralcontrolcharacters. Controlcharacter01H02H03H04H05H06H08H09H MeaningAnMS-DOSdriveletterwillfollow,or“@”andtheservernameofaUNCpathStartpathnameonsamedriveasowndocumentEndofsubdirectorynameParentdirectoryUnencodedURL.FollowedbythelengthoftheURL(1byte),andtheURLitself.StartpathnameininstallationdirectoryofExcelMacrotemplatedirectoryininstallationdirectoryofExcelSheetinthesameworkbook(BIFF4W) IfasheetnamefollowsthefilenameintheencodedURL(BIFF4W-BIFF8),thefilename(butnotthefilepath)willbeenclosedinbrackets.NotethatinEXTERNALBOOKrecords(BIFF8)sheetnamesdonoturandthereforethefilenamesarenotenclosedinbrackets. ExamplesforBIFF2-BIFF4(owndocumentissavedas“C:\path\own.xls”): Formula Encodedfilename =own.xls!
A1 <02H> =ext.xls!
A1 <01H>ext.xls ='sub\ext.xls'!
A1 <01H>sub<03H>ext.xls ='\ext.xls'!
A1 <01H><02H>ext.xls ='\sub\ext.xls'!
A1 <01H><02H>sub<03H>ext.xls ='\sub\sub2\ext.xls'!
A1 <01H><02H>sub<03H>sub2<03H>ext.xls ='..\sub\ext.xls'!
A1 <01H><04H>sub<03H>ext.xls ='D:\sub\ext.xls'!
A1 <01H><01H>Dsub<03H>ext.xls ='\\pc\sub\ext.xls'!
A1 <01H><01H>@pc<03H>sub<03H>ext.xls ='/ext.xls'!
A1 <01H><05H><1EH>/ext.xls
(thelengthoftheURL(30=1EH)followsthe05Hbyte) ExamplesforBIFF4Winternalreferences(allformulasarelocatedon“Sheet1”): Formula Encodedfilename =Sheet1!
A1 <02H> =Sheet2!
A1 <01H><09H>Sheet2 ExamplesforBIFF5internalreferences(allformulasarelocatedon“Sheet1”): Formula Encodedfilename =Sheet1!
A1 <02H> =Sheet2!
A1 <03H>Sheet2 =NonExistentSheet!
A1 <04H> Examples
forBIFF8internalreferences(forexampleinrecordDCONREF): Formula Encodedfilename =Sheet2!
A1 <02H>Sheet2 23 2DocumentStructure ExamplesforBIFF4W-BIFF8externalreferences(owndocumentissavedas“C:\path\own.xls”): Formula Encodedfilename =[ext.xls]Sheet1!
A1 <01H>[ext.xls]Sheet1 ='sub\[ext.xls]Sheet1'!
A1 <01H>sub<03H>[ext.xls]Sheet1 ='\[ext.xls]Sheet1'!
A1 <01H><02H>[ext.xls]Sheet1 ='\sub\[ext.xls]Sheet1'!
A1 <01H><02H>sub<03H>[ext.xls]Sheet1 ='\sub\sub2\[ext.xls]Sheet1'!
A1
<01H><02H>sub<03H>sub2<03H>[ext.xls]Sheet1 ='..\sub\[ext.xls]Sheet1'!
A1 <01H><04H>sub<03H>[ext.xls]Sheet1 ='D:\sub\[ext.xls]Sheet1'!
A1 <01H><01H>Dsub<03H>[ext.xls]Sheet1 ='\\pc\sub\[ext.xls]Sheet1'!
A1
<01H><01H>@pc<03H>sub<03H>[ext.xls]Sheet1 ='/[ext.xls]Sheet1'!
A1 <01H><05H><26H>/[ext.xls]Sheet1(thelengthoftheURL(38=26H)followsthe05Hbyte) 2.5.10EncodedDDEandOLELinks ADDElinkcontainsthenameoftheserverapplicationandtheic.AnOLElinkcontainstheclassnameand theURLofthedocument.Inbothcasesthenamesarestoredinonestring,separatedbythecontrolcharacter03H.TheURLsarenotencoded. Example:AdocumentcontainsaDDElinkandanOLElinktoSheet1inthefile“D:\sub\example.xls”. LinkServer/ClassTopic/URL Encodeddocumentname DDEexcel D:\sub\[example.xls]Sheet1 excel<03H>D:\sub\[example.xls]Sheet1 OLEExcel.Sheet.8D:\sub\example.xls Excel.Sheet.8<03H>D:\sub\example.xls 2.5.11LineStylesforCellBorders(BIFF3-BIFF8) Theselinestylesareusedtodefinecellborders.Thestyles08Hto0DHareavailableinBIFF8only. IndexStyle Sample IndexStyle 00HNoline ThefollowingforBIFF8only: 01HThin 08HMediumdashed 02HMedium 09HThindash-dotted 03HDashed 0AHMediumdash-dotted 04HDotted 0BHThindash-dot-dotted 05HThick 0CHMediumdash-dot-dotted 06HDouble 0DHSlantedmediumdash-dotted 07HHair Sample 24 2.5CommonRecordSubstructures 2.5.12PatternsforCellandChartBackgroundArea Thebackgroundareaofcells(BIFF3-BIFF8)andchartobjects(BIFF2-BIFF8)maycontainapattern.Patterncolourandpatternbackgroundcolouraredefinedseparately.Inthefollowingtableblackisusedaspatterncolourandwhiteaspatternbackgroundcolour. Index00H PatternSampleNopattern IndexPatternSample 01H 0AH 02H 0BH 03H 0CH 04H 0DH 05H 0EH 06H 0FH 07H 10H 08H 11H 09H 12H The
followingtableshowshowapatternisusedwiththecorrectcolourindexes. Cellformat Nobackground(showssystemwindowbackground) PatternPatterncolourindex00HSystemwindowtext(notused) Redbackground(solid) 01HRed Redbackgroundwiththinhorizontalbluelines 0BHBlue Redbackgroundwiththinhorizontallines,automaticcolour 0BHSystemwindowtext Nobackground,thinhorizontalbluelines 0BHBlue Nobackground,thinhorizontallines,automaticcolour 0BHSystemwindowtext BackgroundcolourindexSystemwindowbackground Systemwindowtext(notused)Red Red Systemwindowbackground Systemwindowbackground ThedescriptionofthePALETTErecord(➜5.74)containsinformationhowthespecialsystemcoloursareused.Notethebehaviourofsolidcolouredbackgrounds,wherepattern01Hisusedinconjunctionwiththepatterncolour. 25 2DocumentStructure 2.5.13CellAttributes(BIFF2) AllcellrecordsinBIFF2containacellattributefieldwithasizeof3bytes.TheycontainanindextoanXFrecord(➜5.115)andsomerepeatedcontentsofthereferencedXFrecord.TheXFindexfieldhasasizeofonly6bits,sotheindexrangeis0…63.IfarealXFindexgreaterthan62isused,theXFindexfieldalwayscontainsthevalue63,andanIXFErecord(➜5.62)ursinfrontofacellrecord.TheIXFErecordcontainsthecorrectindexoftheXFrecord.InaROWrecord(➜5.88)thisfieldisnotused,becausetherewillalwaysurarealXFindexfield. Cellattributesfield(3bytes),BIFF2: Offset SizeContents
0 1 CellprotectionandXFindex: BitMaskContents 5-
0 3FHIndextoXFrecord(➜5.115).Thevalue3FH=63indicatesa precedingIXFErecord(➜5.62).NotusedinROWrecords(➜5.88).
6 40H1=Cellislocked
7 80H1=Formulaishidden
1 1 IndexestoFORMATandFONTrecords: BitMaskContents 5-
0 3FHIndextoFORMATrecord(➜5.49) 7-
6 C0HIndextoFONTrecord(➜5.45)
2 1 Cellstyle: BitMaskContents 2-
0 07HXF_HOR_ALIGN–Horizontalalignment(➜5.115.1)
3 08H1=Cellhasleftblackborder
4 10H1=Cellhasrightblackborder
5 20H1=Cellblackborder
6 40H1=Cellhasbottomblackborder
7 80H1=Cellhasshadedbackground 2.5.14CellRangeAddress Acellrangeaddressspecifiesafixedcellrangeinthecurrentsheet. Cellrangeaddress,BIFF2-BIFF5: Offset SizeContents
0 2 Indextofirstrow
2 2 Indextolastrow
4 1 Indextofirstcolumn
5 1 Indextolastcolumn Cellrangeaddress,BIFF8: Offset SizeContents
0 2 Indextofirstrow
2 2 Indextolastrow
4 2 Indextofirstcolumn
6 2 Indextolastcolumn !
Inseveralcases,BIFF8stillwritestheBIFF2-BIFF5formatofcolumnindexes).Thiswillbementionedattherespectiveplace. a cell range address (using 8-bit values for the 26 2.5CommonRecordSubstructures 2.5.15CellRangeAddressList Acellrangeaddresslistconsistsofafieldwiththenumberofrangesandthelistoftherangeaddresses. Cellrangeaddresslist,BIFF2-BIFF8: Offset SizeContents
0 2 Numberoffollowingcellrangeaddresses(nm)
2 6∙nmor8∙nmListofnmcellrangeaddresses(➜2.5.14) !
Inseveralcases,BIFF8stillwritestheBIFF2-BIFF5formatofcolumnindexes).Thiswillbementionedattherespectiveplace. a cell range address (using 8-bit values for the 27 3Formulas 3Formulas 3.1CommonFormulaStructure 3.1.1CommonStructure Formulasarestoredaspartofarecord,forinstanceinsideofaFORMULArecordoraDEFINEDNAMErecord.monformatofaformulaisasfollows: FormulainBIFF2: Offset SizeContents
0 1 Sizeofthefollowingformuladata(RPNtokenarray,sz)
1 sz Formuladata(RPNtokenarray) [1+sz] var. (optional)Additionaldataforspecifictokens(➜3.1.6,forexampletArraytoken,➜3.8.7) FormulainBIFF3-BIFF8: Offset Size
0 2
2 sz [2+sz] var. ContentsSizeofthefollowingformuladata(sz)Formuladata(RPNtokenarray)(optional)Additionaldataforspecifictokens(➜3.1.6,forexampletArraytoken,➜3.8.7) SometimesthesizefieldisnotstoreddirectlybeforetheRPNtokenarray,butsomewhereelse.Ifthishappens,itwillbementionedattherespectiveplace.Iftheredoesnotexistanyformuladata,onlythesizefield(whichcontains0then)ispresent. 3.1.2Tokens Definition:Formulatoken Formulatokensaretheindivisibleparticlesofaformula.Theremightbeoperators,numericalorstringconstantsorfunctionnames. Eachtokencontainsatokenidentifier.Severaltokenscontainadditionalinformation.Atokendoesnotcontainanysizeinformation,soimportingandexportingtokensmustbedonecarefully. Commonstructureofaformulatoken,BIFF2-BIFF8: Offset SizeContents
0 1 Tokenidentifier [1] var. (optional)Additionaldataforthetoken 28 3.1CommonFormulaStructure 3.1.3TokenNotation Tokensarereferredtobytheirname.Alltokennamesstartwithasmall“t”(for“Token”).Ifatokencontainsadditionalinformation,itisappendedtothetokennameinparentheses. Examples:Theadditionoperator+isrepresentedbythetokentAdd.Itdoesnotcontainanyadditionaldata.Theintegerconstant1isrepresentedbythetokentInt
(1).Anabsolutereferencetocell$A$1isrepresentedbythetokentRef($A$1). 3.1.4Operators Thereare3typesofoperators:•Unaryoperatorsliketheminussignthatnegatesavalue.Theseoperatorspopmostoperandfromthestack.•Binaryoperatorslikeadditionormultiplication.Theseoperatorspopthemostoperandsfromthestack.•FunctionoperatorsrepresentthesheetfunctionsofExcel.Theyoperateondifferentnumbersmostoperandson thestack.Eitherthefunctionexpectsafixednumberofoperands(forinstanceSINexpectsoneoperand),oravariablenumberofoperandsgiveninthefunctiontoken(forinstanceSUMisabletoprocess0to30operands).Alloperatorspushthe(single)resultoftheiroperationbackontothestack. 3.1.5TokenArrays Definition:TokenarrayThetokenarrayrepresentsanentireformulaandcontainsallusedtokensoftheformulainaspecificorder.ThetokensoftheformulaarestoredintheReverse-PolishNotation(RPN).Thismeans,firstthereuralloperandsofanoperation,followedbytherespectiveoperator.Example:thesimpleterm1+2consistsofthe3tokens“1”,“+”and“2”.WritteninRPN,theformulaisconvertedtothetokenlist“1”,“2”,“+”.Duringparsingsuchanexpression,operandsarepushedontoastack.Anoperatorpopstheneedednumberofoperandsfromstack,performstheoperationandpushestheresultbackontothestack. 29 3Formulas OtherexamplesforRPNtokenarrays: Formula Tokenarray 2*4+
5 2,
4,*,
5,+ Excelnotation tInt
(2),tInt
(4),tMul,tInt
(5),tAdd 2+4*
5 2,4,
5,*,+ tInt
(2),tInt
(4),tInt
(5),tMul,tAdd ABS(2*–A1)
2,A1,-,*,ABS tInt
(2),tRefV(A1),tUminus,tMul,tFunc(ABS) Parsingresult First,theintegerconstants2and4arepushedontothestack.The*operatorpopsthemfromthestackandpushes8.Thentheconstant5ispushed.The+operatorpops5and8andpushes13(thefinalresult). First,theintegerconstants2,4,and5arepushedontothestack.The*operatorpops5and4andpushes20,the+operatorpops20and2andpushes22(thefinalresult). First,theintegerconstant2andthevaluefromcellA1(forexample3)arepushedontothestack.Theunary-operator(tUminus)popsmostvalue3fromstackandpushesthenegatedvalue-
3.The*operatorpops-3and2andpushes-
6.TheABSfunctionneeds1parameter.Itpops-6andpushes6(thefinalresut). Exampleofpletebyterepresentationoftheformula2*4+
5. •TheRPNrepresentationoftheformulais:2,4,
5,*,+. •WritteninExceltokennotation,thisis:tInt
(2),tInt
(4),tInt
(5),tMul,tAdd. Offset Size ContentsTokennameDescription
0 2 000BH Sizeofthefollowingformuladata(sz) }
2 1 1EH tInt
3 2 0002H Integervaluetokenfor2 }
5 1 1EH tInt
6 2 0004H Integervaluetokenfor4
8 1 05H tMulMultiplicationoperator }
9 1 1EH tInt 10
2 0005H Integervaluetokenfor5 12
1 03H tAddAdditionoperator 30 3.1CommonFormulaStructure 3.1.6AdditionalTokenData Afewtokenscontainadditionaldatathatdoesnotfollowthetokenidentifier,butisappendedtothetokenarray.Itssize isnotcontainedintheleadingfieldcontainingthetokenarraysize.AffectedtokensarethetArraytoken(➜3.8.7),thetMemAreatoken(➜3.9.4),andafewsubtypesofthethetNlrtoken(➜3.10.4).Theadditionaldataofeachtokenisappendedinthesameorderthetokensarelocatedinthetokenarray. Example:Theformula={1}+A1:A2A2:A3+{2}contains3tokenswithadditionaldata:2tArraytokensrepresentingthecontantarrays,andatMemAreatokencontainingtheresultoftheintersectionoperator(thecell referenceA2).ThedetailedexamplebelowappliesforBIFF8. •TheRPNrepresentationoftheformulais:{1},A1:A2,A2:A3,“”,+,{2},+. •WritteninExceltokennotation,thisis(thetermA1:A2,A2:A3,“”isledbyatMemAreatoken):tArrayV({1}),tMemAreaV(A2),tAreaR(A1:A2),tAreaR(A2:A3),tIsect,tAdd,tArrayV({2}),tAdd. Offset SizeTokennameDescription
0 2 Sizeofthefollowingformuladata(44bytes),withouttheadditionaldatafollowingthelasttAddtoken
2 8 tArrayVPlaceholderforfirstconstantarray{1} 10
7 tMemAreaVConstantreferencesubexpressionfollowsinnext19bytes(untiland includingthetIsecttoken) 17
9 tAreaRCellreferenceA1:A2 26
9 tAreaRCellreferenceA2:A3 35
1 tIsectIntersectionoperator 36
1 tAddAdditionoperator 37
8 tArrayVPlaceholderforsecondconstantarray{2} 45
1 tAddAdditionoperator 46 12 Constantvaluearray(➜2.5.8)containingthevaluesoftheconstantarray{1}:containswidth1,height1,value1 58 10 Cellrangeaddresslist(➜2.5.15)containingtheresultofthe referencesubexpressionA1:A2A2:A3:aone-elementlistwiththecelladdressA2 68 12 Constantvaluearray(➜2.5.8)containingthevaluesoftheconstantarray{2}:containswidth1,height1,value2 31 3Formulas 3.2TokenClasses 3.2.1ClassifiedTokens Allfunctionoperatorsandmostoperandtokensexistin3differentversions:asreferenceclasstoken,valueclasstoken,andarrayclasstoken.Thetokenclassdependsonwhichtypeofdatatheinvolvedoperatorexpects.Sometimesonly1or2tokenclassesarevalidforatoken(forexample,arrayclasstokenscannotexistasreferenceclasstokens,buttheycanexistasvalueclasstokens). •Referenceclasstoken:Thereferenceaddressitself,independentofthecellcontents. •Valueclasstoken:Avalue(aconstant,afunctionresult,oronespecificvaluefromadereferencedcellrange). •Arrayclasstoken:Anarrayofvalues(arrayofconstantvalues,anarrayfunctionresult,orallvaluesofacellrange). Thestructureofthe8-bitidentifierofaclassifiedtokenisdescribedinthefollowingtable. Bit MaskContents 4-
0 1FH Basictokenidentifier 6-
5 60H 012=Referenceclasstoken(tokenrange20H-3FH) 102=Valueclasstoken(tokenrange40H-5FH) 112=Arrayclasstoken(tokenrange60H-7FH)
7 80H 02(zero) Thetokenclassismarkedinthenamesofthetokens.Thenamesofreferenceclasstokenscontainatrailing“R”,valueclasstokenscontainatrailing“V”,andthenamesofarrayclasstokensatrailing“A”. Example:ThetAreatoken(➜3.9.3)isn
.........................................................................................................
6 1.1LicenseNotices
6 1.2Abstract
7 1.3ByteOrder
9 2DocumentStructure
...........................................................................................
10 2.1DocumentTypes 10 2.2TheBinaryInterchangeFileFormat 13 2.3FileStructure 14 2.4BIFFRecordStructure 16 2.5CommonRecordSubstructures 17 3Formulas............................................................................................................
28 3.1CommonFormulaStructure 28 3.2TokenClasses 32 3.3CellAddressesinTokens 36 3.4TokenOverview 40 3.5UnaryOperatorTokens 44 3.6BinaryOperatorTokens 45 3.7FunctionOperatorTokens 49 3.8ConstantOperandTokens 51 3.9OperandTokens 54 3.10ControlTokens 65 3.11Built-InSheetFunctions 74 4Worksheet/WorkbookStream............................................................................79 4.1Worksheet/WorkbookStream 80 4.2RecordOrderinStreamsandSubstreams 81 4.3CalculationSettingsBlock 87 4.4PageSettingsBlock 87 4.5SheetViewSettingsBlock 88 4.6CellFormatting 89 4.7CellTableandRowBlocks 90 4.8ArrayFormulasandSharedFormulas 96 4.9MultipleOperationTables 97 4.10InternalandExternalReferences 98 4.11SharedStringTable(BIFF8) 1102 4.12ConditionalFormattingTable(BIFF8) 112 4.13HyperlinkTable(BIFF8) 112 4.14DataValidityTable(BIFF8) 112 4.15AutoFilter,AdvancedFilter 113 4.16Scenarios 113 4.17WebQueries(BIFF8) 113 4.18Worksheet/WorkbookProtection 114 4.19FileProtection 117 5Worksheet/WorkbookRecords........................................................................120 5.1Overview,OrderedbyRecordIdentifier 120 5.2Overview,OrderedbyRecordNames 124 5.3Overview,OrderedbyBIFFVersions 127 5.4ARRAY 132 5.5BACKUP 133 5.6BITMAP 133 5.7BLANK 134 5.8BOF–BeginningofFile 135 5.9BOOKBOOL 137 5.10BOOLERR 137 5.11BOTTOMMARGIN 138 5.12BUILTINFMTCOUNT 138 5.13CALCCOUNT 139 5.14CALCMODE 139 5.15CFHEADER 139 5.16CFRULE–ConditionalFormattingRule 140 5.17CODEPAGE 145 5.18COLINFO 146 5.19COLUMNDEFAULT 147 5.20COLWIDTH 147 5.21CONTINUE 148 5.22COUNTRY 149 5.23CRN 150 5.24DATATABLE–MultipleOperationTable 151 5.25DATATABLE2–MultipleOperationTable 152 5.26DATAVALIDATION 153 5.27DATAVALIDATIONS 154 5.28DATEMODE 154 5.29DBCELL 155 5.30DCONREF–DataConsolidationReference 155 5.31DEFAULTROWHEIGHT 156 5.32DEFCOLWIDTH 156 5.33DEFINEDNAME 157 5.34DELTA 160 5.35DIMENSION 160 5.36DSF–DoubleStreamFile 161 5.37EOF–EndofFile 161 5.38EXTERNALBOOK–ExternalWorkbook 162 5.39EXTERNALNAME 164 5.40EXTERNCOUNT 166 5.41EXTERNSHEET 166 5.42EXTSST–ExtendedSST 167 5.43FILEPASS 168 5.44FILESHARING 170 5.45FONT 171 5.46FONT2 173
3 5.47FONTCOLOR 173 5.48FOOTER 173 5.49FORMAT 174 5.50FORMULA 176 5.51GCW–GlobalColumnWidth 178 5.52GRIDSET 179 5.53GUTS 179 5.54HCENTER 179 5.55HEADER 180 5.56HIDEOBJ 181 5.57HORIZONTALPAGEBREAKS 181 5.58HYPERLINK 182 5.59INDEX 185 5.60INTEGER 186 5.61ITERATION 186 5.62IXFE–IndextoXF 186 5.63LABEL 187 5.64LABELRANGES 187 5.65LABELSST 188 5.66LEFTMARGIN 188 5.67MERGEDCELLS 188 5.68MULBLANK–MultipleBLANK 189 5.69MULRK–MultipleRK 189 5.70NOTE 190 5.71NUMBER 191 5.72OBJECTPROTECT 191 5.73PAGESETUP 192 5.74PALETTE 195 5.75PANE 197 5.76PASSWORD 197 5.77PHONETICPR 198 5.78PLS 198 5.79PRECISION 198 5.80PRINTGRIDLINES 199 5.81PRINTHEADERS 199 5.82PROTECT 200 5.83QUICKTIP 200 5.84RANGEPROTECTION 200 5.85REFMODE 201 5.86RIGHTMARGIN 201 5.87RK 201 5.88ROW 202 5.89RSTRING 204 5.90SAVERECALC 204 5.91SCENPROTECT 205 5.92SCL 205 5.93SELECTION 205 5.94SHAREDFMLA–SharedFormula 206 5.95SHEET 206 5.96SHEETLAYOUT 207 5.97SHEETPR 207 5.98SHEETPROTECTION 208 5.99SORT 209 5.100SST–SharedStringTable 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–CRNCount 218 5.115XF–ExtendedFormat 219 6DrawingObjects
..............................................................................................
225 7Charts...............................................................................................................
226 7.1DocumentIntegration 226 7.2GeneralChartStructure 229 7.3CommonRecordBlocks 231 7.4RecordOverview,OrderedbyIdentifier 234 7.5RecordOverview,OrderedbyName 235 7.6CHAREAFORMAT 236 7.7CHAXISLINE 237 7.8CHBEGIN–BeginofBlock 238 7.9CHEND–EndofBlock 239 7.10CHFRAME 240 7.11CHLINEFORMAT 241 7.12CHSOURCELINK 242 7.13CHSTRING 243 8PivotTables......................................................................................................
244 9ChangeTracking.............................................................................................
245 10WorkspaceDocuments....................................................................................246 10.1WorkspaceStream 246 10.2Overview,OrderedbyRecordIdentifier 248 10.3INDEX 249 10.4SHEETHDR 250 10.5SHEETSOFFSET 2505 1Introduction 1Introduction 1.1LicenseNotices 1.1.1PublicDocumentationLicenseNotice ThecontentsofthisDocumentationaresubjecttothePublicDocumentationLicenseVersion1.0(the"License");youmayonlyusethisDocumentationifplywiththetermsofthisLicense.AcopyoftheLicenseisavailableat.TheOriginalDocumentationis"'sDocumentationoftheMicrosoftExcelFileFormat".TheInitialWriteroftheOriginalDocumentationisSunMicrosystems,Inc.,Copyright©2003.AllRightsReserved.SeetitlepageforAuthorcontactandContributors.AllTrademarksarepropertiesoftheirrespectiveowners. 1.1.2Wikipedia WikipediaDisclaimer:/wiki/Wikipedia:General_disclaimer
6 1.2Abstract 1.2Abstract ThisdocumentcontainsadescriptionofthebinaryfileformatofMicrosoftExcel,includingallavailableExcelversions(forWindows)fromExcel2.xtothecurrentExcel2003.ThisprojecthasbeenstartedinJune2001andisstillinprogress.Atseveralplacestheremark“2do”indicatesanpletesectionofthedocumentation. 1.2.1ProjectStatus Chapter1Introduction2DocumentStructure3Formulas 4Worksheet/WorkbookStream 5Worksheet/WorkbookRecords 6DrawingObjects7Charts8PivotTables9ChangeTracking10WorkspaceDocuments ContentsCommoninformationDocumentstructureoverview-StructureofRPNtokenarrays-DetaileddescriptionofalltokensAbstractdescriptionplexfeaturesrepresentedbyseveralrecordsDetaileddescriptionofallrecordsoftheworksheet/workbookstream Internalsofchartsheetsandchartobjects StatusDoneDoneDoneInprogressInprogress Inprogress NotstartedInprogressNotstartedNotstartedInprogress
7 1Introduction 1.2.2UsedTerms,Symbols,andFormatting •References Areferencetoanotherchapterissymbolisedbyalittlearrow:➜1.1. •Definitions Definitionsofimportanttermsareshowninaboxwithlight-greybackground.Definition:Thisisanexampleofadefinitionbox. •Examples Anexampleisindentedandmarkedwithalight-greyborder.Thisisanexample. •ImportantPassages !
Textpassageswithimportantinformationcontainaleadingexclamationmark.Thisisanimportantpassage. •NumbersandStrings Numericalvaluesareshowninseveralnumbersystems: Numbersystem Marking Example Decimal None 1234 Hexadecimal Trailing“H” 1234H Binary Trailing“2” 10012 Constantstringsareenclosedinquotationmarks.Theymaycontainspecificvalues(controlcharacters,unprintablecharacters).Thesevaluesareenclosedinanglebrackets. Exampleofastringcontainingacontrolcharacter:“abcdef<01H>ghij”. •RecordListings Arecordlistingshowsabundleofrecordsintherequiredorder.Adarkgreyrectanglestandsforasinglerecord,alightgreyrectanglestandsforagroupofrecords.EitherthisisagrouprepresentingaspecificfeatureandisreferredwiththeRecordGroupName,oritisagroupofvariousunspecifiedrecordsthatdonotmatterinthiscontext. RECORDNAME Comments RecordGroupName Comments
8 1.2Abstract •RecordContentListings •Dataoffsetsenclosedinsquarebracketsindicaterecordcontentthatmaybeomitted(theremark“optional”maypointoutthis). •Theterm“Notused”means:Ignorethedataonimportandwritezerobytesonexport.Thesameappliesforunmentionedbitsinbitfields. •Theterm“Unknown”describesdatafieldswithfixedbutunknowncontents.Onexportthesefieldshavetobewrittenasshown. •Atseveralplacesavariableisintroduced,whichrepresentsthevalueofthisfieldforlateruse.Inmoncasesthisisafieldcontainingasizevalue,whichisusedlaterinthe“Size”columnoftherecordcontentlisting.Anexamplecanbefoundin➜2.4. •AlgorithmListings Algorithmsgiveninpseudo-codeareshowninaboxwithlight-greybackground. ALGORITHMExample_Algorithm1)Command12)Command2 Notationconventionsusedinalgorithms: mand1;mand2var←valueJUMPx)RETURN[value]IFcondmandANDORXOR=<><><=>=array[]array[0] Descriptionmandsinoneline,firstmand1,mand2ThevaluevalueisassignedtothevariablevarContinuewithlinex)inthealgorithmReturnsvaluevalueifspecified,otherwisereturnswithoutareturnvaluemandonly,ifconditioncondevaluatestotrueBinaryANDoperationBinaryORoperationBinaryXOR(exclusiveor)operationComparisonoperatorsforconditionalexecutionAnarrayconsistingofequaltypedelementsThefirstelementofthearrayarray[](arraysareusedzero-based) 1.3ByteOrder AlldataitemscontainingmorethanonebytearestoredusingtheLittle-Endianmethod1.Thatmeanstheleastsignificantbyteisstoredfirstandthemostsignificantbytelast.Thisappliesforalldatatypeslike16-bitintegers,32-bitintegers,floating-pointvaluesandUnicodecharacters. Example:The32-bitintegervalue13579BDFHisconvertedintothebytesequenceDFH9BH57H13H. 1Formoreinformationsee/wiki/Endianness.9 2DocumentStructure 2DocumentStructure 2.1DocumentTypes 2.1.1MicrosoftExcelReleases ThefollowingtableshowsthedifferentExcelversionsreleasedforMicrosoftWindowsandAppleMacintosh2: Excelversion MSWindows Releaseyear AppleMacintoshReleaseyear Excel2.x Excel2.0 1987 Excel2.2 1989 Excel3.0 Excel3.0 1990 Excel3.0 1990 Excel4.0 Excel4.0 1992 Excel4.0 1992 Excel5.0 Excel5.0 1993 Excel5.0 1993 Excel7.0 Excel95 1995 ─ Excel8.0 Excel97 1997 Excel98 1998 Excel9.0 Excel2000 1999 Excel2001 2000 Excel10.0 ExcelXP 2001 Excelv.X 2001 Excel11.0 Excel2003 2003 Excel2004 2004 2.1.2WorksheetDocument Definition:WorksheetDocumentAworksheetdocumentconsistsofasinglesheetonly.Variouskindsofsheetsarepossible,forinstancearegularsheet(containingvaluesandformulas),achartsheet(➜7.1.1),oramacrosheet.Thedefaultfileextensionofworksheetdocumentsis“XLS”.Generalstructureofaworksheetdocument: WorksheetdocumentSheet 2Source:/wiki/Microsoft_Excel.10 2.1DocumentTypes 2.1.3WorkbookDocument Definition:WorkbookdocumentAworkbookdocumentcontainsseveralsheets.Itispossiblebinesheetsofalltypesintotheworkbook,forinstanceregularsheets,chartsheets(➜7.1.2),macrosheets,orVisualBasicmodules.Eachworkbookdocumentcontainsglobalsettingsfortheworkbook,calledtheworkbookglobals.Thedefaultfileextensionofworkbookdocumentsis“XLS”. !
Notethedifference:Asheetispartofaworksheetdocumentaswellasaworkbookdocument.Aworkbookdocumentcontainingonlyonesheetispossible,butitisstillcalledaworkbookdocument.Generalstructureofaworkbookdocument: WorkbookdocumentSheet3Sheet2Sheet1Workbookglobals 2.1.4WorkspaceDocument Definition:WorkspacedocumentAworkspacedocumentcontainslinkstoseveralworksheetand/orworkbookdocuments.Itstoresthefilename,windowsize,andwindowpositionofeachdocumentthatispartoftheworkspace.Thedefaultfileextensionofworkspacedocumentsis“XLW”.Notethedifference:Aworkbookdocumentcontainsseveralsheets,butaworkspacedocumentcontainslinksto !
worksheetorworkbookdocumentsthatarestoredinseveralfiles.Norulewithoutexception:AworkspacedocumentwrittenbyExcel4.0isinfactbinationofaworkbook !
documentandaworkspacedocument:Itmaycontainlinkstoworksheetdocuments,andembeddedsheetsthatareloadedfromanexistingworksheetfileorcreatedfromscratch.Theworkspacedocumentcontainspletedataofallembeddedsheets.Generalstructureofaworkspacedocument: WorkspacedocumentLinktoworksheet/workbookdocument1 Linktoworksheet/workbookdocument2 Linktoworksheet/workbookdocument3 Worksheetorworkbookdocument1 Worksheetorworkbookdocument2 Worksheetorworkbookdocument3 Thedetailedstructureofworkspacedocumentsisdescribedinchapter➜10. 11 2DocumentStructure 2.1.5AvailabilityoftheDocumentTypes ThefollowingillustrationshowswhichdocumenttypesareavailableinthedifferentExcelversions:Excel2.xworksheet Excel3.0worksheet Excel3.0workspace Excel4.0worksheet Excelbinedworkbook/workspace Excel5.0/7.0workbook Excel5.0/7.0workspace Excel8.0+workbook Excel8.0+workspace 12 2.2TheBinaryInterchangeFileFormat 2.2TheBinaryInterchangeFileFormat TheExcelfileformatisnamedBIFF(BinaryInterchangeFileFormat).Itisusedtostorealltypesofdocuments:worksheetdocuments(➜2.1.2),workbookdocuments(➜2.1.3),andworkspacedocuments(➜2.1.4).Therearedifferentversionsofthisfileformat,dependingontheversionofExcelthathaswrittenthefile(➜2.1.1),anddependingonthedocumenttype. 2.2.1BIFFVersionsforWorksheet/WorkbookDocuments ThefollowingtableshowswhichExcelversionwriteswhichfileformatforworksheetandworkbookdocuments: Excelversion BIFFversion Documenttype Excel2.x BIFF2 Worksheet Excel3.0 BIFF3 Worksheet Excel4.0 BIFF4 Worksheet Excel5.0 BIFF5 Workbook Excel7.0 BIFF5 Workbook Excel8.0 BIFF8 Workbook Excel9.0 BIFF8 Workbook Excel10.0 BIFF8 Workbook Excel11.0 BIFF8 Workbook BIFF8containsmajorchangestowardsolderBIFFversions,forinstancethehandlingofUnicodestrings. 2.2.2BIFFVersionsforWorkspaceDocuments ThefollowingtableshowswhichExcelversionwriteswhichfileformatforworkspacedocuments: Excelversion BIFFversion Documenttype Excel2.x ─ ─ Excel3.0 BIFF3W Workspace Excel4.0 BIFF4W Combinedworkbook/workspace Excel5.0 BIFF5W Workspace Excel7.0 BIFF5W Workspace Excel8.0 BIFF8W Workspace Excel9.0 BIFF8W Workspace Excel10.0 BIFF8W Workspace Excel11.0 BIFF8W Workspace 13 2DocumentStructure 2.3FileStructure 2.3.1StreamFile AlldocumenttypesandBIFFversionscanbestoredinasimplestreamfile,mostofthemarealwaysstoredthisway.TheonlyexceptionareBIFF5-BIFF8workbookdocuments,whichareusuallystoredpounddocumentfiles(seebelow).Ifthesedocumentsarestoredasstreamfiles,theentirefileconsistsofthe“Book”stream(BIFF5)or“Workbook”stream(BIFF8)only. 2.3.2CompoundDocumentFile(BIFF5-BIFF8) Aworkbookdocumentwithseveralsheets(BIFF5-BIFF8)isusuallystoredusingpounddocumentfileformat(alsoknownas“OLE2storagefileformat”or“Microsoftpatiblestoragefileformat”).Itcontainsseveralstreamsfordifferenttypesofdata.pletedocumentationoftheformatpounddocumentfilescanbefoundatpdocfileformat.pdf. Thefollowingtablelistsnamesofpossiblestreams. Streamname Contents Book BIFF5WorkbookStream(➜2.3.3) Workbook BIFF8WorkbookStream(➜2.3.3) <05H>SummaryInformation<05H>DocumentSummaryInformationCtls DocumentsettingsDocumentsettingsFormattingofformcontrols UserNames Usernamesinsharedworkbooks(➜9) RevisionLog Changetrackinglogstream(➜9) Itispossibletocreatesubstorageslikesubdirectoriesinafilesystem,forinstanceforthePivotTablestreams.Thesestoragescontainsubstreamsitself. Storagename Contents LNKxxxxxxxx StorageforalinkedOLEobject(➜6) MBDxxxxxxxx StorageforanembeddedOLEobject(➜6) _SX_DB_CUR Pivotcachestorage.ThestreamscontaincachedvaluesforPivotTables(➜8). _VBA_PROJECT_CUR VisualBASICprojectstorage !
InBIFF8,theEscherstreamdescribingdrawingobjects(➜6)isnotstoredasseparatestreaminpounddocumentfile,butsplitandembeddedinseveralMSODRAWINGrecordsthatarepartoftheWorkbookStream. 14 2.3FileStructure 2.3.3Worksheet/Workbook/WorkspaceStream Dependingonthedocumenttype,differentnamesareusedforthestream(s)theycontain.Definition:WorksheetStreamBIFF2-BIFF4worksheetdocuments(➜2.1.2)arestoredasstreamfiles(➜2.3.1).TheentirestreamiscalledtheWorksheetStream. TheWorksheetStreamisdescribedindetailin➜4.1.1.Definition:WorkbookStreamBIFF5-BIFF8workbookdocuments(➜2.1.3)thatarestoredinpounddocumentfile(➜2.3.2)containastreamintherootstoragecalledtheWorkbookStream.Thenameofthisstreaminpounddocumentfileis“Book”forBIFF5workbooks,and“Workbook”forBIFF8workbooks.IfaBIFF5-BIFF8workbookdocumentisstoredasstreamfile(➜2.3.1),theentirestreamiscalledtheWorkbookStream. TheWorksheetStreamisdescribedindetailin➜4.1.2.Definition:WorkspaceStreamBIFF3W-BIFF8Wworkspacedocuments(➜2.1.4)arestoredasstreamfiles(➜2.3.1).TheentirestreamiscalledtheWorkspaceStream. TheWorkspaceStreamisdescribedindetailinchapter➜10. 2.3.4Substreams TheBIFF5-BIFF8WorkbookStream(➜2.3.3)isdividedintoseveralpartsthatdescribetheworkbookglobalsandthecontainedsheets(➜2.1.3).Eachofthispartsiscalledasubstream,definedbyastartingandendpositioninthestream. Definition:WorkbookGlobalsSubstreamThesubstreamthatcontainstheglobalinformationofaworkbookiscalledtheWorkbookGlobalsSubstream.ItispartoftheWorkbookStreamofBIFF5-BIFF8workbooks. Definition:SheetSubstreamAsubstreamthatdescribesasheetinaworkbookiscalledSheetSubstream.ItispartoftheBIFF5-BIFF8WorkbookStreamaswellastheBIFF4WWorkspaceStream.TheSheetSubstreaminaWorkbookStreamcanberegardedasaWorksheetStream,becausebothdescribeonesheetandareverysimilarintheirstructure.AspecialtypeofaSheetSubstreamistheChartSubstream(➜7.1.2). •SubstreamsintheBIFF5-BIFF8WorkbookStream InBIFF5-BIFF8WorkbookStreams,theWorkbookGlobalsSubstreamisttheleadingpartofthestream.ItisfollowedbyallSheetSubstreamsinorderofthesheetsthatareinthedocument.CommonstructureofaBIFF5-BIFF8WorkbookStream: WorkbookGlobalsSubstream(required) FirstSheetSubstream(required)SecondSheetSubstream(optional) ThirdSheetSubstream(optional)⋮ 15 2DocumentStructure •SubstreamsintheBIFF4WorkspaceStream InBIFF4WorkspaceStreams,theSheetSubstreamsareembeddedintheWorkspaceStream.TheyaresimilarintheirstructuretoBIFF4WorksheetStreams.CommonstructureofaBIFF4WWorkspaceStream: WorkspaceStream(required)FirstembeddedBIFF4SheetSubstream(optional)SecondembeddedBIFF4SheetSubstream(optional)ThirdembeddedBIFF4SheetSubstream(optional)⋮ 2.4BIFFRecordStructure Definition:BIFFRecord MostoftheExcelstreamsorsubstreams(includingallstreamsdescribedin➜2.3.3andsubstreamsdescribedin➜2.3.4)aredividedintorecords.Eachrecordcontainsspecificdataforthevariouscontentsorfeaturesinadocument.Itconsistsofaheaderspecifyingtherecordtypeandsize,followedbytherecorddata. CommonstructureofaBIFFrecord: Offset SizeContents
0 2 Identifier
2 2 Sizeofthefollowingdata(sz)
4 sz Recorddata }Recordheader ThemaximumsizeoftherecorddataislimitedanddependsontheBIFFversion.Ifthesizeoftherecorddataexceedsthecurrentlimit,oneormoreCONTINUErecords(➜5.21)willbeadded.InsideaCONTINUErecordthedataofthepreviousrecordcontinuesasusual. Inthisdocumentationonlytherecorddatawithouttheheadersisshown.Alloffsetsarerelativetothebeginningoftherecorddataandnottotheentirerecord.ThecontentsofmostoftherecordsdifferfromBIFFversiontoversion.Thiswillbedescribedinseparatetables.AfewolderrecordsarereplacedinnewerBIFFversions.ExceldoesnotwritetheseoldrecordsinnewBIFFversionsanymore. 16 2.5CommonRecordSubstructures 2.5CommonRecordSubstructures Thischaptercontainsinformationaboutbasicsubstructureswhichdonotbelongtospecificrecords,forinstancestrings,errorcodes,constantvalues,URLs,orlineandareaformatting. 2.5.1FormattingRuns Formattingrunsdescribethecharacterformattingofstrings.Aformattingruncontainstheindexofacharacterandtheindexofafontinthefontbuffer.Thefontisusedtoformattheindexedcharacterandthefollowingcharacters,untilthestringendsoranotherformattingrunfollows. Formattingrun,BIFF2-BIFF5: Offset SizeContents
0 1 Firstformattedcharacter(zero-based)
1 1 IndextoFONTrecord(➜5.45) Formattingrun,BIFF8: Offset Size
0 2
2 2 ContentsFirstformattedcharacter(zero-based)IndextoFONTrecord(➜5.45) 2.5.2ByteStrings(BIFF2-BIFF5) AllExcelfileformatsuptoBIFF5containsimplebytestrings.Thebytestringconsistsofthelengthofthestringfollowedbythecharacterarray.Thelengthisstoredeitheras8-bitvalueoras16-bitvalue,dependingonthecurrentrecord.Thestringisnotzero-terminated.Theencodingofthecharacterarrayisdependentonthecurrentrecord(forexampletakenfromtheCODEPAGErecord,➜5.17,orfromtheFONTrecord,➜5.45). Offset SizeContents 01or2 1or2ln Lengthofthestring(charactercount,ln)Characterarray(8-bitcharacters) 2.5.3UnicodeStrings(BIFF8) FromBIFF8on,stringsarealwaysstoredusingUTF-16LE3textencoding.Thecharacterarrayisasequenceof16-bitvalues4.Additionallyitispossibletousepressedformat,whichomitsthehighbytesofallcharacters,iftheyareallzero.Thefollowingtabledescribesthestandardformatoftheentirestring,butinmanyrecordsthestringsdifferfromthisformat.Thiswillbementionedseparately.Itispossible(butnotrequired)tostoreRich-TextformattinginformationandAsianicinformationinsideaUnicodestring.Thisresultsinfourdifferentwaystostoreastring.Thecharacterarrayisnotzero-terminated. 3Formoreinformationsee/wiki/UTF-16.4InmostcaseseachvaluecorrespondstoaUnicodecharacter.OnlytheUnicodecharactersaboveU+FFFFareencodedwitha “surrogatepair”,thataretwo16-bitcodevaluesinUTF-16(seefootnote3). 17 2DocumentStructure •ContentsofaUnicodeString Thestringconsistsofthecharactercount(asusualan8-bitvalueora16-bitvalue),optionflags,thecharacterarrayandoptionalformattinginformation.Ingeneral,theoptionflagsfieldursalsoforemptystrings.Butinafewrecords,thisfieldisomitted,ifthestringisempty.Thisismentionedattherespectiveplace. Offset SizeContents 01or2 1or21 Lengthofthestring(charactercount,ln)Optionflags: [2or3][var.]var.[var.][var.] 24lnor2∙ln4∙rtsz BitMaskContents
0 01Hpressionompr): 02=Compressed(8-bitcharacters) 12=pressed(16-bitcharacters)
2 04HAsianicsettings(ic): 02=DoesnotcontainAsianicsettings 12=ContainsAsianicsettings
3 08HRich-Textsettings(richtext): 02=DoesnotcontainRich-Textsettings 12=ContainsRich-Textsettings (optional,onlyifrichtext=1)NumberofRich-Textformattingruns(rt) (optional,onlyific=1)SizeofAsianicsettingsblock(inbytes,sz) Characterarray(8-bitcharactersor16-bitcharacters,dependentompr) (optional,onlyifrichtext=1)Listofrtformattingruns(➜2.5.1) (optional,onlyific=1)AsianicSettingsBlock(seebelow) 18 2.5CommonRecordSubstructures •AsianicSettingsBlock Asianictext5(Ruby)canbeusedtoprovideextendedicinformationforspecificcharactersorwords.Itappearsabovetheregulartext(ortotherightofverticaltext),andcanrefertosinglecharacters,groupsofcharacters,orentirewords. Offset SizeContents
0 2 Unknownidentifier0001H
2 2 Sizeofthefollowingdata(10+2∙ln+6∙np)
4 2 IndextoFONTrecord(➜5.45)usedfortheAsianictext
6 2 AdditionalsettingsfortheAsianictext: BitMaskContents 1-00003HTypeofJapaneseictext(type): 002=Katakana(narrow) 102=Hiragana 012=Katakana(wide) 3-2000CHAlignmentofallportionsoftheAsianictext(align): 002=Notspecified(Japaneseonly)102=Centered 012=Left(Topforverticaltext) 112=Distributed 5-40030H112(alwaysset)
8 2 NumberofportionstheAsianictextisbrokeninto(np). Ifnp=0,theAsianictextreferstotheentirecelltext. 10
2 TotallengthofthefollowingAsianictext(numberofcharacters,ln) 12
2 Repeatedtotallengthofthetext 1414+2∙ln 2∙lnor26∙np CharacterarrayofAsianictext,noUnicodestringheader,always16-bitcharacters.Note:Ifln=0,thisfieldisnotemptybutcontains0000H. Listofnpstructuresthatdescribethepositionofeachportioninthemaintext.Eachstructurecontainsthefollowingfields: OffsetSizeContents
0 2FirstcharacterintheAsianictextofthisportion(cpa)
2 2Firstcharacterofthemaintextbelongingtothisportion(cpm)
4 2Numberofcharactersinmaintextbelongingtothisportionm) Example:JapanesewordTokyo(東京)withaddedhiragana(とうきょう)
6.ThefollowingexamplesshowthecontentsoftheimportantfieldsoftheAsianicSettingsBlock. Example1:Hiraganacenteredovertheentireword: 東京とうきょう type=102(hiragana)align=102(centered)np=0(noportions,hiraganareferstoentiretext)ln=5(lengthofentirehiraganatext)Noportionstructures 5Formoreinformationsee/wiki/Ruby_characters.6Exampletakenfrom/wiki/Ruby_characters. 19 2DocumentStructure Example2:Hiraganaleft-alignedpercharacter: とう きょう 東京 type=102(hiragana)align=012(left-aligned)np=2(hiraganasplitinto2portions)ln=5(lengthofentirehiraganatext)Portion#1:cpa=0(startwith1sthiraganacharacter);cpm=0;m=1(attachto1stcharacterinmaintext)Portion#2:cpa=2(startwith3rdhiraganacharacter);cpm=1;m=1(attachto2ndcharacterinmaintext) 2.5.4RGBColours ColourvaluesarerepresentedinRGBmode(red/green/blue). Offset SizeContents
0 1 ponent
1 1 ponent
2 1 ponent
3 1 Notused Inthisdocumentation,constantcolourvaluesarewrittenas6-digithexadecimalvaluesinRGBnotation:RRGGBBH. Example:ThecolourvalueFF8000Hdescribesthecolourorange:redisFFH,greenis80H,andblueis00H. 2.5.5RKValues AnRKvalueisanencodedintegerorfloating-pointvalue.RKvalueshaveasizeof4bytesandareusedtodecreasefilesizeforfloating-pointvalues. StructureofanRKvalue(32-bitvalue),BIFF3-BIFF8: Bit Mask Contents
0 00000001H0=Valuenotchanged
1 00000002H0=Floating-pointvalue 31-
2 FFFFFFFCHEncodedvalue 1=Encodedvalueismultipliedby1001=Signedintegervalue Ifbit1iscleared,theencodedvaluerepresentsthe30mostsignificantbitsofanIEEE754floating-pointvalue(64-bitdoubleprecision).The34leastsignificantbitsmustbesettozero.Ifbit1isset,theencodedvaluerepresentsasigned30-bitintegervalue.Togetthecorrectinteger,theencodedvaluehastobeshiftedrightarithmeticallyby2bits.Ifbit0isset,thedecodedvalue(bothintegerandfloating-point)mustbedividedby100togetthefinalresult. Examples:RKvalue3FF00000H3FF00001H004B5646H004B5647H Typefloatfloatintegerinteger Div100noyesnoyes Encodedvalue3FF00000H3FF00000H004B5644H004B5644H Decodedvalue3FF0000000000000H=1.03FF0000000000000H=1.00012D591H=12343210012D591H=1234321 Result1.00.01123432112343.21 20 2.5CommonRecordSubstructures 2.5.6ErrorCodes Ifthecalculationofaformularesultsinanerrororanyotheractionfails,Excelsetsaspecificerrorcode.Theseerrorcodesareusedforinstanceincellrecordsandformulas. Errorcode00H Errorvalue#NULL!
DescriptionIntersectionoftwocellrangesisempty 07H #DIV/0!
Divisionbyzero 0FH #VALUE!
Wrongtypeofoperand 17H #REF!
Illegalordeletedcellreference 1DH #NAME?
Wrongfunctionorrangename 24H #NUM!
Valuerangeoverflow 2AH #N/A Argumentorfunctionnotavailable 2.5.7ConstantValues Sometimesitisneededtostoreconstantvaluesofdifferentdatatypes.Thesevaluesareusedtocreatelinearlists(forinstanceintheCRNrecord,➜5.23),ortwo-dimensionalarrays(➜2.5.8).Thischapterdescribestheformatoftheindividualconstantvalues. •EmptyValue Offset Size
0 1
1 8 Contents00H(Identifierforanemptyconstant)Notused •Number Offset01 SizeContents
1 01H(Identifierforanumericalconstant)
8 IEEE754floating-pointvalue(64-bitdoubleprecision) •StringValue Offset Size
0 1
1 var. Contents 02H(Identifierforastringconstant) BIFF2-BIFF5:Bytestring,8-bitstringlength(➜2.5.2) BIFF8: Unicodestring,16-bitstringlength,optionflagsuralways(➜2.5.3) •BooleanValue Offset Size
0 1
1 1
2 7 Contents04H(IdentifierforaBooleanconstant)0=FALSE,1=TRUENotused 21 2DocumentStructure •ErrorValue Offset Size
0 1
1 1
2 7 Contents10H(Identifierforanerrorconstant)Errorcode(➜2.5.6)Notused 2.5.8ConstantValueArray Two-dimensionalarraysofconstantvaluesareusedtostorecachedDDElinkresults(recordEXTERNALNAME,➜5.39),orforconstantarraysinformulas(tokentArray,➜3.8.7).Thearraystartswiththedimensions(widthandheight),followedbyalistofconstantvalues. Two-dimensionalconstantvaluearray,BIFF2-BIFF5: Offset SizeContents
0 1 Numberofcolumns(nc).Thevalue0represents256columns.
1 2 Numberofrows(nr)
3 var. Listofnc∙nrconstantvalues(➜2.5.7) Two-dimensionalconstantvaluearray,BIFF8: Offset SizeContents
0 1 Numberofcolumnsdecreasedby1(nc)
1 2 Numberofrowsdecreasedby1(nr)
3 var. Listof(nc+1)∙(nr+1)constantvalues(➜2.5.7) 2.5.9EncodedFileURLs TheintentionofencodingfileURLsistomakethemmoreplatformindependent.EncodedURLsurintherecordsEXTERNSHEET(BIFF2-BIFF5,➜5.41)orEXTERNALBOOK(BIFF8,➜5.38),andDCONREF(➜5.30). ThefirstcharacteroftheURLisusedtodeterminethetypeofencoding.InUnicodestrings(BIFF8)thiscouldbea16-bitvalue. Firstcharacter00H 01H02H03H04H BIFF2-BIFF4Notused EncodedURLfollowsReferencetothecurrentsheet(nothingwillfollow)Notused Notused BIFF5 Referencerelativetothecurrentsheet(nothingwillfollow),usede.g.indefinednames EncodedURLfollows Referencetothecurrentsheet(nothingwillfollow) Referencetoasheetintheowndocument(sheetnamefollows) Referencetotheownworkbook,sheetisunspecified(nothingwillfollow) BIFF8Referencerelativetothecurrentsheet(nothingwillfollow),usede.g.indefinednamesEncodedURLfollowsReferencetoasheetintheowndocument(sheetnamefollows)Notused Notused others Notencoded.ThisisalreadythefirstcharacteroftheURL. 22 2.5CommonRecordSubstructures InsideoftheencodedURLtherecanurseveralcontrolcharacters. Controlcharacter01H02H03H04H05H06H08H09H MeaningAnMS-DOSdriveletterwillfollow,or“@”andtheservernameofaUNCpathStartpathnameonsamedriveasowndocumentEndofsubdirectorynameParentdirectoryUnencodedURL.FollowedbythelengthoftheURL(1byte),andtheURLitself.StartpathnameininstallationdirectoryofExcelMacrotemplatedirectoryininstallationdirectoryofExcelSheetinthesameworkbook(BIFF4W) IfasheetnamefollowsthefilenameintheencodedURL(BIFF4W-BIFF8),thefilename(butnotthefilepath)willbeenclosedinbrackets.NotethatinEXTERNALBOOKrecords(BIFF8)sheetnamesdonoturandthereforethefilenamesarenotenclosedinbrackets. ExamplesforBIFF2-BIFF4(owndocumentissavedas“C:\path\own.xls”): Formula Encodedfilename =own.xls!
A1 <02H> =ext.xls!
A1 <01H>ext.xls ='sub\ext.xls'!
A1 <01H>sub<03H>ext.xls ='\ext.xls'!
A1 <01H><02H>ext.xls ='\sub\ext.xls'!
A1 <01H><02H>sub<03H>ext.xls ='\sub\sub2\ext.xls'!
A1 <01H><02H>sub<03H>sub2<03H>ext.xls ='..\sub\ext.xls'!
A1 <01H><04H>sub<03H>ext.xls ='D:\sub\ext.xls'!
A1 <01H><01H>Dsub<03H>ext.xls ='\\pc\sub\ext.xls'!
A1 <01H><01H>@pc<03H>sub<03H>ext.xls ='/ext.xls'!
A1 <01H><05H><1EH>/ext.xls
(thelengthoftheURL(30=1EH)followsthe05Hbyte) ExamplesforBIFF4Winternalreferences(allformulasarelocatedon“Sheet1”): Formula Encodedfilename =Sheet1!
A1 <02H> =Sheet2!
A1 <01H><09H>Sheet2 ExamplesforBIFF5internalreferences(allformulasarelocatedon“Sheet1”): Formula Encodedfilename =Sheet1!
A1 <02H> =Sheet2!
A1 <03H>Sheet2 =NonExistentSheet!
A1 <04H> Examples
forBIFF8internalreferences(forexampleinrecordDCONREF): Formula Encodedfilename =Sheet2!
A1 <02H>Sheet2 23 2DocumentStructure ExamplesforBIFF4W-BIFF8externalreferences(owndocumentissavedas“C:\path\own.xls”): Formula Encodedfilename =[ext.xls]Sheet1!
A1 <01H>[ext.xls]Sheet1 ='sub\[ext.xls]Sheet1'!
A1 <01H>sub<03H>[ext.xls]Sheet1 ='\[ext.xls]Sheet1'!
A1 <01H><02H>[ext.xls]Sheet1 ='\sub\[ext.xls]Sheet1'!
A1 <01H><02H>sub<03H>[ext.xls]Sheet1 ='\sub\sub2\[ext.xls]Sheet1'!
A1
<01H><02H>sub<03H>sub2<03H>[ext.xls]Sheet1 ='..\sub\[ext.xls]Sheet1'!
A1 <01H><04H>sub<03H>[ext.xls]Sheet1 ='D:\sub\[ext.xls]Sheet1'!
A1 <01H><01H>Dsub<03H>[ext.xls]Sheet1 ='\\pc\sub\[ext.xls]Sheet1'!
A1
<01H><01H>@pc<03H>sub<03H>[ext.xls]Sheet1 ='/[ext.xls]Sheet1'!
A1 <01H><05H><26H>/[ext.xls]Sheet1(thelengthoftheURL(38=26H)followsthe05Hbyte) 2.5.10EncodedDDEandOLELinks ADDElinkcontainsthenameoftheserverapplicationandtheic.AnOLElinkcontainstheclassnameand theURLofthedocument.Inbothcasesthenamesarestoredinonestring,separatedbythecontrolcharacter03H.TheURLsarenotencoded. Example:AdocumentcontainsaDDElinkandanOLElinktoSheet1inthefile“D:\sub\example.xls”. LinkServer/ClassTopic/URL Encodeddocumentname DDEexcel D:\sub\[example.xls]Sheet1 excel<03H>D:\sub\[example.xls]Sheet1 OLEExcel.Sheet.8D:\sub\example.xls Excel.Sheet.8<03H>D:\sub\example.xls 2.5.11LineStylesforCellBorders(BIFF3-BIFF8) Theselinestylesareusedtodefinecellborders.Thestyles08Hto0DHareavailableinBIFF8only. IndexStyle Sample IndexStyle 00HNoline ThefollowingforBIFF8only: 01HThin 08HMediumdashed 02HMedium 09HThindash-dotted 03HDashed 0AHMediumdash-dotted 04HDotted 0BHThindash-dot-dotted 05HThick 0CHMediumdash-dot-dotted 06HDouble 0DHSlantedmediumdash-dotted 07HHair Sample 24 2.5CommonRecordSubstructures 2.5.12PatternsforCellandChartBackgroundArea Thebackgroundareaofcells(BIFF3-BIFF8)andchartobjects(BIFF2-BIFF8)maycontainapattern.Patterncolourandpatternbackgroundcolouraredefinedseparately.Inthefollowingtableblackisusedaspatterncolourandwhiteaspatternbackgroundcolour. Index00H PatternSampleNopattern IndexPatternSample 01H 0AH 02H 0BH 03H 0CH 04H 0DH 05H 0EH 06H 0FH 07H 10H 08H 11H 09H 12H The
followingtableshowshowapatternisusedwiththecorrectcolourindexes. Cellformat Nobackground(showssystemwindowbackground) PatternPatterncolourindex00HSystemwindowtext(notused) Redbackground(solid) 01HRed Redbackgroundwiththinhorizontalbluelines 0BHBlue Redbackgroundwiththinhorizontallines,automaticcolour 0BHSystemwindowtext Nobackground,thinhorizontalbluelines 0BHBlue Nobackground,thinhorizontallines,automaticcolour 0BHSystemwindowtext BackgroundcolourindexSystemwindowbackground Systemwindowtext(notused)Red Red Systemwindowbackground Systemwindowbackground ThedescriptionofthePALETTErecord(➜5.74)containsinformationhowthespecialsystemcoloursareused.Notethebehaviourofsolidcolouredbackgrounds,wherepattern01Hisusedinconjunctionwiththepatterncolour. 25 2DocumentStructure 2.5.13CellAttributes(BIFF2) AllcellrecordsinBIFF2containacellattributefieldwithasizeof3bytes.TheycontainanindextoanXFrecord(➜5.115)andsomerepeatedcontentsofthereferencedXFrecord.TheXFindexfieldhasasizeofonly6bits,sotheindexrangeis0…63.IfarealXFindexgreaterthan62isused,theXFindexfieldalwayscontainsthevalue63,andanIXFErecord(➜5.62)ursinfrontofacellrecord.TheIXFErecordcontainsthecorrectindexoftheXFrecord.InaROWrecord(➜5.88)thisfieldisnotused,becausetherewillalwaysurarealXFindexfield. Cellattributesfield(3bytes),BIFF2: Offset SizeContents
0 1 CellprotectionandXFindex: BitMaskContents 5-
0 3FHIndextoXFrecord(➜5.115).Thevalue3FH=63indicatesa precedingIXFErecord(➜5.62).NotusedinROWrecords(➜5.88).
6 40H1=Cellislocked
7 80H1=Formulaishidden
1 1 IndexestoFORMATandFONTrecords: BitMaskContents 5-
0 3FHIndextoFORMATrecord(➜5.49) 7-
6 C0HIndextoFONTrecord(➜5.45)
2 1 Cellstyle: BitMaskContents 2-
0 07HXF_HOR_ALIGN–Horizontalalignment(➜5.115.1)
3 08H1=Cellhasleftblackborder
4 10H1=Cellhasrightblackborder
5 20H1=Cellblackborder
6 40H1=Cellhasbottomblackborder
7 80H1=Cellhasshadedbackground 2.5.14CellRangeAddress Acellrangeaddressspecifiesafixedcellrangeinthecurrentsheet. Cellrangeaddress,BIFF2-BIFF5: Offset SizeContents
0 2 Indextofirstrow
2 2 Indextolastrow
4 1 Indextofirstcolumn
5 1 Indextolastcolumn Cellrangeaddress,BIFF8: Offset SizeContents
0 2 Indextofirstrow
2 2 Indextolastrow
4 2 Indextofirstcolumn
6 2 Indextolastcolumn !
Inseveralcases,BIFF8stillwritestheBIFF2-BIFF5formatofcolumnindexes).Thiswillbementionedattherespectiveplace. a cell range address (using 8-bit values for the 26 2.5CommonRecordSubstructures 2.5.15CellRangeAddressList Acellrangeaddresslistconsistsofafieldwiththenumberofrangesandthelistoftherangeaddresses. Cellrangeaddresslist,BIFF2-BIFF8: Offset SizeContents
0 2 Numberoffollowingcellrangeaddresses(nm)
2 6∙nmor8∙nmListofnmcellrangeaddresses(➜2.5.14) !
Inseveralcases,BIFF8stillwritestheBIFF2-BIFF5formatofcolumnindexes).Thiswillbementionedattherespectiveplace. a cell range address (using 8-bit values for the 27 3Formulas 3Formulas 3.1CommonFormulaStructure 3.1.1CommonStructure Formulasarestoredaspartofarecord,forinstanceinsideofaFORMULArecordoraDEFINEDNAMErecord.monformatofaformulaisasfollows: FormulainBIFF2: Offset SizeContents
0 1 Sizeofthefollowingformuladata(RPNtokenarray,sz)
1 sz Formuladata(RPNtokenarray) [1+sz] var. (optional)Additionaldataforspecifictokens(➜3.1.6,forexampletArraytoken,➜3.8.7) FormulainBIFF3-BIFF8: Offset Size
0 2
2 sz [2+sz] var. ContentsSizeofthefollowingformuladata(sz)Formuladata(RPNtokenarray)(optional)Additionaldataforspecifictokens(➜3.1.6,forexampletArraytoken,➜3.8.7) SometimesthesizefieldisnotstoreddirectlybeforetheRPNtokenarray,butsomewhereelse.Ifthishappens,itwillbementionedattherespectiveplace.Iftheredoesnotexistanyformuladata,onlythesizefield(whichcontains0then)ispresent. 3.1.2Tokens Definition:Formulatoken Formulatokensaretheindivisibleparticlesofaformula.Theremightbeoperators,numericalorstringconstantsorfunctionnames. Eachtokencontainsatokenidentifier.Severaltokenscontainadditionalinformation.Atokendoesnotcontainanysizeinformation,soimportingandexportingtokensmustbedonecarefully. Commonstructureofaformulatoken,BIFF2-BIFF8: Offset SizeContents
0 1 Tokenidentifier [1] var. (optional)Additionaldataforthetoken 28 3.1CommonFormulaStructure 3.1.3TokenNotation Tokensarereferredtobytheirname.Alltokennamesstartwithasmall“t”(for“Token”).Ifatokencontainsadditionalinformation,itisappendedtothetokennameinparentheses. Examples:Theadditionoperator+isrepresentedbythetokentAdd.Itdoesnotcontainanyadditionaldata.Theintegerconstant1isrepresentedbythetokentInt
(1).Anabsolutereferencetocell$A$1isrepresentedbythetokentRef($A$1). 3.1.4Operators Thereare3typesofoperators:•Unaryoperatorsliketheminussignthatnegatesavalue.Theseoperatorspopmostoperandfromthestack.•Binaryoperatorslikeadditionormultiplication.Theseoperatorspopthemostoperandsfromthestack.•FunctionoperatorsrepresentthesheetfunctionsofExcel.Theyoperateondifferentnumbersmostoperandson thestack.Eitherthefunctionexpectsafixednumberofoperands(forinstanceSINexpectsoneoperand),oravariablenumberofoperandsgiveninthefunctiontoken(forinstanceSUMisabletoprocess0to30operands).Alloperatorspushthe(single)resultoftheiroperationbackontothestack. 3.1.5TokenArrays Definition:TokenarrayThetokenarrayrepresentsanentireformulaandcontainsallusedtokensoftheformulainaspecificorder.ThetokensoftheformulaarestoredintheReverse-PolishNotation(RPN).Thismeans,firstthereuralloperandsofanoperation,followedbytherespectiveoperator.Example:thesimpleterm1+2consistsofthe3tokens“1”,“+”and“2”.WritteninRPN,theformulaisconvertedtothetokenlist“1”,“2”,“+”.Duringparsingsuchanexpression,operandsarepushedontoastack.Anoperatorpopstheneedednumberofoperandsfromstack,performstheoperationandpushestheresultbackontothestack. 29 3Formulas OtherexamplesforRPNtokenarrays: Formula Tokenarray 2*4+
5 2,
4,*,
5,+ Excelnotation tInt
(2),tInt
(4),tMul,tInt
(5),tAdd 2+4*
5 2,4,
5,*,+ tInt
(2),tInt
(4),tInt
(5),tMul,tAdd ABS(2*–A1)
2,A1,-,*,ABS tInt
(2),tRefV(A1),tUminus,tMul,tFunc(ABS) Parsingresult First,theintegerconstants2and4arepushedontothestack.The*operatorpopsthemfromthestackandpushes8.Thentheconstant5ispushed.The+operatorpops5and8andpushes13(thefinalresult). First,theintegerconstants2,4,and5arepushedontothestack.The*operatorpops5and4andpushes20,the+operatorpops20and2andpushes22(thefinalresult). First,theintegerconstant2andthevaluefromcellA1(forexample3)arepushedontothestack.Theunary-operator(tUminus)popsmostvalue3fromstackandpushesthenegatedvalue-
3.The*operatorpops-3and2andpushes-
6.TheABSfunctionneeds1parameter.Itpops-6andpushes6(thefinalresut). Exampleofpletebyterepresentationoftheformula2*4+
5. •TheRPNrepresentationoftheformulais:2,4,
5,*,+. •WritteninExceltokennotation,thisis:tInt
(2),tInt
(4),tInt
(5),tMul,tAdd. Offset Size ContentsTokennameDescription
0 2 000BH Sizeofthefollowingformuladata(sz) }
2 1 1EH tInt
3 2 0002H Integervaluetokenfor2 }
5 1 1EH tInt
6 2 0004H Integervaluetokenfor4
8 1 05H tMulMultiplicationoperator }
9 1 1EH tInt 10
2 0005H Integervaluetokenfor5 12
1 03H tAddAdditionoperator 30 3.1CommonFormulaStructure 3.1.6AdditionalTokenData Afewtokenscontainadditionaldatathatdoesnotfollowthetokenidentifier,butisappendedtothetokenarray.Itssize isnotcontainedintheleadingfieldcontainingthetokenarraysize.AffectedtokensarethetArraytoken(➜3.8.7),thetMemAreatoken(➜3.9.4),andafewsubtypesofthethetNlrtoken(➜3.10.4).Theadditionaldataofeachtokenisappendedinthesameorderthetokensarelocatedinthetokenarray. Example:Theformula={1}+A1:A2A2:A3+{2}contains3tokenswithadditionaldata:2tArraytokensrepresentingthecontantarrays,andatMemAreatokencontainingtheresultoftheintersectionoperator(thecell referenceA2).ThedetailedexamplebelowappliesforBIFF8. •TheRPNrepresentationoftheformulais:{1},A1:A2,A2:A3,“”,+,{2},+. •WritteninExceltokennotation,thisis(thetermA1:A2,A2:A3,“”isledbyatMemAreatoken):tArrayV({1}),tMemAreaV(A2),tAreaR(A1:A2),tAreaR(A2:A3),tIsect,tAdd,tArrayV({2}),tAdd. Offset SizeTokennameDescription
0 2 Sizeofthefollowingformuladata(44bytes),withouttheadditionaldatafollowingthelasttAddtoken
2 8 tArrayVPlaceholderforfirstconstantarray{1} 10
7 tMemAreaVConstantreferencesubexpressionfollowsinnext19bytes(untiland includingthetIsecttoken) 17
9 tAreaRCellreferenceA1:A2 26
9 tAreaRCellreferenceA2:A3 35
1 tIsectIntersectionoperator 36
1 tAddAdditionoperator 37
8 tArrayVPlaceholderforsecondconstantarray{2} 45
1 tAddAdditionoperator 46 12 Constantvaluearray(➜2.5.8)containingthevaluesoftheconstantarray{1}:containswidth1,height1,value1 58 10 Cellrangeaddresslist(➜2.5.15)containingtheresultofthe referencesubexpressionA1:A2A2:A3:aone-elementlistwiththecelladdressA2 68 12 Constantvaluearray(➜2.5.8)containingthevaluesoftheconstantarray{2}:containswidth1,height1,value2 31 3Formulas 3.2TokenClasses 3.2.1ClassifiedTokens Allfunctionoperatorsandmostoperandtokensexistin3differentversions:asreferenceclasstoken,valueclasstoken,andarrayclasstoken.Thetokenclassdependsonwhichtypeofdatatheinvolvedoperatorexpects.Sometimesonly1or2tokenclassesarevalidforatoken(forexample,arrayclasstokenscannotexistasreferenceclasstokens,buttheycanexistasvalueclasstokens). •Referenceclasstoken:Thereferenceaddressitself,independentofthecellcontents. •Valueclasstoken:Avalue(aconstant,afunctionresult,oronespecificvaluefromadereferencedcellrange). •Arrayclasstoken:Anarrayofvalues(arrayofconstantvalues,anarrayfunctionresult,orallvaluesofacellrange). Thestructureofthe8-bitidentifierofaclassifiedtokenisdescribedinthefollowingtable. Bit MaskContents 4-
0 1FH Basictokenidentifier 6-
5 60H 012=Referenceclasstoken(tokenrange20H-3FH) 102=Valueclasstoken(tokenrange40H-5FH) 112=Arrayclasstoken(tokenrange60H-7FH)
7 80H 02(zero) Thetokenclassismarkedinthenamesofthetokens.Thenamesofreferenceclasstokenscontainatrailing“R”,valueclasstokenscontainatrailing“V”,andthenamesofarrayclasstokensatrailing“A”. Example:ThetAreatoken(➜3.9.3)isn
声明：
该资讯来自于互联网网友发布，如有侵犯您的权益请联系我们。
