Generate and download .xlsx file using EPPlus in .net

Question

I was trying to generate a .xlsx file and download it by using EPPlus package in .net. Below is the code I have tried.

using (var package = new OfficeOpenXml.ExcelPackage())
            {
                var workbook = package.Workbook;
                var worksheet = workbook.Worksheets.Add("SampleData");

                // Adding header
                worksheet.Cells[1, 1].Value = "Data1";
                worksheet.Cells[1, 2].Value = "Data2";
                worksheet.Cells[1, 3].Value = "Data3";
                worksheet.Cells[1, 4].Value = "Data4";
                worksheet.Cells[1, 5].Value = "Data5";
                worksheet.Cells[1, 6].Value = "Data6";

                // Save to a memory stream
                using (var stream = new MemoryStream())
                {
                    package.SaveAs(stream);

                    // Reset stream position
                    stream.Position = 0;

                    // Set HTTP Response
                    
                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8";
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=SampleData.xlsx");
                    HttpContext.Current.Response.BinaryWrite(stream.ToArray());
                    HttpContext.Current.Response.End();
                }
            }

File is getting downloaded. But when opening it in Excel it showing below error.

"Excel cannot open the file 'SampleData.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file"

What could be the reason for this?.

Thanks

See my answer hereL stackoverflow.com/a/40360873/5836671

VDWWD
– VDWWD

2025-01-21 14:17:55 +00:00
Commented Jan 21 at 14:17 — VDWWD
– VDWWD, Commented Jan 21 at 14:17

Rohan Rao · Accepted Answer · 2025-01-21 14:15:26Z

One reason I could clearly see why it is making a trouble is due to the Stream flushing that is required after saving the file.

Generally working with files involves memory stream and when you are working with writing some data into that stream, it could be possible that the stream might not be flushed before saving the file.

To fix this you can try adding stream.Flush(); just after package.SaveAs(stream) line.

So your entire code looks like this:

using (var package = new OfficeOpenXml.ExcelPackage())
{
   var workbook = package.Workbook;
   var worksheet = workbook.Worksheets.Add("SampleData");

   // Adding header
   worksheet.Cells[1, 1].Value = "Data1";
   worksheet.Cells[1, 2].Value = "Data2";
   worksheet.Cells[1, 3].Value = "Data3";
   worksheet.Cells[1, 4].Value = "Data4";
   worksheet.Cells[1, 5].Value = "Data5";
   worksheet.Cells[1, 6].Value = "Data6";

   // Save to a memory stream
   using (var stream = new MemoryStream())
   {
       package.SaveAs(stream);

       stream.Flush(); // this needs to be added

       stream.Position = 0;

       // Set HTTP Response
       HttpContext.Current.Response.Clear();
       HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
       HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=SampleData.xlsx");
       HttpContext.Current.Response.BinaryWrite(stream.ToArray());
       HttpContext.Current.Response.End();
    }
}

This would fix your issue.

Collectives™ on Stack Overflow

Generate and download .xlsx file using EPPlus in .net

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related