c# - EPPlus 在保存 excel 时抛出 NullReferenceException

Question

我使用 EPPlus 生成 Excel。

这是我的行动

 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");
    }

这是我生成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;
        }
    }

在尝试保存 excel 时，它会抛出 NullReferenceException excel.SaveAs(memoryStream) 行



这个案例有趣的一面是它在 2 天前还在工作。突然它停止工作，现在抛出这个错误。

Answer 1

这里的问题是在 GenerateReportingExcel方法。

在该方法中，您将返回 ExcelPackage在 using 语句中创建的对象。

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

作为由 using 创建的一次性对象将在退出块后立即处理，在方法之外使用此对象将导致 excel.SaveAs(memoryStream) 上的异常因为 excel已经被处置。

您必须移动一些代码才能解决此问题。两种潜在的解决方案(取决于您的需求)是:

移动 excel.SaveAs();调用 GenerateReportingExcel() ，所以它出现在 using 中创建的块 excel

删除 using阻止并手动创建 ExcelPackage返回的对象，然后调用 excel.Dispose()当您在 ExportReportToExcel() 中完成它时

IE。

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

然后在 ExportReportToExcel()

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