Click here to Skip to main content
11,925,292 members (61,074 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


15 bookmarked

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

, 26 Mar 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
I’m going to show one simple common method to export.


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();
SqlDataAdapter da = newSqlDataAdapter(query, con);
IList<ExportToExcelModel> model = newList<ExportToExcelModel>();
for (int i = 0; i <dt.Rows.Count; i++)
                    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:

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";
<ahref="ExportToExcel"class="m-btn red">Export To Excel</a>
@Html.DisplayNameFor(model =>model.Name)
@Html.DisplayNameFor(model =>model.Email)
@Html.DisplayNameFor(model =>model.Mobile)
@foreach (var item in Model) {
@Html.DisplayFor(modelItem =>item.Name)
@Html.DisplayFor(modelItem =>item.Email)
@Html.DisplayFor(modelItem =>item.Mobile)

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.

int i = 0;
int j = 0;
stringsql = null;
string data = null;
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);
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;
charlastColumn = (char)(65 + reader.FieldCount - 1);
xlWorkSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
sql = "SELECT * FROM Person";
SqlDataAdapterdscmd = newSqlDataAdapter(sql, con);
DataSet ds = newDataSet();
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;
xlWorkBook.Close(true, misValue, misValue);
returnRedirectToAction("Index", "ExportToExcel");
obj = null;
obj = null;
//MessageBox.Show("Exception Occurred while releasing object " + ex.ToString());

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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Yogesh Kumar Tyagi
Software Developer Pure Diets India Limited
India India
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 2 Pin
Dharmesh .S. Patil8-Sep-15 1:26
professionalDharmesh .S. Patil8-Sep-15 1:26 
GeneralRe: My vote of 2 Pin
Yogesh Kumar Tyagi8-Sep-15 4:58
professionalYogesh Kumar Tyagi8-Sep-15 4:58 
GeneralRe: My vote of 2 Pin
Dharmesh .S. Patil8-Sep-15 19:16
professionalDharmesh .S. Patil8-Sep-15 19:16 
GeneralRe: My vote of 2 Pin
Yogesh Kumar Tyagi9-Sep-15 1:42
professionalYogesh Kumar Tyagi9-Sep-15 1:42 
Questionhow to export selected rows only to exel Pin
Oday M Saed7-Feb-15 5:36
memberOday M Saed7-Feb-15 5:36 
AnswerRe: how to export selected rows only to exel Pin
Yogesh Kumar Tyagi9-Feb-15 7:05
professionalYogesh Kumar Tyagi9-Feb-15 7:05 
Questiongetting error Pin
Member 1114838727-Nov-14 13:46
memberMember 1114838727-Nov-14 13:46 
AnswerRe: getting error Pin
Yogesh Kumar Tyagi27-Nov-14 19:25
professionalYogesh Kumar Tyagi27-Nov-14 19:25 
BugRe: getting error Pin
Dharmesh .S. Patil8-Sep-15 1:12
professionalDharmesh .S. Patil8-Sep-15 1:12 
QuestionMy Vote of 5* Pin
Developer Rahul Sharma23-Jul-14 19:30
professionalDeveloper Rahul Sharma23-Jul-14 19:30 
AnswerRe: My Vote of 5* Pin
Yogesh Kumar Tyagi23-Jul-14 19:38
professionalYogesh Kumar Tyagi23-Jul-14 19:38 
QuestionA simpler way to do the same thing.. Pin
MikeGledhill23-Jun-14 2:12
memberMikeGledhill23-Jun-14 2:12 
AnswerRe: A simpler way to do the same thing.. Pin
Yogesh Kumar Tyagi23-Jun-14 3:17
professionalYogesh Kumar Tyagi23-Jun-14 3:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.151126.1 | Last Updated 26 Mar 2014
Article Copyright 2014 by Yogesh Kumar Tyagi
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid