Dealing with empty cells when importing Excel file using EPPlus

Question

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 ""
};

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Uwe Keim, Jun 19 '16 at 19:32

score 2 · Answer 1 · answered Jun 19 '16 at 10:37

2

If you are using the latest C# version (6.0) then you can use the null propagation operator:

LAST_NAME = worksheet?.Cells[lastNameColumn + row]?.Value?.ToString()

answered Jun 19 '16 at 10:37

nvoigt

61,531
23
73
116

I can't :( I have VS2013 – DiPix Jun 19 '16 at 10:51
@DiPix C# 6 can be used with VS 2013. – mason Jun 19 '16 at 14:18
@mason So I don't know why i can't Build application: `syntax error` – DiPix Jun 19 '16 at 17:42
I was stuck on that and it has just saved me! – Mar 28 '19 at 15:31

Abdul Khan · Accepted Answer · 2016-06-20T09:33:38.903

2

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.

edited Jun 20 '16 at 09:33

answered Jun 20 '16 at 06:45

Abdul Khan

343
3
13

Second way with extension method works almost good. But returns e.g `"D2"` (it's address CELL) instead string.empty. // EDIT - you forgot to add `.Value` before `.ToNullSafeString();`. Please edit your answer and i'll mark it :) – DiPix Jun 20 '16 at 09:04
What if I have INT instead string? eg. `SKILL1 = Int32.Parse(worksheet6.Cells[skill1Column + row].Value.ToNullSafeString()),` It doesn't work in this case. I have an error: `Invalid format of the input string.` – DiPix Jun 20 '16 at 09:41
That's correct , It will fail as you are using `Int32.Parse` and it is trying to parse null into `integer.Int32`.Parse isn't capable of parsing a null value.You will need your own helper method to it. – Abdul Khan Jun 20 '16 at 09:46
So what can I do now? Some Cells are numbers. – DiPix Jun 20 '16 at 09:47
I think this is what you need instead of Int32.parse write a extension `public static int? ToNullableInt32(this string s) { int i; if (Int32.TryParse(s, out i)) return i; return null; }` also you will call it like this `SKILL1 = (worksheet6.Cells[skill1Column + row].Value.ToNullSafeString().ToNullableInt32());` hope it helps. – Abdul Khan Jun 20 '16 at 09:49

Dealing with empty cells when importing Excel file using EPPlus

2 Answers2