Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Export Data from Database Table to Excel File in ASP.NET MVC

0.00/5 (No votes)
26 Mar 2014 1  
I’m going to show one simple common method to export.

Introduction

Exporting data from database table to Excel is a frequently required feature in web and windows application. There are lots of ways for uploading data from 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 data table that is exported in Excel file.

The design of database table looks like the following:

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

publicActionResult Index()
        {
stringconstring = 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, we create a model in the model folder for data accessing. Add the following code to the model:

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 to the view for display 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 data table. Here, I am using Microsoft “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 Reference. Now click on Add Reference, now click on extension in 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();
varcmd = 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 Occurred 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 Export To Excel link and export data in .xls file. The Excel file looks like:

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here