XML is a platform independent language, so the information formatted in XML can be used in any other platforms (Operating Systems). XML is a self describing language and it gives the data as well as the rules to identify what the data it contains.
Here we are going to read from an XML file content and write the same content to an Excel file. Using an XmlReader for read the XML file to the Dataset . Loop through the Dataset and add the content to the Excel file . For create an excel file you have to add reference of Excel library to you project .
Imports System.Xml
Imports System.Data
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim ds As New DataSet
Dim xmlFile As XmlReader
Dim i, j As Integer
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
xmlFile = XmlReader.Create("Product.xml", New XmlReaderSettings())
ds.ReadXml(xmlFile)
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlWorkSheet.Cells(i + 1, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next
xlWorkSheet.SaveAs("xml2excel.xlsx")
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class