[.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