Add Column Values DataGridView VB.NET

1

I'm doing an app and I want to add the values of the column SUBTOTAL classifying it by brand (column MARK ), the problem is that it will vary the brands. In the first instance it only occupied two marks and solved the problem with this code ...

Sub Sumar()
    Dim totalA As Double = 0
    Dim totalB As Double = 0
    For Each fila As DataGridViewRow In dgvDatos.Rows
        If fila.Cells("SUBTOTAL").Value Is Nothing Then
            Exit Sub
        ElseIf fila.Cells("MARCA").Value = "A" Then
            totalA += Convert.ToDouble(fila.Cells("SUBTOTAL").Value)
        ElseIf fila.Cells("MARCA").Value = "B" Then
            totalB += Convert.ToDouble(fila.Cells("SUBTOTAL").Value)
        End If
    Next
    lblA.Text = Format(totalA, "$ #,##0.000")
    lblB.Text = Format(totalB, "$ #,##0.000")
End Sub

But now the marks can be variable and just as I am handling it, I would have to declare N variables to store the result by brand. Any suggestions to optimize my operation?
Thanks in advance !!

    
asked by Silvestre Silva 24.03.2018 в 01:35
source

3 answers

1

You can use the algorithm known as control cut . In this link that I left you define it in a simple way:

"The control cut is a process in which starting from records ordered by the value of one or more fields, (called key fields or key or ordering criteria) are processed in categories determined by the ordering criteria. In other words, an ordered set of records is processed into subsets determined by order criteria. "

In your case, you should first order the table dgvData by BRAND, in order to use this attribute as a classifier. I'll attach how you could start modifying your code (I do not know much vb, so do not take this example as a solution):

Sub Sumar()
    Dim marca as String // Defines la marca para la cual estás sumando sus valores SUBTOTAL.
    Dim totalMarca as Double = 0 // El SUBTOTAL de una marca.

    marca = //Obtener la primera MARCA (en el caso de tu ejemplo cuando este ordenado sería 'A')

    For Each fila As DataGridViewRow In dgvDatos.Rows
     If fila.Cells("SUBTOTAL").Value Is Nothing Then
        Exit Sub
     if(marca = fila.Cells("MARCA").Value) then
        totalMarca += Convert.ToDouble(fila.Cells("SUBTOTAL").Value)
     else
        lblMARCA.Text = Format(totalMarca, "$ #,##0.000")
        marca = fila.Cells("MARCA").Value
    End if

    Next
End Sub

In this way, ordering your table as a first step, you would have all the rows belonging to the MARK 'A', then all the rows of the next MARK and so on. The algorithm adds all the SUBTOTALS belonging to the first brand, until finding a different one to it and there sets the brand variable with the new brand (the next one to the first defined that is 'A') to start with the whole process again.

    
answered by 24.03.2018 / 02:12
source
1

Use a dictionary, it does not require having the data sorted.

In the code where you do the sum:

Sub Sumar()
    Dim dict as new Dictionary(Of String, Double)

    For Each fila In dgvDatos.Rows.Cast(Of DataGridViewRow)
        If fila.Cells("SUBTOTAL").Value IsNot Nothing Then
            Dim marca = fila.Cells("MARCA").Value.ToString()
            Dim subtotal = Convert.ToDouble(fila.Cells("SUBTOTAL").Value)          
            Dim currentValue = If(dict.ContainsKey(marca), dict.Item(marca), 0.0R)
            If dict.ContainsKey(marca) then
                dict.Item(marca) = currentValue  + subtotal
            else
                dict.Add(marca, currentValue  + subtotal)
            EndIf
        End If
    Next
    lblMarcas.Text = String.Empty
    For Each marca in dict
        lblMarcas.Text += "Subtotal de " & marca.Key & " = " & Format(marca.Value, "$ #,##0.000") & Environment.NewLine
    Next
 End Sub

I do it by heart, I have no visual studio at hand to try

    
answered by 01.04.2018 в 07:07
0

If you have your Datagridview associated with a DataTable (dgvDatos.DataSource = TableDvvData) you can use the Compute method of the DataTable.

Sub Sumar()
    lblA.Text = Format(TablaDgvDatos.Compute("SUM(SUBTOTAL)", "MARCA = 'A'"), "$ #,##0.000")
    lblB.Text = Format(TablaDgvDatos.Compute("SUM(SUBTOTAL)", "MARCA = 'B'"), "$ #,##0.000")
    lblAV.Text = Format(TablaDgvDatos.Compute("SUM(SUBTOTAL)", "MARCA = 'AV'"), "$ #,##0.000")
End Sub

If these marks are going to be variable in time and you mean not having to modify your code when there is a new brand for example, specify how you read the marks you want to add to be able to suggest a solution.

    
answered by 24.03.2018 в 02:49