title: C# OPEN XML: empty cells are getting skipped while getting data from EXCEL to DATATABLE
body: <p><strong>Task</strong></p>
  
  <p>Import data from <code>excel</code> to <code>DataTable</code></p>
  
  <p><strong>Problem</strong></p>
  
  <p>The cell that doesnot contain any data are getting skipped and the very next cell that has data in the row is used as the value of the empty colum.
  E.g</p>
  
  <p><strong>A1</strong> is empty <strong>A2</strong> has a value <code>Tom</code> then while importing the data <code>A1</code> get the value of <strong>A2</strong> and <strong>A2</strong> remains empty</p>
  
  <p>To make it very clear I am providing some screen shots below</p>
  
  <p><strong>This is the excel data</strong></p>
  
  <p><a href="https://i.stack.imgur.com/77vnq.png" rel="noreferrer"><img src="https://i.stack.imgur.com/77vnq.png" alt="enter image description here"></a></p>
  
  <p><strong>This is the DataTable after importing the data from excel</strong>
  <a href="https://i.stack.imgur.com/AI0Yp.png" rel="noreferrer"><img src="https://i.stack.imgur.com/AI0Yp.png" alt="enter image description here"></a></p>
  
  <p><strong>Code</strong></p>
  
  <pre><code>public class ImportExcelOpenXml
  {
      public static DataTable Fill_dataTable(string fileName)
      {
          DataTable dt = new DataTable();
  
          using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, false))
          {
  
              WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
              IEnumerable&lt;Sheet&gt; sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild&lt;Sheets&gt;().Elements&lt;Sheet&gt;();
              string relationshipId = sheets.First().Id.Value;
              WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
              Worksheet workSheet = worksheetPart.Worksheet;
              SheetData sheetData = workSheet.GetFirstChild&lt;SheetData&gt;();
              IEnumerable&lt;Row&gt; rows = sheetData.Descendants&lt;Row&gt;();
  
              foreach (Cell cell in rows.ElementAt(0))
              {
                  dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
              }
  
              foreach (Row row in rows) //this will also include your header row...
              {
                  DataRow tempRow = dt.NewRow();
  
                  for (int i = 0; i &lt; row.Descendants&lt;Cell&gt;().Count(); i++)
                  {
                      tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants&lt;Cell&gt;().ElementAt(i));
                  }
  
                  dt.Rows.Add(tempRow);
              }
  
          }
  
          dt.Rows.RemoveAt(0); //...so i'm taking it out here.
  
          return dt;
      }
  
  
      public static string GetCellValue(SpreadsheetDocument document, Cell cell)
      {
          SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
          string value = cell.CellValue.InnerXml;
  
          if (cell.DataType != null &amp;&amp; cell.DataType.Value == CellValues.SharedString)
          {
              return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
          }
          else
          {
              return value;
          }
      }
  }
  </code></pre>
  
  <p><strong>My Thoughts</strong></p>
  
  <p>I think there is some problem with</p>
  
  <p><code>public IEnumerable&lt;T&gt; Descendants&lt;T&gt;() where T : OpenXmlElement;</code></p>
  
  <p>In case I want the count of columns using <code>Descendants</code></p>
  
  <pre><code>IEnumerable&lt;Row&gt; rows = sheetData.Descendants&lt;&lt;Row&gt;();
  int colCnt = rows.ElementAt(0).Count();
  </code></pre>
  
  <p>OR</p>
  
  <p>If I am getting the count of rows using <code>Descendants</code></p>
  
  <pre><code>IEnumerable&lt;Row&gt; rows = sheetData.Descendants&lt;&lt;Row&gt;();
  int rowCnt = rows.Count();`
  </code></pre>
  
  <p>In both cases <code>Descendants</code> is skipping the empty cells</p>
  
  <p>Is there any alternative of <code>Descendants</code>.</p>
  
  <p>Your suggestions are highly appreciated</p>
  
  <p>P.S: I have also thought of getting the cells values by using column names like <strong>A1, A2</strong> but in order to do that I will have to get the exact count of columns and rows which is not possible by using <code>Descendants</code> function.</p>