Understanding Excel File Internals

You may be aware that Excel (and Office) 2007 has some new file formats using OOXML and this article is aimed at exploring those new formats.

So how does Excel store the file contents - cell data including formulas and formats, conditional formatting, VBA code, etc. etc. Lets investigate. Create a new Excel file MyTest.xlsm and enter some dummy test data in the first sheet. Add some formulas and conditional formatting (if you want to really understand the details).


Let us now see how excel stores this data in the file. Open the file in notepad or a hex editor. Did you notice the first 2 characters? "PK". So Excel compresses its file contents. Now we know why there is not much difference if you compress an Office 2007 file.


Lets look into the compressed contents. Rename the file extension from .xlsm to .zip


Open the MyFile.zip file. Wow! its an extensive structure with xml files to store the workbook, worksheets, calculations, sharedstrings, etc.


This is how the XML of the Sheet1 looks.


Lets explore more. Lets go back to our original file and add some VBA code to it.


Save the file and redo the same steps as earlier to open the xml file structure. We now have another XML file called vbaProject.bin. This is the part that stores the VBA code and any objects such as userforms.

Some more interesting bits. Check how Excel stores its file contents. Look into the file sharedStrings.xml. It stores the strings at a global level, not in the sheet itself. Also look into how it stores the formulas and formatting. Try out things like like dragging a cell content (like Area 1) into 5 cells (producing Area 1, Area 2, Area 3...) and see how it stores the values... using ranges.

That also says that, it may be better to access Excel data directly from these XML files rather than through the Excel application model. We could have much more control and simplified logic for storing (for instance in database), transporting (eg client machine to server), opening and displaying simple content. Many possibilities here...

Well, I was pretty pleased that today I learned something new.

http://www.baniks.com/blog/2010/08/understanding-excel-file-internals.html