Read and Write Open XML Files Using MS Office 2007
 
Published: 06 Nov 2006
Abstract
In this article Zeljko explains the basics of the Open XML file format and specifically XLSX format, the new format for Excel 2007. This is done with the help of a demo application written in C# using Visual Studio 2005.
by Zeljko Svedic
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 32196/ 54

Introduction

With Office 2007, Microsoft decided to change the default application formats from old, proprietary, closed formats (DOC, XLS, PPT) to new, open and standardized XML formats (DOCX, XLSX and PPTX).  New formats share some similarities with old Office XML formats (WordML, SpreadsheetML) and some similarities with competing OpenOffice.org OpenDocument formats, but there are many differences.  Since new formats will be the default in Office 2007 and Microsoft Office is the most predominant office suite, these formats are destined to be popular and you will probably have to deal with them sooner or later.

This article will explain the basics of Open XML file format and specifically XLSX format, the new format for Excel 2007.  Presented is a demo application which writes/reads tabular data to/from XLSX files.  The application is written in C# using Visual Studio 2005.  Created XLSX files can be opened using Excel 2007 Beta (we used build 12.0.3820.1003).

Microsoft Open XML format

Every Open XML file is essentially a ZIP archive containing many other files.  Office-specific data is stored in multiple XML files inside that archive.  This is in direct contrast with old WordML and SpreadsheetML formats which were single, non-compressed XML files.  Although more complex, the new approach offers a few benefits.

•        You do not need to process entire files in order to extract specific data.

•        Images and multimedia are now encoded in native format, not as text streams.

•        Files are smaller as a result of compression and native multimedia storage.

In Microsoft’s terminology, an open XML ZIP file is called a package.  Files inside that package are called parts.  It is important to know that every part has a defined content type and there are no default type presumptions based on the file extension.  Content type can describe anything, application XML, user XML, images, sounds, video or any other binary objects.  Every part must be connected to some other part using a relationship.  Inside the package are special XML files with “.rels” extension which define relationship between parts.  There is also a start part (sometimes called “root”, which is a bit misleading because graph containing all parts does not have to be a tree structure), so the entire structure looks like Figure 1.

Figure 1

 

To cut a long story short, in order to read the data from an Open XML file you need to:

1)       Open the package as a ZIP archive; any standard ZIP library will do.

2)       Find parts that contain data you want to read.  You can navigate through relationship graph (more complex) or you can presume that certain parts have a defined name and path (Microsoft can change that in the future).

3)       Read parts you are interested in using standard XML library (if they are XML) or some other method (if they are images, sounds or of some other type).

On the other hand, if you want to create a new Open XML file, you need to:

1)       Create/get all necessary parts by using some standard XML library (if they are XML), by copying them or by using some other method.

2)       Create all relationships by creating “.rels” files.

3)       Create content types by creating a “[Content_Types].xml” file.

4)       Package everything into a ZIP file with an appropriate extension (DOCX, XLSX or PPTX), any standard ZIP library will do.

The whole story about packages, parts, content types and relations is the same for all Open XML documents (regardless of their originating application) and Microsoft refers to it as Open Packaging Conventions.

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 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> element.

Listing 1

<c r="A1">
<v>100</v> 
</c>

However, string value (like “John”) also gets encoded as a number.

Listing 2 

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

Implementation

Our demo is a Web Forms application (see Listing 2) written in C# using Visual Studio 2005. Since there is no support for ZIP files in .NET Framework 2.0 (only for ZIP algorithm), our demo is using an open-source ZIP library called SharpZipLib (available at: http://www.icsharpcode.net/OpenSource/SharpZipLib).  For demonstration purposes we will extract entire ZIP files to TEMP folder, so we can examine contents of that folder and files while debugging demo application.  In a real world application you may want to avoid extracting to a temporary folder and just read to/write from the ZIP file directly.

For XML processing the choice is simple.  For reading XML files we use XmlTextReader class and for writing we use XmlTextWriter class.  Both come with .NET Framework, but you can also use any other XML processing library.

Figure 2

Data reading

We want to read a simple “In.xlsx” file (in the “Input” folder) and copy its contents to the DataTable.  That file contains a list of people with their first and last names (text values) and their IDs (number values).  When “Read input .xlsx file” button in clicked, the following code is executed:

Listing 3

