DATASET, LISTVIEW ET EXCEL

'============================================================
'Ce code permet de remplir un list view à partir d'un dataset
'============================================================  
Public Sub RemplirListView(ByVal unDataSet As DataSet, ByVal unListView As ListView)
        Dim i As Integer = 0
        Dim myTable As DataTable
        Dim myRow As DataRow
        Dim myColumn As DataColumn
        Dim uneChaine As String
        Dim j As Integer
        REM VIDER LE LIST VIEW AVANT REMPLISSAGE DE NOUVEAU ELEMENT
        unListView.Clear()
        REM DEFINITION DES COLONNES DU LIST VIEW QUI CORRESPONDENT AUX COLONNES DE LA TABLE
        For Each myTable In unDataSet.Tables
            For Each myColumn In myTable.Columns
                unListView.Columns.Add(myTable.Columns(i).ColumnName, 60, HorizontalAlignment.Left)
                i = i + 1
            Next
        Next
        j = 0
        REM ECRITURE DES LIGNES DANS LE LIST VIEW
        For Each myTable In unDataSet.Tables
            For Each myRow In myTable.Rows
                i = 1
                Dim LVI As New ListViewItem
                For Each myColumn In myTable.Columns
                    If i = 1 Then
                        LVI.Text = CType(myRow(myColumn) & "", String)
                    Else
                        uneChaine = CType(myRow(myColumn) & "", String)
                        LVI.SubItems.Add(uneChaine)
                    End If
                    i += 1
                Next myColumn
                REM PERMET D'ALTERNER LES COULEURS DES LIGNES (C BIEN DE FAIRE UN PEU DE DESIGN)
                If (j Mod 2) = 0 Then
                    unListView.Items.Add(LVI).BackColor = Color.White
                Else
                    unListView.Items.Add(LVI).BackColor = Color.AliceBlue
                End If
                j += 1
            Next myRow
        Next myTable
        unListView.View = View.Details
        unListView.AutoArrange = True
    End Sub
'============================================================
'Et ce petit bout permet de générer un dataset
'============================================================
    Public Function GenererDataSet(ByVal uneRequete As String) As DataSet
        Dim uneConnexion As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" _
            + OFDialogExplorer.FileName)
        Dim dsPaiements As New DataSet
        If Not unDs Is Nothing Then
            unDs.Clear()
        End If
        Try
            Dim sqlCmdPaiements As String = uneRequete
            uneConnexion.Open()
            Dim oledbDA As New OleDbDataAdapter(sqlCmdPaiements, uneConnexion)
            GenererDataSet = New DataSet
            oledbDA.Fill(dsPaiements)
            oledbDA.Dispose()
            uneConnexion.Close()
            uneConnexion = Nothing
            unDs = dsPaiements
        Catch Sqlex As OleDbException
            MessageBox.Show(Sqlex.Message, "Erreur SQL")
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Erreur...")
        End Try
        Return dsPaiements
    End Function

Télécharger le zip