<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Understanding Excel File Internals

Published on
12,384 Points
5,484 Views
4 Endorsements
Last Modified:
Approved
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).

Sample sheet
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.

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

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

Folder view
This is how the XML of the Sheet1 looks.

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

Adding code
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
4
Comment
Author:gbanik
1 Comment
LVL 13

Author Comment

by:gbanik
Yes. thats my blog.
0

Featured Post

CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Join & Write a Comment

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month