protected void buttonReadInput_Click(object sender, EventArgs e)
{
  // Input file name.
  string fileName = Request.PhysicalApplicationPath + @"..\Input\In.xlsx";
 
  // Delete contents of the temporary directory.
  ExcelRW.DeleteDirectoryContents(tempDir);
 
  // Unzip input XLSX file to the temporary directory.
  ExcelRW.UnzipFile(fileName, tempDir);
 
  // Open XML file with table of all unique strings used in the workbook..
  FileStream fs = new FileStream(tempDir + @"\xl\sharedStrings.xml",
    FileMode.Open, FileAccess.Read);
  /* ..and call helper method that parses that XML and returns an array of
    Strings*/
  ArrayList stringTable = ExcelRW.ReadStringTable(fs);
 
  // Get DataTable with people from session variable.
  DataTable data = (DataTable)Session["people"];
 
  // Open XML file with worksheet data..
  fs = new FileStream(tempDir + @"\xl\worksheets\sheet1.xml", FileMode.Open,
    FileAccess.Read);
  /* ..and call helper method that parses that XML and fills DataTable with
     values. */
  ExcelRW.ReadWorksheet(fs, stringTable, data);
 
  // Update GridView.
  this.SetDataBinding();
}

Nothing unusual happens here.  XLSX file is unzipped to the TEMP folder and then necessary XML parts (now files) are processed.  File “sharedStrings.xml” contains a global table of unique strings while file “sheet1.xml” contains data for the first sheet.  Helper methods are pretty straightforward XML reading code; you can download demo application code to examine them in more detail.

If everything is OK, after you click the button all data will show up in the GridView.

Data writing

Now we want to write data from a DataTable to the “Out.xlsx” file in the “Output” folder.  When “Write output .xlsx file” button is clicked, the following code is executed:

Listing 4

protected void buttonWriteOutput_Click(object sender, EventArgs e)
{
  // Output file name.
  string fileName = Request.PhysicalApplicationPath + @"..\Output\Out.xlsx";
 
  // Delete contents of the temporary directory.
  ExcelRW.DeleteDirectoryContents(tempDir);
 
  // Unzip template XLSX file to the temporary directory.
  ExcelRW.UnzipFile(templateFile, tempDir);
 
  // We will need two string tables; a lookup Hashtable for fast searching and
  // an ordinary ArrayList where items are sorted by their index.
  Hashtable lookupTable;
 
  DataTable data = (DataTable)Session["people"];
 
  // Call helper methods which creates both tables from input data.
  ArrayList stringTable = ExcelRW.CreateStringTables(data, out lookupTable);
 
  // Create XML file..
  FileStream fs = new FileStream(tempDir + @"\xl\sharedStrings.xml",
    FileMode.Create);
  // ..and fill it with unique strings used in the workbook
  ExcelRW.WriteStringTable(fs, stringTable);
 
  // Create XML file..
  fs = new FileStream(tempDir + @"\xl\worksheets\sheet1.xml", FileMode.Create);
  // ..and fill it with rows and columns of the DataTable.
  ExcelRW.WriteWorksheet(fs, data, lookupTable);
 
  // ZIP temporary directory to the XLSX file.
  ExcelRW.ZipDirectory(tempDir, fileName);
 
  if (this.CheckBoxDownload.Checked)
  {
    // Stream XLS to browser.
    Response.Clear();
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition""attachment; filename=Out.xlsx");
    Response.WriteFile(fileName);
    Response.End();
  }
}

This time code is a bit more complicated.  In order not to generate all necessary parts needed for XLSX file, we decide to use a template file.  We extract template file to the temporary folder and then just change XML parts containing shared string table and worksheet data.  All other parts, relationships and content types stay the same.  We do not need to generate any of that.  Note that we use two string tables: a lookup Hashtable for fast searching and an ordinary ArrayList where items are sorted by their index.  We could pull it out only with ArrayList, but then we would need to search entire ArrayList every time we add a new string (to check if it is already there).  CreateStringTables() helper method builds both string tables, the WriteStringTable() helper method writes string table XML and WriteWorksheet() helper method writes worksheet data XML.

Again, download demo application code to examine helper methods in more detail.

Downloads

[Download Sample]

You can download the latest version of the demo application (together with the C# source code) by clicking the Download Sample link.

The current version works with Beta version of Microsoft Excel 2007.  If necessary, we will be providing an update for the final Excel 2007 version as soon as we get a hold on it.

Alternative ways

As always in programming, there is more than one method to achieve the same thing.

You could use Office automation to start an instance of Excel 2007 (or any other Office application) and then use interop calls to create a document and save it.  However, using automation has some drawbacks I have already written about (see at: http://www.gemboxsoftware.com/GBSpreadsheet.htm#Automation).

The next version of .NET Framework (codename WinFX) will have support for Open Packaging Conventions (package handling and navigating the relationships), but it seems there will be no support for accessing application specific data so you will still need to process XML parts manually.

As another option, you could use some third party component which will come with support for Open XML format.  This will probably cost you some money, but has the advantage that usually more than one format (for example; XLS, XLSX, CSV) are supported within the same API, so your application will be able to target different file formats using the same code.

Summary

New Open XML formats are a big step forward compared with from old, proprietary, closed formats (DOC, XLS, PPT).  They are a little bit more complicated to generate than old Office XML formats, but they are much more powerful and flexible.  They are here to stay, so you should invest your time to learn how to work with them.



User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 6:28:54 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search