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

Export data from a database table to Excel is a frequently required feature in web and window applications. 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 that stores 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 = new SqlConnection(constring);    
    string query = "SELECT * FROM Person";    
    DataTable dt = new DataTable();    
    con.Open();    
    SqlDataAdapter da = new SqlDataAdapter(query, con);    
    da.Fill(dt);    
    con.Close();       
    IList<ExportToExcelModel> model = new List<ExportToExcelModel>();    
    for (int i = 0; i < dt.Rows.Count; i++)    
    {    
        model.Add(new ExportToExcelModel()    
        {    
            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, select Add View, and then click OK. Write the following code to the view to display 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, select Add View, and then click OK. Write the following code for the view to display data.

@model IEnumerable<ExportToExcel.Models.ExportToExcelModel>

@{
    ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
    <a href="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 action method” 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 ActionResult ExportToExcel()
{
    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 = new SqlConnection(conn);
    con.Open();
    
    var cmd = new SqlCommand("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 = new SqlDataAdapter(sql, con);
    DataSet ds = new DataSet();
    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();
    
    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(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 an 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 this.

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