In this article I will explain with an example, how to export DataSet or DataTable to Excel file in ASP.Net 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, please refer my article ASP.Net MVC Hello World Tutorial with Sample Program example.
 
 

Install ClosedXML package using Nuget

In order to install ClosedXML library using Nuget, please refer my article Install ClosedXML Nuget Package.
 
 

Database

I have made use of the following table Customers with the schema as follows.

Export DataTable (DataSet) to Excel file in ASP.Net MVC

 
I have already inserted few records in the table.

Export DataTable (DataSet) to Excel file in ASP.Net MVC

 
Note: You can download the database table SQL by clicking the download link below.
            Download SQL file
 
 

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 following Action methods.

Action method for handling GET operation

Inside this Action method, simply the View is returned.
 

Action method for handling POST operation

This Action method is executed when the Export Button is clicked.
Inside this Action method, an object of XLWorkbook is created and the GetCustomers method (explained later) is called.
The records returned from the GetCustomers method (explained later) are stored into a DataTable object which is added as Worksheet collection to the XLWorkbook object.
Finally, the XLWorkbook object is saved to a MemoryStream class object then it is converted to BYTE Array and exported and downloaded as Excel file using the File function.
 

GetCustomers

Inside this method, first the connection is read from the ConnectionStrings section of the Web.Config file.
Then, a connection to the database is established using the SqlConnection class.
The records are fetched using SqlDataAdapter class and stored into a DataSet object which is returned.
Note: For more details on SqlDataAdapter, please refer my article Implementing SqlDataAdapter in ASP.Net MVC.
 
public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        return View();
    }
 
    [HttpPost]
    public ActionResult 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", "Customers.xlsx");
            }
        }
    }
 
    private DataSet GetCustomers()
    {
        DataSet ds = new DataSet();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string sql "SELECT CustomerId, Name, Country FROM Customers";
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(sql, con))
            {
                sda.Fill(ds);
            }
        }
 
        return ds;
    }
}
 
 

View

HTML Markup

Inside the View, in the very first line the Customer class is declared as Model for the View.
The View consists of an HTML Form which has been created using the Html.BeginForm method with the following parameters.
ActionName – Name of the Action. In this case the name is Export.
ControllerName – Name of the Controller. In this case the name is Home.
FormMethod – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
The View also consists of a Submit Button.
@model IEnumerable<Export_Excel_MVC.Customer>
 
@{
     Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    @using (Html.BeginForm("Export", "Home", FormMethod.Post))
    {
        <input type="submit" value="Export" />
    }
</body>
</html>
 
 

Screenshots

Form

Export DataTable (DataSet) to Excel file in ASP.Net MVC

 

The Excel File

Export DataTable (DataSet) to Excel file in ASP.Net MVC

 
 

Demo

 
 

Downloads



Other available versions