In this article I will explain with an example, how to export DataSet or DataTable to Excel file in ASP.Net Core MVC.
The data from the database is fetched using ADO.Net into a DataTable.
Then the DataTable is exported and downloaded as Microsoft Excel file using ClosedXML Excel Library which is a wrapper for OpenXml Excel library.
Note: For beginners in ASP.Net MVC Core, please refer my article ASP.Net MVC Core Hello World Tutorial with Sample Program example.
 
 
Database
I have made use of the following table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.
ASP.Net Core MVC: Export DataSet (DataTable) to Excel file
 
I have already inserted few records in the table.
ASP.Net Core MVC: Export DataSet (DataTable) to Excel file
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 
Installing and adding reference of ClosedXML Library
In order to install and add reference of ClosedXML library, you will need to:-
1. Right Click the Project in Solution Explorer and click Manage NuGet Packages from the Context Menu.
ASP.Net Core MVC: Export DataSet (DataTable) to Excel file
 
2. Now you will need to look for ClosedXML package and once found, you need to click the Install Button.
ASP.Net Core MVC: Export DataSet (DataTable) to Excel file
 
You can also use to install the ClosedXML package using the following command.
Install-Package ClosedXML -Version 0.95.4
 
 
Namespaces
You will need to import the following namespaces.
using System.IO;
using System.Data;
using System.Data.SqlClient;
using ClosedXML.Excel;
 
 
Controller
The Controller consists of two Action methods.
Action method for handling GET operation
Inside this Action method, the records are fetched from the Customers Table using ADO.Net.
The records are inserted into a DataSet using SqlDataAdapter class object.
Finally, the DataSet is returned to the View.
 
Action method for handling the Excel File Export and Download operation
This Action method is executed when the Export Submit button is clicked.
The Customer records are fetched from the Customers Table into a DataTable.
Then the DataTable is added to the Worksheets collection of the ClosedXML’s XLWorkbook object.
Then XLWorkbook object is saved to a MemoryStream object which then is converted to Byte Array and exported and downloaded as Excel file using the File function.
public class HomeController : Controller
{
    // GET: Home
    public IActionResult Index()
    {
        DataSet ds = this.GetCustomers();
        return View(ds);
    }
 
    [HttpPost]
    public IActionResult Export()
    {
        using (XLWorkbook wb = new XLWorkbook())
        {
            DataTable dt = this.GetCustomers().Tables[0];
            wb.Worksheets.Add(dt);
            using (MemoryStream stream = new MemoryStream())
            {
                wb.SaveAs(stream);
                return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx");
            }
        }
    }
 
    private DataSet GetCustomers()
    {
        DataSet ds = new DataSet();
        string constr = @"Data Source=.\SQL2019;Initial Catalog=AjaxSamples;Integrated Security=true";
        using (SqlConnection con = new SqlConnection(constr))
        {
            string query = "SELECT * FROM Customers";
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.Connection = con;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    sda.Fill(ds);
                }
            }
        }
 
        return ds;
    }
}
 
 
View
Inside the View, the DataSet class is declared as Model for the View.
Displaying data
For displaying the records, an HTML Table is used. A loop will be executed over the rows of the DataTable which will generate the HTML Table rows with the Customer records.
 
Exporting data to Excel
There is also an HTML Submit button enclosed inside a Form with the Action method specified as Export.
When this Button will be clicked, the Grid (Html Table) data will be exported and downloaded as Excel file.
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@using System.Data
@model DataSet
@{
    Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width"/>
    <title>Index</title>
</head>
<body>
    <table cellpadding="0" cellspacing="0">
        <tr>
            <th>Customer Id</th>
            <th>Name</th>
            <th>Country</th>
        </tr>
        @foreach (DataRow row in Model.Tables[0].Rows)
        {
            <tr>
                <td>@row["CustomerId"]</td>
                <td>@row["Name"]</td>
                <td>@row["Country"]</td>
            </tr>
        }
    </table>
    <br/>
    <br/>
    <form method="post" asp-controller="Home" asp-action="Export">
        <input type="submit" value="Export"/>
    </form>
</body>
</html>
 
 
Screenshots
Grid (Html Table)
ASP.Net Core MVC: Export DataSet (DataTable) to Excel file
 
Exported Excel File
ASP.Net Core MVC: Export DataSet (DataTable) to Excel file
 
 
Downloads
Download Code