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 ?