Click here to Skip to main content
Click here to Skip to main content

Tagged as

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

, 26 Mar 2014
Rate this:
Please Sign up or sign in to vote.
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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Yogesh Kumar Tyagi
Software Developer Pure Diets India Limited
India India
No Biography provided
Follow on   Google+

Comments and Discussions

 
QuestionMy Vote of 5* PinprofessionalDeveloper Rahul Sharma23-Jul-14 18:30 
AnswerRe: My Vote of 5* PinprofessionalYogesh Kumar Tyagi23-Jul-14 18:38 
QuestionA simpler way to do the same thing.. PinmemberMikeGledhill23-Jun-14 1:12 
AnswerRe: A simpler way to do the same thing.. PinprofessionalYogesh Kumar Tyagi23-Jun-14 2:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web04 | 2.8.140814.1 | Last Updated 26 Mar 2014
Article Copyright 2014 by Yogesh Kumar Tyagi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid