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 :
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 ?
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
Partager