Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Datagrid Export to Excel - Report Headers

Reply
Thread Tools

Datagrid Export to Excel - Report Headers

 
 
=?Utf-8?B?Sm9obiBXYWxrZXI=?=
Guest
Posts: n/a
 
      04-13-2006
Hello,
Below is my code for exporting a datagrid to Excel. It works fine, but
we're going to need to somehow have a report header, user input parameters
and system date display at the top of the spreadsheet just to make it look
like a nice little report. Is there a way that this can be done?
Thanks!
John

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Response.Cache.SetCacheability(HttpCacheability.No Cache)
Dim objConn As New System.Data.SqlClient.SqlConnection(strConnString)
objConn.Open()

Dim strSQL As String
Dim objDataset As New DataSet
Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter

strSQL = "exec " & sDBAllianceSync & ".dbo.spTruckerPlanning
@StartDiv, @EndDiv, @StartTeam, @EndTeam, @StartETAPOE, @EndETAPOE "

objAdapter.SelectCommand = New
System.Data.SqlClient.SqlCommand(strSQL, objConn)
objAdapter.SelectCommand.Parameters.Add("@StartDiv ",
Session("StartingDivision"))
objAdapter.SelectCommand.Parameters.Add("@EndDiv",
Session("EndingDivision"))
objAdapter.SelectCommand.Parameters.Add("@StartTea m",
Session("StartingTeam"))
objAdapter.SelectCommand.Parameters.Add("@EndTeam" ,
Session("EndingTeam"))
objAdapter.SelectCommand.Parameters.Add("@StartETA POE",
Session("StartingETAPOE"))
objAdapter.SelectCommand.Parameters.Add("@EndETAPO E",
Session("EndingETAPOE"))
objAdapter.SelectCommand.CommandTimeout = 120

' Fill the dataset.
objAdapter.Fill(objDataset)

' Create a new view.
Dim oView As New DataView(objDataset.Tables(0))
' Set up the data grid and bind the data.
DataGrid1.DataSource = oView
DataGrid1.DataBind()

' Set the content type to Excel.
Response.ContentType = "application/vnd.ms-excel"
' Remove the charset from the Content-Type header.
Response.Charset = ""
' Turn off the view state.
Me.EnableViewState = False

Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)

' Get the HTML for the control.
DataGrid1.RenderControl(hw)
' Write the HTML back to the browser.
Response.Write(tw.ToString())
' End the response.
Response.End()
End Sub

 
Reply With Quote
 
 
 
 
Jeff Dillon
Guest
Posts: n/a
 
      04-13-2006
We did exactly the same thing. The answer is actually kind of obvious,
although I didn't get it right away either.

Simply put Respons.Write (header variable & "<BR>") prior to writing out the
Excel info

Jeff

"John Walker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
> Below is my code for exporting a datagrid to Excel. It works fine, but
> we're going to need to somehow have a report header, user input parameters
> and system date display at the top of the spreadsheet just to make it look
> like a nice little report. Is there a way that this can be done?
> Thanks!
> John
>
> Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
> Response.Cache.SetCacheability(HttpCacheability.No Cache)
> Dim objConn As New
> System.Data.SqlClient.SqlConnection(strConnString)
> objConn.Open()
>
> Dim strSQL As String
> Dim objDataset As New DataSet
> Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter
>
> strSQL = "exec " & sDBAllianceSync & ".dbo.spTruckerPlanning
> @StartDiv, @EndDiv, @StartTeam, @EndTeam, @StartETAPOE, @EndETAPOE "
>
> objAdapter.SelectCommand = New
> System.Data.SqlClient.SqlCommand(strSQL, objConn)
> objAdapter.SelectCommand.Parameters.Add("@StartDiv ",
> Session("StartingDivision"))
> objAdapter.SelectCommand.Parameters.Add("@EndDiv",
> Session("EndingDivision"))
> objAdapter.SelectCommand.Parameters.Add("@StartTea m",
> Session("StartingTeam"))
> objAdapter.SelectCommand.Parameters.Add("@EndTeam" ,
> Session("EndingTeam"))
> objAdapter.SelectCommand.Parameters.Add("@StartETA POE",
> Session("StartingETAPOE"))
> objAdapter.SelectCommand.Parameters.Add("@EndETAPO E",
> Session("EndingETAPOE"))
> objAdapter.SelectCommand.CommandTimeout = 120
>
> ' Fill the dataset.
> objAdapter.Fill(objDataset)
>
> ' Create a new view.
> Dim oView As New DataView(objDataset.Tables(0))
> ' Set up the data grid and bind the data.
> DataGrid1.DataSource = oView
> DataGrid1.DataBind()
>
> ' Set the content type to Excel.
> Response.ContentType = "application/vnd.ms-excel"
> ' Remove the charset from the Content-Type header.
> Response.Charset = ""
> ' Turn off the view state.
> Me.EnableViewState = False
>
> Dim tw As New System.IO.StringWriter
> Dim hw As New System.Web.UI.HtmlTextWriter(tw)
>
> ' Get the HTML for the control.
> DataGrid1.RenderControl(hw)
> ' Write the HTML back to the browser.
> Response.Write(tw.ToString())
> ' End the response.
> Response.End()
> End Sub
>



 
Reply With Quote
 
 
 
 
=?Utf-8?B?Sm9obiBXYWxrZXI=?=
Guest
Posts: n/a
 
      04-13-2006
