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 ?