Excel cell blank space check in C#

Question

I am reading an excel file and the contents are then to be stored in DB. The problem is space check in excel sheet column. Some of the columns are spaces and for them DB values will be null. I have a put check for null value while reading cells but somehow it isn't working as expected.

Code

if (workSheet.Cells[rowIterator, 12].Value != null)
store.WeeklyOff = workSheet.Cells[rowIterator, 12].Value.ToString();
if (workSheet.Cells[rowIterator, 13].Value != null)
store.OpenTime = workSheet.Cells[rowIterator, 13].Value.ToString();
if (workSheet.Cells[rowIterator, 14].Value != null)
store.CloseTime = workSheet.Cells[rowIterator, 14].Value.ToString();

In the above snippet I have put null value check for each cell. The issue is column 12 is also spaces and for this check works fine i.e. if condition is false and it moves to next statement but from column 13 onwards the null check isn't working. While debugging I can see the null for column 13 & 14, still != null check isn't validated and the code in the if statement gets executed and I get null pointer exception. I have tried a lot many other things for null check but nothing isn't working as expected. Struggling from last 1 day but nothing seem to be working. All the variables are defined as String. Please advise.

Weird question: if you're just reading the data from the spreadsheet, why aren't you using Oledb? — maniak1982, Mar 29 '16 at 02:52
Also you're going to run into trouble if there is no `Cell` value at that index. You would need to run a null check on that: `if (worksheet.Cells[rowIterator, 12] != null)` — maniak1982, Mar 29 '16 at 02:53
Thanks for the input. But since I am doing this for the first time, I searched for some tutorials, and did as what I understood. Will look for Oledb and try as suggested. Anyways I tried as suggested above removing .Value but still the same. It's still executing condition inside If and I get null pointer exception. — Gaurav, Mar 29 '16 at 03:09
If I knew what line it referred to, I could tell you more about what's going on. Here's some detail about using OLEDB to read from Excel: http://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB — maniak1982, Mar 29 '16 at 03:15
Thanks for the link. I too found this after you suggested. Lemme try with this quickly. — Gaurav, Mar 29 '16 at 03:22
Are you sure that you have columns beyond 12th one in excel ? — Peck_conyon, Mar 29 '16 at 03:36
@Peck_conyon Yes.There are 23 columns in all and 2500 rows to be read. Out of these 23 columns, a few doesn't have any value i.e. they are spaces. — Gaurav, Mar 29 '16 at 03:40

Eminem · Answer 1 · 2016-03-29T04:23:46.243

0

Try the following:

var x = string.IsNullOrEmpty(workSheet.Cells[rowIterator, 13].Value;
if(value != null)
{
  store.OpenTime = workSheet.Cells[rowIterator, 13].Value.ToString();
}

or

Type t = workSheet.Cells[rowIterator, 13].value.GetType();
if (t.Equals(typeof(string)))
{
    var x = string.IsNullOrEmpty(workSheet.Cells[rowIterator, 13].Value;
    if(value != null)
    {
      store.OpenTime = workSheet.Cells[rowIterator, 13].Value.ToString();
    }
}

edited Mar 29 '16 at 04:23

answered Mar 29 '16 at 03:58

Eminem

7,206
15
53
95

Thanks Eminem. But I have already tried this. By using above it again throws null pointer exception as workSheet.Cells[rowIterator, 13].Value.ToString() is null. So this time null pointer exception is thrown at If condition. – Gaurav Mar 29 '16 at 04:00
How about assigning it to a value before. See changed answer – Eminem Mar 29 '16 at 04:04
Then use try and catch blocks and inside catch (NullReferenceException) assign the values as you want may be an empty string for such places – Peck_conyon Mar 29 '16 at 04:05
Or maybe the .ToString() is failing. If it passes the if statement check and fails on the assignment line then the issue has to be converting the value to a string. Gaurav check the type of the values in the if statement. Type t = typeof(workSheet.Cells[rowIterator, 13].Value); ... or something similar – Eminem Mar 29 '16 at 04:08
Yes Eminem's suggestion also need to check – Peck_conyon Mar 29 '16 at 04:11
@Eminem Tried the edited answer but now getting null pointer exception at var x statement. Just noticed one thing. While debugging condition is marked "false", still it's executing statement inside "if". I mean when I place my cursor at != while debugging, it reflects value as false, which means value is null. But still the next statement is executed. Now going to try typeof as suggested. – Gaurav Mar 29 '16 at 04:19
http://stackoverflow.com/questions/13202855/condition-false-but-code-inside-the-if-statement-executed – Eminem Mar 29 '16 at 04:23

score 0 · Answer 2 · answered Mar 29 '16 at 04:09

0

    try
    {
        if(!string.IsNullOrEmpty(workSheet.Cells[rowIterator, 13].Value.ToString()))
        {
            store.OpenTime = workSheet.Cells[rowIterator, 13].Value.ToString();
        }
    }
    catch(NullReferenceException)
    {
       store.OpenTime = SomeValue;
    }

answered Mar 29 '16 at 04:09

Peck_conyon

221
2
13

Thanks. This seem to be working but after 3-4 rows, getting Null Reference Exception again. Also not sure if using try/catch for 23 columns and 2500 rows will be a good bet. I understand try/catch is expensive and should be used as the last resort. – Gaurav Mar 29 '16 at 07:07

Excel cell blank space check in C#

2 Answers2