Object reference not set to instance of object when parsing excel spreadsheet

Question

I wrote my own import from excel method for an excel sheet my company uses to show the budgets for the different departments in the store. All I cared about from the sheet were the two columns related to date and the budget for our department.

public static string[][] ImportBudgets(string filename)
{
    var xlApp = new Application();
    var xlWorkBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    var xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.Item[1];
    var range = xlWorkSheet.UsedRange;

    var budgetsArray = new string[range.Rows.Count][];

    // loop through excel spreadsheet and get data
    for (var rCnt = 3; rCnt <= range.Rows.Count; rCnt++)
    {
        var budgetDate = (string)((Range)range.Cells[rCnt, 1]).Value2;
        var budgetAmount = (decimal)((Range)range.Cells[rCnt, 8]).Value2;
        budgetsArray[rCnt - 3] = new[] { budgetDate, budgetAmount.ToString(CultureInfo.CurrentCulture) };
    }

    // cleanup 
    xlWorkBook.Close(true, null, null);
    xlApp.Quit();

    Marshal.ReleaseComObject(xlWorkSheet);
    Marshal.ReleaseComObject(xlWorkBook);
    Marshal.ReleaseComObject(xlApp);

    return budgetsArray;
}

public string ImportBudgets(string filename)
{
    try
    {
        // get dates and budgets from excel in array
        var budgets = CExcel.ImportBudgets(filename);

        using (var oDc = new StatTrackerTablesDataContext())
        {
            foreach (var innerArray in budgets)
            {
                var budget = new tblBudget();
                DateTime budgetdate;
                if (DateTime.TryParse(innerArray[0], out budgetdate) != true) continue;
                budget.Budget_ID = Guid.NewGuid();
                budget.Budget_Date = budgetdate;
                budget.Budget_Amount = decimal.Parse(innerArray[1]);
                budget.Budget_Goal = decimal.Parse(innerArray[1]) * (decimal)1.1;

                oDc.tblBudgets.InsertOnSubmit(budget);
                oDc.SubmitChanges();
            }
        }
        return "Budgets imported without problems.";
    }
    catch (SqlException ex)
    {
        if (ex.Number == 2627)
        {
            return "Budget already existed for a date in batch. Import aborted.";
        }
        else
        {
            return ex.Message;
        }
    }
    catch (Exception ex)
    {
        if (ex.Message.Contains("Object reference not set to an instance of an object."))
            return "Budgets imported without problems.";
        return ex.Message;
    }
}

The import will run and import all of the dates and budgets properly but will always have null objects after getting to the row after the last one populated properly. I threw in the error handling for the object reference message, but it's obviously not a good tactic if there really is an issue with the import process. I was just seeing if anyone could help me refine it to prevent the message from occurring.

possible duplicate of [What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — mason, Feb 26 '15 at 21:32
I would first try to change for loop to `for (var rCnt = 3; rCnt < range.Rows.Count; rCnt++)` — Pilgerstorfer Franz, Feb 26 '15 at 21:34
What line of code throws an exception? Did you try to debug the code? — Eugene Astafiev, Feb 27 '15 at 09:51

score 0 · Accepted Answer · answered Feb 28 '15 at 16:43

Alright, I hadn't visited this code in a while because it did get the job done. It was just the workaround that I didn't like being in there to avoid an error message. I noticed that I dimensioned the array for the entire row count of the excel document, but the document contained rows that weren't parsed for budgets because they weren't actual dates. There were 2-3 rows per document that get skipped because the "date" column says something like "Week 2". This resulted in the budgets array having 2-3 null innerArrays and my secondary budget import method would try and parse the null arrays. I thought the code:

if (DateTime.TryParse(innerArray[0], out budgetdate) != true) continue;

Would have kept the array from being parsed, but ignored the fact that the exception will be thrown before returning false. I just added

 if (innerArray == null) continue;

at the beginning of the foreach loop and now it imports without needing the

catch (Exception ex)
{
    if (ex.Message.Contains("Object reference not set to an instance of an object."))
        return "Budgets imported without problems.";
    return ex.Message;
}

Object reference not set to instance of object when parsing excel spreadsheet

1 Answers1