export datagrid - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

export datagrid

Author

Topic

jhermiz

3564 Posts

Posted - 2005-04-08 : 09:11:00

Trying to export an asp.net datagrid to excel.

Here we go:


 Private Sub ImageButton1_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click
        Dim oStringWriter As System.IO.StringWriter
        Dim oHTMLTextWriter As System.Web.UI.HtmlTextWriter

        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = ""
        Me.EnableViewState = False

        oStringWriter = New System.IO.StringWriter
        oHTMLTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)
        ClearControls(dgTickets)
        Me.dgTickets.RenderControl(oHTMLTextWriter)

        Response.Write(oStringWriter.ToString())
        Response.End()
    End Sub

    Private Sub ClearControls(ByVal ctl As Control)
        Dim i As Integer
        Dim literal As LiteralControl

        i = ctl.Controls.Count - 1

        Do While i >= 0
            ClearControls(ctl.Controls(i))
            i = i - 1
        Loop


        If (Not (TypeOf (ctl) Is TableCell)) Then

            literal = New LiteralControl
            ctl.Parent.Controls.Add(literal)

            Try '(string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);

                literal.Text = CType(ctl.GetType.GetProperty("SelectedItem").GetValue(ctl, Nothing), String)

            Catch ex As Exception

            End Try
            ctl.Parent.Controls.Remove(ctl)
        Else
            If (ctl.GetType().GetProperty("Text") Is Nothing) Then
                'do nothing
            Else
                literal = New LiteralControl


                ctl.Parent.Controls.Add(literal) 
                literal.Text = CType(ctl.GetType.GetProperty("Text").GetValue(ctl, Nothing), String)
                ctl.Parent.Controls.Remove(ctl)
            End If
        End If
    End Sub

I get an error in the else (in bold)
stating:

System.ArgumentException: 'TableRow' cannot have children of type 'LiteralControl'

Not sure how to handle this :(.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-04-08 : 09:15:39

Here's the CSharp equivalent that I had translated if this helps:

http://www.c-sharpcorner.com/Code/2003/Sept/ExportASPNetDataGridToExcel.asp

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-04-08 : 09:30:06

Instead of adding literal controls to the table rows you may want to try adding table header and table cell controls instead.

Dustin Michaels

jhermiz

3564 Posts

Posted - 2005-04-08 : 10:02:58

Well I tried this as well:


Private Sub RenderGrid()
        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.
        dgTickets.RenderControl(hw)  'errors here
        ' Write the HTML back to the browser.
        Response.Write(tw.ToString())
        ' End the response.
        Response.End()
    End Sub

But I get the notorious:


System.Web.HttpException: Control 'dgTickets__ctl2__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.

Source Error: 


Line 961:        Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Line 962:        ' Get the HTML for the control.
Line 963:        dgTickets.RenderControl(hw)
Line 964:        ' Write the HTML back to the browser.
Line 965:        Response.Write(tw.ToString())

My <form> has a runat=server attribute....
Anyone else with a solution on how to easily export a datagrid to excel ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-08 : 10:36:53

would you consider using Office Web Components - OWC?
they're preety easy to use and quite ok...

Go with the flow & have fun! Else fight the flow

jhermiz

3564 Posts

Posted - 2005-04-08 : 10:37:47

As long as I can easily export to excel Ill be happy to try anything :)

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-08 : 10:48:49

well with owc you build the grid and simply call a method to save file as xls. and that's it.

Go with the flow & have fun! Else fight the flow

jhermiz

3564 Posts

Posted - 2005-04-08 : 10:49:58

...

Build the grid...

Ok the grid is built, do you mean rebuild ?
Got any sample code ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-08 : 10:54:06

in .net no. only in asp...

Go with the flow & have fun! Else fight the flow

jhermiz

3564 Posts

Posted - 2005-04-08 : 11:01:46

hahaha...errrrrrrr

cant use that, that is prolly why many of the .net solutions are using code much like the one i posted...but again I cant seem to get either solution to work :(. I will play some more.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-08 : 11:15:07

weel if the thing works in asp in also works in .net you just need to include the dll so you can see the namespace.

Go with the flow & have fun! Else fight the flow

jhermiz

3564 Posts

Posted - 2005-04-08 : 11:17:03

Ya but I dont want to reference another dll.

Where's michaelp when you need him the most, he always has tricks up his sleeves.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-04-08 : 12:38:05

Perhaps this would work...

If you change the content type to "application/vnd.ms-excel" and have the datagrid render the html to a string builder then you can build a downloadable web page that can be opened up in excel.

Heres some example code that I did to output a report to excel.

' Set the content type to Excel.
Response.ContentType = "application/vnd.ms-excel"

' Remove the charset from the Content-Type header.
Response.Charset = String.Empty

Dim stringWriter As New System.IO.StringWriter
Dim htmlTextWriter As New System.Web.UI.HtmlTextWriter(stringWriter)

' Get the HTML from the datagrid the control.
dgTickets.RenderControl(htmlTextWriter)

' Write the HTML back to the browser.
Response.Write("<html><head><title>The title of your page</title></head><body>")
Response.Write(stringWriter.ToString())
Response.Write("</body></html>")

Response.End()

Dustin Michaels

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-04-08 : 12:39:32

Also I think its possible to right click the html table created by the datagrid and saving it to excel. You could have users do this if a solution cannot be found.

Dustin Michaels

jhermiz

3564 Posts

Posted - 2005-04-08 : 13:11:36

Well I have tried what you have as well, but the problem is I am using template columns.
THe error I recieve is: System.Web.HttpException: Control 'dgTickets__ctl2__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.
I have buttons within my grid whihc are linkbuttons that maybe throwing this off hence the original post with the ClearControls function.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-04-11 : 07:55:56

Bump

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

naijacoder
Starting Member

5 Posts

Posted - 2005-05-08 : 23:43:52

try this:-
http://www.c-sharpcorner.com/Code/2003/Sept/ExportASPNetDataGridToExcel.asp

jhermiz

3564 Posts

Posted - 2005-05-09 : 08:10:47

I already posted that link, read the thread its like the third reply up.

Its no help since that is a basic datagrid

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

rhoenig
Starting Member

11 Posts

Posted - 2005-05-09 : 22:13:58

Have you had a look at this page??

http://www.dotnet247.com/247reference/msgs/26/132455.aspx

naijacoder
Starting Member

5 Posts

Posted - 2005-05-10 : 01:56:20

This should help:-
http://www.aspnetpro.com/NewsletterArticle/2003/09/asp200309so_l/asp200309so_l.asp
Naijacoder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources