Moderators, if this should go into another forum that is more appropriate, please feel free to place it there.
I've seen a lot of examples of how to export data into an Excel spreadsheet using ASP.NET and most have been wrong.
This code works and is very simple to follow.
This is my first contribution to the forums, enjoy...Paul
// these namespaces need to be added to your code behind file
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace MySpot.UserPages
{
public partial class Journal : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySpotDBConnStr"].ConnectionString);
DataTable dt = new DataTable();
// regular page_load from .aspx file
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
}
}
// added a button with ID=btnDownload and double clicked it's onclick event to auto create method
protected void btnDownload_Click(object sender, EventArgs e)
{
string queryStr = "SELECT * from table";
SqlDataAdapter sda = new SqlDataAdapter(queryStr, conn);
sda.Fill(dt);
ExportTableData(dt);
}
// this does all the work to export to excel
public void ExportTableData(DataTable dtdata)
{
string attach = "attachment;filename=journal.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attach);
Response.ContentType = "application/ms-excel";
if (dtdata != null)
{
foreach (DataColumn dc in dtdata.Columns)
{
Response.Write(dc.ColumnName + "\t");
//sep = ";";
}
Response.Write(System.Environment.NewLine);
foreach (DataRow dr in dtdata.Rows)
{
for (int i = 0; i < dtdata.Columns.Count; i++)
{
Response.Write(dr[i].ToString() + "\t");
}
Response.Write("\n");
}
Response.End();
}
}
}
}
In IE9 the regular bar comes on down at the bottom asking to save or open, just click open and there it is
In FireFox that pesky download box comes on, then pops up a dialog, click open and there it is
in Opera it asks to save or open, click open and there it is.
Thanks, I tried a few of the ones posted here and I couldn't get any of them to work. I took bits and pieces and and rewrote the code to actually work. I had done this about a decade ago back when i worked for ENRON--before they went bankrupt.
I actually need to convert a word document to excel which has text and tables in it..the number of word documents very, they all should come into one excel sheet. here the problem is the tables are not in database they are in my system in a folder..I just
need to copy the word table into the excel sheet with respect to their cell numbers.Please help me with this mann!!!
Really nice. Data can be transfered from source to another in various ways. "Microsoft.Office.Interop.Excel" gives us a way to export data to "Excel" and source. I am presenting a link which has an article on "Exporting data in Excel", which
allows us to export data into any version of excel.
pnoneal
Member
353 Points
166 Posts
This is how to export data from ASP.Net database into Excel
Feb 11, 2012 04:40 PM|LINK
Moderators, if this should go into another forum that is more appropriate, please feel free to place it there.
I've seen a lot of examples of how to export data into an Excel spreadsheet using ASP.NET and most have been wrong.
This code works and is very simple to follow.
This is my first contribution to the forums, enjoy...Paul
// these namespaces need to be added to your code behind file
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace MySpot.UserPages
{
public partial class Journal : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySpotDBConnStr"].ConnectionString);
DataTable dt = new DataTable();
// regular page_load from .aspx file
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
}
}
// added a button with ID=btnDownload and double clicked it's onclick event to auto create method
protected void btnDownload_Click(object sender, EventArgs e)
{
string queryStr = "SELECT * from table";
SqlDataAdapter sda = new SqlDataAdapter(queryStr, conn);
sda.Fill(dt);
ExportTableData(dt);
}
// this does all the work to export to excel
public void ExportTableData(DataTable dtdata)
{
string attach = "attachment;filename=journal.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attach);
Response.ContentType = "application/ms-excel";
if (dtdata != null)
{
foreach (DataColumn dc in dtdata.Columns)
{
Response.Write(dc.ColumnName + "\t");
//sep = ";";
}
Response.Write(System.Environment.NewLine);
foreach (DataRow dr in dtdata.Rows)
{
for (int i = 0; i < dtdata.Columns.Count; i++)
{
Response.Write(dr[i].ToString() + "\t");
}
Response.Write("\n");
}
Response.End();
}
}
}
}
In IE9 the regular bar comes on down at the bottom asking to save or open, just click open and there it is
In FireFox that pesky download box comes on, then pops up a dialog, click open and there it is
in Opera it asks to save or open, click open and there it is.
eyell0
Member
298 Points
76 Posts
Re: This is how to export data from ASP.Net database into Excel
Feb 12, 2012 08:49 AM|LINK
http://blogs.msdn.com/b/erikaehrli/archive/2009/01/30/how-to-export-data-to-excel-from-an-asp-net-application-avoid-the-file-format-differ-prompt.aspx
http://weblogs.asp.net/gunnarpeipman/archive/2007/09/16/exporting-gridview-data-to-excel.aspx
Dino He - MS...
Star
8068 Points
1023 Posts
Microsoft
Re: This is how to export data from ASP.Net database into Excel
Feb 13, 2012 09:34 AM|LINK
Hi
Very appreciate you provide this wonderful codes, I try it and it works fine.
I suggest you write a article in your bolg next time. So if any one have this
problem we can inhert your bolg to him, that will help more pepole~
Anyway thanks for you contribution, good job.
Dino
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
pnoneal
Member
353 Points
166 Posts
Re: This is how to export data from ASP.Net database into Excel
Feb 14, 2012 01:58 AM|LINK
Thanks, I tried a few of the ones posted here and I couldn't get any of them to work. I took bits and pieces and and rewrote the code to actually work. I had done this about a decade ago back when i worked for ENRON--before they went bankrupt.
Paul
vivekreddy
Member
200 Points
96 Posts
Re: This is how to export data from ASP.Net database into Excel
Feb 24, 2012 05:52 AM|LINK
Can we use this with out any database conection?
I actually need to convert a word document to excel which has text and tables in it..the number of word documents very, they all should come into one excel sheet. here the problem is the tables are not in database they are in my system in a folder..I just need to copy the word table into the excel sheet with respect to their cell numbers.Please help me with this mann!!!
Thanks in adv!
bhaskar.mule
Contributor
2270 Points
659 Posts
Re: This is how to export data from ASP.Net database into Excel
May 30, 2012 06:58 AM|LINK
hi
hope it wil help you
http://www.aspdotnet-sharepoint.com/2011/11/export-data-to-excel-in-c.html
Site:Rare technical solutions
raghu1
Contributor
2004 Points
558 Posts
Re: This is how to export data from ASP.Net database into Excel
Jun 05, 2012 06:19 PM|LINK
I used closedXML to export data table to excel and it works fine :
http://closedxml.codeplex.com/
arun banik
Member
90 Points
21 Posts
Re: This is how to export data from ASP.Net database into Excel
Feb 02, 2013 11:38 AM|LINK
Really nice. Data can be transfered from source to another in various ways. "Microsoft.Office.Interop.Excel" gives us a way to export data to "Excel" and source. I am presenting a link which has an article on "Exporting data in Excel", which allows us to export data into any version of excel.
http://www.encodedna.com/2013/01/asp.net-export-to-excel.htm
Arun