Reading and Writing to Excel 2007 or Excel 2010 from C# - Part II: Basics

Published on 25 July 2009 Estimated reading time: 3 minutes

[Note: See the series index for a list of all parts in this series.]

To get support for the technologies we’ll use in this series, we need to add a few assembly references to our solution:

WindowsBase.dll
System.Xml
System.Xml.Linq
System.Core

Next, make sure you have the following namespaces added to your using/imports:

System.IO.Packaging: This provides the functionality to open the files.
System.Xml
System.Xml.Linq
System.Linq
System.IO

Right next, there’s an XML namespace (not to be confused with .NET code namespaces) we need to use for most of our queries: http://schemas.openxmlformats.org/spreadsheetml/2006/main and a second one we’ll use seldom: http://schemas.openxmlformats.org/officeDocument/2006/relationships. I dumped this into a nice static class as follows:

namespace XlsxWriter
{
    using System.Xml.Linq;

    internal static class ExcelNamespaces
    {
        internal static XNamespace excelNamespace = XNamespace.Get("http://schemas.openxmlformats.org/spreadsheetml/2006/main");
        internal static XNamespace excelRelationshipsNamespace = XNamespace.Get("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
    }
}

Next, we need to create an instance of the System.IO.Packaging.Package class (from WindowsBase.dll) and instantiate it by calling the static method Open:

Package xlsxPackage = Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite);

Note: This is where the file is opened—important because Excel locks an open file. If you try to open a locked file, a lovely exception is thrown. Always call the Close method on the package, for example:

xlsxPackage.Close();

When you open the XLSX file manually, the first file you’ll see is [Content_Types].xml, a manifest of all the files in the ZIP archive. Using Packaging, you can call the GetParts method to get a collection of Parts—essentially the files within the XLSX file.

new file dialog The contents of the XLSX if renamed to a ZIP file and opened.

The various files listed in the [Content_Types].xml file.

We’ll use the ContentType parameter to filter parts to the specific item we want to work with. In the second image above, note the ContentType for a worksheet: application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml.

Once we have all the parts of the XLSX file, we can navigate through it to extract the bits we need to read the content, involving two steps:

Finding the shared strings part: Another XML file that allows shared strings between worksheets. While optional for writing, it’s required for reading values.
Locating the worksheet: A separate part from the shared strings.

Let’s start with reading the shared strings part—the basis for reading any part later in this series. We need to get the first PackagePart with the type: application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml:

PackagePart sharedStringsPart = (from part in allParts
    where part.ContentType.Equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml")
    select part).Single();

Next, we extract the XML content from the PackagePart using GetStream, load it into an XmlReader, and then into an XElement—a slightly convoluted but efficient process:

XElement sharedStringsElement = XElement.Load(XmlReader.Create(sharedStringsPart.GetStream()));

Now we can work with the XElement to perform real operations. In the next parts, we’ll explore what we can do with it and how to translate a single part into an actual sheet.