Click here to Skip to main content
15,867,308 members
Articles / Web Development / ASP.NET
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.88/5 (11 votes)
26 Mar 2014CPOL1 min read 93.5K   16   15
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:

Image 1

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.

C#
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:

C#
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.

HTML
@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.

C#
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.

Image 2

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:

Image 3

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

License

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


Written By
Software Developer Pure Diets India Limited
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questioncan we have colum name having space like "Mobile No" Pin
Asutosha22-Mar-17 5:51
professionalAsutosha22-Mar-17 5:51 
GeneralMy vote of 2 Pin
Dharmesh .S. Patil8-Sep-15 0:26
professionalDharmesh .S. Patil8-Sep-15 0:26 
GeneralRe: My vote of 2 Pin
Yogesh Kumar Tyagi8-Sep-15 3:58
professionalYogesh Kumar Tyagi8-Sep-15 3:58 
GeneralRe: My vote of 2 Pin
Dharmesh .S. Patil8-Sep-15 18:16
professionalDharmesh .S. Patil8-Sep-15 18:16 
GeneralRe: My vote of 2 Pin
Yogesh Kumar Tyagi9-Sep-15 0:42
professionalYogesh Kumar Tyagi9-Sep-15 0:42 
Questionhow to export selected rows only to exel Pin
Oday M Saed7-Feb-15 4:36
professionalOday M Saed7-Feb-15 4:36 
if i had a view that contain a table with checkbox beside every row and a want to export selected rows only, thank you
AnswerRe: how to export selected rows only to exel Pin
Yogesh Kumar Tyagi9-Feb-15 6:05
professionalYogesh Kumar Tyagi9-Feb-15 6:05 
Questiongetting error Pin
Member 1114838727-Nov-14 12:46
Member 1114838727-Nov-14 12:46 
AnswerRe: getting error Pin
Yogesh Kumar Tyagi27-Nov-14 18:25
professionalYogesh Kumar Tyagi27-Nov-14 18:25 
BugRe: getting error Pin
Dharmesh .S. Patil8-Sep-15 0:12
professionalDharmesh .S. Patil8-Sep-15 0:12 
QuestionMy Vote of 5* Pin
Developer Rahul Sharma23-Jul-14 18:30
professionalDeveloper Rahul Sharma23-Jul-14 18:30 
AnswerRe: My Vote of 5* Pin
Yogesh Kumar Tyagi23-Jul-14 18:38
professionalYogesh Kumar Tyagi23-Jul-14 18:38 
GeneralRe: My Vote of 5* Pin
JJamesCChowdare10-Jul-17 1:39
JJamesCChowdare10-Jul-17 1:39 
QuestionA simpler way to do the same thing.. Pin
Michael Gledhill23-Jun-14 1:12
Michael Gledhill23-Jun-14 1:12 
AnswerRe: A simpler way to do the same thing.. Pin
Yogesh Kumar Tyagi23-Jun-14 2:17
professionalYogesh Kumar Tyagi23-Jun-14 2: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.