Export Data From Database Table to Excel File in ASP.Net MVC

Export data from database table to Excel is a frequently required feature in web and window application, there are many ways to upload data from a database to Excel and here I'm going to show one simple common method to export.

To start this task you need to create a database for storing data in a data table that exports an Excel file.

The design of the database table looks like the following.

First of all open Visual Studio 2012 then select "New project" and click on ASP.NET MVC4 Web Application in Visual C#. Name the project ExportToExcel or whatever you like. Create a controller named ExportToExcelController and in this controller create an ActionResult method named Index. Here I select data from the database for display on index view:

Public ActionResult Index()
{
    String constring = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
    SqlConnection con = newSqlConnection(constring);
    string query = "select * From Person";
    DataTabledt = newDataTable();
    con.Open();
    SqlDataAdapter da = newSqlDataAdapter(query, con);
    da.Fill(dt);
    Con.Close();
    IList<ExportToExcelModel> model = newList<ExportToExcelModel>();
    for (int i = 0; i <dt.Rows.Count; i++)
    {
        model.Add(newExportToExcelModel()
        {
             Id = Convert.ToInt32(dt.Rows[i]["Id"]),
             Name = dt.Rows[i]["Name"].ToString(),
             Email = dt.Rows[i]["Email"].ToString(),
             Mobile = dt.Rows[i]["Mobile"].ToString(),
        });
    }
    return View(model);
}

Now create a view, right-click on the Indexaction method and select Add View and then click OK. Write the following code to the view for displaying the data.

namespace ExportToExcel.Models
{
    public class ExportToExcelModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Mobile { get; set; }
    }
}

Now create a view, right-click on the Indexaction method and select Add View and then click OK. Write the following code for the view for displaying data.

@model IEnumerable<ExportToExcel.Models.ExportToExcelModel>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
<ahref="ExportToExcel"class="m-btn red">Export To Excel</a>
</p>
<table>
<tr>
<th>@Html.DisplayNameFor(model =>model.Name)</th>
<th>@Html.DisplayNameFor(model =>model.Email)</th>
<th>@Html.DisplayNameFor(model =>model.Mobile)</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>@Html.DisplayFor(modelItem =>item.Name)</td>
<td>@Html.DisplayFor(modelItem =>item.Email)</td>
<td>@Html.DisplayFor(modelItem =>item.Mobile)</td>
</tr>
}
</table>

Now create another actionmethod”ExportToExcel()” for exporting data from the data table. Here I am using Microsoft's “Microsoft.Office.Interop.Excel” library (for this your system must contain Microsoft Office 2007) for converting the data into Excel form. You can add this library by right-clicking on References. Now click on Add Reference, now click on extension in the Assembly tab now select Microsoft.Office.Interop.Excel.

public Action ResultExportToExcel()
{
    int i = 0;
    int j = 0;
    string sql = null;
    string data = null;
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;
    xlApp = new Excel.Application();
    xlApp.Visible = false;
    xlWorkBook = (Excel.Workbook)(xlApp.Workbooks.Add(Missing.Value));
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
    string conn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
    SqlConnection con = newSqlConnection(conn);
    con.Open();
    var cmd = newSqlCommand("SELECT TOP 0 * FROM Person", con);
    var reader = cmd.ExecuteReader();
    int k = 0;
    for (i = 0; i <reader.FieldCount; i++)
    {
        data = (reader.GetName(i));
        xlWorkSheet.Cells[1, k + 1] = data;
        k++;
    }
    char lastColumn = (char)(65 + reader.FieldCount - 1);
    xlWorkSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
    xlWorkSheet.get_Range("A1", lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
    reader.Close();
    sql = "SELECT * FROM Person";
    SqlDataAdapter dscmd = newSqlDataAdapter(sql, con);
    DataSet ds = newDataSet();
    dscmd.Fill(ds);
    for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
    {
        var newj = 0;
        for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
        {
            data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
           xlWorkSheet.Cells[i + 2, newj + 1] = data;
           newj++;
        }
    }
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();
    release Object(xlWorkSheet);
    release Object(xlWorkBook);
    release Object(xlApp);
    return RedirectToAction("Index", "ExportToExcel");
}
private void releaseObject(object obj)
{
    try
    {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
         obj = null;
   }
   catch
   {
       obj = null;
        //MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
   }
   finally
   {
       GC.Collect();
   }
}

Now build and run your application.

This is your Index page with Export to Excel link. Now click on the Export To Excel link and export the data as a .xls file. The Excel file will look like.

 

If you have any issues and queries then feel free to contact me.