Adding to Export Of GridView to Excel

rsearing · January 23rd, 2007, 07:25 PM

Imar,

Sorry if this is trivial, but trying to figure out how to do this. I have tried the following code and it doesn't work. I tried looking at properties of LiteralControl and Literal--not sure how to add this.

  Dim myLiteral As New LiteralControl()
        myLiteral.Text = "<br/>"
        Dim myPanel As Panel = New Panel()
        Dim myLabel As New Label
        Dim MyGridView As New GridView()
        MyGridView = GridView2
        myLabel.Text = "Table Header"
        MyGridView.BorderColor = Drawing.Color.Black
        MyGridView.BorderWidth = New Unit(1)
        myPanel.Controls.Add(myLabel)
        myPanel.Controls.Add(myLiteral)
        myPanel.Controls.Add(MyGridView)
        Dim attachment As String = "attachment; filename=Contacts.xls"
        Response.ClearContent()
        Response.AddHeader("content-disposition", attachment)
        Response.ContentType = "application/ms-excel"
        Dim sw As IO.StringWriter = New IO.StringWriter()
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        myPanel.RenderControl(htw)
        Response.Write(sw.ToString())
        Response.End()

Regards,
Rob

Imar · January 23rd, 2007, 07:37 PM

Can you define "it doesn't work"? It's hard to see the solution without a problem description.

One thing I notice is the way you set up the GridView. Instead of assigning it GridView2, you probably want to bind it you your original data source:

myGridView.DataSource = SomeCallInOneOfYourClassesThatReturnsData()
myGridView.DataBind()

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

rsearing · January 23rd, 2007, 08:50 PM

Sorry...the GridView works fine. I don't get an error--I get the actual Excel doc---there's just not the extra line between the label and the gridview.

(Again, sorry).

Rob

Imar · January 24th, 2007, 03:17 PM

If Excel doesn't understand breaks, try giving it a <table> instead.

Create a new Table, a table Row and a table Cell. Add the Cell to the Row, the Row to the Table and the Table to the Panel.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: The Ship Song by Nick Cave & the Bad Seeds (Track 5 from the album: The Good Son) What's This?

rsearing · January 24th, 2007, 03:38 PM

Imar,

Could you provide sample code or an example of how I'd do that? I'm lost. I would assume that I add a table..row..then the gridview to the panel?

Sorry---
Rob

Imar · January 24th, 2007, 03:46 PM

No need to say sorry....

Untested, but something like this should work:

Code:

    Dim myPanel As Panel = New Panel()
    Dim myLabel As New Label
    Dim myGridView As New GridView()

    Dim myTable As New Table()
    Dim myRow As New TableRow()
    Dim myCell As New TableCell()
    myCell.Text = "<br />"

    ' Add the cell to the row
    myRow.Cells.Add(myCell)

    ' Add the row to the table
    myTable.Rows.Add(myRow)

    myGridView = GridView2
    myLabel.Text = "Table Header"
    MyGridView.BorderColor = Drawing.Color.Black
    MyGridView.BorderWidth = New Unit(1)
    myPanel.Controls.Add(myLabel)

    ' Add the table to the Panel
    myPanel.Controls.Add(myTable)

    myPanel.Controls.Add(myGridView)
    Dim attachment As String = "attachment; filename=Contacts.xls"
    Response.ClearContent()
    Response.AddHeader("content-disposition", attachment)
    Response.ContentType = "application/ms-excel"
    Dim sw As IO.StringWriter = New IO.StringWriter()
    Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
    myPanel.RenderControl(htw)
    Response.Write(sw.ToString())
    Response.End()

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: Play In The Sunshine by Prince (Track 2 from the album: Sign Of The Times (Disk 1)) What's This?

rsearing · January 24th, 2007, 11:34 PM

That worked. Just wish there was a way to mess with gridlines. You can't add gridlines to the label/table nicely....

Thanks Imar!!

:)
Rob

Imar · January 25th, 2007, 02:41 AM

There are ways to do that, but you need to pay for them.

Look at commercial Excel writers like http://officewriter.softartisans.com/

They write true Excel data, not just some HTML that Excel interprets.

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.