Library tutorials & articles
Read and write Open XML files (MS Office 2007)
- Introduction
- Excel 2007 Open XML Specifics
- Implementation
Excel 2007 Open XML Specifics
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 note 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 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 file you will notice that all of the sheet data is inside <sheetData> element. For every row there is a <row> element, for every cell there is a element. Finally, value of the cell is stored in a element.
However, real world XML is never simple as schoolbook XML. You will notice that numbers get encoded as numbers inside element:
<c r="A1">
<v>100</v>
</c>
However, string value (like “John”), also gets encoded as number:
<c r="B1" t="s">
<v>0</v>
</c>
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 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 entire workbook, not just 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 better using some commercial component which does all tedious work for you.
Zeljko Svedic (Željko Švedic) works as a lead developer at GemBox Software. He primarily works on GemBox.Spreadsheet, a .NET component for reading and writing native XLS files. He got interested in...
Related articles
Related discussion
-
Creating a Windows Service in VB.NET
by davidvanr (108 replies)
-
hey developers out there
by pitsophera (0 replies)
-
How can i develop opc server using .net?
by vairajaig (1 replies)
-
High-Performance .NET Application Development & Architecture
by Manjot Bawa (0 replies)
-
write to XML file vb.net
by acnetonline (2 replies)
Related podcasts
-
Vote of No Confidence
In this episode Ward Bell (from IdeaBlade) and Jeremy Miller discuss the Vote of No Confidence on Microsoft's Entity Framework and what it means for the Alt.NET and .NET communities.This episode does not have a sponsor, so if you are interested in sponsoring the podcast please contact us.Ward and...
Events coming up
-
Nov
18
15 Minutes of Fame
Dresher, United States
This is a yearly tradition. We select 10 of the favorite speakers from monthly meetings, code camps, and hands on labs. Each one does a 15 minute talk on their favorite .NET technology. This is our 10th anniversary so we plan a gala event with special prizes and refreshments.
Not a bad post. I was looking for a way to create Excel 2007 files from code and came accross the article. However, seeing how much work was involved - I thought it would be much easier to create an Excel 2003 file and let my Office 2007 using colleagues open the file in compatibility mode.
Fortunately, I came accross another article, which explains how to use the OfficeOpenXml package provided by Microsoft. Using this namespace is a breeze!
For more info - check here: http://www.codeproject.com/office/ExcelPackage.asp
At work I've been tasked with opening an xlsx file and extracting the information.
Many thanks for this article which has made understanding the new file format easier.
Excellent article. It will save me a least a couple of days of digging this information out from msdn.
This thread is for discussions of Read and write Open XML files (MS Office 2007).