Public Class WebForm1
Inherits System.Web.UI.Page
#Region "Main GridView"
Protected Sub btnGetData_Click(sender As Object, e As EventArgs) Handles btnGetData.Click
Dim tst As New TestRecord
Dim recs As List(Of TestRecord) = tst.LoadData
Session("Data") = recs
gvwRecord.DataSource = recs
gvwRecord.DataBind()
gvwRecordDetail.Visible = False
End Sub
Private Sub gvwRecord_RowCreated(sender As Object, e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvwRecord.RowCreated
If e.Row.RowType = DataControlRowType.Header Then
Dim headerCell1 As TableCell = New TableCell()
Dim headerCell2 As TableCell = New TableCell()
headerCell1.ColumnSpan = 3
headerCell1.Text = "Main Header 1"
headerCell1.BackColor = Drawing.Color.LightGray
headerCell1.HorizontalAlign = HorizontalAlign.Center
headerCell2.ColumnSpan = 2
headerCell2.Text = "Main Header 2"
headerCell2.BackColor = Drawing.Color.LightGray
headerCell2.HorizontalAlign = HorizontalAlign.Center
Dim rowHeader1 As GridViewRow = New GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Normal)
rowHeader1.Cells.Add(headerCell1)
rowHeader1.Cells.Add(headerCell2)
rowHeader1.ForeColor = Drawing.Color.Black
rowHeader1.Font.Bold = True
rowHeader1.Visible = True
gvwRecord.Controls(0).Controls.AddAt(0, rowHeader1)
Dim fields2 As TableCellCollection = e.Row.Cells
Dim headerCell11 As TableCell = New TableCell()
Dim headerCell12 As TableCell = New TableCell()
headerCell11.ColumnSpan = 2
headerCell11.Text = "ID and Name"
headerCell11.BackColor = Drawing.Color.Maroon
headerCell12.ColumnSpan = 3
headerCell12.Text = "Age and Sex"
headerCell12.BackColor = Drawing.Color.Maroon
Dim rowHeader2 As GridViewRow = New GridViewRow(1, 1, DataControlRowType.Header, DataControlRowState.Normal)
rowHeader2.Cells.Add(headerCell11)
rowHeader2.Cells.Add(headerCell12)
rowHeader2.Font.Size = 12
rowHeader2.ForeColor = Drawing.Color.White
rowHeader2.HorizontalAlign = HorizontalAlign.Center
rowHeader2.Visible = True
rowHeader2.Font.Bold = True
gvwRecord.Controls(0).Controls.AddAt(1, rowHeader2)
ElseIf e.Row.RowType = DataControlRowType.Footer Then
Dim footerCell1 As TableCell = New TableCell()
Dim footerCell2 As TableCell = New TableCell()
Dim footerCell3 As TableCell = New TableCell()
Dim footerCell4 As TableCell = New TableCell()
Dim dataRecs As List(Of TestRecord) = Nothing
Dim totalRec As Integer = 0
Dim totalMale As Integer = 0
Dim totalFemale As Integer = 0
Dim totalAgeBelow30 As Integer = 0
Dim totalAgeAbove30 As Integer = 0
If Session("Data") IsNot Nothing Then
dataRecs = DirectCast(Session("Data"), List(Of TestRecord))
If Not dataRecs Is Nothing AndAlso dataRecs.Count > 0 Then
totalRec = dataRecs.Count
Dim list1 = From r As TestRecord In dataRecs Where r.Sex = "Male"
totalMale = list1.Count
Dim list2 = From r As TestRecord In dataRecs Where r.Sex = "Female"
totalFemale = list2.Count
Dim list3 = From r As TestRecord In dataRecs Where r.Age < 30
totalAgeBelow30 = list3.Count
Dim list4 = From r As TestRecord In dataRecs Where r.Age >= 30
totalAgeAbove30 = list4.Count
End If
End If
footerCell1.Text = "Summary"
footerCell2.Text = "Age [< 30yrs: " & totalAgeBelow30.ToString() & "] [>=30yrs:" & totalAgeAbove30.ToString() & "]"
footerCell3.ColumnSpan = 2
footerCell3.Text = "Male: " & totalMale.ToString() & " Female:" & totalFemale.ToString()
footerCell4.Text = "Total Count:" & totalRec.ToString()
Dim rowFooter1 As GridViewRow = New GridViewRow(gvwRecord.Rows.Count + 3, 0, DataControlRowType.Footer, DataControlRowState.Normal)
rowFooter1.Cells.Add(footerCell1)
rowFooter1.Cells.Add(footerCell2)
rowFooter1.Cells.Add(footerCell3)
rowFooter1.Cells.Add(footerCell4)
rowFooter1.ForeColor = Drawing.Color.Black
rowFooter1.BackColor = Drawing.Color.LightGray
rowFooter1.HorizontalAlign = HorizontalAlign.Left
rowFooter1.Font.Bold = True
rowFooter1.Visible = True
For cellCount As Integer = 0 To rowFooter1.Cells.Count - 1
rowFooter1.Cells(cellCount).Wrap = False
Next
gvwRecord.Controls(0).Controls.AddAt(gvwRecord.Rows.Count + 3, rowFooter1)
End If
End Sub
Private Sub gvwRecord_RowDataBound(sender As Object, e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvwRecord.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
Dim paramString As String = ""
Dim value As String = ""
value = CType(e.Row.Cells(2).FindControl("hplAge"), HyperLink).Text
paramString = "javascript:ShowGrid('Age'," & value & ");"
CType(e.Row.Cells(2).FindControl("hplAge"), HyperLink).Attributes.Add("onclick", paramString)
value = CType(e.Row.Cells(3).FindControl("hplSex"), HyperLink).Text
paramString = "javascript:ShowGrid('Sex','" & value & "');"
CType(e.Row.Cells(3).FindControl("hplSex"), HyperLink).Attributes.Add("onclick", paramString)
End If
End Sub
Private Sub btnShowData_Click(sender As Object, e As System.EventArgs) Handles btnShowData.Click
Dim typeName As String = Me.txtType.Text
Dim value As String = Me.txtValue.Text
Dim testRecs As List(Of TestRecord) = Nothing
Dim filteredRecs As List(Of TestRecord) = Nothing
testRecs = DirectCast(Session("Data"), List(Of TestRecord))
If typeName = "Age" Then
Dim recList = From r As TestRecord In testRecs Where r.Age = CInt(value)
filteredRecs = recList.ToList
ElseIf typeName = "Sex" Then
Dim recList = From r As TestRecord In testRecs Where r.Sex.Equals(value)
filteredRecs = recList.ToList
End If
Me.gvwRecordDetail.Visible = True
Me.gvwRecordDetail.DataSource = filteredRecs
Me.gvwRecordDetail.DataBind()
End Sub
#End Region
#Region "Exports"
Private Sub btnExportData_Click(sender As Object, e As System.EventArgs) Handles btnExportData.Click
If Me.gvwRecord.Rows.Count > 0 Then
Select Case Me.ddlExportData.SelectedItem.Value.ToUpper()
Case "CSV"
ExportDataToCSV()
Case "XLS"
ExportDataToExcel()
Case "XML"
ExportToXML()
End Select
Else
Response.Write("No Data available for export")
End If
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
End Class
Public Class TestRecord
Public Property ID As Integer = 0
Public Property Name As String = String.Empty
Public Property Age As Integer = 18
Public Property Sex As String = "Male"
Public Property Title As String = "CEO"
Public Sub New()
End Sub
Public Sub New(ByVal ID As Integer, ByVal name As String, ByVal age As Integer, ByVal sex As String, ByVal title As String)
Me.ID = ID
Me.Name = name
Me.Age = age
Me.Sex = sex
Me.Title = title
End Sub
Public Function LoadData() As List(Of TestRecord)
Dim recs As New List(Of TestRecord)
For i = 0 To 10
Dim rec As New TestRecord(i, "LastName" & i.ToString & ", FirstName" & i.ToString(), 25 + CInt(IIf(i Mod 2 = 0, 0, i)), IIf(i Mod 2 = 0, "Male", "Female").ToString, "Title #" & i.ToString)
recs.Add(rec)
Next
Return recs
End Function
End Class