VB.NET - How to sort a DataGridView by color

Navigation
0

I have a DataGridView, where I do a document review. The DataGridView is populated by a file, I use OpenFileDialog to select the file and populate the DataGridView. Then I call a function where it is made a criticism by the CNPJ / CPF field, assigning colors to the grid lines:
 - Gray: Canceled;
 - Red: Returned
 - Green: Always delivered;
 - Yellow: Delivered / Returned
 - White: First record.

I already have a function done where I check the critiques of the document, but there is a carnival inside the DataGridView. Here's the example in the image:

IwantedtoknowifthereisawayIcanorganizethisDataGridViewbycolor,firstallthegraylines,thentheredlines,thenthegreenlines,thentheyellowlines,andfinallyallthewhitelines.p>

Followmycurrentcode:

ImportsMySql.Data.MySqlClientPublicClasssalvar_arquivoDimsqlcoon=NewMySqlConnection''conexaoDimcommandAsMySqlCommandPrivatedatagridAsNewDataTable'loaddoformPrivateSubsalvar_arquivo_Load(senderAsObject,eAsEventArgs)HandlesMyBase.Load''carregatodoosmonitoramentodobancodedadoscad_monitoramentoDimdatagridAsNewDataTable'INSERECOLUNASNOGRIDVIEWdatagrid.Columns.Add("Identificação")
        datagrid.Columns.Add("Protocolo")
        datagrid.Columns.Add("Destinatario")
        datagrid.Columns.Add("Endereço")
        datagrid.Columns.Add("Bairro")
        datagrid.Columns.Add("Cep")
        datagrid.Columns.Add("Data")
        datagrid.Columns.Add("CNPF/CPF")
        datagrid.Columns.Add("Situacao")
        datagrid.Columns.Add("Cliente")
        datagrid.Columns.Add("data_processamento")
        datagrid.Columns.Add("data_vencimento")
        datagrid.Columns.Add("numeracao")
        DataGridView1.DataSource = datagrid
        ' inserindo o a largura das colunas

        ' deixando invisivel a coluna do banco de dados
        DataGridView1.Columns("Situacao").Visible = False
        DataGridView1.Columns("Cliente").Visible = False
        DataGridView1.Columns("data_processamento").Visible = False
        DataGridView1.Columns("data_vencimento").Visible = False
        DataGridView1.Columns("numeracao").Visible = False
    ' ORDENAS OS TITULOS DA COLUNA NO CENTRALIZADO
    DataGridView1.Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(2).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(3).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(4).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(5).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(6).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(7).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(8).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(9).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(10).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(11).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(12).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter


        txtcod.Select()
    End Sub

    ' butao selecionar e ler arquivo
    Private Sub btnarquivo_Click(sender As Object, e As EventArgs) Handles btnarquivo.Click
        selecionar()

End Sub


    ' Função para ler o arquivo linha por linha separado por tab
    Private Sub selecionar()
        abrir.ShowDialog()
        '  Dim x As String = ("62210980259, 10507378001020")
        Dim separador As Char

        Dim dr As DataRow
    ' INSERE COLUNAS NO GRIDVIEW
    Try

            datagrid.Columns.Add("Identificação")
            datagrid.Columns.Add("Protocolo")
            datagrid.Columns.Add("Destinatario")
            datagrid.Columns.Add("Endereço")
            datagrid.Columns.Add("Bairro")
            datagrid.Columns.Add("Cep")
            datagrid.Columns.Add("Data")
            datagrid.Columns.Add("CNPF/CPF")
            datagrid.Columns.Add("Situacao")
            datagrid.Columns.Add("Cliente")
            datagrid.Columns.Add("data_processamento")
            datagrid.Columns.Add("data_vencimento")
            datagrid.Columns.Add("numeracao")

            DataGridView1.AutoResizeColumns()
            DataGridView1.AutoResizeRows()

            dr = datagrid.NewRow()

        '
        ' FAZ A LEITURA DO ARQUIVO - COM EXPAÇAMENTO POR TAB
        Dim arquivo As New System.IO.StreamReader(abrir.FileName)
            separador = vbTab
            ' faz um split de leitura de linha a linha do arquivo
            While arquivo.Peek() <> -1
                datagrid.Rows.Add(arquivo.ReadLine().Split(separador))
            End While
            DataGridView1.DataSource = datagrid

            TextBox2.Text = DataGridView1.RowCount.ToString
        '
        ' chama a função de critica do arquivo colocando cores nas linhas de acordo com a sentaça
        relacionar_cnpj()


        '
    Catch ex As Exception
            MessageBox.Show("ex.Message")

        End Try
    End Sub

