CODEWITHSUNDEEP
c#.netexcelxlsxepplus
Midlaj
Midlaj

Reputation: 21

Generate and download .xlsx file using EPPlus in .net

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

Upvotes: 0

Views: 51

Answers (1)

Rohan Rao
Rohan Rao

Reputation: 2603

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.

Upvotes: 0

Related Questions