G
Guest
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.NoCache)
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("@StartTeam",
Session("StartingTeam"))
objAdapter.SelectCommand.Parameters.Add("@EndTeam",
Session("EndingTeam"))
objAdapter.SelectCommand.Parameters.Add("@StartETAPOE",
Session("StartingETAPOE"))
objAdapter.SelectCommand.Parameters.Add("@EndETAPOE",
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
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.NoCache)
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("@StartTeam",
Session("StartingTeam"))
objAdapter.SelectCommand.Parameters.Add("@EndTeam",
Session("EndingTeam"))
objAdapter.SelectCommand.Parameters.Add("@StartETAPOE",
Session("StartingETAPOE"))
objAdapter.SelectCommand.Parameters.Add("@EndETAPOE",
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