Sunday, January 22, 2012

Export GridView Data to CSV/Excel/XML format

Many times your customers will ask if they can export the Gridview data in one of the most popular formats like CSV, Excel or XML. I had written an example before for exporting Gridview data to Excel only, here I am combining all of three popular formats at one place so that you can choose whatever it may fit your need or give the opportunity to end user to select whatever format they would like to choose to export the data.

I am using my previous example as the base. I will add two simple controls on the WebForm1.aspx page, which will be visible only when data is populated in Gridview. The following controls are for export options, which will go right after button Get Data.

<br />
 Export Format 
<asp:DropDownList ID="ddlExportData" runat="server" >
    <asp:ListItem Value="CSV" Text="CSV"/>
    <asp:ListItem Value="XLS" Text="Excel"/>
    <asp:ListItem Value="XML" Text="XML"/>
</asp:DropDownList>&nbsp;
<asp:Button ID="btnExportData" runat="server" CausesValidation="false" Text="Export Data" />    

Code behind, add the following methods for handing the export options

#Region "Exports"
    Private Sub btnExportData_Click(sender As Object, e As System.EventArgs) Handles btnExportData.Click
        Select Case Me.ddlExportData.SelectedItem.Value.ToUpper()
            Case "CSV"
                ExportDataToCSV()
 
            Case "XLS"
                ExportDataToExcel()
 
            Case "XML"
                ExportToXML()
        End Select
    End Sub
 
    Private Sub ExportDataToCSV()
        Dim fileName As String = "FileName_" & Format(Now, "yyyyMMddhhmms") & ".csv"
        Dim localGridView As GridView = GetLocalMktSourceSummaryGridView()
 
        If localGridView IsNot Nothing AndAlso localGridView.Rows.Count < 65536 Then
            HttpContext.Current.Response.Clear()
            ' Set the response headers to fit our CSV file 
            HttpContext.Current.Response.ContentType = "text/plain"
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" & fileName)
 
            Using writer As New System.IO.StreamWriter(HttpContext.Current.Response.OutputStream)
                Dim columnHeader As String = String.Empty
                For i As Integer = 0 To localGridView.Columns.Count - 1
                    columnHeader += localGridView.Columns(i).HeaderText & IIf(i < localGridView.Columns.Count - 1, ",", "").ToString()
                Next
 
                writer.WriteLine(columnHeader)
                'writer.WriteLine(AddCSVHeaderRow()) ' Only if you need custom headers to be added
                ' Add all the data rows 
                For Each row As GridViewRow In localGridView.Rows
                    writer.WriteLine(GetCSVLine(row.Cells))
                Next
            End Using
 
            ' End the current response. Otherwise, excel will open with the whole page inside.
            HttpContext.Current.Response.End()
 
        Else
            Response.Write("Too many rows - Export to Excel not possible")
        End If
 
    End Sub
 
    Private Function GetLocalMktSourceSummaryGridView() As GridView
        Dim rptData As List(Of TestRecord) = Nothing
        Dim localGridView As New GridView
        If Session("Data") IsNot Nothing Then rptData = DirectCast(Session("Data"), List(Of TestRecord))
        localGridView.AllowPaging = False
        localGridView.AllowSorting = False
        localGridView.AutoGenerateColumns = False
 
        localGridView.DataSource = rptData
 
        Dim col01 As New BoundField
        Dim col02 As New BoundField
        Dim col03 As New BoundField
        Dim col04 As New BoundField
        Dim col05 As New BoundField
 
        col01.HeaderText = "ID"
        col01.DataField = "ID"
        localGridView.Columns.Add(col01)
 
        col02.HeaderText = "Name"
        col02.DataField = "Name"
        localGridView.Columns.Add(col02)
 
        col03.HeaderText = "Age"
        col03.DataField = "Age"
        localGridView.Columns.Add(col03)
 
        col04.HeaderText = "Sex"
        col04.DataField = "Sex"
        localGridView.Columns.Add(col04)
 
        col05.HeaderText = "Title"
        col05.DataField = "Title"
        localGridView.Columns.Add(col05)
 
        localGridView.DataBind()
        Return localGridView
    End Function
 
    Private Shared Function GetCSVLine(ByVal cellsToAdd As TableCellCollection) As String
        Dim line As String = String.Empty
        Dim isFirst As Boolean = True
        For Each cell As TableCell In cellsToAdd
            If Not isFirst Then
                line += ","
            End If
            isFirst = False
            line += """" & Replace(cell.Text, "&nbsp;", "") & """"
        Next
        Return line
    End Function
 
    Private Sub ExportDataToExcel()
        If gvwRecord.Rows.Count + 1 < 65536 Then
            Dim tw As New System.IO.StringWriter()
            Dim hw As New System.Web.UI.HtmlTextWriter(tw)
            Dim frmExport As HtmlForm = New HtmlForm()
            Dim fileName As String = "FileName_" & Format(Now, "yyyyMMddhhmms") & ".xls"
            Dim outputString As String = String.Empty
            Page.Response.ContentType = "application/vnd.ms-excel"
            Page.Response.AddHeader("content-disposition", "attachment;filename=" & fileName)
            Page.Response.Charset = ""
            Page.EnableViewState = False
            Page.Controls.Add(frmExport)
            frmExport.Controls.Add(gvwRecord)
            frmExport.RenderControl(hw)
            outputString = tw.ToString()
            outputString = Replace(outputString, "<a href", "<span id")
            outputString = Replace(outputString, "</a>", "</span>")
 
            Response.Write(outputString)
            Response.End()
        Else
            Response.Write("Too many rows - Export to Excel not possible")
        End If
    End Sub
 
    Private Sub ExportToXML()
        Dim reportData As List(Of TestRecord) = Nothing
        If Session("Data") IsNot Nothing Then reportData = DirectCast(Session("Data"), List(Of TestRecord))
        Dim fileName As String = "FileName_" & Format(Now, "yyyyMMddhhmms") & ".xml"
        If reportData IsNot Nothing Then
            Dim reportXML As XElement = New XElement("TestRecords", From lr In reportData
                                            Select New XElement("Data",
                                                                        New XElement("ID", lr.ID),
                                                                        New XElement("Name", lr.Name),
                                                                        New XElement("Age", lr.Age),
                                                                        New XElement("Sex", lr.Sex),
                                                                        New XElement("Title", lr.Title)))
 
 
            Response.ClearHeaders()
            Response.AppendHeader("Content-Disposition", "attachment; filename=" & fileName)
            Response.ContentType = "text/xml"
            Response.Write(reportXML.ToString())
            Response.End()
        Else
            Response.Write("No Record found.")
        End If
 
 
    End Sub
 
#End Region

You are all set to export the data in any of the form you would like to.

No comments: