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!
-
Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Exporting data to excel
- Thread starter meckeard
- Start date
crazyboybert
Programmer
Do a keyword search for excel in this forum. You will find this has been discussed many times before.
Rob
Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927
Rob
Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927
I use this for a datagrid
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
newGrid.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
newGrid.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
meckeard: Take a look at the following page (there is an Excel button there that creates an Excel file, if you think this is in the neighborhood I can post the code for that page. Link:
First create the grid by:
1. Pick a County (say Cullman)
2. Hit the "Go" button
3. Hit the "make Excel file button"
The code runs along the lines of what checkai posted.
First create the grid by:
1. Pick a County (say Cullman)
2. Hit the "Go" button
3. Hit the "make Excel file button"
The code runs along the lines of what checkai posted.
kc: The image on the SiteCounty page calls the following page (the excel spreadsheet will be of course, the same grid on the SiteCounty page, q.v.,
[code[
Sub imgSites_Click (Sender As Object, e As ImageClickEventArgs)
Response.Redirect("ExcelSiteCounty.aspx?County=" & ddCty.SelectedItem.Text)
End sub
[/code]
'********** make excel spreadsheet ************
<%@ Page Language="VB" Debug="false"%>
<%@Import Namespace = "Microsoft.VisualBasic"%>
<%@Import Namespace = "System"%>
<%@Import Namespace = "System.Web"%>
<%@Import Namespace = "System.Web.UI"%>
<%@Import Namespace = "System.Web.UI.WebControls"%>
<%@Import Namespace = "System.Web.UI.HtmlControls"%>
<%@Import Namespace = "System.Data"%>
<%@Import Namespace = "System.Data.OleDb"%>
<script runat="server">
Private Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
Dim intCt As Integer
'open database...
Dim cmdSelect As OLEDbCommand
Dim dbconnSiteRecs As OleDbConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.MapPath(".\fpdb\Sites.mdb;"))
cmdSelect = New OLEDbCommand("SELECT AwwSiteCode, Active, County, Description, Waterbody_Name, Latitude, Longitude, Group_Name, LastDate, ChemCt, BacCt, IPStreams FROM WebMasterSites WHERE " & "County ='" & Request.QueryString("County") & "'", dbconnSiteRecs)
dbconnSiteRecs.Open()
dgGroups.DataSource = cmdSelect.ExecuteReader()
dgGroups.DataBind()
dbconnSiteRecs.Close()
dbconnSiteRecs = Nothing
'Set the content type to Excel...
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
dgGroups.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
End If
End Sub
</script>
<HTML>
<HEAD>
<title>Export Site by County to Excel</title>
</HEAD>
<body background="images/lgtbrown.gif">
<form id="Form1" runat="server">
<TR>
<div style="height:500px;overflow:auto;width:850px;">
<asp
ataGrid id="dgGroups" runat="server" AutogenerateColumns="false" align="left" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4">
<ItemStyle ForeColor="#330099" BackColor="white" Font-Size="9" ></ItemStyle>
<HeaderStyle Font-Size="10" horizontalalign="Center" verticalalign="Middle" ForeColor="#FFFFCC" BackColor="#990000"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="AWWSitecode" HeaderText="Site Code" ItemStyle-width="30px" SortExpression="AwwSiteCode"/>
<asp:BoundColumn DataField="Group_Name" HeaderText="Group Name" ItemStyle-width="140px" Sortexpression="Group_Name"/>
<asp:BoundColumn DataField="Waterbody_Name" HeaderText="Waterbody Name" ItemStyle-width="100px" Sortexpression="Waterbody_Name"/>
<asp:BoundColumn DataField="Description" HeaderText="Site Description" ItemStyle-width="140px" Sortexpression="Description"/>
<asp:BoundColumn DataField="County" HeaderText="Site County" ItemStyle-width="70px"/>
<asp:BoundColumn DataField="Latitude" HeaderText="Latitude" DataFormatString="{0:f4}" ItemStyle-width="50px"/>
<asp:BoundColumn DataField="Longitude" HeaderText="Longitude" DataFormatString="{0:f4}" ItemStyle-width="50px"/>
<asp:BoundColumn DataField="LastDate" HeaderText="Last Date" DataFormatString="{0:d}" ItemStyle-width="40px" Sortexpression="LastDate"/>
<asp:BoundColumn DataField="ChemCt" HeaderText="#Chm" Sortexpression="ChemCt"/>
<asp:BoundColumn DataField="BacCt" HeaderText="#Bac" Sortexpression="BacCt"/>
<asp:BoundColumn DataField="Active" HeaderText="Active?" Sortexpression="BacCt"/>
<asp:BoundColumn DataField="IPStreams" HeaderText="303(d)" Sortexpression="BacCt"/>
</Columns>
</aspataGrid>
<asp:Label id="Huc" runat="server" visible="False" Text="Red: Within 303(d) HUC11 watershed; Yellow: Unknown, no Latitude/Longitude; Green: Not within 303(d) impaired watershed Impaired 303(d) watershed is any HUC11 watershed that contains an impaired 303(d) stream"/>
</DIV>
</form>
</body>
</HTML>
[code[
Sub imgSites_Click (Sender As Object, e As ImageClickEventArgs)
Response.Redirect("ExcelSiteCounty.aspx?County=" & ddCty.SelectedItem.Text)
End sub
[/code]
'********** make excel spreadsheet ************
<%@ Page Language="VB" Debug="false"%>
<%@Import Namespace = "Microsoft.VisualBasic"%>
<%@Import Namespace = "System"%>
<%@Import Namespace = "System.Web"%>
<%@Import Namespace = "System.Web.UI"%>
<%@Import Namespace = "System.Web.UI.WebControls"%>
<%@Import Namespace = "System.Web.UI.HtmlControls"%>
<%@Import Namespace = "System.Data"%>
<%@Import Namespace = "System.Data.OleDb"%>
<script runat="server">
Private Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
Dim intCt As Integer
'open database...
Dim cmdSelect As OLEDbCommand
Dim dbconnSiteRecs As OleDbConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.MapPath(".\fpdb\Sites.mdb;"))
cmdSelect = New OLEDbCommand("SELECT AwwSiteCode, Active, County, Description, Waterbody_Name, Latitude, Longitude, Group_Name, LastDate, ChemCt, BacCt, IPStreams FROM WebMasterSites WHERE " & "County ='" & Request.QueryString("County") & "'", dbconnSiteRecs)
dbconnSiteRecs.Open()
dgGroups.DataSource = cmdSelect.ExecuteReader()
dgGroups.DataBind()
dbconnSiteRecs.Close()
dbconnSiteRecs = Nothing
'Set the content type to Excel...
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
dgGroups.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
End If
End Sub
</script>
<HTML>
<HEAD>
<title>Export Site by County to Excel</title>
</HEAD>
<body background="images/lgtbrown.gif">
<form id="Form1" runat="server">
<TR>
<div style="height:500px;overflow:auto;width:850px;">
<asp
ataGrid id="dgGroups" runat="server" AutogenerateColumns="false" align="left" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4"><ItemStyle ForeColor="#330099" BackColor="white" Font-Size="9" ></ItemStyle>
<HeaderStyle Font-Size="10" horizontalalign="Center" verticalalign="Middle" ForeColor="#FFFFCC" BackColor="#990000"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="AWWSitecode" HeaderText="Site Code" ItemStyle-width="30px" SortExpression="AwwSiteCode"/>
<asp:BoundColumn DataField="Group_Name" HeaderText="Group Name" ItemStyle-width="140px" Sortexpression="Group_Name"/>
<asp:BoundColumn DataField="Waterbody_Name" HeaderText="Waterbody Name" ItemStyle-width="100px" Sortexpression="Waterbody_Name"/>
<asp:BoundColumn DataField="Description" HeaderText="Site Description" ItemStyle-width="140px" Sortexpression="Description"/>
<asp:BoundColumn DataField="County" HeaderText="Site County" ItemStyle-width="70px"/>
<asp:BoundColumn DataField="Latitude" HeaderText="Latitude" DataFormatString="{0:f4}" ItemStyle-width="50px"/>
<asp:BoundColumn DataField="Longitude" HeaderText="Longitude" DataFormatString="{0:f4}" ItemStyle-width="50px"/>
<asp:BoundColumn DataField="LastDate" HeaderText="Last Date" DataFormatString="{0:d}" ItemStyle-width="40px" Sortexpression="LastDate"/>
<asp:BoundColumn DataField="ChemCt" HeaderText="#Chm" Sortexpression="ChemCt"/>
<asp:BoundColumn DataField="BacCt" HeaderText="#Bac" Sortexpression="BacCt"/>
<asp:BoundColumn DataField="Active" HeaderText="Active?" Sortexpression="BacCt"/>
<asp:BoundColumn DataField="IPStreams" HeaderText="303(d)" Sortexpression="BacCt"/>
</Columns>
</asp
ataGrid> <asp:Label id="Huc" runat="server" visible="False" Text="Red: Within 303(d) HUC11 watershed; Yellow: Unknown, no Latitude/Longitude; Green: Not within 303(d) impaired watershed Impaired 303(d) watershed is any HUC11 watershed that contains an impaired 303(d) stream"/>
</DIV>
</form>
</body>
</HTML>
Good question, just happened kc; I'm not sure, just used that code and took what I could get. Might have something to do with the fact that my page was a NotePad page and not in VS?? I'm moving all my code over to VS now so I'll check this and get back with you.
...thanks
I was just creating a tab delimited string to create the .xls, so I tried your way, thinking that maybe it was the fact that you were using the RenderControl method. It may also be the fact that I'm pulling from a html table and not a datagrid.
KJ
I was just creating a tab delimited string to create the .xls, so I tried your way, thinking that maybe it was the fact that you were using the RenderControl method. It may also be the fact that I'm pulling from a html table and not a datagrid.
KJ
this looks great! .... but how can I save the Excel file output on the server instead of displaying it
Can the Response object be used .. or is it the StringWriter that must be used!!
Server.MapPath("") & ... & "MyExcelFile.xls" ..... ?????????
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
newGrid.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
Can the Response object be used .. or is it the StringWriter that must be used!!
Server.MapPath("") & ... & "MyExcelFile.xls" ..... ?????????
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
newGrid.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question