Controls [vb.net] DataTable 날짜를 그룹으로 설정하고 그룹별 합계내기
페이지 정보
본문
지식인 답변내용입니다.
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim D As New DataTable, R As DataRow
D.Columns.Add("금액", GetType(Integer))
D.Columns.Add("날짜", GetType(Date))
D.Columns.Add("종류", GetType(String))
D.Columns.Add("품명", GetType(String))
For i = 1 To 10
R = D.NewRow
R("금액") = Int(Rnd() * 100) * 10
R("날짜") = Date.Today.AddDays(Int(Rnd() * 4) - 3)
R("종류") = Choose(Int(Rnd() * 4) + 1, "과일", "채소", "음료", "육류")
R("품명") = "내가산 품명 " & i
D.Rows.Add(R)
Next
DataGridView1.DataSource = D
End Sub
Private Sub DataGridView1_DataSourceChanged(sender As Object, e As EventArgs) Handles DataGridView1.DataSourceChanged,
DataGridView2.DataSourceChanged
DATAGRIDVIEW_DEFAULT_SETTING(sender)
End Sub
Public Function DATAGRIDVIEW_DEFAULT_SETTING(ByRef DGV As DataGridView) As DataGridView
DGV.AllowUserToAddRows = False
DGV.RowHeadersVisible = False
DGV.AllowUserToDeleteRows = False
DGV.ColumnHeadersDefaultCellStyle.BackColor = Color.LightGray
DGV.ColumnHeadersDefaultCellStyle.Font = New Font("맑은 고딕", 9.0!, FontStyle.Bold)
DGV.BackgroundColor = Color.White
DGV.AutoResizeColumnHeadersHeight()
DGV.AutoResizeColumns()
For Each C As DataGridViewColumn In DGV.Columns
C.SortMode = DataGridViewColumnSortMode.Programmatic '// 해더 클릭시 Sort X
Next
Return DGV
End Function
?Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If Not IsDate(TextBox2.Text) Then MsgBox("[시작일] 날짜형식이 아닙니다.") : Return
If Not IsDate(TextBox3.Text) Then MsgBox("[종료일] 날짜형식이 아닙니다.") : Return
Dim D As DataTable = DataGridView1.DataSource
Dim expression As String = String.Format(
"날짜 >= '{0}' AND 날짜 <= '{1}'",
DateValue(TextBox2.Text).ToString("yyyy-MM-dd"),
DateValue(TextBox3.Text).ToString("yyyy-MM-dd")
)
D = D.Select(expression).CopyToDataTable()
Dim fruitGroups = D.AsEnumerable().GroupBy(Function(row) row.Field(Of Date)("날짜"))
Dim tableResult = D.Clone()
For Each grp In fruitGroups
tableResult.Rows.Add(
grp.Sum(Function(row) row.Field(Of Int32)("금액")), grp.Key,
grp.First().Field(Of String)("종류"),
grp.First().Field(Of String)("품명")
)
Next
DataGridView2.DataSource = tableResult
End Sub
End Class
원칙적으로 아래와 같이 하는것이 맞을것이다.
Dim D As DataTable = DataGridView1.DataSource
Dim q = From r In D.AsEnumerable()
Select C1 = r.Field(Of Integer)("금액"),
C2 = r.Field(Of String)("날짜"),
C3 = r.Field(Of Integer)("종류"),
C4 = r.Field(Of Integer)("품명")
Group By C2, C3, C4 Into Group
Select C1 = Group.Sum(Function(x) x.C1), C2, C3, C4
Dim result = D.Clone()
For Each item In q
result.Rows.Add(item.C1, item.C2, item.C3)
Next
DataGridView2.DataSource = result
첨부파일
-
검색.zip (441.6K)
213회 다운로드 | DATE : 2020-07-16 20:21:17
- 이전글[vb.net] Visual Basic에서 예외 기록 / LOG 기록 20.07.22
- 다음글[vb.net] DataTable Sort 20.07.15
댓글목록
등록된 댓글이 없습니다.