How to create excel sheet from xml file using vb.net

Question

First time doing this, like I said in the title I want to import an xml file in excel. I have a pdf form containing 6 rows of radiobuttons and 1 text field(comments) on submit it gives me this xml file:

<?xml version="1.0" encoding="UTF-8"?>
<form1
><Table1
><HeaderRow xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/" xfa:dataNode="dataGroup"
/><Row1 xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/" xfa:dataNode="dataGroup"
/><Row2 xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/" xfa:dataNode="dataGroup"
/><Row3 xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/" xfa:dataNode="dataGroup"
/><Row4 xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/" xfa:dataNode="dataGroup"
/><Row5 xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/" xfa:dataNode="dataGroup"
/></Table1
><Table2
><Row1 xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/" xfa:dataNode="dataGroup"
/></Table2
><Table3
><Row1 xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/" xfa:dataNode="dataGroup"
/><Row1 xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/" xfa:dataNode="dataGroup"
/></Table3
><QualityWork
>1</QualityWork
><Ontime
>2</Ontime
><QualityReport
>3</QualityReport
><Needs
>4</Needs
><Comm
>5</Comm
><Global
>6</Global
><Comments
>This is a comment</Comments
></form1
>

I'm trying to create a vb.net program that will create an excel sheet with the data from the xml file.

I tried using this vb.net code

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim filePath = create_xml.Text()

    Dim m_xmld As XmlDocument
    'Create the XML Reader
    m_xmld = New XmlDocument()
    m_xmld.Load(filePath)

    Dim m_nodelist As XmlNodeList
    Dim m_node As XmlNode

    m_nodelist = m_xmld.SelectNodes("/form1")


    Dim total_untokenized = ""

    For Each m_node In m_nodelist

        total_untokenized = m_node.ChildNodes.Item(0).InnerText
        comments = m_node.ChildNodes.Item(1).InnerText
    Next

    Dim total_tokenized As Integer
    total_tokenized = 0

    For i = 1 To 6
        total_tokenized = total_tokenized + Strings.Mid(total_untokenized, i, 1)
    Next


    Dim total_col = 7

    Dim MyArrayList = New ArrayList(total_col)

    MyArrayList.Add(comments)
    For i = 1 To 6
        MyArrayList.Add(Strings.Mid(total_untokenized, i, 1))
    Next
    MyArrayList.Add(total_tokenized)


    Dim MyArrayList2 = New ArrayList(total_col)


    MyArrayList2.Add("QualityWork")
    MyArrayList2.Add("Ontime")
    MyArrayList2.Add("QualityReport")
    MyArrayList2.Add("Needs")
    MyArrayList2.Add("Comm")
    MyArrayList2.Add("Global")
    MyArrayList2.Add("Comments")


    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
    oBook = oExcel.Workbooks.Add
    oSheet = oBook.Worksheets(1)

    For col = 0 To total_col - 1
        oSheet.Cells(1, col + 1) = MyArrayList2.Item(col)
    Next

    oSheet.Rows("1:1").Font.Bold = True
    For col = 0 To total_col - 1
        oSheet.Cells(2, col + 1) = MyArrayList.Item(col)
    Next
 End Sub

But this m_nodelist seems to stay empty, I always get an error at this line total_tokenized = total_tokenized + Strings.Mid(total_untokenized, i, 1)

EDIT1: I've post how I fixed it below

what part of the xml do you want to retrieve? its not clear what you're trying to do with this tokenized code. — Ezi
– Ezi, Commented Apr 20, 2015 at 14:42

Ezi · Accepted Answer · 2015-04-20 15:14:08Z

1

This is one way of doing it...

   Dim m_xmld As XmlDocument
    'Create the XML Reader
    m_xmld = New XmlDocument()
    m_xmld.Load(filePath)

    Dim QualityWork = m_xmld.GetElementsByTagName("QualityWork").Item(0).InnerText
    Dim Ontime = m_xmld.GetElementsByTagName("Ontime").Item(0).InnerText
    Dim QualityReport = m_xmld.GetElementsByTagName("QualityReport").Item(0).InnerText
    Dim Needs = m_xmld.GetElementsByTagName("Needs").Item(0).InnerText
    Dim Comm = m_xmld.GetElementsByTagName("Comm").Item(0).InnerText
    Dim Glob = m_xmld.GetElementsByTagName("Global").Item(0).InnerText
    Dim Comments = m_xmld.GetElementsByTagName("Comments").Item(0).InnerText

answered Apr 20, 2015 at 15:14

Ezi

2,2308 gold badges34 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

phil652 Over a year ago

I was able to fix it myself. See answer below. Thank you for trying

phil652 Over a year ago

I gave you +1 for trying!

Fjodr · Accepted Answer · 2015-04-20 15:09:41Z

0

Why wouldn't you use this simple method:

 Workbooks.OpenXML

see here

answered Apr 20, 2015 at 15:09

Fjodr

1,02314 silver badges32 bronze badges

3 Comments

phil652 Over a year ago

It's hard for me to understand how to implement this in my code since there is no example

Fjodr Over a year ago

You can see the link for more details, but I agree that choosing this solution would ask many modification, but is more simple once done.

phil652 Over a year ago

I was able to fix it myself. See answer below.

phil652 · Accepted Answer · 2015-04-20 15:46:29Z

I was able to fix it by changing my vb.net like this. By looking at the other answers I know there must be easier ways to do this but since I'm a beginner I did not want to restart from the beginning and get stuck somewhere else. I was able to modify the code I already had.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    If create_xml.Text() = "" Then
        MsgBox("You have not specified an XML file!")
        Exit Sub
    End If
    Dim filePath = create_xml.Text()
    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
    Dim totalCol As Integer = 7
    Dim MyArrayList2 = New ArrayList(totalCol)

    MyArrayList2.Add("QualityWork")
    MyArrayList2.Add("Ontime")
    MyArrayList2.Add("QualityReport")
    MyArrayList2.Add("Needs")
    MyArrayList2.Add("Comm")
    MyArrayList2.Add("Global")
    MyArrayList2.Add("Comments")

    xlApp = New Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")

    xmlFile = XmlReader.Create(filePath, New XmlReaderSettings())
    ds.ReadXml(xmlFile)

    For i = 0 To MyArrayList2.Count - 1


        xlWorkSheet.Cells(1, i + 1) = MyArrayList2.Item(i)
    Next



    For i = 0 To ds.Tables(0).Rows.Count - 1
        For j = 1 To ds.Tables(0).Columns.Count - 1
            xlWorkSheet.Cells(2, j) = _
            ds.Tables(0).Rows(i).Item(j)
        Next
    Next
    ' Fix first row
    xlWorkSheet.Activate()
    xlWorkSheet.Application.ActiveWindow.SplitRow = 1
    xlWorkSheet.Application.ActiveWindow.FreezePanes = True
    ' Now apply autofilter
    Dim firstRow As Excel.Range = xlWorkSheet.Rows(1)
    firstRow.AutoFilter(1,
               Type.Missing,
               Excel.XlAutoFilterOperator.xlAnd,
               Type.Missing,
               True)


    xlWorkSheet.SaveAs("F:\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

Collectives™ on Stack Overflow

How to create excel sheet from xml file using vb.net

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related