This functionality is missing in the GridView control.
There are various ways to go about it. I thought I will do it without Ajax or other overheads.
The following code takes in a Datatable as input and returns the grouped Datatable as output.
Make the output datatable the source of any gridview!
The parameters allow multi column grouping and sorting
becomes
-----------------------------------------------------------------------
Public Shared Function GroupDataTable(ByVal T1 As DataTable, ByVal GroupByColumns As String(), _
Optional ByVal SortOrder As Boolean() = Nothing, _
Optional ByVal SumColumns As String() = Nothing, _
Optional ByVal HeaderCell As Integer = 0 _
) As DataTable
Dim T2 As New DataTable
Dim sortStr As String = ""
Dim i, j, N, T, m As Integer
Dim dR1 As DataRow
Dim drSum As DataRow
Dim xS As Double
Try
Dim dView As DataView = T1.DefaultView
For i = 0 To GroupByColumns.Length - 1
If String.IsNullOrEmpty(sortStr) Then
sortStr = GroupByColumns(i)
Else
sortStr = sortStr & ", " & GroupByColumns(i)
End If
If Not IsNothing(SortOrder) Then
If SortOrder.Length > i Then
If SortOrder(i) = False Then
sortStr = sortStr & " " & "DESC"
End If
End If
End If
Next
dView.Sort = sortStr
T2 = dView.ToTable
' Sorting Done
Dim Gr1 As String = ""
Dim Gr2 As String
T = T2.Rows.Count
drSum = T2.NewRow
For j = 0 To T - 1
m = m + 1
Gr2 = T2.Rows(j + N)(GroupByColumns(0))
For i = 1 To GroupByColumns.Length - 1
Gr2 = Gr2 & ">" & T2.Rows(j + N)(GroupByColumns(i))
Next
If Gr2 <> Gr1 Then 'Group changes
'Header row
Gr1 = Gr2
dR1 = T2.NewRow
dR1(HeaderCell) = Gr1
T2.Rows.InsertAt(dR1, j + N)
N = N + 1
'Summary row
If Not IsNothing(SumColumns) And j > 0 Then
drSum(HeaderCell) = "Rows: " & (m - 1)
T2.Rows.InsertAt(drSum, j + N - 1)
N = N + 1
drSum = T2.NewRow
xS = 0
m = 1
End If
End If
'create summary rows
If Not IsNothing(SumColumns) Then
For i = 1 To SumColumns.Length
If Not IsDBNull(drSum(SumColumns(i - 1))) Then xS = drSum(SumColumns(i - 1))
If Not IsDBNull(T2.Rows(j + N)(SumColumns(i - 1))) Then xS = xS + T2.Rows(j + N)(SumColumns(i - 1))
drSum(SumColumns(i - 1)) = xS
Next
End If
'---------
Next
'Last summary row
If Not IsNothing(SumColumns) Then
drSum(HeaderCell) = "Rows: " & m
T2.Rows.Add(drSum)
End If
'Row increment done
T2.AcceptChanges()
T2 = T2.Copy
Catch ex As Exception
Throw
End Try
Return T2
End Function
Comments
Post a Comment