470,632 Members | 2,554 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,632 developers. It's quick & easy.

Datagrid Export to Excel - Report Headers

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

Apr 13 '06 #1
2 2066
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" <Jo********@discussions.microsoft.com> wrote in message
news:10**********************************@microsof t.com...
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

Apr 13 '06 #2
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" <Jo********@discussions.microsoft.com> wrote in message
news:10**********************************@microsof t.com...
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


Apr 13 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by PerryC | last post: by
5 posts views Thread by Jonny | last post: by
1 post views Thread by Mortar | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.