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;
}
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.