[vb.net] DataTable 날짜를 그룹으로 설정하고 그룹별 합계내기

지식인 질문에 대한 답변내용입니다.

https://kin.naver.com/qna/detail.nhn?d1id=1&dirId=1040103&docId=362704813

비주얼베이직 vb.net 질문드려요 textbox 검색 기능

사진과 같이 왼쪽 textbox 내용을 기간별로 검색해서 오른쪽 텍스트박스에 출력하게끔 하려면 어떻게 해야 될까요??그리고 출력된 내용에서 10/2020-07-0310/2020-0...

kin.naver.com

​

​

​

 

 

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

        '// 원칙적으로 아래와 같이 하는것이 옳을것이다.
        '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
        
    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

0.43MB