Discussion :

[DeadPooleuh]

Bonjour , je me dirige vers où pour essayer de saisir pourquoi ce bout de code ne fonctionne pas.

Débutant en Vb.net , je désire pour une application transformer un fichier excel en xml.

Après quelques recherches , hourra j'ai trouvé mon bonheur sur internet mais ... ce code ne me prends que la première feuille de mon fichier excel.

Je ne comprends pas pourquoi ...

Voici le code en question :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
Imports System.IO 
Imports System.Text.RegularExpressions 
Imports DocumentFormat.OpenXml.Packaging 
Imports DocumentFormat.OpenXml.Spreadsheet 
 
 
Public Class ConvertExcelToXml 
 
    ''' <summary> 
    '''  Read Data from selected excel file into DataTable 
    ''' </summary> 
    ''' <param name="filename">Excel File Path</param> 
    ''' <returns></returns> 
    Private Function ReadExcelFile(filename As String) As DataTable 
        ' Initialize an instance of DataTable 
        Dim dt As New DataTable() 
 
        Try 
            ' Use SpreadSheetDocument class of Open XML SDK to open excel file 
            Using spreadsheetDocument__1 As SpreadsheetDocument = SpreadsheetDocument.Open(filename, False) 
                ' Get Workbook Part of Spread Sheet Document 
                Dim workbookPart As WorkbookPart = spreadsheetDocument__1.WorkbookPart 
 
                ' Get all sheets in spread sheet document  
                Dim sheetcollection As IEnumerable(Of Sheet) = spreadsheetDocument__1.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)() 
 
                ' Get relationship Id 
                Dim relationshipId As String = sheetcollection.First().Id.Value 
 
                ' Get sheet1 Part of Spread Sheet Document 
                Dim worksheetPart As WorksheetPart = DirectCast(spreadsheetDocument__1.WorkbookPart.GetPartById(relationshipId), WorksheetPart) 
 
                ' Get Data in Excel file 
                Dim sheetData As SheetData = worksheetPart.Worksheet.Elements(Of SheetData)().First() 
                Dim rowcollection As IEnumerable(Of Row) = sheetData.Descendants(Of Row)() 
 
                If rowcollection.Count() = 0 Then 
                    Return dt 
                End If 
 
                ' Add columns 
                For Each cell As Cell In rowcollection.ElementAt(0) 
                    dt.Columns.Add(GetValueOfCell(spreadsheetDocument__1, cell)) 
                Next 
 
                ' Add rows into DataTable 
                For Each row As Row In rowcollection 
                    Dim temprow As DataRow = dt.NewRow() 
                    Dim columnIndex As Integer = 0 
                    For Each cell As Cell In row.Descendants(Of Cell)() 
                        ' Get Cell Column Index 
                        Dim cellColumnIndex As Integer = GetColumnIndex(GetColumnName(cell.CellReference)) 
 
                        If columnIndex < cellColumnIndex Then 
                            Do 
                                temprow(columnIndex) = String.Empty 
                                columnIndex += 1 
 
                            Loop While columnIndex < cellColumnIndex 
                        End If 
 
                        temprow(columnIndex) = GetValueOfCell(spreadsheetDocument__1, cell) 
                        columnIndex += 1 
                    Next 
 
                    ' Add the row to DataTable 
                    ' the rows include header row 
                    dt.Rows.Add(temprow) 
                Next 
            End Using 
 
            ' Here remove header row 
            dt.Rows.RemoveAt(0) 
            Return dt 
        Catch ex As IOException 
            Throw New IOException(ex.Message) 
        End Try 
    End Function 
 
    ''' <summary> 
    '''  Get Value of Cell  
    ''' </summary> 
    ''' <param name="spreadsheetdocument">SpreadSheet Document Object</param> 
    ''' <param name="cell">Cell Object</param> 
    ''' <returns>The Value in Cell</returns> 
    Private Shared Function GetValueOfCell(spreadsheetdocument As SpreadsheetDocument, cell As Cell) As String 
        ' Get value in Cell 
        Dim sharedString As SharedStringTablePart = spreadsheetdocument.WorkbookPart.SharedStringTablePart 
        If cell.CellValue Is Nothing Then 
            Return String.Empty 
        End If 
 
        Dim cellValue As String = cell.CellValue.InnerText 
 
        ' The condition that the Cell DataType is SharedString 
        If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then 
            Return sharedString.SharedStringTable.ChildElements(Integer.Parse(cellValue)).InnerText 
        Else 
            Return cellValue 
        End If 
    End Function 
 
    ''' <summary> 
    ''' Get Column Name From given cell name 
    ''' </summary> 
    ''' <param name="cellReference">Cell Name(For example,A1)</param> 
    ''' <returns>Column Name(For example, A)</returns> 
    Private Function GetColumnName(cellReference As String) As String 
        ' Create a regular expression to match the column name of cell 
        Dim regex As New Regex("[A-Za-z]+") 
        Dim match As Match = regex.Match(cellReference) 
        Return match.Value 
    End Function 
 
    ''' <summary> 
    ''' Get Index of Column from given column name 
    ''' </summary> 
    ''' <param name="columnName">Column Name(For Example,A or AA)</param> 
    ''' <returns>Column Index</returns> 
    Private Function GetColumnIndex(columnName As String) As Integer 
        Dim columnIndex As Integer = 0 
        Dim factor As Integer = 1 
 
        ' From right to left 
        For position As Integer = columnName.Length - 1 To 0 Step -1 
            ' For letters 
            If [Char].IsLetter(columnName(position)) Then 
                columnIndex += factor * ((AscW(columnName(position)) - AscW("A"c)) + 1) - 1 
 
                factor *= 26 
            End If 
        Next 
 
        Return columnIndex 
    End Function 
 
    ''' <summary> 
    ''' Convert DataTable to Xml format 
    ''' </summary> 
    ''' <param name="filename">Excel File Path</param> 
    ''' <returns>Xml format string</returns> 
    Public Function GetXML(filename As String) As String 
        Using ds As New DataSet() 
            ds.Tables.Add(Me.ReadExcelFile(filename)) 
            Return ds.GetXml() 
        End Using 
    End Function 
 
