Solved

asp.net, vb - Export to excel

Posted on 2010-09-15
3
562 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

816 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

8 Experts available now in Live!

Get 1:1 Help Now