vb.net et expresse 2010

exemple d'écriture dans fichier excel

Imports System.IO
  Imports excel = Microsoft.Office.Interop.Excel
Public Class Form1
 
  Public Class chanteuretchanson
        Public name As String
        Public chanson As New List(Of String)
    End Class
 
    Public listchanteurs As New List(Of chanteuretchanson)
 
   Private Sub BuildListchanteurs(ByVal pathfile As String)
        Dim artist As String
        Dim singersongs As New chanteuretchanson
        artist = String.Empty
        Try
            For Each foundFile As String In My.Computer.FileSystem.GetFiles(pathfile, Microsoft.VisualBasic.FileIO.SearchOption.SearchAllSubDirectories, "*.*")
 
                Dim parts As String()
                parts = foundFile.Split("-"c)
                If IO.Path.GetFileNameWithoutExtension(parts(0)) <> artist Then
                    If singersongs.chanson.Count > 0 Then
                        listchanteurs.Add(singersongs)
                    End If
                    singersongs = New chanteuretchanson
 
                    artist = IO.Path.GetFileNameWithoutExtension(parts(0))
                    singersongs.name = artist
                End If
                singersongs.chanson.Add(IO.Path.GetFileNameWithoutExtension(parts(1)))
            Next
            listchanteurs.Add(singersongs)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
 
    Private Sub WriteToExcel()
        Dim objexcel As New excel.Application
        Dim xlBook As excel.Workbook
        Dim xlworksheet As excel.Worksheet
        Dim numbersrows As Integer
        Dim nextrow As Integer
        Dim letter As Char
        Dim counterletter As Integer
        Dim firstrow As Integer
        Dim r As excel.Range
        Try
            xlBook = objexcel.Workbooks.Add
            xlworksheet = CType(xlBook.ActiveSheet, excel.Worksheet)
 
            numbersrows = 1
            firstrow = 1
            counterletter = 0
 
            letter = "A"c
            With xlworksheet
                'numbers of used rows  in worksheet
 
                For iter = 0 To listchanteurs.Count - 1
 
                    .Range(letter & (numbersrows).ToString).Value = listchanteurs(iter).name
                    For iter2 = 0 To listchanteurs(iter).chanson.Count - 1
                        numbersrows += 1
                        r = CType(xlworksheet.Rows(numbersrows), excel.Range)
                        If r.PageBreak = excel.XlPageBreak.xlPageBreakAutomatic Then
                            nextrow = numbersrows
                            numbersrows = firstrow
                            'pass to the next column
                            counterletter += 1
                            letter = Convert.ToChar(Convert.ToInt32("A"c) + counterletter)
                            If letter = "D" Then
                                firstrow = nextrow
                                numbersrows = nextrow
                                counterletter = 0
                                letter = "A"c
                            End If
                        End If
                        .Range(letter & (numbersrows).ToString).Value = listchanteurs(iter).chanson(iter2)
                    Next
                    If CType(xlworksheet.Rows(numbersrows + 2), excel.Range).PageBreak = excel.XlPageBreak.xlPageBreakAutomatic Or CType(xlworksheet.Rows(numbersrows + 1), excel.Range).PageBreak = excel.XlPageBreak.xlPageBreakAutomatic Then
                        numbersrows = firstrow
                        nextrow = numbersrows
                        'pass to the next column
                        counterletter += 1
                        letter = Convert.ToChar(Convert.ToInt32("A"c) + counterletter)
                        If letter = "D" Then
                            firstrow = nextrow
                            numbersrows = nextrow
                            counterletter = 0
                            letter = "A"c
                        End If
                    Else
                        numbersrows += 2
                    End If
                Next
            End With
            objexcel.Visible = True
            objexcel = Nothing
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
 
    End Sub
 
   'appelle les   BuildListchanteurs et WriteToExcel  où  tu as  besoin
   ' important  BuildListchanteurs doit etre appellé en premier 
    'ex dans le load event
     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
       BuildListchanteurs(TbCheminFichiers.Text & "\")
        WriteToExcel()
  end sub 
 
End Class