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>
<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, " ", "") & """"
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:
Post a Comment