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