How to Export Database Records into Excel File in ASP.NET MVC

Last Updated: June 7, 2021

Here in this tutorial I will teach you how to export your Database Records into Excel file using 2 different methods. You can choose any one of these methods when doing your data export to excel file.

Now Create a PDF file in ASP.NET with iTextSharp DLL. You can easily use this DLL to create unlimited PDF files free of charge.

Method 1: Export to Excel using Response.ContentType = “application/vnd.ms-excel”

In this method the DataSet is filled with Records from the Database. Then I loop through these records in the DataSet’s table and convert them to an HTML table. Finally the HTML table is wrote into an excel file.

View

Add the following code to the Index View:

@using (Html.BeginForm("Index", "ExportExcel"))
{
    <button id="submitButton" type="submit">Create Simple Excel</button>
}

Controller

In the Controller add the below code:

[HttpPost]
[ActionName("Index")]
public ActionResult Index_Post()
{
    //Fill dataset with records
    DataSet dataSet = GetRecordsFromDatabase();
 
    StringBuilder sb = new StringBuilder();
 
    sb.Append("<table>");
 
    //LINQ to get Column names
    var columnName = dataSet.Tables[0].Columns.Cast<DataColumn>()
                         .Select(x => x.ColumnName)
                         .ToArray();
    sb.Append("<tr>");
    //Looping through the column names
    foreach (var col in columnName)
        sb.Append("<td>" + col + "</td>");
    sb.Append("</tr>");
 
    //Looping through the records
    foreach (DataRow dr in dataSet.Tables[0].Rows)
    {
        sb.Append("<tr>");
        foreach (DataColumn dc in dataSet.Tables[0].Columns)
        {
            sb.Append("<td>" + dr[dc] + "</td>");
        }
        sb.Append("</tr>");
    }
 
    sb.Append("</table>");
 
    //Writing StringBuilder content to an excel file.
    Response.Clear();
    Response.ClearContent();
    Response.ClearHeaders();
    Response.Charset = "";
    Response.Buffer = true;
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("content-disposition", "attachment;filename=UserReport.xls");
    Response.Write(sb.ToString());
    Response.Flush();
    Response.Close();
 
    return View();
}
 
DataSet GetRecordsFromDatabase()
{
    DataSet dataSet = new DataSet();
 
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "Select * FROM Sale";
    cmd.Connection = conn;
 
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
    sqlDataAdapter.SelectCommand = cmd;
    sqlDataAdapter.Fill(dataSet);
 
    return dataSet;
}

Explanation:

I created an Action called Index_Post.
Next I call function GetRecordsFromDatabase() which fills a DataSet with Records from the Database.
Then by using LINQ I select the column name of that DataSet’s table and put them in a variable called columnName, then I loop through them and add the names of these columns in ‘tr’ & ‘td’.
I use StringBuilder class to create the HTML table structure.
Then through the foreach command, I loop through each record of the DataSet’s table and add them in ‘tr’ & ‘td’ elements.

When the HTML table is created I write the StringBuilder object’s content (which is an HTML Table) into an excel file, and in this way I accomplish my Export to Excel procedure

Notice these 3 lines of code:

Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=UserReport.xls");
Response.Write(sb.ToString());

When writing the content to an excel file, you need to put Response.ContentType as application/vnd.ms-excel, and pass content-disposition”, “attachment;filename=UserReport.xls for the parameter of Response.AddHeader function.

Here ‘UserReport.xls’ is the name of the excel file which you can change if you wish.

Finally using the Response.Write() function I write the content to the excel file.

This is the simplest and fastest method to accomplish the Export to Excel task in ASP.NET MVC.

I have also written a tutorial to Read Excel file and show it’s content in an HTML. Also check my other tutorial on Import Excel file’s content into database table.

Method 2: Export to Excel using Microsoft.Office.Interop.Excel DLL

In this method you have to add Microsoft.Office.Interop.Excel DLL to your project. This DLL is available from NuGet.

View

In the view add the below code:

@using (Html.BeginForm("Interop", "ExportExcel"))
{
    <button id="interOpButton" type="submit">Create Microsoft Office Interop Excel</button>
}

Controller

In the controller you add the namespace using Microsoft.Office.Interop.Excel; and then create an Interop Action which has the code given below:

[HttpPost]
public ActionResult Interop()
{
    DataSet dataSet = GetRecordsFromDatabase();
     
    //Creating Object of Microsoft.Office.Interop.Excel and creating a Workbook
    var excelApp = new Application();
    excelApp.Visible = true;
    excelApp.Workbooks.Add();
 
    Worksheet workSheet = (Worksheet)excelApp.ActiveSheet; //creating excel worksheet
    workSheet.Name = "Sale"; //name of excel file
 
    //LINQ to get Column of dataset table
    var columnName = dataSet.Tables[0].Columns.Cast<DataColumn>()
                         .Select(x => x.ColumnName)
                         .ToArray();
    int i = 0;
    //Adding column name to worksheet
    foreach (var col in columnName)
    {
        i++;
        workSheet.Cells[1, i] = col;
    }
 
    //Adding records to worksheet
    int j;
    for (i = 0; i < dataSet.Tables[0].Rows.Count; i++)
    {
        for (j = 0; j < dataSet.Tables[0].Columns.Count; j++)
        {
            workSheet.Cells[i + 2, j+1] = Convert.ToString(dataSet.Tables[0].Rows[i][j]);
        }
    }
     
    //Saving the excel file to “e” directory
    workSheet.SaveAs("e:\\" + workSheet.Name);
    return RedirectToAction("Index");
}

Explanation

In this method first the Interop Object is created and a ‘workbook’ is added. Next a ‘Worksheet’ is created and is given a name. The data will be added to this worksheet and then the excel file will be created using this DLL.
Like what I did before, here I looped through the columns and records and add them to the worksheet. The code workSheet.Cells[row, column] = “value” will add the value to the rows and columns of the worksheet.
In the end ,when all the data is added to worksheet, the excel file is saved to a location.

In this way I did the Export to Excel of my records using the DLL method.

I used E drive in my code which you can change if you desire. Also check the download link.

DOWNLOAD

Conclusion

I provided the 2 methods to Export your data to an Excel File in ASP.NET method. Both these methods are extremely useful and will help you in your project development.