null reference on excel doc C#

Question

Hi I keep getting this error from the below code, was wondering if anyone can help.

error processing excel file: cannot perform runtime binding on a null reference

Code:

    private void Import_Click(object sender, RoutedEventArgs e)
    {
        Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();

        // Show open file dialog box
        Nullable<bool> result = dlg.ShowDialog();

        // Process open file dialog box results
        if (result == true)
        {
            // Open document
            string filename = dlg.FileName;

            Microsoft.Office.Interop.Excel.Application vExcelObj = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                Microsoft.Office.Interop.Excel.Workbook theWorkbook = vExcelObj.Workbooks.Open(filename, Type.Missing, true);

                Microsoft.Office.Interop.Excel.Worksheet sheet = theWorkbook.Worksheets[1];  

                string vFirstName = "temp";
                string vLastName = "temp";
                int vIndex = 1;

                while (vFirstName != "")
                {
                    // Change the letters of the appropriate columns here!  
                    // In my example, 'A' is first name, 'B' last name
                    vFirstName = sheet.get_Range("A" + vIndex.ToString()).Value.ToString(); // if i take out the exception handling the error is on this line
                    vLastName = sheet.get_Range("B" + vIndex.ToString()).Value.ToString();


                    this.SaveNewCustomer(vFirstName, vLastName); 

                    vIndex++;

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error processing excel file : " + ex.Message);
            }
            finally
            {
                vExcelObj.Quit();
            }
        }
    }

    private void SaveNewCustomer(string firstName, string lastName) 
    {
        string uri = "http://localhost:8002/Service/Customer";
        StringBuilder sb = new StringBuilder();
        sb.Append("<Customers>");
        sb.AppendLine("<FirstName>" + firstName + "</FirstName>");
        sb.AppendLine("<LastName>" + lastName + "</LastName>");
        sb.AppendLine("</Customers>");
        string NewStudent = sb.ToString();
        byte[] arr = Encoding.UTF8.GetBytes(NewStudent);
        HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);
        req.Method = "POST";
        req.ContentType = "application/xml";
        req.ContentLength = arr.Length;
        Stream reqStrm = req.GetRequestStream();
        reqStrm.Write(arr, 0, arr.Length);
        reqStrm.Close();
        HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
        reqStrm.Close();
        resp.Close();
    }

}

The code just takes a excel document and trys to send the data to my web service.

So I tryed using the below method but it freezes the application :S no error just hangs. Edit attempt:

                while (vFirstName != "")
                {
                        var columnACell = sheet.get_Range("A" + vIndex.ToString());
                        var columnBCell = sheet.get_Range("B" + vIndex.ToString());
                        var columnACellValue = columnACell.Value;
                        var columnBCellValue = columnBCell.Value;


                    if (columnACellValue != null && columnBCellValue != null)
                    {
                        vFirstName = columnACellValue.ToString();
                        vLastName = columnBCellValue.ToString();

                        this.SaveNewStaff(vFirstName, vLastName); //, vPassword

                        vIndex++;
                    }
                }
            }

Answer 1

EDIT 2

Just took the code, and stepped through it. Found the problem. I think I misunderstood what was happening originally.

What's happening is that the loop while (vFirstName != "") will keep going until vFirstName is an empty string. But this will never happen! Here's why:

• Everything will be fine as long as columns A and B will have values. The code will behave as expected.
• When the code gets to an Excel row that doesn't have a value, it hits an empty cell, which will have .Value set to null. This causes the exception.

So the real solution here is to have the loop keep going until it hits a cell with a null value, and then exit. Kind of like this:

while (true) {
    // Split the satements 
    var columnACell = sheet.get_Range("A" + vIndex.ToString());
    var columnBCell = sheet.get_Range("B" + vIndex.ToString());
    var columnACellValue = columnACell.Value;
    var columnBCellValue = columnBCell.Value;

    if (columnACellValue != null && columnBCellValue != null) {
        vFirstName = columnACellValue.ToString();
        vLastName = columnBCellValue.ToString();

    } else {
        break;
    }

    this.SaveNewCustomer(vFirstName, vLastName);

    vIndex++;

};

Just tested this on my end, and it seems to work.

On a separate note, make sure that you're fully quitting Excel, because calling Excel.Quit() is often not enough. Open Task Manager and check whether there are any extra instances of EXCEL.exe floating around. To prevent those I usually kill Excel after I'm done with it (easier than properly releasing Excel's COM objects), as described in this post.

---

ORIGINAL POST

It sounds like there are a few options here:

• The cell is empty, which means that it's .Value will be null.
• The sheet is null,
• get_Range() returns null -- that sounds unlikely.

Split the line into separate statements and see which one of them throws an error. That will tell you where to look further.

Judging by what you're doing -- searching the column until you find first name -- it sounds like you're running into nulls inside the cells' Values. To deal with that, I usually add a quick if-statement to test Value for null.

EDIT

Here's an example (may not compile) that will hopefully fix null values inside the cells and help to pinpoint other null-related problems. Replace the offending lines with something like this:

var columnACell = sheet.get_Range("A" + vIndex.ToString());
var columnBCell = sheet.get_Range("B" + vIndex.ToString())
var columnACellValue = columnACell.Value;
var columnBCellValue = columnBCell.Value;

if (columnACellValue != null && columnBCellValue != null) {
    vFirstName = columnACellValue.ToString(); 
    vLastName = columnBCellValue.ToString();
}

Note that I assume that your C# compiler supports implicit static typing through var.

Answer 2

Sheet is null. Perhaps there is only 1 worksheet? You're using Worksheets[1] which returns the second one, of course.. :)

Answer 3

Try to split the code that reads the value and test if it is null.

object oName = sheet.get_Range("A" + vIndex.ToString()).Value;
vFirstName = (oName == null ? string.Empty : oName.ToString();
object oLast = sheet.get_Range("B" + vIndex.ToString()).Value;
vLastName = (oLast == null ? string.Empty : oLast.ToString());
if(vFirstName.Length > 0 && vLastName.Length > 0)
     this.SaveNewCustomer(vFirstName, vLastName);   

And noticed that in your SaveNewStaff/Customer(....) you close the RequestStream two times. Perhaps the second close freeze your code.

    reqStrm.Close();  
    HttpWebResponse resp = (HttpWebResponse)req.GetResponse();  
    reqStrm.Close();  // <= Already closed before?