DataGrid export to excel

Discussion in 'ASP .Net Datagrid Control' started by kenneth, Aug 6, 2003.

  1. kenneth

    kenneth Guest

    I've asked this question once before, but am still having problems.
    When I try to export this grid to excel i get the error message i've
    seen several people talk about :

    Control 'DataGrid1__ctl1__ctl0' of type 'DataGridLinkButton' must be
    placed inside a form tag with runat=server

    the export works fine as long as i don't enable sorting or put any
    controls in the datagrid...

    (and i'm doing this entirely in notepad, so cut me a little slack)
    :)

    but basically i have an initial page with a datagrid where you click a
    cell value and it sends you to this page.. (the "id" is the passed
    value used in the SELECT to fill the table).

    i'm sure it's something simple i'm overlooking, but it's driving me
    crazy. any help would be GREATLY appreciated!
    thanks-
    kenneth


    <%@ Import Namespace="System " %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <script language="VB" runat="server">

    Dim dt as DataTable
    Dim dv as DataView
    Dim conn As New SqlConnection("server...")

    Sub Page_Load(Sender As Object, E As EventArgs)
    If Not Page.IsPostBack Then
    Dim strCallID As String = Request.querystring("id")
    Dim dtJournals As New DataTable()
    Dim odaJournals As New SqlDataAdapter( _
    "SELECT EntryDate, Tracker, EntryText FROM ....")
    odaJournals.Fill(dtJournals)
    DataGrid1.DataSource = dtJournals
    DataGrid1.DataBind()
    Session("MyDataTable") = dtJournals
    End If
    End Sub

    Sub Sort_Grid(sender As Object, e As DataGridSortCommandEventArgs)
    dt = New DataTable
    dt = Session("MyDataTable")
    dv = New DataView(dt)
    dv.Sort = e.SortExpression
    DataGrid1.DataSource = dv
    DataGrid1.DataBind()
    End Sub

    Sub Export_Click(Sender As Object, E As EventArgs)
    ' 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 sw As New System.IO.StringWriter()
    Dim hw As New System.Web.UI.HtmlTextWriter(sw)

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

    </script>
    <form runat="server">
    <asp:Button id="Button1" Text="Export" OnClick="Export_Click"
    runat="server"></asp:Button>
    <asp:DataGrid id="DataGrid1" runat="server"
    AutoGenerateColumns="False" AllowSorting="True"
    OnSortCommand="Sort_Grid">
    <AlternatingItemStyle
    BackColor="Gainsboro"></AlternatingItemStyle>
    <HeaderStyle BackColor="#00aaaa" HorizontalAlign="Center">
    </HeaderStyle>
    <Columns>
    <asp:BoundColumn
    HeaderText="Entry Date"
    DataField="EntryDate"
    ItemStyle-Wrap="False"
    ItemStyle-HorizontalAlign="Center"
    ItemStyle-VerticalAlign="Top"
    SortExpression="EntryDate">
    </asp:BoundColumn>
    <asp:BoundColumn
    HeaderText="User ID"
    DataField="Tracker"
    ItemStyle-Wrap="False"
    ItemStyle-HorizontalAlign="Center"
    ItemStyle-VerticalAlign="Top"
    SortExpression="Tracker">
    </asp:BoundColumn>
    <asp:BoundColumn
    HeaderText="Entry Text"
    DataField="EntryText"
    ItemStyle-VerticalAlign="Top">
    </asp:BoundColumn>
    </Columns>
    </asp:DataGrid>
    </form>
     
    kenneth, Aug 6, 2003
    #1
    1. Advertising

  2. Hi Kenneth,
    It's caused by the LinkButtons that are present in the grid when you turn on
    sorting or paging. The workaround is to remove the LinkButtons, call
    RenderControl to do the export, then put back the LinkButtons. It sounds
    crazy, but it works :) Here's some sample code I've used in the past:

    Sub DoExcel(strFileName As String)
    Dim tblGrid As Table = grdSales.Controls(0)
    Dim alLinks As New ArrayList()
    Dim TC As TableCell
    Dim LB As LinkButton
    Dim i As Integer
    'For i = 0 to tblGrid.Rows(0).Cells.Count - 1
    For Each TC in tblGrid.Rows(0).Cells
    'TC = tblGrid.Rows(0).Cells(i)
    If TC.Controls.Count > 0 Then
    LB = TC.Controls(0)
    TC.Controls.RemoveAt(0)
    TC.Text = LB.Text
    Else
    LB = New LinkButton
    End If
    alLinks.Add(LB)
    Next

    Dim SW As New StringWriter()
    Dim HW As New HtmlTextWriter(SW)
    grdSales.RenderControl(HW)
    Dim TW As TextWriter = File.CreateText(Server.Mappath(strFileName))
    TW.WriteLine(SW.ToString())
    TW.Close

    For Each TC in tblGrid.Rows(0).Cells
    LB = alLinks(i)
    i = i + 1
    If LB.Text <> "" Then TC.Controls.Add(LB)
    Next
    End Sub

    Hope that helps!
    Datagrid Girl
    http://www.datagridgirl.com
    http://weblogs.asp.net/datagridgirl

    "kenneth" <> wrote in message
    news:...
    > I've asked this question once before, but am still having problems.
    > When I try to export this grid to excel i get the error message i've
    > seen several people talk about :
    >
    > Control 'DataGrid1__ctl1__ctl0' of type 'DataGridLinkButton' must be
    > placed inside a form tag with runat=server
    >
    > the export works fine as long as i don't enable sorting or put any
    > controls in the datagrid...
    >
    > (and i'm doing this entirely in notepad, so cut me a little slack)
    > :)
    >
    > but basically i have an initial page with a datagrid where you click a
    > cell value and it sends you to this page.. (the "id" is the passed
    > value used in the SELECT to fill the table).
    >
    > i'm sure it's something simple i'm overlooking, but it's driving me
    > crazy. any help would be GREATLY appreciated!
    > thanks-
    > kenneth
    >
    >
    > <%@ Import Namespace="System " %>
    > <%@ Import Namespace="System.Data" %>
    > <%@ Import Namespace="System.Data.SqlClient" %>
    > <script language="VB" runat="server">
    >
    > Dim dt as DataTable
    > Dim dv as DataView
    > Dim conn As New SqlConnection("server...")
    >
    > Sub Page_Load(Sender As Object, E As EventArgs)
    > If Not Page.IsPostBack Then
    > Dim strCallID As String = Request.querystring("id")
    > Dim dtJournals As New DataTable()
    > Dim odaJournals As New SqlDataAdapter( _
    > "SELECT EntryDate, Tracker, EntryText FROM ....")
    > odaJournals.Fill(dtJournals)
    > DataGrid1.DataSource = dtJournals
    > DataGrid1.DataBind()
    > Session("MyDataTable") = dtJournals
    > End If
    > End Sub
    >
    > Sub Sort_Grid(sender As Object, e As DataGridSortCommandEventArgs)
    > dt = New DataTable
    > dt = Session("MyDataTable")
    > dv = New DataView(dt)
    > dv.Sort = e.SortExpression
    > DataGrid1.DataSource = dv
    > DataGrid1.DataBind()
    > End Sub
    >
    > Sub Export_Click(Sender As Object, E As EventArgs)
    > ' 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 sw As New System.IO.StringWriter()
    > Dim hw As New System.Web.UI.HtmlTextWriter(sw)
    >
    > ' Get the HTML for the control.
    > DataGrid1.RenderControl(hw)
    > ' Write the HTML back to the browser.
    > Response.Write(sw.ToString())
    > ' End the response.
    > Response.End()
    > End Sub
    >
    > </script>
    > <form runat="server">
    > <asp:Button id="Button1" Text="Export" OnClick="Export_Click"
    > runat="server"></asp:Button>
    > <asp:DataGrid id="DataGrid1" runat="server"
    > AutoGenerateColumns="False" AllowSorting="True"
    > OnSortCommand="Sort_Grid">
    > <AlternatingItemStyle
    > BackColor="Gainsboro"></AlternatingItemStyle>
    > <HeaderStyle BackColor="#00aaaa" HorizontalAlign="Center">
    > </HeaderStyle>
    > <Columns>
    > <asp:BoundColumn
    > HeaderText="Entry Date"
    > DataField="EntryDate"
    > ItemStyle-Wrap="False"
    > ItemStyle-HorizontalAlign="Center"
    > ItemStyle-VerticalAlign="Top"
    > SortExpression="EntryDate">
    > </asp:BoundColumn>
    > <asp:BoundColumn
    > HeaderText="User ID"
    > DataField="Tracker"
    > ItemStyle-Wrap="False"
    > ItemStyle-HorizontalAlign="Center"
    > ItemStyle-VerticalAlign="Top"
    > SortExpression="Tracker">
    > </asp:BoundColumn>
    > <asp:BoundColumn
    > HeaderText="Entry Text"
    > DataField="EntryText"
    > ItemStyle-VerticalAlign="Top">
    > </asp:BoundColumn>
    > </Columns>
    > </asp:DataGrid>
    > </form>
    >
     
    Datagrid Girl [MVP], Aug 11, 2003
    #2
    1. Advertising

  3. kenneth

    kenneth Guest

    Re: DataGrid export to excel - THANK YOU!

    <on hands and knees bowing>
    "weeeee're noooot worrrrrthy"
    "weeeee're noooot worrrrrthy"
    :)

    Thank you so much!!!
    Worked like a charm-

    Kenneth



    On Mon, 11 Aug 2003 18:37:28 +1000, "Datagrid Girl [MVP]"
    <> wrote:

    >Hi Kenneth,
    >It's caused by the LinkButtons that are present in the grid when you turn on
    >sorting or paging. The workaround is to remove the LinkButtons, call
    >RenderControl to do the export, then put back the LinkButtons. It sounds
    >crazy, but it works :) Here's some sample code I've used in the past:
    >
    >Sub DoExcel(strFileName As String)
    > Dim tblGrid As Table = grdSales.Controls(0)
    > Dim alLinks As New ArrayList()
    > Dim TC As TableCell
    > Dim LB As LinkButton
    > Dim i As Integer
    > 'For i = 0 to tblGrid.Rows(0).Cells.Count - 1
    > For Each TC in tblGrid.Rows(0).Cells
    > 'TC = tblGrid.Rows(0).Cells(i)
    > If TC.Controls.Count > 0 Then
    > LB = TC.Controls(0)
    > TC.Controls.RemoveAt(0)
    > TC.Text = LB.Text
    > Else
    > LB = New LinkButton
    > End If
    > alLinks.Add(LB)
    > Next
    >
    > Dim SW As New StringWriter()
    > Dim HW As New HtmlTextWriter(SW)
    > grdSales.RenderControl(HW)
    > Dim TW As TextWriter = File.CreateText(Server.Mappath(strFileName))
    > TW.WriteLine(SW.ToString())
    > TW.Close
    >
    > For Each TC in tblGrid.Rows(0).Cells
    > LB = alLinks(i)
    > i = i + 1
    > If LB.Text <> "" Then TC.Controls.Add(LB)
    > Next
    >End Sub
    >
    >Hope that helps!
    >Datagrid Girl
    >http://www.datagridgirl.com
    >http://weblogs.asp.net/datagridgirl
    >
    >"kenneth" <> wrote in message
    >news:...
    >> I've asked this question once before, but am still having problems.
    >> When I try to export this grid to excel i get the error message i've
    >> seen several people talk about :
    >>
    >> Control 'DataGrid1__ctl1__ctl0' of type 'DataGridLinkButton' must be
    >> placed inside a form tag with runat=server
    >>
    >> the export works fine as long as i don't enable sorting or put any
    >> controls in the datagrid...
    >>
    >> (and i'm doing this entirely in notepad, so cut me a little slack)
    >> :)
    >>
    >> but basically i have an initial page with a datagrid where you click a
    >> cell value and it sends you to this page.. (the "id" is the passed
    >> value used in the SELECT to fill the table).
    >>
    >> i'm sure it's something simple i'm overlooking, but it's driving me
    >> crazy. any help would be GREATLY appreciated!
    >> thanks-
    >> kenneth
    >>
    >>
    >> <%@ Import Namespace="System " %>
    >> <%@ Import Namespace="System.Data" %>
    >> <%@ Import Namespace="System.Data.SqlClient" %>
    >> <script language="VB" runat="server">
    >>
    >> Dim dt as DataTable
    >> Dim dv as DataView
    >> Dim conn As New SqlConnection("server...")
    >>
    >> Sub Page_Load(Sender As Object, E As EventArgs)
    >> If Not Page.IsPostBack Then
    >> Dim strCallID As String = Request.querystring("id")
    >> Dim dtJournals As New DataTable()
    >> Dim odaJournals As New SqlDataAdapter( _
    >> "SELECT EntryDate, Tracker, EntryText FROM ....")
    >> odaJournals.Fill(dtJournals)
    >> DataGrid1.DataSource = dtJournals
    >> DataGrid1.DataBind()
    >> Session("MyDataTable") = dtJournals
    >> End If
    >> End Sub
    >>
    >> Sub Sort_Grid(sender As Object, e As DataGridSortCommandEventArgs)
    >> dt = New DataTable
    >> dt = Session("MyDataTable")
    >> dv = New DataView(dt)
    >> dv.Sort = e.SortExpression
    >> DataGrid1.DataSource = dv
    >> DataGrid1.DataBind()
    >> End Sub
    >>
    >> Sub Export_Click(Sender As Object, E As EventArgs)
    >> ' 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 sw As New System.IO.StringWriter()
    >> Dim hw As New System.Web.UI.HtmlTextWriter(sw)
    >>
    >> ' Get the HTML for the control.
    >> DataGrid1.RenderControl(hw)
    >> ' Write the HTML back to the browser.
    >> Response.Write(sw.ToString())
    >> ' End the response.
    >> Response.End()
    >> End Sub
    >>
    >> </script>
    >> <form runat="server">
    >> <asp:Button id="Button1" Text="Export" OnClick="Export_Click"
    >> runat="server"></asp:Button>
    >> <asp:DataGrid id="DataGrid1" runat="server"
    >> AutoGenerateColumns="False" AllowSorting="True"
    >> OnSortCommand="Sort_Grid">
    >> <AlternatingItemStyle
    >> BackColor="Gainsboro"></AlternatingItemStyle>
    >> <HeaderStyle BackColor="#00aaaa" HorizontalAlign="Center">
    >> </HeaderStyle>
    >> <Columns>
    >> <asp:BoundColumn
    >> HeaderText="Entry Date"
    >> DataField="EntryDate"
    >> ItemStyle-Wrap="False"
    >> ItemStyle-HorizontalAlign="Center"
    >> ItemStyle-VerticalAlign="Top"
    >> SortExpression="EntryDate">
    >> </asp:BoundColumn>
    >> <asp:BoundColumn
    >> HeaderText="User ID"
    >> DataField="Tracker"
    >> ItemStyle-Wrap="False"
    >> ItemStyle-HorizontalAlign="Center"
    >> ItemStyle-VerticalAlign="Top"
    >> SortExpression="Tracker">
    >> </asp:BoundColumn>
    >> <asp:BoundColumn
    >> HeaderText="Entry Text"
    >> DataField="EntryText"
    >> ItemStyle-VerticalAlign="Top">
    >> </asp:BoundColumn>
    >> </Columns>
    >> </asp:DataGrid>
    >> </form>
    >>

    >
     
    kenneth, Aug 12, 2003
    #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. Cowboy \(Gregory A. Beamer\)

    Export datagrid(with allowsorting) to Excel

    Cowboy \(Gregory A. Beamer\), Jun 25, 2003, in forum: ASP .Net
    Replies:
    6
    Views:
    525
  2. =?Utf-8?B?U3JpZGhhcg==?=

    using Microsoft Excel image for Export to Excel button

    =?Utf-8?B?U3JpZGhhcg==?=, Dec 9, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    3,112
    =?Utf-8?B?U3JpZGhhcg==?=
    Dec 9, 2005
  3. =?Utf-8?B?SGVtYW50IFNpcGFoaW1hbGFuaQ==?=

    Export to Excel (Default File Type - Excel)

    =?Utf-8?B?SGVtYW50IFNpcGFoaW1hbGFuaQ==?=, Apr 24, 2006, in forum: ASP .Net
    Replies:
    15
    Views:
    17,532
    syed hassan
    May 21, 2009
  4. Grey
    Replies:
    4
    Views:
    1,989
    Mark Rae [MVP]
    Oct 17, 2007
  5. 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:
    225
    Elton Wang
    Jul 21, 2005
Loading...

Share This Page