Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
13. March 2011 17:52

I had heard good things about OpenXML, but until now I didn’t have time to give it a try. After attending a rather intimate session on the topic at the MVP Summit, I realized I should look into it. For those of you like me who haven’t kept up with the news, the general idea is that, since the release of Office 2007, Office files are no longer saved as obscure proprietary files: they are essentially zipped xml files. If you rename an Excel file from MyFile.xlsx to MyFile.zip and open it, you will see that it is simply a collection of xml files, describing the various parts of your Workbook and their relationships. This has a few interesting implications, one of them being that you can create or edit an Excel file without using Excel, or even having Excel installed on your machine.

The OpenXML SDK is a free library which provides strongly typed .NET classes to manipulate these files without having to deal with raw XML, and are LINQ-friendly, which is awesome.

One scenario where this comes very handy is if you have some form of a .NET application which needs to read input data from an Excel file; another interesting case is a .NET application which needs to produce some Office outputs for the user. Rather than launch an instance of the Office application and use the COM Interop, you can perform all these tasks safely in .NET, without having to worry about cleanly closing the application.

In line with the first scenario, my initial goal was to see if I could read the contents of an Excel Workbook with a console app. Rather than going into lengthy explanations, here is the code I ended up with, which borrows heavily from the samples provided with the SDK:

namespace OpenXmlApp
{
using System;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;

public static class Program
{
private static void Main(string[] args)
{
var filePath = @"C:/Tests/protectedFile.xlsx";
using (var document = SpreadsheetDocument.Open(filePath, false))
{
var workbookPart = document.WorkbookPart;
var workbook = workbookPart.Workbook;

var sheets = workbook.Descendants<Sheet>();
foreach (var sheet in sheets)
{
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
var sharedStringPart = workbookPart.SharedStringTablePart;
var values = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();

var cells = worksheetPart.Worksheet.Descendants<Cell>();
foreach (var cell in cells)
{
Console.WriteLine(cell.CellReference);
// The cells contains a string input that is not a formula
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
var index = int.Parse(cell.CellValue.Text);
var value = values[index].InnerText;
Console.WriteLine(value);
}
else
{
Console.WriteLine(cell.CellValue.Text);
}

if (cell.CellFormula != null)
{
Console.WriteLine(cell.CellFormula.Text);
}
}
}
}

}
}
}