Export to Excel in ASP.Net Core 2.0

Question

I used to export data to excel in asp.net mvc using below code

    Response.AppendHeader("content-disposition", "attachment;filename=ExportedHtml.xls");
    Response.Charset = "";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = "application/vnd.ms-excel";
    this.EnableViewState = false;
    Response.Write(ExportDiv.InnerHtml);
    Response.End();

When this Code Run it create a file and ask for a location to save

I tried working with NPOI and create Excel file very well but cant save file on client location .

Is there any way to make above code works on asp.net core 2.0 or any other way where I can save data in excel format on client machine ?

Answer 1

There are many ways to achieve that.

Option 1: save to wwwroot

You can generate the Excel and save it to the wwwroot folder. And then you can serve it as static content on the page.

For example you have a folder called 'temp' inside the wwwroot folder to contain all the newly generated excels.

<a href="\temp\development\user1\2018\5\9\excel1.xlsx" download>Download</a>

There are limitations on this approach. 1 of them is the new download attribute. It only works on modern browsers.

Option 2: byte array

Another way is to generate the Excel, convert it into byte array and send it back to the controller. For that I use a library called "EPPlus" (v: 4.5.1) which supports .Net Core 2.0.

The following is just some sample codes I put together to give you an idea. It's not production ready.

using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace DL.SO.Web.UI.Controllers
{
    public class ExcelController : Controller
    {
        public IActionResult Download()
        {
            byte[] fileContents;

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

                // Put whatever you want here in the sheet
                // For example, for cell on row1 col1
                worksheet.Cells[1, 1].Value = "Long text";

                worksheet.Cells[1, 1].Style.Font.Size = 12;
                worksheet.Cells[1, 1].Style.Font.Bold = true;

                worksheet.Cells[1, 1].Style.Border.Top.Style = ExcelBorderStyle.Hair;

                // So many things you can try but you got the idea.

                // Finally when you're done, export it to byte array.
                fileContents = package.GetAsByteArray();
            }

            if (fileContents == null || fileContents.Length == 0)
            {
                return NotFound();
            }

            return File(
                fileContents: fileContents,
                contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                fileDownloadName: "test.xlsx"
            );
        }
    }
}

Answer 2

Agreed with David Liang's answer.

Slide modifications if want to export whole DataTable.

            string export="export";
            DataTable dt = new DataTable();
            //Fill datatable
            dt = *something*

            byte[] fileContents;
            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add(export);
                worksheet.Cells["A1"].LoadFromDataTable(dt, true);
                fileContents = package.GetAsByteArray();
            }
            if (fileContents == null || fileContents.Length == 0)
            {
                return NotFound();
            }
            return File(
                fileContents: fileContents,
                contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                fileDownloadName: export + ".xlsx"
            );

Answer 3

Here is our solution to this:

using OfficeOpenXml;

public class XmlService
{
    // [...]
    public void getXlsxFile(SomeTableObject tbl, ref byte[] bytes)
    {
        using (ExcelPackage pck = new ExcelPackage())
        {
            ExcelWorksheet ws = pck.Workbook.Worksheets.Add(tbl.name);
            ws.Cells["A1"].LoadFromDataTable(tbl, true);
            bytes = pck.GetAsByteArray();
        }
    }
}

More information on EPPlus is available here and the source code above can be found at our open source repo (GPL).

Answer 4

Just use this code instead of your own:

    Response.Clear();
    Response.ContentType = "application/vnd.ms-excel";
    Response.Headers[HeaderNames.ContentDisposition] = "attachment; filename=ExportedHtml.xls";
    Response.WriteAsync(sb.ToString()).Wait();