title: OpenXML with Excel: How can I count the total number of rows if there is empty row?
body: <p>I'm new to XML and I've tried to understand the format of XML and have tried it myself and searched online. But can't seem to find an example to guide me to what I need. So I'll start with the question. I've unzipped an XLSX excel file and this is the ending part where it's giving me a different count from what I see in excel:</p>
  
  <pre><code>    &lt;row r="2306" spans="1:20" x14ac:dyDescent="0.3"&gt;
          &lt;c r="A2306" s="2"&gt;
              &lt;v&gt;42872&lt;/v&gt;
          &lt;/c&gt;&lt;c r="B2306" t="s"&gt;
              &lt;v&gt;53&lt;/v&gt;
          &lt;/c&gt;&lt;c r="C2306"&gt;
              &lt;v&gt;12002398&lt;/v&gt;
          &lt;/c&gt;&lt;c r="D2306" t="s"&gt;
              &lt;v&gt;15&lt;/v&gt;
          &lt;/c&gt;&lt;c r="E2306" t="s"&gt;
              &lt;v&gt;20&lt;/v&gt;
          &lt;/c&gt;&lt;c r="F2306" t="s"&gt;
              &lt;v&gt;0&lt;/v&gt;
          &lt;/c&gt;&lt;c r="G2306" t="s"&gt;
              &lt;v&gt;1&lt;/v&gt;
          &lt;/c&gt;&lt;c r="H2306" t="s"&gt;
              &lt;v&gt;2&lt;/v&gt;
          &lt;/c&gt;&lt;c r="I2306"&gt;
              &lt;v&gt;120002122&lt;/v&gt;
          &lt;/c&gt;&lt;c r="J2306"&gt;
              &lt;v&gt;10&lt;/v&gt;
          &lt;/c&gt;&lt;c r="K2306" s="3" t="s"&gt;
              &lt;v&gt;50&lt;/v&gt;
          &lt;/c&gt;&lt;c r="L2306" s="3" t="s"&gt;
              &lt;v&gt;18&lt;/v&gt;
          &lt;/c&gt;&lt;c r="M2306"&gt;
              &lt;v&gt;2800&lt;/v&gt;
          &lt;/c&gt;&lt;c r="N2306" t="s"&gt;
              &lt;v&gt;3&lt;/v&gt;
          &lt;/c&gt;&lt;c r="O2306" s="4"&gt;
              &lt;v&gt;6.15&lt;/v&gt;
          &lt;/c&gt;&lt;c r="P2306" s="5"&gt;
              &lt;v&gt;17220&lt;/v&gt;
          &lt;/c&gt;&lt;c r="Q2306" t="s"&gt;
              &lt;v&gt;4&lt;/v&gt;
          &lt;/c&gt;&lt;c r="R2306" t="s"&gt;
              &lt;v&gt;5&lt;/v&gt;
          &lt;/c&gt;
          &lt;c r="S2306" t="s"&gt;
              &lt;v&gt;342&lt;/v&gt;
          &lt;/c&gt;
          &lt;c r="T2306" t="s"&gt;
              &lt;v&gt;6&lt;/v&gt;
          &lt;/c&gt;
      &lt;/row&gt;
      &lt;row r="10451" spans="1:1" x14ac:dyDescent="0.3"&gt;
          &lt;c r="A10451" s="1"/&gt;
      &lt;/row&gt;
      &lt;row r="23679" spans="1:1" x14ac:dyDescent="0.3"&gt;
          &lt;c r="A23679" s="1"/&gt;
      &lt;/row&gt;
  </code></pre>
  
  <p>Here in the XML file and I can see 2306 rows in EXCEL, but when I use OpenXML SAX method reading ROW attribute and calculate the rows, I get 2308. Extra 2 rows of data which is produced by the empty row 10451 and 23679. Is there anyway I can ignore these 2 rows? I'm trying to check if the cell has cellValue , if there is, I will skip to the next row and increment totalRow. However, I've tried using reader.skip() and it only skips 1 cell. Anyway I can skip the whole row and go to the next? Here's my code for the time being, hope someone can guide me. Thanks.</p>
  
  <pre><code>// The SAX approach.
      static void ReadExcelFileSAX(string fileName)
      {
          using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
          {
              WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
              WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
  
              OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
              int totalRow = 0;
              while (reader.Read())
              {
                  if (reader.ElementType == typeof(Row))
                  {
                      reader.ReadFirstChild();
                      do
                      {
                          if (reader.ElementType == typeof(Cell))
                          {
                              Cell c = (Cell)reader.LoadCurrentElement();
                              if(c.CellReference != null)
                              {
                                  string column = c.CellReference;
                                  column = column.Substring(0,1);
                                  if(column == "B")
                                  {
                                      //MessageBox.Show(GetCellValue(workbookPart.SharedStringTablePart.SharedStringTable, c));
                                      totalRow++;
                                  }
                              }                                
                          }                            
                      }
                      while (reader.ReadNextSibling());
                  }
              }
              MessageBox.Show("Total Row: " + totalRow.ToString());
          }
      }
  
      public static string GetCellValue(SharedStringTable sharedStringTable, Cell cell)
      {
          string value = cell.CellValue.InnerText;
  
          if (cell.DataType != null &amp;&amp; cell.DataType.Value == CellValues.SharedString)
          {
              return sharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
          }
          else
          {
              return value;
          }
      }
  </code></pre>