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

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

4

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

363
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

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

3

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

75,013
26
93
142

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
Me too! this's my solution! – BlackSD Jan 26 '23 at 22:15

EhsanAlam · Answer 3 · 2023-06-15T12:09:28.980

In some cases NAN consider as a string to deal such NAN string values in column using EPPlus I have overcome to this issue using this method . Try

if (AbcFileName.Name.Contains("abc")) {

                int rows = worksheet.Dimension.Rows;
                int cols = worksheet.Dimension.Columns;

                // Loop through each row and column in the worksheet
                for (int row = 1; row <= rows; row++)
                {
                    for (int col = 1; col <= cols;col++) 
                    {
                        // Get the cell value as a string
                        string cellValue = worksheet.Cells[row, col].GetCellValue<string>();
                        

                        // Check for "NAN" values and replace them with a blank value
                        if (cellValue == "NaN")
                        {
                            worksheet.Cells[row, col].Value = "NAN".ToUpper();
                        }
                    }
                }

            }

Dealing with empty cells when importing Excel file using EPPlus

3 Answers3