how to save database record in to excel using C#

narasiman rao

My code as follows

string str = "Server=(local);initial catalog=testingdb;Trusted_Connection=True";
SqlConnection objConn = new SqlConnection(str);
objConn.Open();
SqlDataAdapter daAuthors = new SqlDataAdapter("Select * From Empdetails", objConn);
DataSet dsPubs = new DataSet("Pubs");
daAuthors.FillSchema(dsPubs, SchemaType.Source, "Empdetails");
daAuthors.Fill(dsPubs,"Empdetails");

DataTable tblAuthors;
tblAuthors = dsPubs.Tables"Empdetails"];

foreach (DataRow drCurrent in tblAuthors.Rows)
{
Console.WriteLine("{0} {1} {2} {3} {4} {5} {6}",
drCurrent["ID"].ToString(),
drCurrent["Name"].ToString(),
drCurrent["Address"].ToString(),
drCurrent["City"].ToString(),
drCurrent["Mobile"].ToString(),
drCurrent["Actvie"].ToString(),
drCurrent["Type"].ToString());
}

When i execute the above code in command prompt output as follows

ID Name Address City Mobile Actvie Type
1 Ramya Road Kochi 9840575350 A 1
2 Giriesh Nagar Kochi 9745554689 A 1
3 Vimal BoagRoad Kochi 9645777566 A 1

But i want the above database record to be save in excel,

how to save the above database record into excel.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Karthik Elumalai10y ago
Hi narasiman rao,
Its very simple , you just need to add the following code.
//Export data to excel from datatable
protected void btnExportTable_Click(object sender, EventArgs e)
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}","Customers.xls"));
Response.ContentType = "application/ms-excel";
DataTable dt = BindDatatable();
string str = string.Empty;
foreach (DataColumn dtcol in dt.Columns)
{
Response.Write(str + dtcol.ColumnName);
str = "\t";
}
Response.Write("\n");
foreach (DataRow dr in dt.Rows)
{
str = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
Response.Write(str + Convert.ToString(dr[j]));
str = "\t";
}
Response.Write("\n");
}
Response.End();
}
In depth details :http://www.aspdotnet-suresh.com/2014/11/export-data-to-excel-from-datatable-gridview-aspnet-csharp.html
Hope it will surely solve your problem , kindly let me know your thoughts
thanks
karthik
alice yang10y ago
Hi,

Check the following links:

http://www.c-sharpcorner.com/UploadFile/83fe73/solutions-to-export-data-from-database-to-excel-in-C-Sharp/

http://csharp.net-informations.com/excel/csharp-excel-export.htm
Tapan Patel10y ago
My article is in approval list, but you can take a look at it on wordpress here.\

https://programminghulk.wordpress.com/2016/08/15/110/

Please mark it as answer if it resolves the issue.
Manas Mohapatra10y ago
Below links may help you:

http://www.aspsnippets.com/Articles/Export-DataSet-or-DataTable-to-Word-Excel-PDF-and-CSV-Formats.aspx

http://www.c-sharpcorner.com/uploadfile/deveshomar/exporting-datatable-to-excel-in-c-sharp-using-interop/

http://www.codescratcher.com/asp-net/export-datatable-to-excel-in-asp-net/
Vincent Maverick Durano10y ago
Here you go:http://www.aspsnippets.com/Articles/Write-data-to-Excel-file-xls-and-xlsx-in-ASPNet.aspx