How to Skipped Empty Cells, in excel Using OPEN XML in .Net MVC C#

0
(0)

How to Skipped Empty Cells, in excel Using OPEN XML in .Net MVC C#

While reading Excel in C# / MVC, the Cells that doesn’t have any data are skipped and the data of very next cell having data in the row is used as the value of the empty column.


Solution:-
instead of checking only cells having data,

foreach (Cell cell in row.Descendants<Cell>()) {
  dataTable.Rows[dataTable.Rows.Count - 1][i] = GetValue(doc, cell);
  i++;
}

 

Lets check its ordinal value/ actual position

for (int col = 0; col < row.Descendants<Cell>().Count(); col++) {
  Cell cell = row.Descendants<Cell>().ElementAt(col);
  int actualCellIndex = CellReferenceToIndex(cell);
  var cell_value = GetValue(doc, cell);
  dataTable.Rows[dataTable.Rows.Count - 1][actualCellIndex] = GetValue(doc, cell);
}

private static int CellReferenceToIndex(Cell cell) {
  int index = 0;
  string reference = cell.CellReference.ToString().ToUpper();
  foreach (char ch in reference) {
    if (Char.IsLetter(ch)) {
       int value = (int)ch - (int)'A';
       index = (index == 0) ? value : ((index + 1) * 26) + value;
    }
    else
       return index;
    }
    return index;
}

More on Asp.Net …

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Be the first to comment

Leave a Reply

Your email address will not be published.


*


17 − 7 =