Excel 2007 extends on the basis of Open Packaging
Conventions by adding its own application-specific XML types. Reference
schemas for all XML files used in Office can be downloaded from MSDN, but some
things are still open to change until the final Excel 2007 release.
We just want to write/read worksheet data, so we need to
look in the folder “\xl\worksheets” inside XLSX file where all the worksheets
are located. For every worksheet there is a separate XML file, “sheet1.xml,” “sheet2.xml”
and so on. When you open such a file you will notice that all of the sheet
data is inside the <sheetData> element. For every row there is a
<row> element and for every cell there is a <c> element. Finally, the
value of the cell is stored in a <v> element.
However, real world XML is never as simple as schoolbook
XML. You will notice that numbers get encoded as numbers inside <v>
However, string value (like “John”) also gets encoded as a
<c r="B1" t="s">
That is because MS Excel uses internal table of unique
strings (for performance reasons). Zero is an index of that string in an
internal table of strings and attribute t="s" tells us that the underlying
type is a string, not a number. So where is the table of unique strings
located? It is in “\xl\sharedStrings.xml” XML file and contains all strings
used in the entire workbook, not just a specific worksheet.
This approach is used for many other things: cell styles, borders,
charts, number formats, etc. In fact, that becomes the major programming
problem when working with XLSX files- updating and maintaining various tables
of some unique Excel objects. In this article we will just write/read data
values, but if you require some complex formatting you should probably be using
some commercial component which does all the tedious work for you.