Unable to bind runtime to null reference - Empty Excel cells

Question

I don't seem to think of a way to fix the error mentioned in the title, and was looking for some ideas on what to do.

I am trying to read the rows of an Excel table in an object.

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

But when it hits row 2, column 1 and column 2 of row 2, it crashes with the above error, because these cells in the spreadsheet are “empty”

I just can't decide where I can put some “if null” checks.

Can anyone suggest how to do this?

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() }); } } } 

Answer 1

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

 col1 = Convert.ToString(row.Cells[i + 1, 1].Value2); col2 = Convert.ToString(row.Cells[i + 1, 2].Value2); 

Answer 2

You need them before calling ToString . Perhaps you can even move if before adding, since I think it is not useful to add blank lines, but this might be wrong 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 for the exception is that Value2 is dynamic , so the return value is determined at runtime. And if Value2 is null , it cannot determine the ToString method for the call.

Answer 3

You can check inside the 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(); } }); } }