.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.

Answer 1

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);
        }
    }
}