Datagridview vers excel

Version imprimable

Bonjour,

Aprés avoir tester le code fournit dans la FAQ pour exporter des données d'un datagridview vers Excel, j'obtient un message d'erreur sur :
'

Code:

1
2
3
4
pour chaque colonne et chaque ligne on transfert les données      
            For y = 0 To nbrLigne
                xlSheet.Cells(y + 2, x + 1) = DataGridView1.Item(y, x)
            Next.

voici le code complet :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Private Sub bt_export_total_antenne_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_export_total_antenne.Click
        Dim mycommand As New OleDbCommand("SELECT COUNT(*) as NOMBRE, antenne AS ANTENNES FROM T_intervention GROUP BY antenne", ObjetConnection)
        Dim MonDataTable As DataTable
        Dim MonDataAdapter As OleDbDataAdapter
        Dim MonDataSet As New DataSet
        MonDataAdapter = New OleDbDataAdapter(mycommand)
        MonDataAdapter.Fill(MonDataSet, "t_intervention")
        MonDataTable = MonDataSet.Tables("t_intervention")
 
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
 
        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
 
        ' ici on compte le nombre de lignes et de colonnes du datatable
        Dim nbrLigne As Integer = MonDataSet.Tables("t_intervention").Rows.Count - 1
        Dim nbrColon As Integer = MonDataSet.Tables("t_intervention").Columns.Count - 1
        Dim x, y As Integer
 
        For x = 0 To nbrColon
            ' ici on prends le titre des colonnes du datatable
            xlSheet.Cells(1, x + 1) = MonDataSet.Tables("t_intervention").Columns(x).ColumnName
            ' on mets la première ligne en gras
            xlSheet.Rows(1).Font.Bold = True
 
            ' pour chaque colonne et chaque ligne on transfert les données      
            For y = 0 To nbrLigne
                xlSheet.Cells(y + 2, x + 1) = DataGridView1.Item(y, x)
            Next
        Next
 
        ' ici on affiche les résultat dans excel
        xlSheet.Application.Visible = True
        ' on peut sauvegarder notre document sur le disque
        xlSheet.SaveAs("C:\nom_document.xls")
        ' on quitte l'application et on détruit les objets
        xlApp.Quit()
        xlSheet = Nothing
        xlBook = Nothing
        xlApp = Nothing
        MonDataAdapter = Nothing
        MonDataSet = Nothing
        MonDataTable = Nothing
 
    End Sub

Message d'erreur :

Citation:

L'exception argumentoutofrangeexception n'a pas été géré
L'index était hors limites. Il ne doit pas être négatif et doit être inférieur à la taille de la collection. Nom du paramètre : index

Quelqu'un peut aider svp Merci

15/06/2009, 10h58
yann_DZ
Bonjour
Les indices des datagridviews sont inversés.
Colonnes d'abord et lignes ensuite.

Essaye donc avec ceci (inversion x et y) :
Code:

1 2 3 4 For y = 0 To nbrLigne xlSheet.Cells(y + 2, x + 1) = DataGridView1.Item(x, y) Next

J'ai résolu le problème en corrigeant le code:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 Private Sub bt_export_total_antenne_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_export_total_antenne.Click
        'verfying the datagridview having data or not
        If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
            MessageBox.Show("Aucunes données à exporter !", "Erreur !", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
 
        'Creating dataset to export
        Dim dset As New DataSet
        Dim dset2 As New DataSet
        'add table to dataset
        dset.Tables.Add()
        dset2.Tables.Add()
        'add column to that table
        For i As Integer = 0 To DataGridView1.ColumnCount - 1
            dset.Tables(0).Columns.Add()
        Next
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To DataGridView1.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                dr1(j) = DataGridView1.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next
        For i As Integer = 0 To DataGridView2.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DataGridView2.Columns.Count - 1
                dr1(j) = DataGridView2.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next
        For i As Integer = 0 To DataGridView3.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DataGridView3.Columns.Count - 1
                dr1(j) = DataGridView3.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next
        For i As Integer = 0 To DataGridView4.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DataGridView4.Columns.Count - 1
                dr1(j) = DataGridView4.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next
 
        dr1 = dset.Tables(0).NewRow
        dset.Tables(0).Rows.Add(l_nb_inter_par_periode.Text)
        dr1 = dset.Tables(0).NewRow
        dset.Tables(0).Rows.Add(l_nb_inter_tel.Text)
        dr1 = dset.Tables(0).NewRow
        dset.Tables(0).Rows.Add(l_nb_inter_mail.Text)
        dr1 = dset.Tables(0).NewRow
        dset.Tables(0).Rows.Add(l_nb_inter_autre.Text)
        dr1 = dset.Tables(0).NewRow
        dset.Tables(0).Rows.Add(l_nb_dep_effectue.Text)
        dr1 = dset.Tables(0).NewRow
        dset.Tables(0).Rows.Add(l_nb_resolu_distance.Text)
 
        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
 
        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()
 
        Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0
 
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(1, colIndex) = dc.ColumnName
        Next
        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
            Next
        Next
        wSheet.Columns.AutoFit()
        Dim strFileName As String = "C:\export.xls"
        Dim blnFileOpen As Boolean = False
        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
        End Try
 
        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If
 
        wBook.SaveAs(strFileName)
        excel.Workbooks.Open(strFileName)
        excel.Visible = True
    End Sub