Export to Excel from .NET Core 3.0 / .NET Standard in Minutes

I’ve had a few post addressing this before and was so happy to finally see this issue has a quick solution that does NOT rely on Report Viewer/Report Designer (rdlc) and remains portable!

It’s simple using ClosedXML:

  1. Install NuGet Package ClosedXML
  2. Set ContentType
  3. Set FileName
  4. Create a workbook
  5. Create a worksheet
  6. Populating top headers and use a forloop for data rows
  7. Save to MemoryStream and create file for download

The code for the quick answer:

public async Task<IActionResult> DownloadExcel()
{
    List<ProjectQuotes> filteredProjectQuotes = await GetProjectQuotesAsync();

    string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    string fileName = "ProjectQuotes(" + DateTime.Now.ToString().Replace(" ", "_") + ").xlsx";
      
    try
    {
        using (var workbook = new XLWorkbook())
        {
            //define by row, column the headings
            IXLWorksheet worksheet = workbook.Worksheets.Add("Quotes");
            worksheet.Cell(1, 1).Value = "Id";
            worksheet.Cell(1, 2).Value = "ProjectName";
            worksheet.Cell(1, 8).Value = "DateAccepted";
            worksheet.Cell(1, 9).Value = "ApprovedBy";

            //set initial row for data 
            int excelRow = 2;
            foreach (var quote in filteredProjectQuotes)
            {
                worksheet.Cell(excelRow, 1).Value = quote.Id;
                worksheet.Cell(excelRow, 2).Value = quote.ProjectName;
                worksheet.Cell(excelRow, 8).Value = quote.DateAccepted;
                worksheet.Cell(excelRow, 9).Value = quote.ApprovedBy;

                excelRow++;
            }

            using (var stream = new MemoryStream())
            {
                workbook.SaveAs(stream);
                var content = stream.ToArray();
                return File(content, contentType, fileName);
            }
        }
    }
    catch (Exception ex)
    {
        TempData["error"] = ex;
        return View();
    }
}

 

You may also like