Archive for June, 2011

summarize a datatable

June 23, 2011

This might be ugly code … but I just dropped it into an admin page on a project, and it worked first time. Contains comments that tell how to use it.
What does it do? It takes a datatable and sums the columns that you tell it to, and then appends this row to the bottom of the table.

' ---------------------------------------------------------
' purpose - summarize a datatable by summing selected columns
' INPUTS: title column, sum columns
' HOW TO USE:
'Dim iret As Integer
'iret = objD.SummaryRow(dt,0, 3, "Total:", 5)
' (put the count of rows in the 0 column, the Total string in col 3, and the sum in column 5)
' you can add up to 10 columns to sum
' be careful not to put strings into datetime columns, for example
Function SummaryRow( _
ByRef dt As System.Data.DataTable, _
ByVal iCountCol As Integer, _
ByVal iTitleCol As Integer, _
ByVal sTitle As String, _
Optional ByVal i0 As Integer = 0, _
Optional ByVal i1 As Integer = 0, _
Optional ByVal i2 As Integer = 0, _
Optional ByVal i3 As Integer = 0, _
Optional ByVal i4 As Integer = 0, _
Optional ByVal i5 As Integer = 0, _
Optional ByVal i6 As Integer = 0, _
Optional ByVal i7 As Integer = 0, _
Optional ByVal i8 As Integer = 0, _
Optional ByVal i9 As Integer = 0) As Integer
Dim iParms As New ArrayList
If (i0 > 0) Then iParms.Add(i0)
If (i1 > 0) Then iParms.Add(i1)
If (i2 > 0) Then iParms.Add(i2)
If (i3 > 0) Then iParms.Add(i3)
If (i4 > 0) Then iParms.Add(i4)
If (i5 > 0) Then iParms.Add(i5)
If (i6 > 0) Then iParms.Add(i6)
If (i7 > 0) Then iParms.Add(i7)
If (i8 > 0) Then iParms.Add(i8)
If (i9 > 0) Then iParms.Add(i9)
Try
Dim NewSummaryRow As System.Data.DataRow = dt.NewRow
NewSummaryRow(iTitleCol) = sTitle
Dim s As Decimal = 0

For i As Integer = 0 To iParms.Count - 1
s = 0
Dim kolumn As Integer = iParms(i)
NewSummaryRow(kolumn) = 666 ' default it

For Each r As System.Data.DataRow In dt.Rows
s += r(kolumn)
Next
NewSummaryRow(kolumn) = s
Next
NewSummaryRow(iCountCol) = "Count:" + dt.Rows.Count.ToString
dt.Rows.Add(NewSummaryRow)
Catch ex As Exception
Diagnostics.Debug.Print("!!!!!!!!!!!!!!!!!! ERROR utils_data.SummaryRow " & ex.ToString())
Return -1
End Try
Return 1
End Function