CODEWITHSUNDEEP
c#epplus
Nigar Alizade
Nigar Alizade

Reputation: 369

EPPlus when save excel it throws NullReferenceException

I use EPPlus for generating Excel.

This is my action 

 public ActionResult ExportReportToExcel()
    {
        var model = new ReportingViewModel();
        int numOfInvolvedCompanies, numOfRefusedCompanies, numOfSuccessfullCompanies, numOfEmployeesInvolved, projectsCount;
        model.Projects = db.GetProjectsReport(1, 1, out projectsCount, out numOfInvolvedCompanies, out numOfRefusedCompanies, out numOfSuccessfullCompanies, out numOfEmployeesInvolved);
        model.AllProjectsReport.NumberOfCompanyInvolved = numOfInvolvedCompanies;
        model.AllProjectsReport.NumberOfRefusedCompanies = numOfRefusedCompanies;
        model.AllProjectsReport.NumberOfSuccessfullParticipated = numOfSuccessfullCompanies;
        model.AllProjectsReport.NumberOfEmployeeInvolved = numOfEmployeesInvolved;

        ExcelPackage excel =  ExcelGenerator.GenerateReportingExcel(model);

        string excelName = "Reporting";

        using (var memoryStream = new MemoryStream())
        {
            try
            {
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment; filename=" + excelName + ".xlsx");
                excel.SaveAs(memoryStream);
                memoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
            catch(Exception e)
            {
                throw;
            }

        }

        return RedirectToAction("Reporting");
    }

And this is my method which generates excel 

        public static ExcelPackage GenerateReportingExcel(ReportingViewModel model)
    {
        using (ExcelPackage excel = new ExcelPackage())
        {
            var workSheet = excel.Workbook.Worksheets.Add("Reporting");

            workSheet.TabColor = System.Drawing.Color.Black;
            workSheet.DefaultRowHeight = 12;
            workSheet.Row(1).Height = 20;
            workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            workSheet.Row(1).Style.Font.Bold = true;

            workSheet.Cells[1, 1].Value = "Name";
            workSheet.Cells[1, 2].Value = "Sector";
            workSheet.Cells[1, 3].Value = "N of companies involved";
            workSheet.Cells[1, 4].Value = "N of refused companies";
            workSheet.Cells[1, 5].Value = "N of successful participated";
            workSheet.Cells[1, 6].Value = "N of employee involved";
            workSheet.Cells[1, 7].Value = "Start date";
            workSheet.Cells[1, 8].Value = "Finish date";



            int i = 2;
            foreach (var item in model.Projects)
            {
                workSheet.Cells[i, 1].Value = item.Name;
                workSheet.Cells[i, 2].Value = item.SectorValues;
                workSheet.Cells[i, 3].Value = item.NumberOfCompanyInvolved;
                workSheet.Cells[i, 4].Value = item.NumberOfRefusedCompanies;
                workSheet.Cells[i, 5].Value = item.NumberOfSuccessfullParticipated;
                workSheet.Cells[i, 6].Value = item.NumberOfEmployeeInvolved;
                workSheet.Cells[i, 7].Value = item.StartDate;
                workSheet.Cells[i, 8].Value = item.FinishDate;
                i++;
            }


            workSheet.Cells[i, 2].Value = "Total";
            workSheet.Cells[i, 3].Value = model.AllProjectsReport.NumberOfCompanyInvolved;
            workSheet.Cells[i, 4].Value = model.AllProjectsReport.NumberOfRefusedCompanies;
            workSheet.Cells[i, 5].Value = model.AllProjectsReport.NumberOfSuccessfullParticipated;
            workSheet.Cells[i, 6].Value = model.AllProjectsReport.NumberOfEmployeeInvolved;


            for (int colNum = 1; colNum <= 8; colNum++)
            {
                workSheet.Column(colNum).AutoFit();
            }

            return excel;
        }
    }

While trying save excel it throws NullReferenceException excel.SaveAs(memoryStream) line

enter image description here

The interesting side of this case is that it was working 2 days ago. And suddenly it stopped working and now throws this error.

Upvotes: 4

Views: 2151

Answers (1)

sbsd
sbsd

Reputation: 306

The problem here is in the GenerateReportingExcel method.

In that method, you're returning an ExcelPackage object that's created in the using statement.

using (ExcelPackage excel = new ExcelPackage())
{
      ...         
      return excel;
}

As disposable objects created by using will be disposed as soon as you exit the block, using this object outside of the method will cause an exception on excel.SaveAs(memoryStream) because excel has already been disposed.

You'll have to move some of your code around to resolve this. Two potential solutions (depending on what your needs are) are:

  1. Move the excel.SaveAs(); call into GenerateReportingExcel(), so it occurs within the using block that created excel
  2. Remove the using block and manually create the ExcelPackage object that's returned, then call excel.Dispose() when you're done with it in ExportReportToExcel()

i.e.

public static ExcelPackage GenerateReportingExcel(ReportingViewModel)
{
    var excel = new ExcelPackage();
    ...
    return excel;
}

then in ExportReportToExcel()

try
{
    ...
    excel.SaveAs(memoryStream);
    excel.Dispose();
    ...
}

Upvotes: 2

Related Questions