EPPlus export model to Excel: System.NullReferenceException

Question

I have an MVC view that correctly displays a model. Now that I have been asked to put an Export to Excel function on that page, I heard about EPPlus and gave it a try. From this site I thought I had something that would simply do the job.

After making changes to fit my needs, I developed this. When I tested the functionality in the view, it worked. I clicked the html actionlink and the Excel file was saved correctly.

public void ExportToExcel(IEnumerable<FourCourseAudit> audit)
{
  string pathUser = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
  string pathDownload = Path.Combine(pathUser, "Documents");

  FileInfo newFile = new FileInfo(pathDownload + "\\MyExport.xslx");

  using (var excelFile = new ExcelPackage(newFile))
  {
      var worksheet = excelFile.Workbook.Worksheets.FirstOrDefault(x=>x.Name=="Sheet1");
      if (worksheet == null) 
      {
          worksheet.Workbook.Worksheets.Add("Sheet1");
      }

      worksheet.Cells["A1"].LoadFromCollection(Collection:audit, PrintHeaders:true);
      excelFile.Save();
  }
}

However, when I published the website and tested it as a normal user, I got the dreaded "Null Exception" error: System.NullReferenceException: Object reference not set to an instance of an object.

The possible culprits are:

• Null worksheet
• Null newFile
• Null audit

This is puzzling because it works when I run this in debug mode with VS, but the null exception error appears when it's running off the production server.

Is there anything obvious I'm doing wrong with the code example?

Answer 1

You can't (well certainly SHOULDN'T) try to save directly to a user's filesytem from a web application.

I suspect your null reference is somewhere in the Environment.SpecialFolders... object.

It woud be better to return the file as a byte array to the response in your controller action. This way the user will then get the choice to save the file in thier own filesystem. Something like this ought to do the trick:

public ActionResult ExportToExcel(IEnumerable<FourCourseAudit> audit)
{
    byte[] response;
    using (var excelFile = new ExcelPackage())
    {
        var worksheet = excelFile.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells["A1"].LoadFromCollection(Collection:audit,PrintHeaders:true);
        response = excelFile.GetAsByteArray();
    }
    return File(response, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Export.xlsx");
}