DiPix DiPix - 3 months ago 21
ASP.NET (C#) Question

Dealing with empty cells when importing Excel file using EPPlus

I want to import data from Excel to DataBase using EPPLUS. From here I took code: https://www.paragon-inc.com/resources/blogs-posts/easy_excel_interaction_pt6

The problem is that sometimes in excel are empty Cells. And if cell is empty then I receive an error:

NullReferenceException
, and my application stops. I think good solution would be assign
null
value to specific variable if there is no reference e.g.
if(LAST_NAME returns NullReferenceException then LAST_NAME = null)
- but I don't know how to do this in code.

var newRecord = new DB_USER
{
ID = Int32.Parse(worksheet.Cells[idColumn + row].Value.ToString()),
FIRST_NAME = worksheet.Cells[firstNameColumn + row].Value.ToString(),
LAST_NAME = worksheet.Cells[lastNameColumn + row].Value.ToString() //If this value has NullReferenceException then assign null or ""
};

Answer

I thing its fine to assign a empty string i.e. string.Empty for empty cells .And if you are fine you can put it this way :

var newRecord = new DB_USER
      {
           ID = Int32.Parse(worksheet.Cells[idColumn + row].Value.ToString()),
           FIRST_NAME = worksheet.Cells[firstNameColumn + row].Value.ToString(),
           LAST_NAME = worksheet.Cells[lastNameColumn + row].Value ?? string.Empty).ToString() //for a null value assign a empty string else the string value
       };

A cleaner approach would be Extension method :

public static string ToNullSafeString(this object obj)
{
    return (obj ?? string.Empty).ToString();
}

and use it as :

LAST_NAME = worksheet.Cells[lastNameColumn + row].Value.ToNullSafeString();

still if you wish to return a null instead of string.Empty then a slight modification to ToNullSafeString extension method above will work.

Comments