' click f2 para abrir o form de pesquisa de selecionar cliente 
Private Sub txtcod_KeyUp(sender As Object, e As KeyEventArgs) Handles txtcod.KeyUp
        If e.KeyCode = Keys.F2 Then

    End If
    End Sub

    Private Sub salvar_arquivo_KeyPress(sender As Object, e As KeyPressEventArgs) Handles Me.KeyPress
        If AscW(e.KeyChar) = 27 Then  ' vbKeyReturn = Tecla Esc
            Me.Close()
        End If
    End Sub


Private Sub salvar_arquivo_Closed(sender As Object, e As EventArgs) Handles Me.Closed
    txtcod.Clear()
    txtrazao.Clear()
End Sub

' funcção de critica do arquivo - cada sentença mostra uma cor diferente no grid.
Private Sub relacionar_cnpj()
    Dim COMMAND2 As MySqlCommand
    Using sqlcoon As MySqlConnection = GetConnectionMySQL()

        Try
            Dim READER2 As MySqlDataReader


            sqlcoon.Open()

            For r As Integer = 0 To DataGridView1.Rows.Count - 1
                Dim Query As String
                Query = "select cnpjcpf_cnpjcpf,cnpjcpf_entregue,cnpjcpf_devolvido,cnpjcpf_cancelado from speed.cnpjcpf where cnpjcpf_cnpjcpf ='" & DataGridView1.Rows(r).Cells(7).Value.ToString() & "'"

                COMMAND2 = New MySqlCommand(Query, sqlcoon)
                READER2 = COMMAND2.ExecuteReader


                While READER2.Read
                    Dim cnpjcpf_cnpjcpf = READER2.GetString("cnpjcpf_cnpjcpf")
                    Dim cnpjcpf_entregue = READER2.GetString("cnpjcpf_entregue")
                    Dim cnpjcpf_devolvido = READER2.GetString("cnpjcpf_devolvido")
                    Dim cnpjcpf_cancelado = READER2.GetString("cnpjcpf_cancelado")

                    ' se for a primeira vez do registro do cnpj
                    If cnpjcpf_entregue = "N" And cnpjcpf_devolvido = "N" And cnpjcpf_cancelado = "N" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.White

                        ' se for devolvido
                    ElseIf cnpjcpf_entregue = "N" And cnpjcpf_devolvido = "S" And cnpjcpf_cancelado = "N" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.IndianRed

                        ' se for cancelado
                    ElseIf cnpjcpf_entregue = "N" And cnpjcpf_devolvido = "S" And cnpjcpf_cancelado = "S" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Silver

                        ' se for sempre entregue
                    ElseIf cnpjcpf_entregue = "S" And cnpjcpf_devolvido = "N" And cnpjcpf_cancelado = "N" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.ForestGreen
                        ' se for entregue e ja volto como devolvido (Ambos)
                    ElseIf cnpjcpf_entregue = "S" And cnpjcpf_devolvido = "S" And cnpjcpf_cancelado = "N" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Yellow
                        ' se for entregue e ja volto como devolvido e cancelado 
                    ElseIf cnpjcpf_entregue = "S" And cnpjcpf_devolvido = "S" And cnpjcpf_cancelado = "S" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Yellow
                        End If


                End While

                READER2.Close()
            Next

        Catch ex As MySqlException
            MsgBox(ex.Message)
        Finally

            sqlcoon.Close()
        End Try
    End Using
End Sub
End Class
    
asked by anonymous 13.10.2018 / 22:17

1 answer

0

You can create a unbound column on your grid and call it "IndexColor", for example, and you would assign numbers in the order that you want the colors to appear:

  • IndexCor = 1 - > Gray colored lines;
  • IndexCor = 2 - > Red lines;
  • IndexCor = 3 - > Green lines;
  • Core Index = 4 - > Yellow lines;
  • IndexCor = 5 - > White lines;

And then I would organize the grid by this column, something like this: 

DataGridView1.Rows(r).Cells("IndiceCor").Value = 1   'Linha Cinza.
DataGridView1.Rows(r).Cells("IndiceCor").Value = 2   'Linha Vermelha.
DataGridView1.Rows(r).Cells("IndiceCor").Value = 3   'Linha Verde.
DataGridView1.Rows(r).Cells("IndiceCor").Value = 4   'Linha Amarela.
DataGridView1.Rows(r).Cells("IndiceCor").Value = 5   'Linha Branca.

'[...]

DataGridView1.Sort(DataGridView1.Columns("IndiceCor"), ListSortDirection.Ascending)

The problem is that this way you could only organize by color, and could not put the CNPJ as a second option, for example. So another option would be to put this virtual column with the index of the direct color in a DataTable, for example, that has the data of your base, so you could use more than one field in the organization. p>     

13.10.2018 / 23:10
How to format cells in excel through php? Dealing with objects in JavaScript