Not able to access empty cells EPPlus

Question

Trying to figure out how to read all the cells, even the empty ones.

Problem: When a cell is empty it is ignored and the value from the next cell is used instead..and an exception is thrown.

This:

Name..........Number

Car...............10

Box...............26

.....................19

Apple............26

Ends up:

Name..........Number

Car...............10

Box..............26

19.............Apple

26.................

var ws = excelPackage.Workbook.Worksheets.First();

for (var i = 3; i <= ws.Dimension.End.Row; i++) 
  {
  var cellValues = ws.Cells[i, 1, i, ws.Dimension.End.Column].ToList(); 

 var newThings = new Store
      {
        Name = cellValues[0].Text,
        Number = cellValues[1].Text,
      };

I've tried things like

Name = string.IsNullOrWhiteSpace(cellValues[0].Text) ? " " : cellValues[0].Text;

but it is too late since the damage is already done. Would be awesome to get this thing working, but I can't seem to get the how.

I would really appreciate any help you can provide. :)

Answer 1

You could try reading each row/column individually instead of using the EPPlus range. Similar to this (this is untested):

for (var j = 3; j <= excelSheet.Dimension.End.Row; ++j)
{
    var name = (excelSheet.Cells[j, 0].Value ?? "").ToString();
    var number = (excelSheet.Cells[j, 1].Value ?? "").ToString();
    var newThings = new Store
    {
        Name = name,
        Number = number
    };
}