14

I cannot seem to think of a way to correct the error mentioned in the title and was looking for some ideas on what should be done.

I am trying to read the rows of a excel spreadsheet into an object.

The first time it loops I have no problems because row 1, column 1 and row 1 column 2 have data in them.

But when it gets to row 2, column 1 and row 2 column 2 it falls over with the above error because those cells in spreadsheet are "empty"

I just cannot work out where I can put some "if null" checks in.

Can anyone suggest how to do it please?

Here is my code...

private static void IterateRows(Excel.Worksheet worksheet)
    {
        //Get the used Range
        Excel.Range usedRange = worksheet.UsedRange;

        // create an object to store the spreadsheet data
        List<SPREADSHEETModel.spreadsheetRow> spreadsheetrows = new List<SPREADSHEETModel.spreadsheetRow>();

        //Iterate the rows in the used range
        foreach (Excel.Range row in usedRange.Rows)
        {
            for (int i = 0; i < row.Columns.Count; i++)
            {
                spreadsheetrows.Add(new SPREADSHEETModel.spreadsheetRow()
                {
                    col1 = row.Cells[i + 1, 1].Value2.ToString(),
                    col2 = row.Cells[i + 1, 2].Value2.ToString()
                });
            }
        }
    }
Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
Trevor Daniel
  • 3,785
  • 12
  • 53
  • 89

3 Answers3

38

Do not use .ToString() it will cause null reference exception when the value is null. Use Convert.ToString(), it will return empty string for the null value.

col1 = Convert.ToString(row.Cells[i + 1, 1].Value2);
col2 = Convert.ToString(row.Cells[i + 1, 2].Value2);
Saravana Kumar
  • 3,669
  • 5
  • 15
  • 35
4

You need them before the call to ToString. Maybe you can even move if before the adding, since I think it isn't useful to add empty rows, but that might nog be true in your scenario:

if (row.Cells[i + 1, 1].Value2 != null && row.Cells[i + 1, 2].Value2 != null)
{
    spreadsheetrows.Add(new SPREADSHEETModel.spreadsheetRow()
    {
        col1 = row.Cells[i + 1, 1].Value2.ToString(),
        col2 = row.Cells[i + 1, 2].Value2.ToString()
    });
}

Otherwise this is probably what you need:

col1 = row.Cells[i + 1, 1].Value2 != null ? row.Cells[i + 1, 1].Value2.ToString() : null,

The reason behind the exception is that Value2 is a dynamic, so the return value is determined on runtime. And if Value2 is null, it can't determine the ToString method to call.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
  • as you spotted, my scenario is that row 2, column 3 has data in it which I do want to get. there are 17 columns in total :( – Trevor Daniel Jan 30 '15 at 11:48
  • @TrevorDaniel: Okay, then see the second code sample. – Patrick Hofman Jan 30 '15 at 11:49
  • You could even split that logic off in a method if you use it frequently. – Patrick Hofman Jan 30 '15 at 11:49
  • I went with Kumars answer because he spotted the .tostring() was causing it, which I should have.... Hope you don't mind. I really appreciate the help! – Trevor Daniel Jan 30 '15 at 12:06
  • 1
    @TrevorDaniel: That was exactly what I was telling you. The first sentence mentions `ToString` and the last paragraph explains why the error is different from the `NullReferenceException` you would normally see... – Patrick Hofman Jan 30 '15 at 12:49
1

You can check inside in for loop:

    //Iterate the rows in the used range
    foreach (Excel.Range row in usedRange.Rows)
    {
        for (int i = 0; i < row.Columns.Count; i++)
        {
            spreadsheetrows.Add(new SPREADSHEETModel.spreadsheetRow()
            {
        if (row.Cells[i + 1, 1].Value2 != null)
        {                       
            col1 = row.Cells[i + 1, 1].Value2.ToString();
        }
        if (row.Cells[i + 1, 2].Value2 != null)
        {
                        col2 = row.Cells[i + 1, 2].Value2.ToString();
        }
        if (row.Cells[i + 1, 3].Value2 != null)
        {
                        col3 = row.Cells[i + 1, 3].Value2.ToString();
        }
            });
        }
    }
vignesh
  • 97
  • 5