DataGrid export to excel

[kenneth]

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>
<aspataGrid 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>
</aspataGrid>
</form>

 

[Datagrid Girl [MVP]]

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]

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


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

Kenneth