Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export a Datagrid into Excell 1

Status
Not open for further replies.
RachelK

RachelK

Programmer
Mar 18, 2002
171
GB
Hi,

Any idea's how to export the data in a datagrid into excell.

Response.AddHeader("content-disposition", "attachment; filename=Proform_Job_Checker.xls")
Response.ContentType = "application/vnd.ms-excel"

I have this code so far it only seems to work when I allow paging whic of course only exports 10 lines. I would like tp export all the data that comes back from the query.

Cheers,


Rachel.
 
Sort by date Sort by votes
Rac: Do a search here at Tek-Tips - there are about 3 or 4 threads tackling this issue. Here is an example aspx page that accomplishes this (also a recent threat involved opening up the excel sheet in a new pop up window (about 4 days old today).

<%@ Page Language=&quot;VB&quot; Debug=&quot;true&quot;%>
<%@Import Namespace = &quot;Microsoft.VisualBasic&quot;%>
<%@Import Namespace = &quot;System&quot;%>
<%@Import Namespace = &quot;System.Web&quot;%>
<%@Import Namespace = &quot;System.Web.UI&quot;%>
<%@Import Namespace = &quot;System.Web.UI.WebControls&quot;%>
<%@Import Namespace = &quot;System.Web.UI.HtmlControls&quot;%>
<%@Import Namespace = &quot;System.Data&quot;%>
<%@Import Namespace = &quot;System.Data.OleDb&quot;%>
<script runat=&quot;server&quot;>
Private Sub Page_Load(sender As Object, e As EventArgs)
'open database...
Dim cmdSelect As OLEDbCommand
Dim dbconnRec As OleDbConnection = New OleDbConnection( _
&quot;Provider=Microsoft.Jet.OLEDB.4.0; &quot; & _
&quot;Data Source=&quot; & Server.MapPath(&quot;.\fpdb\NewData.mdb;&quot;))
cmdSelect = New OLEDbCommand(&quot;SELECT TimeStamp,SampleDate,pH,WaterTemp,Alkalinity,Hardness,Turb1,Turb2,AwwSiteCode,Weather,Streams,DO1,DO2,Secchi,Comments,CID,SID FROM tblNewChemData&quot;, dbconnRec)
dbconnRec.Open()
dgChemRecs.DataSource = cmdSelect.ExecuteReader()
dgChemRecs.DataBind()
lblNo.Text = dgChemRecs.Items.Count.ToString()
dbconnRec.Close()
End Sub

Sub btnSaveExcel_Click (Sender as Object, E As EventArgs)
'Set the content type to Excel...
Response.ContentType = &quot;application/vnd.ms-excel&quot;
Response.Charset = &quot;&quot;
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
dgChemRecs.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
End sub
</script>
<HTML>
<HEAD>
<title>New Chem Records on the Server</title>
</HEAD>
<body>
<form id=&quot;Form1&quot; runat=&quot;server&quot;>
<p></p>
Send these Records to the New Chem Excel file:  <asp:Button id=&quot;btnEx&quot; runat=&quot;server&quot; width=&quot;100px&quot; Text=&quot;Creat Excel File&quot; OnClick=&quot;btnSaveExcel_Click&quot;/>
<br><asp:Label id=&quot;rec&quot; runat=&quot;server&quot; Text=&quot;Records: &quot; ForeColor=&quot;Blue&quot;/> <asp:Label id=&quot;lblNo&quot; runat=&quot;server&quot;/>
<asp:DataGrid id=&quot;dgChemRecs&quot; AutoGenerateColumns=&quot;false&quot; Width=&quot;95%&quot; BackColor=&quot;LightSteelBlue&quot; BorderColor=&quot;Blue&quot; CellPadding=1 CellSpacing=1 Font-Name=&quot;Times New Roman&quot; Font-Size=&quot;8pt&quot; ForeColor=&quot;Black&quot; HeaderStyle-BackColor=&quot;Wheat&quot; DataKeyField=&quot;AwwSiteCode&quot; runat=&quot;server&quot;>
<Columns>
<asp:BoundColumn DataField=&quot;AwwSiteCode&quot; HeaderText=&quot;<pre><br>AwwSiteCode</pre>&quot;/>
<asp:BoundColumn DataField=&quot;TimeStamp&quot; HeaderText=&quot;<pre><br> Date &<br> Time<br> Posted</pre>&quot;/>
<asp:BoundColumn DataField=&quot;SampleDate&quot; HeaderText=&quot;<pre><br> Sample Date</pre>&quot; DataFormatString=&quot;{0:d}&quot;/>
<asp:BoundColumn DataField=&quot;pH&quot; HeaderText=&quot;<pre><br> pH </pre> &quot;/>
<asp:BoundColumn DataField=&quot;WaterTemp&quot; HeaderText=&quot;<pre><br> Water<br> (°C)</pre>&quot;/>
<asp:BoundColumn DataField=&quot;Hardness&quot; HeaderText=&quot;Hardness&quot; DataFormatString=&quot;{0:f2}&quot;/>
<asp:BoundColumn DataField=&quot;Alkalinity&quot; HeaderText=&quot;Alkalinity&quot; DataFormatString=&quot;{0:f2}&quot;/>
<asp:BoundColumn DataField=&quot;Turb1&quot; HeaderText=&quot;<pre><br> Turb1 </pre>&quot; DataFormatString=&quot;{0:f2}&quot;/>
<asp:BoundColumn DataField=&quot;Turb2&quot; HeaderText=&quot;<pre><br> Turb2 </pre>&quot; DataFormatString=&quot;{0:f2}&quot;/>
<asp:BoundColumn DataField=&quot;Weather&quot; HeaderText=&quot;Weather&quot;/>
<asp:BoundColumn DataField=&quot;CID&quot; HeaderText=&quot;Monitors&quot;/>
<asp:BoundColumn DataField=&quot;DO1&quot; HeaderText=&quot;<pre><br> DO1 </pre>&quot; DataFormatString=&quot;{0:f2}&quot;/>
<asp:BoundColumn DataField=&quot;DO2&quot; HeaderText=&quot;<pre><br> DO2 </pre>&quot; DataFormatString=&quot;{0:f2}&quot;/>
<asp:BoundColumn DataField=&quot;Secchi&quot; HeaderText=&quot;Secchi&quot;/>
<asp:BoundColumn DataField=&quot;SID&quot; HeaderText=&quot;Entered by&quot;/>
<asp:BoundColumn DataField=&quot;Comments&quot; HeaderText=&quot;<pre><br>Comments</pre>&quot;/>
</Columns>
</asp:DataGrid>
</TR>
</form>
</body>
</HTML>
 
Upvote 0 Downvote
Thanks for that. I did search the site. I'll have another look this looks a bit to complicated for me. Cheers. Rachel
 
Upvote 0 Downvote
Rac: Its nothing more than cut and paste - the code I sent you converts a datagrid into an excel spreadsheet - it is easy, believe me.
 
Upvote 0 Downvote
Where do I paste it into the aspx page.
 
Upvote 0 Downvote
Rac: Are you using Visual Studio and code behind? All of the script would go in the code behind, and the html elements in the aspx page. Of course, your datatable will generate a differnt grid, etc...

Also ignore the header text's:

HeaderText=&quot;<pre><br>AwwSiteCode</pre>&quot;

just keep it simple

HeaderText=&quot;AwwSiteCode&quot;

Just reference the aspx page I pasted in here, modified for your table, and link to it (open it up).

 
Upvote 0 Downvote
Any idea if when having AllowSorting = True that this export fails? my export works when i have no sorting on...

dlc
 
Upvote 0 Downvote
AllowSorting=True will cause it to fail. It confuses button with clicking column of datagrid. To fix, what I did was create a second grid that is not sortable, and fill it with your dataset and kick it to excel. You won't see the grid, so it will be seamless to the user.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Makumbi256
  • Locked
  • Question
How to make days vertical and grid view editable using asp.net vb
Replies
0
Views
274
Makumbi256
Makumbi256
weberm
  • Locked
  • Question
How do I make a page with multiple AJAX custom controls?
Replies
2
Views
354
weberm
weberm
AhmedAzizBarbary
  • Locked
  • Question
How to read data response body after success login razor page ?
Replies
0
Views
391
AhmedAzizBarbary
AhmedAzizBarbary
a6green
  • Locked
  • Question
How to configure AT&T / Avaya Definity G3 incoming ISDN trunk
Replies
2
Views
384
a6green
a6green
snakehips2000
  • Locked
  • Question
Entity Framework, MVC, ASP.NET, ASP.NET Core etc… etc
Replies
0
Views
316
snakehips2000
snakehips2000

Part and Inventory Search

Sponsor

Back
Top