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.

namespaceExportToExcel.Models

{

    publicclassExportToExcelModel

    {

        publicint Id { get; set; }

        publicstring Name { get; set; }

        publicstring Email { get; set; }

        publicstring 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.

publicActionResultExportToExcel()

{

    int i = 0;

    int j = 0;

    stringsql = null;

    string data = null;

    Excel.ApplicationxlApp;

    Excel.WorkbookxlWorkBook;

    Excel.WorksheetxlWorkSheet;

    objectmisValue = System.Reflection.Missing.Value;

    xlApp = newExcel.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++;

    }

    charlastColumn = (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";

    SqlDataAdapterdscmd = newSqlDataAdapter(sql, con);

    DataSet ds = newDataSet();

   dscmd.Fill(ds);

   for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)

   {

        varnewj = 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);

    returnRedirectToAction("Index", "ExportToExcel");
}

privatevoidreleaseObject(objectobj)

{

    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 issue and query than feel free to contact me.