Datagrid Export to Excel - Report Headers

Discussion in 'ASP .Net' started by =?Utf-8?B?Sm9obiBXYWxrZXI=?=, Apr 13, 2006.

  1. 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
    =?Utf-8?B?Sm9obiBXYWxrZXI=?=, Apr 13, 2006
    #1
    1. Advertising

  2. =?Utf-8?B?Sm9obiBXYWxrZXI=?=

    Jeff Dillon Guest

    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" <> wrote in message
    news:...
    > 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
    >
    Jeff Dillon, Apr 13, 2006
    #2
    1. Advertising

  3. 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" <> wrote in message
    > news:...
    > > 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
    > >

    >
    >
    >
    =?Utf-8?B?Sm9obiBXYWxrZXI=?=, Apr 13, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Anil Kripalani

    Crystal Report Export to Excel: Blank

    Anil Kripalani, May 23, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    723
    Anil Kripalani
    May 23, 2005
  2. Benny Ng
    Replies:
    0
    Views:
    2,468
    Benny Ng
    Feb 13, 2006
  3. Paul D. Fox

    Display a Datagrid & Export a Datagrid to Excel

    Paul D. Fox, Jul 21, 2005, in forum: ASP .Net Datagrid Control
    Replies:
    1
    Views:
    211
    Elton Wang
    Jul 21, 2005
  4. sridevi

    Export report to Excel

    sridevi, May 6, 2005, in forum: ASP General
    Replies:
    2
    Views:
    128
    Tom Kaminski [MVP]
    May 6, 2005
  5. shankumar
    Replies:
    1
    Views:
    168
Loading...

Share This Page