.NET Core 2 download Excel file from memory stream

Question

I was working on a .NET Core project where i was supposed to create a spreadsheet file from records and download it without saving it in server. Searching the above title didn't really help much but gave some directions. Also, most of the ways used in ASP.NET didn't work in .NET Core. So, i could finally came up with a solution that worked fine. I will be sharing it in the answer below for those who will do the same search like me.

Solution

First off, you will need to install the EPPlus.Core package. If you will install it via Package Manager Console, you can do:

Install-Package EPPlus.Core

Then add your using statement for OfficeOpenXml:

using OfficeOpenXml;

And then an example method to download the file will look like this:

using OfficeOpenXml;

namespace MyProject.Controllers
{
    public class SubscribersController : Controller
    {
        private readonly ApplicationDbContext _context;

        public SubscribersController(ApplicationDbContext context)
        {
            _context = context;
        }

        public async Task<IActionResult> ExportToExcel()
        {
            var stream = new System.IO.MemoryStream();
            using (ExcelPackage package = new ExcelPackage(stream))
            {
                var subscribers = await _context.Subscribers.ToListAsync();                        

                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Subscribers");

                worksheet.Cells[1, 1].Value = "Name";
                worksheet.Cells[1, 2].Value = "Email";
                worksheet.Cells[1, 3].Value = "Date Subscribed";
                worksheet.Row(1).Style.Font.Bold = true;

                for (int c = 2; c < subscribers.Count + 2; c++)
                {
                    worksheet.Cells[c, 1].Value = subscribers[c - 2].Name;
                    worksheet.Cells[c, 2].Value = subscribers[c - 2].Email;
                    worksheet.Cells[c, 3].Value = subscribers[c - 2].DateCreated.ToString();
                }

                package.Save();
            }

            string fileName = "Subscribers.xlsx";
            string fileType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            stream.Position = 0;
            return File(stream, fileType, fileName);
        }
    }
}

Comments

[Mardoxx]

File() has an overload which tales a stream, should be ok to seek it to 0 and then use this.

[Elnoor]

@Mardoxx thanks, that works too. I added the changes

[RoastBeast]

@Elnoor - won't you get an error on the stream.Position = 0; line because the package will have been disposed at that point, along with the stream?

[Elnoor]

@RoastBeast nope. Package will be disposed but not stream

[Jay]

This does not propertly dispose the memory stream. Better to use a using clause for the memory stream, where you call memStream.ToArray(), which stores the result in a byte array which is declared outside of the using clause scope. You can easily return a File result using a byte array.