A variable has a value but throws an object reference not set to an instance of an object

Question

I have a function that loops a DataTable and saves it as an Excel File using SpreadSheetLight for C#

noticing that looping through the DataTable and saving its Excel File output takes time depending on how many cells and rows and columns a DataGrid had, or wherever that DataTable was extracted from.

before using BackgroundWorker in my function, the function executes properly. the only sidenote without BackgroundWorker of course is that the UI freezes while the codes executes. and by utilizing BackgroundWorker i know i can eliminate this by processing the save function on another thread.

but after applying BackgroundWorker in my function, the whole save function breaks. it throws me an Object Reference not set to an instance of an Object but i debugged and traced and made myself sure that both the variables passed had values in it, and is not null. the loop i used was the same as before, and i've even tried using a For Loop.

here is a snippet of what i've encountered. and as you can see, the window below shows that the cell and value variable both has contents in it. yet throws me null reference.

moreover, here is the complete code of my function

        public void exportSingleDataGridToExcelFile(DataTable dt) {
            using(var sfd = new SaveFileDialog()) {
                sfd.FileName = string.Format("WIP Monitoring-{0}", DateTime.Now.ToString("MM.dd.yyyy"));
                sfd.Filter = "Excel File (*.xlsx)|*.xlsx";

                if(sfd.ShowDialog() == DialogResult.OK) {
                    clb.Enabled = false;
                    cb.Enabled = false;
                    btn.Visible = false;
                    pb.Visible = true;
                    pb.Value = 50;

                    using(var excel = new SLDocument()) {
                        var style = excel.CreateStyle();
                        style.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
                        style.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);

                        var bgw = new BackgroundWorker();
                        bgw.WorkerReportsProgress = true;

                        bgw.DoWork += (ss, ee) => {
                            var worker = ss as BackgroundWorker;

                            var count = (dt.Rows.Count + 1) * dt.Columns.Count;
                            var steps = (double) count / 100;
                            var prog = 0.0;
                            var row = 0;
                            var col = 0;

                            //Get Column Headers
                            foreach(DataColumn dc in dt.Columns) {
                                var cell = string.Format("{0}1", col.getExCol());
                                var value = dc.ColumnName;

                                excel.SetCellValue(cell, value);
                                excel.AutoFitColumn(string.Format("{0}1", col.getExCol()));
                                excel.SetCellStyle(string.Format("{0}1", col.getExCol()), style);

                                prog += steps;
                                worker.ReportProgress(((int) prog * 100));

                                col++;
                            }

                            col = 0;

                            //Get Cell Data
                            foreach(DataRow dr in dt.Rows) {
                                foreach(DataColumn dc in dt.Columns) {
                                    var cell = string.Format("{0}{1}", col.getExCol(), row);
                                    var value = dr[dc].ToString();

                                    excel.SetCellValue(cell, value);
                                    excel.AutoFitColumn(cell, row);
                                    excel.SetCellStyle(cell, style);

                                    prog += steps;
                                    worker.ReportProgress(((int) prog * 100));

                                    col++;
                                }
                                col = 0;
                                row++;
                            }
                        };

                        bgw.ProgressChanged += (ss, ee) => {
                            pb.Value = ee.ProgressPercentage / 100;
                        };

                        bgw.RunWorkerCompleted += (ss, ee) => {
                            try {
                                excel.SaveAs(sfd.FileName);
                                form.Close();
                            } catch(Exception ex) {
                                MessageBox.Show(ex.Message, "Error while saving", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                        };

                        bgw.RunWorkerAsync();
                    }
                }
            }
        }

originally, this same code worked before i've implemented my BackgroundWorker.

hoping i could receive some walk through here.

Answer 1

It's highly probable that something inside the SLDocuemnt instance in excel is null when your background worker is executed.

You are creating excel in a using statement. That means that at the end of the using block, excel will be disposed. But inside that block you start a background worker that uses this excel variable.

The background worker surely runs longer than your using block, so excel is already disposed when you try to access it and execute the line that throws the exception.

The immediate solution would be to not use using here. But maybe it's better to instantiate that excel variable inside the background worker's method, as it seems you don't need it outside anyway.

Answer 2

I'm pretty sure your excel variable is the one that is Null, when reading the exception. Not the values in the method call.

I suspect this is due to the background work your doing.

Answer 3

public FileStreamResult GenerateReport()
{
    MemoryStream ms = new MemoryStream();
    using (SLDocument sl = new SLDocument())
    {
        sl.SetCellValue("B3", "I love ASP.NET MVC");
        sl.SaveAs(ms);
    }
    // this is important. Otherwise you get an empty file
    // (because you'd be at EOF after the stream is written to, I think...).
    ms.Position = 0;

    return File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx");
}