Solved

asp.net, vb - Export to excel

Posted on 2010-09-15
3
559 Views
Last Modified: 2012-06-22
I found the following code on the web to export the contents of an asp.net gridview to Excel.
It works fine, but how can I make also export the text from other controls? Eg. a label that sits above my gridview and a table and a textbox that sits below the gridview?
Partial Class exportToExcel

    Inherits System.Web.UI.Page



    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        '// Disable paging

        GridView1.AllowPaging = False



        '// exluded columns arraylist

        Dim defaultExcludedColumns As New ArrayList()



        '// Always exclude these columns

        defaultExcludedColumns.Add("")



        '// Send to base Excel export method 

        ExportGridView(GridView1, "Excel-Name", defaultExcludedColumns)



        '// Rebind with paging enabled

        GridView1.AllowPaging = True

    End Sub





    '/// <summary>

    '/// Export GridView data to Excel.

    '/// </summary>

    '/// <param name="grdView">GridView control to export.</param>

    '/// <param name="filename">Filename of excel spreadsheet.</param>

    '/// <param name="excludedColumnList">ArrayList of columns to exlude.</param>

    Sub ExportGridView(ByVal grdView As GridView, ByVal filename As String, ByVal excludedColumnList As ArrayList)

        '// Clear response content & headers

        Response.Clear()

        Response.ClearContent()

        Response.ClearHeaders()





        '// Add header

        Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls")

        Response.Charset = String.Empty

        Response.Cache.SetCacheability(System.Web.HttpCacheability.Public)

        Response.ContentType = "application/vnd.xls"



        '// Create stringWriter

        Dim stringWrite As New System.IO.StringWriter()



        '// Create HtmlTextWriter

        Dim htmlWrite As New HtmlTextWriter(stringWrite)





        '// Remove controls from Column Headers

        If (grdView.HeaderRow IsNot Nothing And grdView.HeaderRow.Cells IsNot Nothing) Then

            Dim ct As Integer

            For ct = 0 To grdView.HeaderRow.Cells.Count - 1 Step ct + 1

                ' Save initial text if found

                Dim headerText As String = grdView.HeaderRow.Cells(ct).Text



                ' Check for controls in header

                If grdView.HeaderRow.Cells(ct).HasControls() Then

                    ' Check for link button

                    If grdView.HeaderRow.Cells(ct).Controls(0).GetType().ToString() = "System.Web.UI.WebControls.DataControlLinkButton" Then

                        ' link button found, get text

                        headerText = (CType(grdView.HeaderRow.Cells(ct).Controls(0), LinkButton)).Text

                    End If



                    ' Remove controls from header

                    grdView.HeaderRow.Cells(ct).Controls.Clear()

                End If



                ' Reassign header text

                grdView.HeaderRow.Cells(ct).Text = headerText

            Next



        End If

        '// Remove footer

        If (grdView.FooterRow IsNot DBNull.Value) Then



            grdView.FooterRow.Visible = False

        End If



        '// Remove unwanted columns (header text listed in removeColumnList arraylist)

        Dim field As DataControlField

        For Each field In grdView.Columns



            If (excludedColumnList.Contains(field.HeaderText)) Then



                field.Visible = False

            End If

        Next



        '// Call gridview's renderControl

        grdView.RenderControl(htmlWrite)



        '// Write Response to browser

        Response.Write(stringWrite.ToString())



        Response.End()

    End Sub



    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

        'MyBase.VerifyRenderingInServerForm(control)



    End Sub



End Class

Open in new window

0
Comment
Question by:Rick
3 Comments
 
LVL 5

Accepted Solution

by:
Varghese Jacob earned 500 total points
ID: 33689282
You have to use Response.Write() to put what ever you want before and after the below lines in the code snippet mentioned above.

        '// Write Response to browser
        Response.Write(stringWrite.ToString())

0
 
LVL 29

Expert Comment

by:Kumaraswamy R
ID: 33690213
0
 
LVL 13

Author Closing Comment

by:Rick
ID: 33691550
That's right. I remember doing this about 2 yrs. ago with Windows forms.   Thanks!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now