End Class

Je pense que ça bloque au niveau du remplissage du Data , il ne prends en compte que la première feuille de donnée de mon fichier Excel , peut-être il manque une boucle ?

[DotNetMatt]

A vue de nez comme ca je pense que tu peux boucler sur ta variable sheetcollection (ligne 25 du code ci-dessus). Il faudra aussi revoir le fonctionnement pour récupérer le relationshipId (ligne 28) car là tu récupères systématiquement celui de la première feuille.

[DeadPooleuh]

Problème résolu !

Je suis parti de zéro et je partage ma solution au cas où quelqu'un en aurait besoin
C'est pas du code d'exception mais sa peut toujours servir pour un nouveau.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic
Imports System.IO
Imports Microsoft.Office.Interop.Excel
Imports System.Windows.Forms
 
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
 
        Dim xlApp As New Microsoft.Office.Interop.Excel.Application
        Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
        Dim xlsh As Microsoft.Office.Interop.Excel.Worksheet
        Dim i As Integer = 1
 
        xlWb = xlApp.Workbooks.Open("X:\G022596\My Documents\Alternance Schmitt\Contenu table v280115.xls.xlsx")
        Dim ds As New DataSet
 
        Dim cnExcel As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=X:\G022596\My     Documents\Alternance Schmitt\Contenu table v280115.xls.xlsx;Extended Properties=""Excel 12.0;HDR=YES;""")
        cnExcel.Open()
 
        Do While i <= xlWb.Worksheets.Count
            xlsh = xlWb.Worksheets(i)
            'On ajoute chaque feuille dans le DataSet 
            Dim da As New OleDbDataAdapter("SELECT * From [" & xlsh.Name & "$]", cnExcel)
            da.Fill(ds)
            i = i + 1
        Loop
        cnExcel.Close()
        xlWb.Close()
        xlApp.Quit()
 
        ds.WriteXml("X:\G022596\My Documents\Alternance Schmitt\test.xml")
        MsgBox("Ok")
 
    End Sub