Great I'll give it a shot.
Thanks!

"Jeff Dillon" wrote:

> We did exactly the same thing. The answer is actually kind of obvious,
> although I didn't get it right away either.
>
> Simply put Respons.Write (header variable & "<BR>") prior to writing out the
> Excel info
>
> Jeff
>
> "John Walker" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hello,
> > Below is my code for exporting a datagrid to Excel. It works fine, but
> > we're going to need to somehow have a report header, user input parameters
> > and system date display at the top of the spreadsheet just to make it look
> > like a nice little report. Is there a way that this can be done?
> > Thanks!
> > John
> >
> > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
> > System.EventArgs) Handles MyBase.Load
> > Response.Cache.SetCacheability(HttpCacheability.No Cache)
> > Dim objConn As New
> > System.Data.SqlClient.SqlConnection(strConnString)
> > objConn.Open()
> >
> > Dim strSQL As String
> > Dim objDataset As New DataSet
> > Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter
> >
> > strSQL = "exec " & sDBAllianceSync & ".dbo.spTruckerPlanning
> > @StartDiv, @EndDiv, @StartTeam, @EndTeam, @StartETAPOE, @EndETAPOE "
> >
> > objAdapter.SelectCommand = New
> > System.Data.SqlClient.SqlCommand(strSQL, objConn)
> > objAdapter.SelectCommand.Parameters.Add("@StartDiv ",
> > Session("StartingDivision"))
> > objAdapter.SelectCommand.Parameters.Add("@EndDiv",
> > Session("EndingDivision"))
> > objAdapter.SelectCommand.Parameters.Add("@StartTea m",
> > Session("StartingTeam"))
> > objAdapter.SelectCommand.Parameters.Add("@EndTeam" ,
> > Session("EndingTeam"))
> > objAdapter.SelectCommand.Parameters.Add("@StartETA POE",
> > Session("StartingETAPOE"))
> > objAdapter.SelectCommand.Parameters.Add("@EndETAPO E",
> > Session("EndingETAPOE"))
> > objAdapter.SelectCommand.CommandTimeout = 120
> >
> > ' Fill the dataset.
> > objAdapter.Fill(objDataset)
> >
> > ' Create a new view.
> > Dim oView As New DataView(objDataset.Tables(0))
> > ' Set up the data grid and bind the data.
> > DataGrid1.DataSource = oView
> > DataGrid1.DataBind()
> >
> > ' Set the content type to Excel.
> > Response.ContentType = "application/vnd.ms-excel"
> > ' Remove the charset from the Content-Type header.
> > Response.Charset = ""
> > ' Turn off the view state.
> > Me.EnableViewState = False
> >
> > Dim tw As New System.IO.StringWriter
> > Dim hw As New System.Web.UI.HtmlTextWriter(tw)
> >
> > ' Get the HTML for the control.
> > DataGrid1.RenderControl(hw)
> > ' Write the HTML back to the browser.
> > Response.Write(tw.ToString())
> > ' End the response.
> > Response.End()
> > End Sub
> >

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Excel Report from ASP Output (Dynamic Stage) shankumar ASP General 1 06-19-2006 09:54 AM
Is possible to export the crystal report to Excel file with multiple sheet? Benny Ng ASP .Net 0 02-13-2006 02:14 AM
Display a Datagrid & Export a Datagrid to Excel Paul D. Fox ASP .Net Datagrid Control 1 07-21-2005 08:38 PM
Crystal Report Export to Excel: Blank Anil Kripalani ASP .Net 0 05-23-2005 02:45 PM
Export report to Excel sridevi ASP General 2 05-06-2005 12:38 PM



Advertisments