Consistent procedure to extract Excel document data using Open XML SDK?

Question

If anything, this serves as my notes for parsing/importing Open XML Excel file data.

I created code below using Open XML SDK 2.0 that extracts Excel 2007 data from one Excel file successfully but fails returning empty strings on a different but similar Excel file. I don't know if I'm using the right methods in the right hierarchy correctly or if this changes depending on the Excel file version (2007, 2010, 2003 with compatibility, etc.).

Is there a consistent method for extracting data from Excel files using Open XML SDK 2/2.5?

I made some LinqPad examples below commented with my findings/example questions.

    // Obtain a reference to the spreadsheet file 
var doc = SpreadsheetDocument.Open(@"C:\MyExcelFile.xlsx", false);

    // Only one WorkbookPart per spreadsheet
    // Parts have a common Root property and differing methods to set them
    // like WorkbookPart.Workbook sets the root for WorkbookPart
var workbookpart = doc.WorkbookPart;

    // Find sheet names and ids
var sheets = doc.WorkbookPart.Workbook.Sheets;
var sheetname = sheets.Descendants<Sheet>().FirstOrDefault().Name.Value;
var sheetId = sheets.Descendants<Sheet>().FirstOrDefault().SheetId.Value;
var id = sheets.Descendants<Sheet>().FirstOrDefault().Id.Value;

    // "Parts" hold collections even if that part only has one sub part
    // How many Worksheet parts should we expect?  One per Sheet?

var sheet1id = doc.WorkbookPart.Workbook.Descendants<Sheet>()
               .Where(p => p.Name.Value == "Sheet1")
               .Select(q => q.Id.Value).FirstOrDefault();

var worksheetpart = (WorksheetPart) workbookpart.GetPartById( sheet1id);
//  Removed below because I don't know how to read WorksheetPart id
//var worksheetpart = workbookpart.WorksheetParts.FirstOrDefault();

    // Worksheet is the root of WorkSheetPart
    // Worksheet.Descendants<Column>() has usable min, max, width, customWidth
    // Worksheet.Descendants<Row>() (or any other <type>) is empty
    // Why is Worksheet.SheetDimension empty? How do you determine sheet size?
    // Other than doc.WorkbookPart.GetPartById(sheetId) I have no idea
    // how to determine this worksheet's id, name, or sheetid
    // maybe their index as an Array matches the Sheets Id?
var worksheet = worksheetpart.Worksheet;

    // Expect multiple SheetData?  Why?
    // .Descendants< ... >() retrieves objects cast to their proper type
var sheetdata = worksheet.Descendants<SheetData>().FirstOrDefault();

    // Is this where we should access Rows or under Worksheet?
var row = sheetdata.Descendants<Row>().FirstOrDefault();

var cell = row.Descendants<Cell>().FirstOrDefault();

    // Print out the Cell's values (Need to reference shared values elsewhere)
cell.CellReference.Value.Dump();
cell.DataType.Value.Dump();
cell.CellValue.Text.Dump();

    // Close the spreadsheet
doc.Close();

(add LinqPad Query References of DocumentFormat.OpenXml, WindowsBase.dll and Namespace imports of DocumentFormat.OpenXml, DocumentFormat.OpenXml.Spreadsheet, and DocumentFormat.OpenXml.Packaging to make this work.)

Encarmine Encarmine · Accepted Answer · 2012-11-16T05:27:08

As I don't know, what you want to do with data, I'll post example that reads values from all Cells in all sheets. Code taken from article, written by Mathias Brandewinder here

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);                    
                  }
               }
            }
         }

Consistent procedure to extract Excel document data using Open XML SDK?

1 Answers