Downloading Excel file after creating using EPPlus

Question

I am using the EPPlus library to generate an excel file which I successfully save in a folder on the server.

How can download this file to my local machine?

This is my code

public void CreateExcelFirstTemplate()
{   
   var fileName = "C:\ExcelDataTest\ExcellData.xlsx";
   var file = new FileInfo(fileName);
   using (var package = new OfficeOpenXml.ExcelPackage(file))
   {
      var worksheet = package.Workbook.Worksheets.FirstOrDefault(x => x.Name == "Attempts");
      worksheet = package.Workbook.Worksheets.Add("Assessment Attempts");
      worksheet.Row(1).Height = 20;

      worksheet.TabColor = Color.Gold;
      worksheet.DefaultRowHeight = 12;
      worksheet.Row(1).Height = 20;

      worksheet.Cells[1, 1].Value = "Employee Number";
      worksheet.Cells[1, 2].Value = "Course Code";

      var cells = worksheet.Cells["A1:J1"];
      var rowCounter = 2;
      foreach (var v in userAssessmentsData)
      {
        worksheet.Cells[rowCounter, 1].Value = v.CompanyNumber;
        worksheet.Cells[rowCounter, 2].Value = v.CourseCode;

        rowCounter++;
      }
      worksheet.Column(1).AutoFit();
      worksheet.Column(2).AutoFit();


      package.Workbook.Properties.Title = "Attempts";
      package.Save();
  }
}

Do you want to save this file on server or you just want to generate it each time that user requests a download? — Alex Art.
– Alex Art., Commented Jan 20, 2015 at 15:24
I want to generate the file each time the user requests a download — Arianule
– Arianule, Commented Jan 20, 2015 at 15:29

Keith Harrison · Accepted Answer · 2016-06-30 13:44:22Z

28

If you are generating this file on each request you don't need to save it on the server:

public void CreateExcelFirstTemplate()
{
       var fileName = "ExcellData.xlsx";
       using (var package = new OfficeOpenXml.ExcelPackage(fileName))
       {
          var worksheet = package.Workbook.Worksheets.FirstOrDefault(x => x.Name == "Attempts");
          worksheet = package.Workbook.Worksheets.Add("Assessment Attempts");
          worksheet.Row(1).Height = 20;

          worksheet.TabColor = Color.Gold;
          worksheet.DefaultRowHeight = 12;
          worksheet.Row(1).Height = 20;

          worksheet.Cells[1, 1].Value = "Employee Number";
          worksheet.Cells[1, 2].Value = "Course Code";

          var cells = worksheet.Cells["A1:J1"];
          var rowCounter = 2;
          foreach (var v in userAssessmentsData)
          {
            worksheet.Cells[rowCounter, 1].Value = v.CompanyNumber;
            worksheet.Cells[rowCounter, 2].Value = v.CourseCode;

            rowCounter++;
          }
          worksheet.Column(1).AutoFit();
          worksheet.Column(2).AutoFit();


          package.Workbook.Properties.Title = "Attempts";
          this.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
          this.Response.AddHeader(
                    "content-disposition",
                    string.Format("attachment;  filename={0}", "ExcellData.xlsx"));
          this.Response.BinaryWrite(package.GetAsByteArray());
      }
}

edited Jun 30, 2016 at 13:44

Keith Harrison

1,58614 silver badges22 bronze badges

answered Jan 20, 2015 at 15:44

Alex Art.

8,7813 gold badges32 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Prasad Joshi Over a year ago

I am using sample code provided by you.. But I am calling this using ajax call and when I execute it, it goes in ajax error and not able to download excel file. Thanks in advance.

Alex Art. Over a year ago

You don't need to use ajax in order to download file. Do a regular get/post. Because of the Content-Disposition headed that we set, browsers will know how to handle the response correctly (downloading an attachment). You can read about this header here: developer.mozilla.org/en-US/docs/Web/HTTP/Headers/…

jefmaus Over a year ago

where is the "Response" declaration? you use this.Reponse but not declare

RaZzLe Over a year ago

The file I download via this method is full of weird encrypted characters like PK�UU�H��3 Any ideas why? Full description is here: stackoverflow.com/questions/73399628/…

Steven V · Accepted Answer · 2015-01-20 15:42:51Z

11

Instead of using package.Save() you can use package.GetAsByteArray() which will return a byte array which you can then stream using FileResult or FileContentResult from the MVC action to trigger a file download. This method will allow you to download the file without saving it to the server first.

answered Jan 20, 2015 at 15:42

Steven V

16.7k3 gold badges66 silver badges77 bronze badges

1 Comment

CodeBreaker Over a year ago

This is the way to go, thank you for simplifying the seemingly simple!

Pankaj Kapare · Accepted Answer · 2015-01-20 15:38:13Z

2

Here is sample action to download file. Feel free to modify it as per your requirement.

public FileActionResult DownloadMyFile()
{
    var filePath = "C:\ExcelDataTest\ExcellData.xlsx";
    var fileName = "ExcellData.xlsx";
    var mimeType = "application/vnd.ms-excel";
    return File(new FileStream(filePath, FileMode.Open),mimeType, fileName);
}

answered Jan 20, 2015 at 15:38

Pankaj Kapare

7,8725 gold badges42 silver badges58 bronze badges

1 Comment

Panagiotis Kanavos Over a year ago

application/vnd.ms-excel is the MIME type for xls files, not xlsx

Collectives™ on Stack Overflow

Downloading Excel file after creating using EPPlus

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related