Hello, I am working on my college assignment and I have been assigned to Convert an xml file into and excel file using vb.net.. I followed a code i got on internet but I am facing an error while running it and not during its compilation .. I am pretty much
new to vb.net , so i would be pretty grateful if any1 could help me with it..
Basically I need to import xml file into memory using filedialog and convert it into excel sheet and make a button to save the excel sheet.. ( I tried using the datagrid way, but my xml consists of multiple child tables being inherited by parent tables.. hence failed doing it that way)
Can anybody help me? I would really like it if I could get the solution or any help soon, I have 3 days left to submit my assignment!
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.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.Application
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
xmlFile = XmlReader.Create("e:\product.xml", New XmlReaderSettings())
ds.ReadXml(xmlFile, XmlReadMode.IgnoreSchema)
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("ConvertedExcel.xlsx")
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
MsgBox("Excel file created , you can find the file in the Application Directory")
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