In this article I will explain with an example, how to export DataSet or DataTable to Excel file in ASP.Net Core MVC.
Then the DataTable is exported and downloaded as Microsoft Excel file using ClosedXML Excel Library which is a wrapper for OpenXml Excel library.
I have made use of the following table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.
I have already inserted few records in the table.
Note: You can download the database table SQL by clicking the download link below.
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.
2. Now you will need to look for ClosedXML package and once found, you need to click the Install Button.
You can also use to install the ClosedXML package using the following command.
Install-Package ClosedXML -Version 0.95.4
You will need to import the following namespaces.
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();
public IActionResult Export()
using (XLWorkbook wb = new XLWorkbook())
DataTable dt = this.GetCustomers().Tables;
using (MemoryStream stream = new MemoryStream())
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))
Inside the View, the DataSet class is declared as Model for the View.
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.
Layout = null;
<meta name="viewport" content="width=device-width"/>
<table cellpadding="0" cellspacing="0">
@foreach (DataRow row in Model.Tables.Rows)
<form method="post" asp-controller="Home" asp-action="Export">
<input type="submit" value="Export"/>
Grid (Html Table)
Exported Excel File