System.NullReferenceException when looping through empty values in Excel C#

Question

I am having a small problem, regarding extracting data from an Excel file, ignore the first part where I am deleting some stuff, it is because the file being retrieved is 24 columns where the targeted one is around 12, so I am eliminating some columns.

Long story short, the part where I am trying to match if the value of column 5 in any row is "x" then write for example "E" in the second column of the same row ..else, check if the "X" is in the 6th column, then it means that "P" should be in the second column of that row and so on...

The problem is that for every cell it is either value "x" or it is empty ..(there is no value in the excel file), so there seems to be a problem whenever the code loops through empty elements, it throws this error

 l = new Microsoft.Office.Interop.Excel.Application();
 xl.Visible = false;
 Microsoft.Office.Interop.Excel.Workbook workbook = xl.Workbooks.Open(p_sUBKPath, Type.Missing, Type.Missing, 4, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

 Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];

 Microsoft.Office.Interop.Excel.Range range = ws.UsedRange;
 // delete columns that we don't need from the new excel file
 Microsoft.Office.Interop.Excel.Range range2 = ws.get_Range("A1","A1"); 
 range2.EntireColumn.Delete();
 Microsoft.Office.Interop.Excel.Range range3 = ws.get_Range("B1", "B1");
 range3.EntireColumn.Delete();
 Microsoft.Office.Interop.Excel.Range range4 = ws.get_Range("D1", "L1");
 range4.EntireColumn.Delete();
 Microsoft.Office.Interop.Excel.Range range5 = ws.get_Range("I1", "M1");
 range5.EntireColumn.Delete();
 Microsoft.Office.Interop.Excel.Range range6 = ws.get_Range("K1", "K1");
 range6.EntireColumn.Delete();

 //insert a new column ( Category)
 Microsoft.Office.Interop.Excel.Range range7 = ws.get_Range("B1", "B1");
 range7.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight); 

 object[,] values = (object[,])range.Value2;
 values[1, 2] = (string)"Cat.";


 for (int row = 2; row <= values.GetUpperBound(0); row++)
 {
     try
     {
          if ((!String.IsNullOrEmpty(values[row, 5].ToString())) && values[row, 5].ToString() == "x")
          {
              values[row, 2] = (string)"E";
          }
          else if ((!String.IsNullOrEmpty(values[row, 6].ToString())) && values[row, 6].ToString() == "x")
          {
              values[row, 2] = (string)"P";
          }
          else if ((!String.IsNullOrEmpty(values[row, 7].ToString())) && values[row, 7].ToString() == "x")
          {
              values[row, 2] = (string)"Phy";
          }
          else if ((!String.IsNullOrEmpty(values[row, 8].ToString())) && values[row, 8].ToString() == "x")
          {
              values[row, 2] = (string)"L";
          }
          else if ((!String.IsNullOrEmpty(values[row, 9].ToString())) && values[row, 9].ToString() == "x")
          {
              values[row, 2] = (string)"Ex";
          }
          else
              MessageBox.Show("unknow");
       }
       catch (Exception ex)
       {
          MessageBox.Show(ex.ToString());
       }
}

The error is :

System.Nullreferenceexception object reference not set to an instance of an object

I am adding this column to the sheet on runtime, but I am converting it to string because it is of type object in the values array — eMizo
– eMizo, Commented Sep 20, 2013 at 15:01
I think it's probably because of your lines that are checking to see if the string is null or empty. if values[row, x] is null, then values[row, x].ToString() will throw this exception. Could you try just casting them to strings -- (string)values[row, x] ? — Jon La Marr
– Jon La Marr, Commented Sep 20, 2013 at 15:07

Adam Miller · Accepted Answer · 2013-09-20 15:08:49Z

1

I would do a null check before doing .tostring(). i.e.:

(!String.IsNullOrEmpty(values[row, 5].ToString()))

To

!(values[row, 5] == null) && (!String.IsNullOrEmpty(values[row, 5].ToString()))

answered Sep 20, 2013 at 15:08

Adam Miller

7771 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

System.NullReferenceException when looping through empty values in Excel C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related