title: Get All Cells from Row Using the SAX Approch Open XML
body: <p>I just read a big xlsx file using the DOM approach with Open XML SDK. It works fine; however, it takes forever to do so. So I wanted to do the same thing using the SAX approach. However, I am not getting anywhere on this one.
  What I do in the DOM approach is that for each worksheet in the workbook I get the name of the sheet. Then I assume that the first row has all of the column names. Next, I create a class on-the-fly that has all of the properties that are listed in the first row. After that, I read the rest of the rows. For each row, I create a new object with the custom class that I created on the fly. Then I iterate through each of the cell in the row to populate the object with the values that I get. </p>
  
  <p>Here is the code that I am using to do the task that I just described using the DOM approach. </p>
  
  <pre><code>public static List&lt;Object&gt; ConvertExcelArchiveToListObjects(string filePath)
      {
          ... 
  using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
          {
              WorkbookPart wbPart = spreadsheetDocument.WorkbookPart;
              Sheets theSheets = wbPart.Workbook.Sheets;
  
              SharedStringTablePart sstPart = spreadsheetDocument.WorkbookPart.GetPartsOfType&lt;SharedStringTablePart&gt;().FirstOrDefault();
              ...
              var sheets = wbPart.Workbook.Sheets.Cast&lt;Sheet&gt;().ToList();
  
              foreach (WorksheetPart worksheetpart in wbPart.WorksheetParts)
              {
                  Worksheet worksheet = worksheetpart.Worksheet;
  
                  string partRelationshipId = wbPart.GetIdOfPart(worksheetpart);
                  var correspondingSheet = sheets.FirstOrDefault(
                      s =&gt; s.Id.HasValue &amp;&amp; s.Id.Value == partRelationshipId);
                  Debug.Assert(correspondingSheet != null);
                  // Grab the sheet name
                  string sheetName = correspondingSheet.GetAttribute("name", "").Value;
  
                  ...
  
                  dynamic expandoObjectClass = new ExpandoObject();
                  List&lt;Object&gt; listObjectsCustomClasses  = new List&lt;Object&gt;();
                  foreach (var dataRow in rowContent)
                  {
                      Type generatedType = typeBuilder.CreateType();
                      object generatedObject = Activator.CreateInstance(generatedType);
  
                      PropertyInfo[] properties = generatedType.GetProperties();
  
                      int propertiesCounter = 0;
  
                      // Loop over the values that we will assign to the properties
  
                      var rowCells = dataRow.Descendants&lt;Cell&gt;();
                      var value = string.Empty;
                      foreach (var rowCell in rowCells)
                      {
                          if (rowCell.DataType != null
                              &amp;&amp; rowCell.DataType.HasValue
                              &amp;&amp; rowCell.DataType == CellValues.SharedString
                              &amp;&amp; int.Parse(rowCell.CellValue.InnerText) &lt; ssTable.ChildElements.Count)
                          {
                              value = ssTable.ChildElements[int.Parse(rowCell.CellValue.InnerText)].InnerText ?? string.Empty;
                          }
                          else
                          {
                              if (rowCell.CellValue != null &amp;&amp; rowCell.CellValue.InnerText != null)
                              {
                                  value = rowCell.CellValue.InnerText;
                              }
                              else
                              {
                                  value = string.Empty;
                              }
                          }
                          properties[propertiesCounter].SetValue(generatedObject, value, null);
                          propertiesCounter++;
                      }
                      listObjectsCustomClasses.Add(generatedObject);
                  }
                  listObjects.Add(listObjectsCustomClasses);
              }
          }
          DateTime end = DateTime.UtcNow;
          Console.WriteLine("Measured time: " + (end - begin).TotalMinutes + " minutes.");
          return listObjects;
      }
  </code></pre>
  
  <p>However, whenever I read big xlsx files (with a size bigger than 30 MB), the aforementioned method takes a lot of time to execute. I have written this code to at least get the rows without digging deeper to get the cells in each row. </p>
  
  <pre><code>public static List&lt;Object&gt; ConvertExcelArchiveToListObjectsSAXApproach(string filePath)
      {
          DateTime begin = DateTime.UtcNow;
          List&lt;Object&gt; listObjects = new List&lt;Object&gt;();
          using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
          {
              WorkbookPart wbPart = spreadsheetDocument.WorkbookPart;
              Sheets theSheets = wbPart.Workbook.Sheets;
  
              SharedStringTablePart sstPart = spreadsheetDocument.WorkbookPart.GetPartsOfType&lt;SharedStringTablePart&gt;().FirstOrDefault();
              SharedStringTable ssTable = null;
              if (sstPart != null)
                  ssTable = sstPart.SharedStringTable;
  
              // Get the CellFormats for cells without defined data types
              WorkbookStylesPart workbookStylesPart = spreadsheetDocument.WorkbookPart.GetPartsOfType&lt;WorkbookStylesPart&gt;().First();
              CellFormats cellFormats = (CellFormats)workbookStylesPart.Stylesheet.CellFormats;
              var sheets = wbPart.Workbook.Sheets.Cast&lt;Sheet&gt;().ToList();
  
              foreach (WorksheetPart worksheetpart in wbPart.WorksheetParts)
              {
                  //Worksheet worksheet = worksheetpart.Worksheet;
                  OpenXmlPartReader reader = new OpenXmlPartReader(worksheetpart);
                  bool firstRow = false;
  
                  while (reader.Read())
                  {
                      if (reader.ElementType == typeof(Row))
                      {
                         ...
                      }
  
                      if (reader.ElementType != typeof(Worksheet)) // Dont' want to skip the contents of the worksheet
                          reader.Skip(); // Skip contents of any node before finding the first row.
                  }    DateTime end = DateTime.UtcNow;
          Console.WriteLine("Measured time: " + (end - begin).TotalMinutes + " minutes.");
          return listObjects;
      }
  </code></pre>
  
  <p>However, the breakpoint that I set within</p>
  
  <pre><code>if (reader.ElementType == typeof(Row))
                      {
                         ...
                      } 
  </code></pre>
  
  <p>does not even get hit. Any ideas as to what am I missing? Thank you!</p>