Conversion d'un fichier Excel au format XML
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:
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 ?
