[.Net] Importer une ListView vers Excel

Imports Microsoft.Office.Interop

Imports Microsoft.Office.Interop.Excel

'L’ajout des références au DDL se fait comme ci-dessous :

1-Aller au menu Projet et lancer Propriétés projet
2-Dans la fenêtre affichée cliquer sur Références
3-Cliquer sur le bouton Ajouter
4-Aller sur l’onglet COM et rajouter les DLL pour Excel.

 

   Public Sub ListviewToExcel(ByRef p_ListView As ListView, ByRef p_SaveFileDialog As SaveFileDialog, Optional ByVal p_NombreColonne As Integer = 0)

       Dim numLig As Integer

       Dim NumCol As Integer

       Dim NumColExcel As Integer

       Dim NombreColonneListView As Integer

       p_SaveFileDialog.Title = "Save Excel File"

       p_SaveFileDialog.Filter = "Excel files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xslx"

       p_SaveFileDialog.ShowDialog()

       If p_SaveFileDialog.FileName = "" Then

           Exit Sub

       End If

       'créer les objects Excel

       Dim xls As New Excel.Application

       Dim book As Excel.Workbook

       Dim sheet As Excel.Worksheet

       'créer workbook et lire reference du premier worksheet

       xls.Workbooks.Add()

       book = xls.ActiveWorkbook

       sheet = book.ActiveSheet

       NumCol = 0

       If p_NombreColonne <= 0 Then

           NombreColonneListView = p_ListView.Columns.Count - 1

       Else

           NombreColonneListView = p_NombreColonne - 1

       End If

       'importer les titres des colonnes listview

       NumColExcel = 1

       For NumCol = 0 To NombreColonneListView

           sheet.Cells(1, NumColExcel) = p_ListView.Columns(NumCol).Text.ToString

           NumColExcel = NumColExcel + 1

       Next

       'importer le contenu de la listview

       NumCol = 1

       numLig = 2

       For Each item As ListViewItem In p_ListView.Items

           For i As Integer = 0 To NombreColonneListView

               sheet.Cells(numLig, NumCol) = item.SubItems(i).Text

               NumCol = NumCol + 1

           Next

           numLig += 1

           NumCol = 1

       Next

       'sauvegarder le fichier Excel

       If p_SaveFileDialog.FileName <> "" And p_SaveFileDialog.FileName <> vbNullString Then

           book.SaveAs(p_SaveFileDialog.FileName)

       End If

       xls.Workbooks.Close()

       xls.Quit()

       releaseObject(sheet)

       releaseObject(book)

       releaseObject(xls)

   End Sub

   Private Sub releaseObject(ByVal obj As Object)

       'Release an automation object

       Try

           System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

           obj = Nothing

       Catch ex As Exception

           obj = Nothing

       Finally

           GC.Collect()

       End Try

   End Sub