OpenXML SDK: reading Excel 2007/2010 documents


Handling Microsoft office document hasn’t aways been as easy as it is today, especially when we where going to run the software server side. We had to install almost full Office package to be able to run our software that read and wrote data from/to Word and Excel. Not an ideal world, as you know as a developer.

With Office 2007, the word, excel and powerpoint documents where stored in a new format, different from the propritary old format in prior version. The new format for Office 2007, and now Office 2010 stores it’s files in xml format inside a zip formatted file. This new format is based on the standards ECMA-273 and ISO/IEC 29500 Standard.

But a few years back Microsoft released the OpenXML SDK v2.0. Downloadthe SDK  here. See OpenXML Development Center for more information on OpenXML.

OpenXML code snippets for Visual Studio 2010.

This SDK enables you to parse the office documents as standard XML files, or using LINQ if you are more familiar with SQL like syntax. Some Excel OpenXML examples:

// Open Excel document OpenXML.xlsx for reading
var _doc = SpreadsheetDocument.Open(“OpenXML.xlsx”, false);
// Retrieve the worksheet named 'Sheet1' if it exists in the opened workbook 
var sheet = _doc.WorkbookPart.Workbook.Descendants<Sheet>()                        
                    .Where(s => s.Name == “Sheet1”).FirstOrDefault();
// Retrieve all rows for 'Sheet1'
var worksheetPart = (WorksheetPart)_doc.WorkbookPart.GetPartById(sheet.Id);
var allRows = worksheetPart.Worksheet.Descendants<Row>()
                           .Where(x => x.RowIndex > 1).ToList();
// Retrieve a specified row for 'Sheet1'
Row row = worksheetPart.Worksheet.Descendants<Row>().ElementAt(2);
var rowCells = row. Descendants<Cell>().ToList();
// Retrieve a specified cell value (cell B49)
WorkbookPart wbPart = _doc.WorkbookPart;
var wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));
var theCell = wsPart.Worksheet.Descendants<Cell>()
               . Where(c => c.CellReference == “B49”).FirstOrDefault();
var cellValue = CellToString(wbPart, theCell);
 
Happy Excel coding...
Advertisements

One thought on “OpenXML SDK: reading Excel 2007/2010 documents

  1. Pingback: 2012 – most popular blog posts | Sveroa's Developer Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s