0

Task

Import data from excel to DataTable

Problem

Some of Rows that does not contain any data are getting skipped and the very next Row that has data in the row is used as the value of the empty Row

In Excel Totally in have 37 Rows when i use openxml to convert excel to Datatable it skipped empty rows and read 29 rows only

WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Descendants<Row>();
foreach (Row row in rows) //this will also include your header row...
{
    DataRow tempRow = dt.NewRow();
    int ko = row.Descendants<Cell>().Count();
    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
    {
        tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
    }
    dt.Rows.Add(tempRow);
}
Chris Catignani
  • 5,040
  • 16
  • 42
  • 49
rajadurai
  • 3
  • 3
  • Maybe the same problem as this question: https://stackoverflow.com/questions/3837981/reading-excel-open-xml-is-ignoring-blank-cells – johey Jan 31 '20 at 12:18
  • Empty Cells not issue for me. it's coming perfectly. when i have a Empty ROWS it's skipped the rows. In Excel Totally in have 37 Rows It's read 29 rows only. – rajadurai Jan 31 '20 at 14:41
  • Did my answer below help you solve the problem? Or do you need more input to solve it? – Thomas Barnekow Feb 02 '20 at 09:51

1 Answers1

2

If you look at the Open XML markup of an Excel worksheet, you will see that empty rows don't even exist in the markup. This means that when you are reading your rows in your foreach loop, you will skip those empty, non-existent rows right there.

If you want those empty rows in your DataTable, you will have to read each existing row, keeping track of the last row number you have seen. If there is a gap between the current row number and the last row number you have seen, you need to fill in the gap, adding empty DataRow instances before you add a new DataRow for the current row.

Update 2020-02-03

To find out how you can determine the row numbers, you should look at the Open XML markup of a sample worksheet. For example, the following markup shows a reduced and simplified example worksheet with just the sheetData element and a number of row child elements. You will see that each row element (instance of Row class) has an attribute called r (RowIndex property of Row class), which specifies the row index. In this example, we see rows 2, 3, 5, and 8, so we see that rows 4, 6, and 7 are missing.

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <sheetData>
    <row r="2" spans="2:2" x14ac:dyDescent="0.25">
      <c r="B2">
        <v>2</v>
      </c>
    </row>
    <row r="3" spans="2:2" x14ac:dyDescent="0.25">
      <c r="B3">
        <v>3</v>
      </c>
    </row>
    <row r="5" spans="2:2" x14ac:dyDescent="0.25">
      <c r="B5">
        <v>5</v>
      </c>
    </row>
    <row r="8" spans="2:2" x14ac:dyDescent="0.25">
      <c r="B8">
        <v>8</v>
      </c>
    </row>
  </sheetData>
</worksheet>
Community
  • 1
  • 1
Thomas Barnekow
  • 2,059
  • 1
  • 12
  • 21
  • thanks @Thomas when we debug the first line of => using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(file, false)) It's return 29 rows only. then how can i check row numbers. – rajadurai Feb 03 '20 at 06:45
  • @rajadurai, I updated my answer, showing some sample markup with multiple rows (`row` elements with `r` attribute) and pointing out the relevant strongly-typed class (`Row` with `RowIndex` property). – Thomas Barnekow Feb 03 '20 at 15:19
  • Ya it's worked for me. thanks @Thomas . Based on previous and current row value i added empty rows if any rows missing. – rajadurai Feb 05 '20 at 12:56
  • @rajadurai, sounds good. Then please accept this answer and upvote. – Thomas Barnekow Feb 05 '20 at 16:07