setelah dicoba-coba akhirnya dapat procedure seperti dibawah ini. Procedurenya simpan di modul
ini procedure SubTotal
Public Sub SubTotalDGV(dgv As DataGridView, GroupOn As Integer, TotalOn As Integer, Warna As Color)
Dim clST As New DataGridViewTextBoxColumn
Dim clSTID As New DataGridViewTextBoxColumn
Dim Total As Double = 0
Dim SubTotalID As String = ""
Dim i As Integer = 0
If dgv.Rows.Count = 0 Then Exit Sub
With clST
.HeaderText = "SubTotalID"
.Name = "SubTotalID"
.Width = 120
.ReadOnly = True
.Visible = False
End With
With clSTID
.HeaderText = "KodeAsal"
.Name = "KodeAsal"
.Width = 120
.ReadOnly = True
.Visible = False
End With
If dgv.Columns.Contains("SubTotalID") = False Then dgv.Columns.Add(clST)
If dgv.Columns.Contains("KodeAsal") = False Then dgv.Columns.Add(clSTID)
For k As Integer = 0 To dgv.Rows.Count - 1
If dgv.Rows(k).Cells("SubTotalID").Value Is Nothing Then
dgv.Rows(k).Cells("SubTotalID").Value = "0"
dgv.Rows(k).Cells("KodeAsal").Value = dgv.Rows(k).Cells(GroupOn).Value
End If
Next
SubTotalID = dgv.Rows(0).Cells(GroupOn).Value.ToString
Do
If SubTotalID = dgv.Rows(i).Cells("KodeAsal").Value.ToString And dgv.Rows(i).Cells("SubTotalID").Value.ToString = "0" Then
Total += Convert.ToDouble(dgv.Rows(i).Cells(TotalOn).Value)
Else
If dgv.Rows(i).Cells("SubTotalID").Value.ToString = "0" Then
dgv.Rows.Insert(i, 1)
End If
dgv.Rows(i).Cells(TotalOn).Value = Total
dgv.Rows(i).Cells("SubTotalID").Value = "1"
dgv.Rows(i).Cells("KodeAsal").Value = SubTotalID
dgv.Rows(i).DefaultCellStyle.BackColor = Warna
If (i) < dgv.Rows.Count - 1 Then
i += 1
SubTotalID = dgv.Rows(i).Cells(GroupOn).Value.ToString
Total = (Convert.ToDouble(dgv.Rows(i).Cells(TotalOn).Value))
End If
End If
If dgv.Rows.Count - 1 = (i) Then
If dgv.Rows(i).Cells("SubTotalID").Value.ToString = "0" Then
dgv.Rows.Insert(i + 1, 1)
dgv.Rows(i + 1).Cells(TotalOn).Value = Total
dgv.Rows(i + 1).Cells("SubTotalID").Value = "1"
dgv.Rows(i + 1).Cells("KodeAsal").Value = SubTotalID
dgv.Rows(i + 1).DefaultCellStyle.BackColor = Warna
Else
dgv.Rows(i).Cells(TotalOn).Value = Total
dgv.Rows(i).Cells("SubTotalID").Value = "1"
dgv.Rows(i).Cells("KodeAsal").Value = SubTotalID
dgv.Rows(i).DefaultCellStyle.BackColor = Warna
End If
Exit Do
End If
i += 1
Loop Until (i) > dgv.Rows.Count - 1
End Sub
ini Procedure Grand Total
Public Sub GrandTotalDGV(dgv As DataGridView, TotalOn As Integer, Warna As Color)
Dim Total As Double = 0
If dgv.Columns.Contains("SubTotalID") = False Then
dgv.Columns.Add("SubTotalID", "SubTotalID")
dgv.Columns("SubTotalID").Visible = False
For k As Integer = 0 To dgv.Rows.Count - 1
If dgv.Rows(k).Cells("SubTotalID").Value Is Nothing Then
dgv.Rows(k).Cells("SubTotalID").Value = "0"
End If
Next
End If
For i As Integer = 0 To dgv.Rows.Count - 1
If dgv.Rows(i).Cells("SubTotalID").Value.ToString = "0" Then
Total += Convert.ToDouble(dgv.Rows(i).Cells(TotalOn).Value)
End If
Next
Dim x As Integer = dgv.Rows.Count - 1
If dgv.Rows(dgv.Rows.Count - 1).Cells("SubTotalID").Value.ToString = "2" Then
dgv.Rows(x).Cells(TotalOn).Value = Total
dgv.Rows(x).DefaultCellStyle.BackColor = Warna
Else
dgv.Rows.Insert(x + 1, 1)
dgv.Rows(x + 1).Cells(TotalOn).Value = Total
dgv.Rows(x + 1).Cells("SubTotalID").Value = "2"
dgv.Rows(x + 1).DefaultCellStyle.BackColor = Warna
End If
End Sub
Ini Procedure untuk menghapus Sub Total dan Grand Total
Public Sub ClearSubTotalDGV(dgv As DataGridView)
For i As Integer = dgv.Rows.Count - 1 To 0 Step -1
If dgv.Columns.Contains("SubTotalID") = True Then
If dgv.Rows(i).Cells("SubTotalID").Value.ToString <> "0" Then
dgv.Rows.RemoveAt(i)
End If
End If
Next
If dgv.Columns.Contains("SubTotalID") = True Then dgv.Columns.Remove("SubTotalID")
If dgv.Columns.Contains("KodeAsal") = True Then dgv.Columns.Remove("KodeAsal")
End Sub
ClearSubTotalDGV(dgv)
SubTotalDGV(dgv, 1, 2, Color.Aquamarine)
SubTotalDGV(dgv, 1, 3, Color.Aquamarine)
GrandTotalDGV(dgv, 2, Color.Yellow)
GrandTotalDGV(dgv, 3, Color.Yellow)
Silahkan dicoba procedure diatas semoga bisa bermanfaat
Syafruddin
1 komentar:
bang kalo subtotal sama grandtotalnya udah ada gimana caranya?
Posting Komentar