 |
|||||||||
|
|||||||||
|
|||||||||
Languages »
C# »
General
Intermediate
Office 2007 .bin file formatBy Stephane Rodriguez.Undocumented binary file formats coming in the Office 2007 timeframe |
C#, VC6, C++, Windows, .NET, .NET 1.1, .NET 2.0VS.NET2003, VS, Dev Posted: 17 Aug 2006 Updated: 24 Jan 2007 Views: 85,102 |
||
|
|
|||
|
Advanced Search Sitemap |
|
|
|
||||||||||||||||
- Download OLE read/write (C++) - 9 Kb
- Download BIFF12 Reader (C++) - 404 Kb
- Download OLE read/write (C#) - 10 Kb
- Download BIFF12 Reader (C#) - 404 Kb
Stephane Rodriguez, August 2006 - this document is not endorsed by Microsoft.
Introduction
The new Office 2007 file formats are ZIP files that contain parts some of which are XML, some others are native file formats such as JPEG pictures, and the remaining binary parts end up being referred to as BIN parts. BIN parts are of particular interest for the file format consumer or updater since the underlying file formats are undocumented (at the time of writing, August 10 2006) and are several additional file formats to deal with.
BIN parts appear in a number of cases. If you insert a VBA macro or an OLE object in a Word 2007, Excel 2007 or Powerpoint 2007 document, then there will be one or more BIN parts of interest. BIN parts are zip entries consisting of files with extension .BIN, that actually contain their own file format depending on the MIME type defined in the relationships part (xxx.rels) :
- VBA macros: vbaProject.bin (MIME type: application/vnd.ms-office.vbaProject)
- OLE objects: oleObjectxxx.bin (MIME type: application/vnd.openxmlformats-officedocument.oleObject)
An example of vbaProject.bin when cracking-open a Word 2007 .DOCM file
An example of oleObjectxxx.bin when cracking-open a Powerpoint 2007 .PPTX file
Before I get into further details about these two BIN parts, there are also BIN parts introduced by a new variant of the Excel 2007 file format known as Excel binary workbook which is a file ending with .XLSB. Apparently for performance reasons, it was decided to store an Excel file using a number of BIN parts instead of XML parts. Those BIN parts are a subset of the XML parts most affected by performance and scalability issues, most noteworthy each worksheet because of its arbitrary size. For some reason, the workbook, styles and a number of other small parts are also BIN parts despite the fact that those contribute only marginally to the overall processing of the workbook. Again, there are underlying file formats to deal with for both the consumer and the implementer.
Some of the BIN parts from an Excel 2007 binary .XLSB file with 3 worksheets
In addition to VBA projects and embedded OLE objects, we find BIN parts in Excel 2007 .XLSB files for the following reasons :
- Workbook part workbook.bin (MIME type: application/vnd.ms-excel.workbook)
- Styles dictionary part styles.bin (MIME type: application/vnd.ms-excel.styles)
- For each worksheet,
- an index part worksheets/binaryIndexxx.bin (MIME type: application/vnd.ms-excel.binIndexWs)
- a worksheet part worksheets/sheetxxx.bin (MIME type: application/vnd.ms-excel.worksheet)
- an optional printer settings part printerSettings/printerSettingsxxx.bin (MIME type: application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings)
- Optional calculation chain part calcChain.bin (MIME type: application/vnd.ms-excel.calcChain)
- Optional comments parts commentsxxx.bin (MIME type: application/vnd.ms-office.legacyDrawing)
- Optional tables parts tables/tablexxx.bin (MIME type: application/vnd.ms-excel.table)
- Optional connections parts connections.bin (MIME type: application/vnd.ms-excel.connections)
- Optional Chartsheet, Dialogsheet, Macrosheet parts (chartsheets/sheetxxx.bin, dialogsheets/sheetxxx.bin, macrosheets/sheetxxx.bin) (MIME type: application/vnd.ms-excel.chartsheet, application/vnd.ms-excel.dialogsheet, application/vnd.ms-excel.macrosheet)
- Optional Pivot Table parts (pivotTables/pivotTablexxx.bin) (MIME type: application/vnd.ms-excel.pivotTable)
- Optional Pivot Table Cache definition and records parts (pivotCache/pivotCacheDefinitionxxx.bin, pivotCache/pivotCacheRecordsxxx.bin) (MIME type: application/vnd.ms-excel.pivotCacheDefinition, application/vnd.ms-excel.pivotCacheRecords)
- Optional Query tables (queryTables/queryTablexxx.bin) (MIME type: application/vnd.ms-excel.queryTable)
Note that the MIME content types don't differentiate whether the actual files are stored in XML or BIN.
Out of the full list of parts taken from the Ecma specs, other optional parts of the Excel 2007 .XLSB file format are generally left as XML and thus the same than regular Excel 2007 files (.XLSX, .XLSM, ...) are :
- Theme part (theme/themexxx.xml)
- Background image part (drawings/imagexxx.xml)
- Core and app-specific document properties parts (docProps/core.xml, docProps/app.xml)
- Custom properties part (customproperty.bin)
- Custom XML mapping part (xmlMaps.xml)
- Chart part (charts/chartxxx.xml)
- Drawing part (drawings/drawingxxx.xml, drawings/legacyDrawingxxx.vml)
- External workbook references part (externalLinks/externalLinkxxx.xml)
- Metadata part (metadata.xml)
- Shared workbook revisions parts (revisions/...)
- Single Cell Table part (tables/tableSinceCellsxxx.xml)
- Volatile dependencies part (volatileDependencies.xml)
A notable exception pointed above are legacy drawings, stored using the VML file format. While VML is XML markup, it requires an outstanding effort to read, write and possibly render from it. When legacy drawings are dropped in, they may (OLE object for instance) or may not (comment for instance) contain relations markup to other parts.
Printer settings in Excel 2007 files are always stored as BIN parts whether it's .XLSB or not.
Reading or updating vbaProject.bin parts
In previous versions of Word, Excel and Powerpoint, VBA projects were stored as an OLE sub-container of the OLE document container. For the record, .doc / .dot / .xls / .xlt / .xlm / .xla / .ppt / .pot / .ppm / .ppa files are OLE document containers. As pictured below, we have created a Word 97 document and added a VBA macro to it. Notice the Macros sub-container. It contains a VBA container, which contains a number of streams, as well as two other streams.
To view an OLE document container, you can either use one of the tools part of Visual Studio 6.0 known as the DocFile Viewer, or you can use an OLE viewer freely available here.
In previous Word versions, VBA macros were stored as an OLE sub-container (Macros) including a number of streams.
If you double-click on a stream, you can see the actual content. Obviously, each stream is a file format by itself, that needs to be paid special attention to if you are hoping to read or update it. A basic scenario is to make a replacement of one stream by another, and does not imply you know anything that constitutes the streams themselves.
Let's return to Word 2007, Excel 2007 and Powerpoint 2007. What you can do is extract the vbaProject.bin zip entry from a file with an inserted VBA macro and open it in the OLE viewer. What a surprise indeed when you see the following appear :
vbaProject.bin is the content of the Macros container defined above.
To read or update vbaProject.bin parts, you need native API calls represented by IStream for streams, and IStorage for containers. As a sidenote, the mandatory reliance on native API calls makes any client code unable to execute in a partial trust environment such as Click-Once: executing native code implies full trust.
At the top of the article is a sample code that reads an arbitrary OLE container using C++ and C#.
Reading or updating oleObjectxxx.bin parts
Much like the vbaProject.bin parts, oleObjectxxx.bin parts are OLE sub-containers. You can use the same tool (Doc File viewer or equivalent) to view the content of the file, and you can use the same source code provided in the previous section to read or update that file.
As an example, let's create a simple Excel 97 document, insert an OLE object in it (a Wordpad document for instance), then close it and view the resulting .xls file in the OLE viewer. Notice a MBD0032B277 sub-container with two streams inside :
In previous versions of Office, embedded OLE objects are stored as sub-containers (MBD0032B277) of the OLE file.
Now let's return to Word 2007, Excel 2007 or Powerpoint 2007. Just extract the oleObjectxxx.bin part from any such file were you have also inserted an OLE object, open it in the OLE viewer, to see something equivalent to :
oleObjectxxx.bin is the content of the MBD0032B277 container defined above.
What we have figured out so far is that BIN parts in the new file formats contain different underlying structures although they share common interfaces to traverse it (IStream/IStorage). To read and update VBA macros parts and OLE objects parts, you need interfaces to IStream and IStorage, and possibly the knowledge of the underlying content of streams (not a requirement in basic replacement scenarios). That applies equally to Word, Excel and Powerpoint.
With Excel 2007 binary workbooks however, other BIN parts don't follow the structure and content.
Reading Excel 2007 BIN parts
The remainder of the article describes some of the BIN parts. Source code is provided in C++ and C# to read (and possibly write) those BIN parts. In addition to VBA project parts and OLE objects parts being documented in the first part of this article, in green are the parts about to get documented :
- workbook part workbook.bin (MIME type: application/vnd.ms-excel.workbook)
- styles dictionary part styles.bin (MIME type: application/vnd.ms-excel.styles)
- for each worksheet,
- an index part worksheets/binaryIndexxx.bin (MIME type: application/vnd.ms-excel.binIndexWs)
- a worksheet part worksheets/sheetxxx.bin (MIME type: application/vnd.ms-excel.worksheet)
- an optional printer settings part printerSettings/printerSettingsxxx.bin (MIME type: application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings)
- optional calculation chain part calcChain.bin (MIME type: application/vnd.ms-excel.calcChain)
- optional comments parts commentsxxx.bin (MIME type: application/vnd.ms-office.legacyDrawing)
- optional tables parts tables/tablexxx.bin (MIME type: application/vnd.ms-excel.table)
- optional connections parts connections.bin (MIME type: application/vnd.ms-excel.connections)
- optional Chartsheet, Dialogsheet, Macrosheet parts (chartsheets/sheetxxx.bin, dialogsheets/sheetxxx.bin, macrosheets/sheetxxx.bin) (MIME type: application/vnd.ms-excel.chartsheet, application/vnd.ms-excel.dialogsheet, application/vnd.ms-excel.macrosheet)
- optional Pivot Table parts (pivotTables/pivotTablexxx.bin) (MIME type: application/vnd.ms-excel.pivotTable)
- optional Pivot Table Cache definition and records parts (pivotCache/pivotCacheDefinitionxxx.bin, pivotCache/pivotCacheRecordsxxx.bin) (MIME type: application/vnd.ms-excel.pivotCacheDefinition, application/vnd.ms-excel.pivotCacheRecords)
- optional Query tables (queryTables/queryTablexxx.bin) (MIME type: application/vnd.ms-excel.queryTable)
Those parts in green are sufficient to read and possibly update arbitrary cells complete with associated formatting in an Excel 2007 workbook.
Introducing BIFF12
Each BIN part may be made up of its own underlying structure. Fortunately, most BIN parts share a common structure known as BIFF12. This word I have made up based on the name of the binary file format name of older Excel versions, where BIFF stands for Binary Interchange File Format. The history of BIFF is worth an article of its own, but the most interesting thing to know about it is that the latest known major revision was BIFF8, introduced in Excel 97. And the Excel team over at Microsoft were apparently so traumatized by the file format snafu that occurred when customers were forced to migrate from Excel 95 to Excel 97, two different file formats, that they have never considered changing the file format version again, despite adding a ton of new records in BIFF8 in Excel 2000, Excel XP and Excel 2003. Excel 2007 continues that trend in the sense that, if you save back an Excel 2007 file as a "Excel97-2003" compatible file, then Excel 2007 will save the bulk of its features in new BIFF8 records, thereby enabling round-tripping scenarios. BIFF is an OLE stream part inside an OLE document container. VBA projects, OLE objects, document summary properties, and XML maps (Excel 2003) are also stored as separate OLE streams. BIFF8 is basically a sequence of records where each record is identified using two bytes, followed by the length of the record also in two bytes, followed by the record content itself. That's all there is. If you'd like to get the last public BIFF8 documentation from Microsoft (bundled with the MSO documentation), consider buying MSDN Library CDs of March 1998. BIFF12 kind of inherits this, but chose to make some interesting changes, like disregarding the existing BIFF8 record identifiers.
Just like BIFF8, BIFF12 consists of a sequence of records consisting of an identifier, a length and the record content itself. Where it differs is that both the record identifiers and the record length are encoded using a variable-length technique. It works as follows : the first byte of the record identifier is read. If the most significant bit of that byte is set to 1, then another byte will have to be read, up to a maximum of 4 bytes (i.e. the record identifier can always be stored in a DWORD). This most significant bit is irrelevant and thus appropriate shifting needs to occur to construct a record identifier.
So for example if you read byte 0x80, the most significant bit is set, you need to read another byte. Let's assume the other byte is 0x01, the most significant bit is not set, so the record identifier is obtained. Shifting aside, the record identifier is 0x0180. Note that, because you may want to match this record identifier against a number of known record identifiers, you can do the match using the unshifted record identifier, and as a bonus be able to figure out record identifiers in a BIFF12 hexadecimal dump in a straight forward manner.
It works the same for the record length. Shifting to the left in the containing DWORD must occur in order to construct a proper length.
| BIFF12 record structure | ||
|
record id (variable length) |
record length (variable length) | record content (size defined by record length) |
| record id (variable length) | record length (variable length) | record content (size defined by record length) |
| ... | ... | ... |
Reading BIFF12 records
Note that, because Windows uses the little Endian notation, record identifiers and any two-byte, 4-byte or 8-byte value must be read right-to-left. If you are using C# and store the BIN part in a byte[] array, then you must provide the appropriate function helpers to decode such structure. Here is how to decode words (2 bytes), dwords (4 bytes), single-precision floats (4 bytes), double-precision floats (8 bytes), and strings :
public static UInt16 GetWord(byte[] buffer, UInt32 offset)
{
UInt16 val = (UInt16) (buffer[offset + 1] << 8);
val += (UInt16) (buffer[offset + 0]);
return val;
}
public static UInt32 GetDword(byte[] buffer, UInt32 offset)
{
return ((UInt32)(buffer[offset + 3]) << 24) +
((UInt32)(buffer[offset + 2]) << 16) +
((UInt32)(buffer[offset + 1]) << 8) +
((UInt32)(buffer[offset + 0]));
}
public double GetDouble(byte[] buffer, UInt32 offset)
{
double d = 0;
// ReadDouble() can read a IEEE 8-byte double
// straight from a buffer
using (MemoryStream mem = new MemoryStream())
{
BinaryWriter bw = new BinaryWriter(mem);
for (UInt32 i = 0 ; i < 8; i++)
bw.Write(buffer[offset + i]);
mem.Seek(0,SeekOrigin.Begin);
BinaryReader br = new BinaryReader(mem);
d = br.ReadDouble();
br.Close();
bw.Close();
}
return d;
}
public static String GetString(byte[] buffer,
UInt32 offset, UInt32 len)
{
StringBuilder sb = new StringBuilder((int)len);
for (UInt32 i = offset; i < offset + 2 * len; i += 2)
sb.Append((Char)GetWord(buffer, i));
return sb.ToString();
}
public static bool GetRecordID(byte[] buffer,
ref UInt32 offset, ref UInt32 recid)
{
recid = 0;
if (offset >= buffer.Length)
return false;
byte b1 = buffer[offset++];
recid = (UInt32)(b1 & 0x7F);
if ((b1 & 0x80) == 0)
return true;
if (offset >= buffer.Length)
return false;
byte b2 = buffer[offset++];
recid = ((UInt32)(b2 & 0x7F) << 7) | recid;
if ((b2 & 0x80) == 0)
return true;
if (offset >= buffer.Length)
return false;
byte b3 = buffer[offset++];
recid = ((UInt32)(b3 & 0x7F) << 14) | recid;
if ((b3 & 0x80) == 0)
return true;
if (offset >= buffer.Length)
return false;
byte b4 = buffer[offset++];
recid = ((UInt32)(b4 & 0x7F) << 21) | recid;
return true;
}
As a sidenote, the structure of strings stored inside records is the following : 4 bytes for the length (encoded in little Endian) which defines the number of string characters (not bytes) to follow, followed by such number of Unicode characters (2 bytes each, also encoded in little Endian). The strings are never zero-terminated, and I have never encountered in this reverse engineering game strings encoded in something else other than Unicode.
Once you've got this, you can read a BIFF12 structure with code like this :
// A generic BIFF12 part is a sequence of BIFF12 records
// A BIFF12 record is a record identifier, followed by a record length,
// followed by the content itself
// The record identifier is stored in variable length
// The record length is stored in variable length as well
// The record content is arbitrary content whose underlying structure
// is associated to the record identifier, and is defined once for all
// by the implementers of the file format
// It is the responsability of a record handler to parse
// any underlying record structure
UInt32 offset = 0;
while (offset < buffer.Length)
{
UInt32 recid = 0;
UInt32 reclen = 0;
if (!BaseRecord.GetRecordID(buffer, ref offset, ref recid) ||
!BaseRecord.GetRecordLen(buffer, ref offset, ref reclen))
{
Console.WriteLine("***Damaged buffer***");
break;
}
// h is a hashTable which registers record handlers
BaseRecord recHandler = (BaseRecord) h[recid];
if (recHandler != null)
{
Console.Write( String.Format("<{0}>\r\n[rec=0x{1:X} len=0x{2:X}]",
recHandler.GetTag(), recid, reclen) );
for (int i = 0; i < reclen; i++)
{
Console.Write( String.Format(" {0:X2}", buffer[offset + i]) );
}
Console.WriteLine();
// decode the record content itself,
// and possibly the underlying structure if any
recHandler.Read(buffer, ref offset, recid, reclen, h, w);
if (offset == UInt32.MaxValue)
{
Console.WriteLine("***Damaged buffer***");
break;
}
}
else
{
Console.Write( String.Format("[rec=0x{0:X}
len=0x{1:X}]", recid, reclen) );
// we have no idea what this thing is, just dump the content in hexa
for (int i = 0; i < reclen; i++)
{
Console.Write( String.Format(" {0:X2}", buffer[offset + i]) );
}
Console.WriteLine();
}
offset += reclen;
Console.WriteLine();
}
When applying this code to a worksheet BIN part, it produces the following :
// Here is how to read what follows
// For each record known by the BIFF12 reader, we come up with
// the XML markup tag associated to the record.
// This provides clues as how to swap from the XML part to the
// BIN part and vice versa and is easier to understand.
// Followed by square brackets enclosing the record identifier
// and associated length
// Followed by the record content itself
// (i.e. nothing if the length is zero)
// Anytime the record has an underlying structure
// (as with <sheetData>, the structure
// is decoded, and human readable info is provided).
*** Dumping a worksheet part
<worksheet>
[rec=0x181 len=0x0]
<sheetPr>
[rec=0x193 len=0xF] C9 04 02 00 40 00 00 00 00 00 00 00 00 00 00
info : <tabColor rgb=.../>
info : <outlinePr showOutlineSymbols=.../>
info : <pageSetUpPr .../>
info : </sheetPr>
<dimension>
[rec=0x194 len=0x10] 04 00 00 00 04 00 00 00 00 00 00 00 07 00 00 00
info : r1=4, c1=0, r2=4, c2=7
<sheetViews>
[rec=0x185 len=0x0]
<sheetView>
[rec=0x189 len=0x1E] DC 03 00 00 00 00 01 00 00 00 00 00 00 00 40
00 00 00 64 00 00 00 00 00 00 00 00 00 00 00
<selection>
[rec=0x198 len=0x24] 03 00 00 00 04 00 00 00 00 00 00 00 00 00 00
00 01 00 00 00 04 00 00 00 04 00 00 00 00 00 00 00 FF 3F 00 00
</sheetView>
[rec=0x18A len=0x0]
</sheetViews>
[rec=0x186 len=0x0]
<sheetFormatPr>
[rec=0x3E5 len=0xC] FF FF FF FF 08 00 2C 01 00 00 00 01
<cols>
[rec=0x386 len=0x0]
info : colmin=1, colmax=2, width=9,140625, style=1,
outline=false, resize=false, hidden=false
info : colmin=4, colmax=5, width=9,140625, style=2,
outline=false, resize=false, hidden=false
info : colmin=6, colmax=6, width=9,140625, style=2,
outline=true, resize=true, hidden=false
info : colmin=7, colmax=7, width=9,140625, style=0,
outline=true, resize=true, hidden=false
info : colmin=8, colmax=8, width=0, style=0,
outline=false, resize=true, hidden=true
info : colmin=9, colmax=9, width=11, style=0,
outline=false, resize=true, hidden=false
</cols>
[rec=0x387 len=0x0]
<sheetData>
[rec=0x191 len=0x0]
info : row=4, height=405, style=0, outline=false,
resize=true, hidden=false
info : col=0, style=0, v:stringindex=0 v:string=a
</sheetData>
[rec=0x192 len=0x0]
[rec=0x497 len=0x42] 00 00 00 00 00 00 01 00 00 00 01 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 01 00 00 00
<printOptions>
[rec=0x3DD len=0x2] 10 00
<pageMargins>
[rec=0x3DC len=0x30] 66 66 66 66 66 66 E6 3F 66 66 66 66 66
66 E6 3F 00 00 00 00 00 00 E8 3F 00 00 00 00 00 00 E8 3F 33
33 33 33 33 33 D3 3F 33 33 33 33 33 33 D3 3F
<pageSetup>
[rec=0x3DE len=0x22] 01 00 00 00 64 00 00 00 2C 01 00 00 2C
01 00 00 01 00 00 00 01 00 00 00 01 00 00 00 01 00 00 00 00 00
<headerFooter>
[rec=0x3DF len=0x1A] 0C 00 FF FF FF FF FF FF FF FF FF FF FF
FF FF FF FF FF FF FF FF FF FF FF FF FF
[rec=0x3E0 len=0x0]
</worksheet>
[rec=0x182 len=0x0]
BIFF12 records for a number of BIN parts
So what are some of those important records you ask?
// Workbook records
public const int BIFF12_DEFINEDNAME = 0x27;
public const int BIFF12_FILEVERSION = 0x0180;
public const int BIFF12_WORKBOOK = 0x0183;
public const int BIFF12_WORKBOOK_END = 0x0184;
public const int BIFF12_BOOKVIEWS = 0x0187;
public const int BIFF12_BOOKVIEWS_END = 0x0188;
public const int BIFF12_SHEETS = 0x018F;
public const int BIFF12_SHEETS_END = 0x0190;
public const int BIFF12_WORKBOOKPR = 0x0199;
public const int BIFF12_SHEET = 0x019C;
public const int BIFF12_CALCPR = 0x019D;
public const int BIFF12_WORKBOOKVIEW = 0x019E;
public const int BIFF12_EXTERNALREFERENCES = 0x02E1;
public const int BIFF12_EXTERNALREFERENCES_END = 0x02E2;
public const int BIFF12_EXTERNALREFERENCE = 0x02E3;
public const int BIFF12_WEBPUBLISHING = 0x04A9;
// Worksheet records
public const int BIFF12_ROW = 0x00;
public const int BIFF12_BLANK = 0x01;
public const int BIFF12_NUM = 0x02;
public const int BIFF12_BOOLERR = 0x03;
public const int BIFF12_BOOL = 0x04;
public const int BIFF12_FLOAT = 0x05;
public const int BIFF12_STRING = 0x07;
public const int BIFF12_FORMULA_STRING = 0x08;
public const int BIFF12_FORMULA_FLOAT = 0x09;
public const int BIFF12_FORMULA_BOOL = 0x0A;
public const int BIFF12_FORMULA_BOOLERR = 0x0B;
public const int BIFF12_COL = 0x3C;
public const int BIFF12_WORKSHEET = 0x0181;
public const int BIFF12_WORKSHEET_END = 0x0182;
public const int BIFF12_SHEETVIEWS = 0x0185;
public const int BIFF12_SHEETVIEWS_END = 0x0186;
public const int BIFF12_SHEETVIEW = 0x0189;
public const int BIFF12_SHEETVIEW_END = 0x018A;
public const int BIFF12_SHEETDATA = 0x0191;
public const int BIFF12_SHEETDATA_END = 0x0192;
public const int BIFF12_SHEETPR = 0x0193;
public const int BIFF12_DIMENSION = 0x0194;
public const int BIFF12_SELECTION = 0x0198;
public const int BIFF12_COLS = 0x0386;
public const int BIFF12_COLS_END = 0x0387;
public const int BIFF12_CONDITIONALFORMATTING = 0x03CD;
public const int BIFF12_CONDITIONALFORMATTING_END = 0x03CE;
public const int BIFF12_CFRULE = 0x03CF;
public const int BIFF12_CFRULE_END = 0x03D0;
public const int BIFF12_ICONSET = 0x03D1;
public const int BIFF12_ICONSET_END = 0x03D2;
public const int BIFF12_DATABAR = 0x03D3;
public const int BIFF12_DATABAR_END = 0x03D4;
public const int BIFF12_COLORSCALE = 0x03D5;
public const int BIFF12_COLORSCALE_END = 0x03D6;
public const int BIFF12_CFVO = 0x03D7;
public const int BIFF12_PAGEMARGINS = 0x03DC;
public const int BIFF12_PRINTOPTIONS = 0x03DD;
public const int BIFF12_PAGESETUP = 0x03DE;
public const int BIFF12_HEADERFOOTER = 0x03DF;
public const int BIFF12_SHEETFORMATPR = 0x03E5;
public const int BIFF12_HYPERLINK = 0x03EE;
public const int BIFF12_DRAWING = 0x04A6;
public const int BIFF12_LEGACYDRAWING = 0x04A7;
public const int BIFF12_COLOR = 0x04B4;
public const int BIFF12_OLEOBJECTS = 0x04FE;
public const int BIFF12_OLEOBJECT = 0x04FF;
public const int BIFF12_OLEOBJECTS_END = 0x0580;
public const int BIFF12_TABLEPARTS = 0x0594;
public const int BIFF12_TABLEPART = 0x0595;
public const int BIFF12_TABLEPARTS_END = 0x0596;
//SharedStrings records
public const int BIFF12_SI = 0x13;
public const int BIFF12_SST = 0x019F;
public const int BIFF12_SST_END = 0x01A0;
//Styles records
public const int BIFF12_FONT = 0x2B;
public const int BIFF12_FILL = 0x2D;
public const int BIFF12_BORDER = 0x2E;
public const int BIFF12_XF = 0x2F;
public const int BIFF12_CELLSTYLE = 0x30;
public const int BIFF12_STYLESHEET = 0x0296;
public const int BIFF12_STYLESHEET_END = 0x0297;
public const int BIFF12_COLORS = 0x03D9;
public const int BIFF12_COLORS_END = 0x03DA;
public const int BIFF12_DXFS = 0x03F9;
public const int BIFF12_DXFS_END = 0x03FA;
public const int BIFF12_TABLESTYLES = 0x03FC;
public const int BIFF12_TABLESTYLES_END = 0x03FD;
public const int BIFF12_FILLS = 0x04DB;
public const int BIFF12_FILLS_END = 0x04DC;
public const int BIFF12_FONTS = 0x04E3;
public const int BIFF12_FONTS_END = 0x04E4;
public const int BIFF12_BORDERS = 0x04E5;
public const int BIFF12_BORDERS_END = 0x04E6;
public const int BIFF12_CELLXFS = 0x04E9;
public const int BIFF12_CELLXFS_END = 0x04EA;
public const int BIFF12_CELLSTYLES = 0x04EB;
public const int BIFF12_CELLSTYLES_END = 0x04EC;
public const int BIFF12_CELLSTYLEXFS = 0x04F2;
public const int BIFF12_CELLSTYLEXFS_END = 0x04F3;
//Comment records
public const int BIFF12_COMMENTS = 0x04F4;
public const int BIFF12_COMMENTS_END = 0x04F5;
public const int BIFF12_AUTHORS = 0x04F6;
public const int BIFF12_AUTHORS_END = 0x04F7;
public const int BIFF12_AUTHOR = 0x04F8;
public const int BIFF12_COMMENTLIST = 0x04F9;
public const int BIFF12_COMMENTLIST_END = 0x04FA;
public const int BIFF12_COMMENT = 0x04FB;
public const int BIFF12_COMMENT_END = 0x04FC;
public const int BIFF12_TEXT = 0x04FD;
//Table records
public const int BIFF12_AUTOFILTER = 0x01A1;
public const int BIFF12_AUTOFILTER_END = 0x01A2;
public const int BIFF12_FILTERCOLUMN = 0x01A3;
public const int BIFF12_FILTERCOLUMN_END= 0x01A4;
public const int BIFF12_FILTERS = 0x01A5;
public const int BIFF12_FILTERS_END = 0x01A6;
public const int BIFF12_FILTER = 0x01A7;
public const int BIFF12_TABLE = 0x02D7;
public const int BIFF12_TABLE_END = 0x02D8;
public const int BIFF12_TABLECOLUMNS = 0x02D9;
public const int BIFF12_TABLECOLUMNS_END= 0x02DA;
public const int BIFF12_TABLECOLUMN = 0x02DB;
public const int BIFF12_TABLECOLUMN_END = 0x02DC;
public const int BIFF12_TABLESTYLEINFO = 0x0481;
public const int BIFF12_SORTSTATE = 0x0492;
public const int BIFF12_SORTCONDITION = 0x0494;
public const int BIFF12_SORTSTATE_END = 0x0495;
//QueryTable records
public const int BIFF12_QUERYTABLE = 0x03BF;
public const int BIFF12_QUERYTABLE_END = 0x03C0;
public const int BIFF12_QUERYTABLEREFRESH = 0x03C1;
public const int BIFF12_QUERYTABLEREFRESH_END = 0x03C2;
public const int BIFF12_QUERYTABLEFIELDS = 0x03C7;
public const int BIFF12_QUERYTABLEFIELDS_END = 0x03C8;
public const int BIFF12_QUERYTABLEFIELD = 0x03C9;
public const int BIFF12_QUERYTABLEFIELD_END = 0x03CA;
//Connection records
public const int BIFF12_CONNECTIONS = 0x03AD;
public const int BIFF12_CONNECTIONS_END = 0x03AE;
public const int BIFF12_CONNECTION = 0x01C9;
public const int BIFF12_CONNECTION_END = 0x01CA;
public const int BIFF12_DBPR = 0x01CB;
public const int BIFF12_DBPR_END = 0x01CC;
Workbook part
How to proceed for each BIN part of interest is quite straight forward in fact. You can create a regular Excel 2007 file that uses a particular feature, for instance a chart, and save it both as .XLSX and .XLSB. Then you can unzip the content in separate folders, take the parts side-by-side and try to figure out which XML markup corresponds to which BIFF12 record identifier. The XML markup itself is not mandatory at all, it only makes the whole thing approachable for human beings...
I have done some of this work for a number of important BIN parts, but I concentrated on what was really needed to make sure I was able to read the content of cells. That's why, while records are for the most part identified and matched with their XML markup siblings, the record content itself is not. It's not either because I was too lazy to do it, or because it does not serve the goal, or because it can't be disambiguated that easily. For instance, if you take a look at a regular workbook BIN part below, you'll notice a few records with no associated XML markup :
// This is what a workbook part looks like in XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/5/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion lastEdited="4" lowestEdited="4" rupBuild="4017"/>
<workbookPr defaultThemeVersion="123820"/>
<bookViews>
<workbookView xWindow="360" yWindow="60" windowWidth="11295"
windowHeight="5580"/>
</bookViews>
<sheets>
<sheet name="Sheet1" sheetId="1" r:id="rId1"/>
<sheet name="Sheet2" sheetId="2" r:id="rId2"/>
<sheet name="Sheet3" sheetId="3" r:id="rId3"/>
</sheets>
<calcPr calcId="122211"/>
<webPublishing codePage="1252"/>
</workbook>
// This is what a workbook part looks like in BIN
83 01 00 80 01 14 04 04 b1 0f 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 99 01 0c 20 00 01 00 ac e3 01 00 00 00 00 00 87 01 00 9e 01
1d 68 01 00 00 3c 00 00 00 1f 2c 00 00 cc 15 00 00 58 02 00 00 00 00
00 00 00 00 00 00 78 88 01 00 8f 01 00 9c 01 28 00 00 00 00 00 00 00
00 01 00 00 00 04 00 00 00 72 00 49 00 64 00 31 00 06 00 00 00 53 00
68 00 65 00 65 00 74 00 31 00 9c 01 28 00 00 00 00 00 00 00 00 02 00
00 00 04 00 00 00 72 00 49 00 64 00 32 00 06 00 00 00 53 00 68 00 65
00 65 00 74 00 32 00 9c 01 28 00 00 00 00 00 00 00 00 03 00 00 00 04
00 00 00 72 00 49 00 64 00 33 00 06 00 00 00 53 00 68 00 65 00 65 00
74 00 33 00 90 01 00 9d 01 19 63 dd 01 00 01 00 00 00 64 00 00 00 fc
a9 f1 d2 4d 62 50 3f 01 00 00 00 6a 96 04 06 00 00 00 00 00 00 9a 01
01 00 a9 04 0b 07 00 03 60 00 00 00 e4 04 00 00 9b 01 01 00 84 01 00
// This is how, after breaking the BIN part in records, you can match
// records to the XML markup.
// Note that record identifiers are the two bytes on the left,
// the associated record length is surrounded by parentheses,
// and it's followed by the record content itself.
<workbook>
83 01 (00)
<fileVersion lastEdited="4" lowestEdited="4" rupBuild="4017"/>
(hint : 4017 = 0x0FB1)
80 01 (14) 04 04 b1 0f 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
<workbookPr defaultThemeVersion="123820"/>
(hint : 123820 = 0x0001E3AC)
99 01 (0c) 20 00 01 00 ac e3 01 00 00 00 00 00
<bookViews>
87 01 (00)
<workbookView xWindow="360" yWindow="60" windowWidth="11295"
windowHeight="5580"/>
9e 01 (1d) 68 01 00 00 3c 00 00 00 1f 2c 00 00 cc 15 00 00 58 02 00
00 00 00 00 00 00 00 00 00 78
</bookViews>
88 01 (00)
<sheets>
8f 01 (00)
<sheet name="Sheet1" sheetId="1" r:id="rId1"/>
9c 01 (28) 00 00 00 00 00 00 00 00
01 00 00 00 sheetid
04 00 00 00 length of string to follow
(in characters, not bytes)
72 00 49 00 64 00 31 00 relation identifier
06 00 00 00 length of string to follow
(in characters, not bytes)
53 00 68 00 65 00 65 00 74 00 31 00 sheetname
<sheet name="Sheet2" sheetId="2" r:id="rId2"/>
9c 01 (28) 00 00 00 00 00 00 00 00
02 00 00 00 sheetid
04 00 00 00 length of string to follow
(in characters, not bytes)
72 00 49 00 64 00 32 00 relation identifier
06 00 00 00 length of string to follow
(in characters, not bytes)
53 00 68 00 65 00 65 00 74 00 32 00 sheetname
<sheet name="Sheet3" sheetId="3" r:id="rId3"/>
9c 01 (28) 00 00 00 00 00 00 00 00
03 00 00 00 sheetid
04 00 00 00 length of string to follow
(in characters, not bytes)
72 00 49 00 64 00 33 00 relation identifier
06 00 00 00 length of string to follow
(in characters, not bytes)
53 00 68 00 65 00 65 00 74 00 33 00 sheetname
</sheets>
90 01 (00)
<externalReferences>
e1 02 (00)
<externalReference r:id="rId4" />
e3 02 (0c) 04 00 00 00 length of string to follow
(in characters, not bytes)
72 00 49 00 64 00 34 00
string representing a relation identifier
e5 02 (00)
ea 02 1c 02 00 00 00 00 00 00 00 ff ff ff ff ff ff ff ff 00 00 00 00
00 00 00 00 00 00 00 00
</externalReferences>
e2 02 (00)
<definedName name="externalrange" comment="">[1]Sheet1!$B$3
</definedName>
27 (3c) 00 00 grbits
00 00 00
ff ff ff ff nametype
0d 00 00 00 length of string to follow (in characters, not bytes)
65 00 78 00 74 00 65 00 72 00 6e 00 61 00 6c 00 72 00 61 00
6e 00 67 00 65 00 defined name
09 00 00 00 length of formula to follow (in bytes)
3a 01 00 02 00 00 00 01 00 formula
00 00 00 00 00 00 00 00
<definedName name="anotherrange">Sheet1!$B$9:$C$10</definedName>
27 (40) 00 00 grbits
00 00 00
ff ff ff ff nametype
0c 00 00 00 length of string to follow (in characters, not bytes)
61 00 6e 00 6f 00 74 00 68 00 65 00 72 00 72 00 61 00 6e
00 67 00 65 00 defined name
0f 00 00 00 length of formula to follow (in bytes)
3b 00 00 08 00 00 00 09 00 00 00 01 00 02 00 formula
00 00 00 00 ff ff ff ff
<definedName name="Database1" localSheetId="1" hidden="1">Sheet2!$A$1:$D$6</definedName>
27 (3a) 01 00 grbits
00 00 00
01 00 00 00 nametype
09 00 00 00 length of string to follow
(in characters, not bytes)
44 00 61 00 74 00 61 00 62 00 61 00 73 00 65
00 31 00 defined name
0f 00 00 00 length of formula to follow (in bytes)
3b 01 00 00 00 00 00 05 00 00 00 00 00 03 00 formula
00 00 00 00 ff ff ff ff
<definedName name="myrange">Sheet1!$C$2:$D$3</definedName>
27 (36) 00 00 grbits
00 00 00
ff ff ff ff nametype
07 00 00 00 length of string to follow
(in characters, not bytes)
6d 00 79 00 72 00 61 00 6e 00 67 00 65 00 defined name
0f 00 00 00 length of formula to follow (in bytes)
3b 00 00 01 00 00 00 02 00 00 00 02 00 03 00 formula
00 00 00 00 ff ff ff ff
<definedName name="_xlnm.Print_Area" localSheetId="0">Sheet1!$A$1:$E$7</definedName>
27 (3c) 20 00 grbits
00 00 00
00 00 00 00 nametype
0a 00 00 00 length of string to follow
(in characters, not bytes)
50 00 72 00 69 00 6e 00 74 00 5f 00 41 00 72
00 65 00 61 00 defined name
0f 00 00 00 length of formula to follow (in bytes)
3b 00 00 00 00 00 00 06 00 00 00 00 00 04 00 formula
00 00 00 00 ff ff ff ff
<definedName name="_xlnm.Print_Titles" localSheetId="0">Sheet1!$2:$3
</definedName>
27 (40) 20 00 grbits
00 00 00
00 00 00 00 nametype
0c 00 00 00 length of string to follow (in characters, not bytes)
50 00 72 00 69 00 6e 00 74 00 5f 00 54 00 69 00 74 00
6c 00 65 00 73 00 defined name
0f 00 00 00 length of formula to follow (in bytes)
3b 00 00 01 00 00 00 02 00 00 00 00 00 ff 3f formula
00 00 00 00 ff ff ff ff
<definedName name="myrange" hidden="1">Sheet1!$B$2:$B$3
</definedName>
27 (36) 01 00 grbits
00 00 00
ff ff ff ff nametype
07 00 00 00 length of string to follow (in characters, not bytes)
6d 00 79 00 72 00 61 00 6e 00 67 00 65 00 defined name
0f 00 00 00 length of formula to follow (in bytes)
3b 00 00 01 00 00 00 02 00 00 00 01 00 01 00 formula
00 00 00 00 ff ff ff ff
<calcPr calcId="122211"/>
9d 01 (19) 63 dd 01 00 01 00 00 00 64 00 00 00 fc a9 f1 d2 4d 62 50
3f 01 00 00 00 6a
96 04 (06) 00 00 00 00 00 00
9a 01 (01) 00
<webPublishing codePage="1252"/>
a9 04 (0b) 07 00 03 60 00 00 00 e4 04 00 00
9b 01 (01) 00
</workbook>
84 01 (00)
As you can see, I could not easily find the markup associated to record identifiers 0x0496, 0x019A and 0x019B above. While it's not blocking at this point, let's take a few moments to discuss the issue.
The big impedance mismatch
The example clearly shows that the person who wrote the workbook BIN part serializer knows more than the person who developed the workbook XML part serializer. Unfortunately, the opposite is also true since the XML markup contains namespaces and associated semantics that is for obvious reasons nowhere to be found in the BIN part. Where are we going?
Short of Microsoft providing the exact specs for the BIN serializers of every involved part, consumers and implementers of the file format will have to stick to replicating structures that cannot be understood because of a discrepancy between serializers. It goes all the way up to guessing default values of the objects you work with, that's why it's such a big deal. One of those well-known file format loopholes, the ones that can give a vendor a say in the format's future as well as any interoperability scenario, across Windows and non-Windows platforms.
How to read the workbook BIN part using C# or C++ ? Using the source code provided in an attachment to the article, you can easily do that.
// how to read the workbook part in C#
Hashtable h = new Hashtable();
// Register Workbook record handlers
h[C.BIFF12_DEFINEDNAME] = new DefinedNameRecord();
h[C.BIFF12_FILEVERSION] = new FileVersionRecord();
h[C.BIFF12_WORKBOOK] = new WorkbookRecord();
h[C.BIFF12_WORKBOOK_END] = new WorkbookEndRecord();
h[C.BIFF12_BOOKVIEWS] = new BookViewsRecord();
h[C.BIFF12_BOOKVIEWS_END] = new BookViewsEndRecord();
h[C.BIFF12_SHEETS] = new SheetsRecord();
h[C.BIFF12_SHEETS_END] = new SheetsEndRecord();
h[C.BIFF12_WORKBOOKPR] = new WorkbookPRRecord();
h[C.BIFF12_SHEET] = new SheetRecord();
h[C.BIFF12_CALCPR] = new CalcPRRecord();
h[C.BIFF12_WORKBOOKVIEW] = new WorkbookViewRecord();
h[C.BIFF12_EXTERNALREFERENCES] = new ExternalReferencesRecord();
h[C.BIFF12_EXTERNALREFERENCES_END] = new ExternalReferencesEndRecord();
h[C.BIFF12_EXTERNALREFERENCE] = new ExternalReferenceRecord();
h[C.BIFF12_WEBPUBLISHING] = new WebPublishingRecord();
// This class can be used to pass objects deeper in the reading flow
Workbook w = new Workbook();
// Note that the part is already unzipped and available in a regular folder
using (FileStream fs =
new FileStream(@"..\..\Excel12_files\Book1.xlsb\xl\workbook.bin",
FileMode.Open, FileAccess.Read))
{
// use the BCL to load the part in a byte[] buffer
byte[] bufferWorkbookPart = new BinaryReader(fs).ReadBytes((int)fs.Length);
// use our BIFF12 reader
Read(w, h, bufferWorkbookPart);
}
The workbook provides the list of worksheet references we are interested in. For each worksheet, we can see :
- The sheet ordering id. If you happen to change the order of worksheet tabs in an Excel file, those ids are reordered, and the rest of the file (relationships and parts) is left untouched.
- The sheet relationship identifier, or r:id. You can lookup this identifier in the associated workbook.bin.rels file to know what part it refers to.
- the sheet name itself, stored as a 4-byte length followed by a Unicode UCS2 string.
Worksheet part
The worksheet part describes the values in cells, along with formulas whenever it applies, and also describes objects mapping to cells or cell ranges (charts, pivot tables, ...). Here is an example of a reverse engineered worksheet which contains a double-precision float, a float formula which happens to return a "division by zero" error, and a copy of that cell elsewhere :
Reverse engineering numeric cell values with and without formulas.
// This is what a worksheet part looks like in XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/5/main"
xmlns:r=
"http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<dimension ref="D3:D6"/>
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0">
<selection activeCell="D4" sqref="D4"/>
</sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="15"/>
<cols>
<col min="4" max="4" width="12.5703125" customWidth="1"/>
</cols>
<sheetData>
<row r="3" spans="4:4"><c r="D3"><v>
2.123456789</v></c></row>
<row r="4" spans="4:4"><c r="D4" t="e"><f>5/E3</f><v>#DIV/0!</v>
</c></row>
<row r="6" spans="4:4"><c r="D6" t="e"><v>
#DIV/0!</v></c></row>
</sheetData>
<printOptions/>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75"
header="0.3" footer="0.3"/>
<headerFooter/>
</worksheet>
// This is how, after breaking the BIN part in records, you can match
// records to the XML markup.
// Note that record identifiers are the two bytes on the left,
// the associated record length is surrounded by parentheses,
// and it's followed by the record content itself.
<worksheet/>
81 01 (00)
<sheetPr/> (figured out)
93 01 (0f) c9 04 02 00 40 00 00 00 00 00 00 00 00 00 00
<dimension ref="D3:D6"/>
94 01 (10) 02 00 00 00 05 00 00 00 03 00 00 00 03 00 00 00
<sheetViews>
85 01 (00)
<sheetView tabSelected="1" workbookViewId="0">
89 01 (1e) dc 03 00 00 00 00 00 00 00 00 00 00 00 00 40 00 00 00 64
00 00 00 00 00 00 00 00 00 00 00
<selection activeCell="D4" sqref="D4"/>
98 01 (24) 03 00 00 00 02 00 00 00 02 00 00 00 00 00 00 00 01 00 00
00 02 00 00 00 02 00 00 00 02 00 00 00 02 00 00 00
</sheetView>
8a 01 (00)
</sheetViews>
86 01 (00)
<sheetFormatPr defaultRowHeight="15"/>
e5 03 (0c) ff ff ff ff 08 00 2c 01 00 00 00 00
<cols>
86 03 (00)
<col min="4" max="4" width="12.5703125" customWidth="1"/>
3c (12)
03 00 00 00 colmin (0-based)
03 00 00 00 colmax (0-based)
92 0c 00 00 width * 256
00 00 00 00 style (0-based)
02 00 flags
</cols>
87 03 (00)
<sheetData>
91 01 (00)
<row r="3" spans="4:4"></row>
00 (19) 02 00 00 00 00 00 00 00 2c 01 00 00 00 01 00 00 00 03 00 00
00 03 00 00 00
<c r="D3"><v>2.123456789</v></c>
05 (10)
03 00 00 00 col (0-based)
00 00 00 00 style (0-based)
1b cb b9 e9 d6 fc 00 40 float (IEEE 8 bytes)
<row r="4" spans="4:4"></row>
00 (19) 03 00 00 00 00 00 00 00 2c 01 00 00 00 01 00 00 00 03 00 00
00 03 00 00 00
<c r="D4" t="e"><f>5/E3</f><v>
#DIV/0!</v></c