This is how to export data from ASP.Net database into Excelhttp://forums.asp.net/t/1768549.aspx?This+is+how+to+export+data+from+ASP+Net+database+into+ExcelWed, 17 Sep 2014 11:41:43 -040017685494827740http://forums.asp.net/p/1768549/4827740.aspx?This+is+how+to+export+data+from+ASP+Net+database+into+ExcelThis is how to export data from ASP.Net database into Excel
<p>Moderators, if this should go into another forum that is more appropriate, please feel free to place it there.</p>
<p>I've seen a lot of examples of how to export data into an Excel spreadsheet using ASP.NET and most have been wrong.<br>
This code works and is very simple to follow.<br>
This is my first contribution to the forums, enjoy...Paul</p>
<p>// these namespaces need to be added to your code behind file<br>
using System.Configuration;<br>
using System.Data.SqlClient;<br>
using System.Data;</p>
<p>namespace MySpot.UserPages<br>
{<br>
&nbsp;&nbsp;&nbsp; public partial class Journal : System.Web.UI.Page<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[&quot;MySpotDBConnStr&quot;].ConnectionString);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DataTable dt = new DataTable();<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; // regular page_load from .aspx file<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; protected void Page_Load(object sender, EventArgs e)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (!IsPostBack)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; // added a button with ID=btnDownload and double clicked it's onclick event to auto create method<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; protected void btnDownload_Click(object sender, EventArgs e)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; string queryStr = &quot;SELECT * from table&quot;;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SqlDataAdapter sda = new SqlDataAdapter(queryStr, conn);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sda.Fill(dt);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ExportTableData(dt);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; // this does all the work to export to excel<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public void ExportTableData(DataTable dtdata)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; string attach = &quot;attachment;filename=journal.xls&quot;;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.ClearContent();<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.AddHeader(&quot;content-disposition&quot;, attach);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.ContentType = &quot;application/ms-excel&quot;;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (dtdata != null)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; foreach (DataColumn dc in dtdata.Columns)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.Write(dc.ColumnName &#43; &quot;\t&quot;);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //sep = &quot;;&quot;;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.Write(System.Environment.NewLine);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; foreach (DataRow dr in dtdata.Rows)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for (int i = 0; i &lt; dtdata.Columns.Count; i&#43;&#43;)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.Write(dr[i].ToString() &#43; &quot;\t&quot;);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.Write(&quot;\n&quot;);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Response.End();<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>
&nbsp;&nbsp;&nbsp; }<br>
}</p>
<p>In IE9 the regular bar comes on down at the bottom asking to save or open, just click open and there it is<br>
In FireFox that pesky download box comes on, then pops up a dialog, click open and there it is<br>
in Opera it asks to save or open, click open and there it is.</p>
2012-02-11T17:40:05-05:004830631http://forums.asp.net/p/1768549/4830631.aspx?Re+This+is+how+to+export+data+from+ASP+Net+database+into+ExcelRe: This is how to export data from ASP.Net database into Excel
<p>Thanks, I tried a few of the ones posted here and I couldn't get any of them to work.&nbsp; I took bits and pieces and and rewrote the code to actually work.&nbsp; I had done this about a decade ago back when i worked for ENRON--before they went bankrupt.</p>
<p>Paul</p>
2012-02-14T02:58:09-05:004848537http://forums.asp.net/p/1768549/4848537.aspx?Re+This+is+how+to+export+data+from+ASP+Net+database+into+ExcelRe: This is how to export data from ASP.Net database into Excel
<p>Can we use this with out any database conection?</p>
<p>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!!!</p>
<p>Thanks in adv!</p>
2012-02-24T06:52:21-05:005012528http://forums.asp.net/p/1768549/5012528.aspx?Re+This+is+how+to+export+data+from+ASP+Net+database+into+ExcelRe: This is how to export data from ASP.Net database into Excel
<p>I used closedXML to export data table to excel and it works fine :</p>
<p>http://closedxml.codeplex.com/</p>
<p></p>
2012-06-05T19:19:46-04:005289883http://forums.asp.net/p/1768549/5289883.aspx?Re+This+is+how+to+export+data+from+ASP+Net+database+into+ExcelRe: This is how to export data from ASP.Net database into Excel
<p>Really nice. Data can be transfered from source to another in various ways. &quot;<strong>Microsoft.Office.Interop.Excel&quot;&nbsp;</strong>gives us a way to export data to &quot;Excel&quot; and source. I am presenting a link which has an article on &quot;Exporting data in Excel&quot;, which
allows us to export data into any version of excel.&nbsp;</p>
<p><a href="http://www.encodedna.com/2013/01/asp.net-export-to-excel.htm" title="Export to Excel in Asp.Net">http://www.encodedna.com/2013/01/asp.net-export-to-excel.htm</a></p>
<p>Arun</p>
<p></p>
<p></p>
<p></p>
2013-02-02T12:38:16-05:005448790http://forums.asp.net/p/1768549/5448790.aspx?Re+This+is+how+to+export+data+from+ASP+Net+database+into+ExcelRe: This is how to export data from ASP.Net database into Excel
<p>Hi!, I'm newbie...</p>
<p>What should I put after the connection string????</p>
<p>&nbsp; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[&quot;*******HERE****&quot;].ConnectionString);</p>
<p>Please your help coz I need this urgent!,</p>
<p>regards,</p>
<p>&nbsp;</p>
<p>Leonardo</p>
2013-07-10T16:45:49-04:005591333http://forums.asp.net/p/1768549/5591333.aspx?Re+This+is+how+to+export+data+from+ASP+Net+database+into+ExcelRe: This is how to export data from ASP.Net database into Excel
<p>This one give you the detailed about excel database connection <a href="http://csharp.net-informations.com/excel/csharp-excel-oledb.htm">
http://csharp.net-informations.com/excel/csharp-excel-oledb.htm</a>&nbsp;c# excel connected trough oledb.</p>
<p>vivan</p>
<p>&nbsp;</p>
2013-12-26T05:53:34-05:005636921http://forums.asp.net/p/1768549/5636921.aspx?Re+This+is+how+to+export+data+from+ASP+Net+database+into+ExcelRe: This is how to export data from ASP.Net database into Excel
<p>Try This small demo this will help you http://fantasyaspnet.blogspot.in/2014/02/how-to-export-data-from-gridview-to.html</p>
2014-02-26T12:40:10-05:005655425http://forums.asp.net/p/1768549/5655425.aspx?Re+This+is+how+to+export+data+from+ASP+Net+database+into+ExcelRe: This is how to export data from ASP.Net database into Excel
<p>pnoneal,</p>
<p>Great article. do you have an article that is just as simple for importing same spreadsheet that was exported.</p>
<p>Also. It might be because I'm using MS Excel 2013, but I get a small error &quot;file or file extension is not formated correctly&quot;. Select &quot;Yes&quot; or No to open. I select &quot;Yes&quot; and it works.</p>
<p>Any ideas?</p>
2014-03-20T13:44:55-04:005778103http://forums.asp.net/p/1768549/5778103.aspx?Re+This+is+how+to+export+data+from+ASP+Net+database+into+ExcelRe: This is how to export data from ASP.Net database into Excel
<p>hiiiiii</p>
<p>abbyz here,</p>
<p>i have create a web application with access database,and i want to export my data to excel,</p>
<p>can u help me for dat????</p>
2014-09-17T11:41:43-04:00