0

I am completely new with XML operations in VB. I am very much confused with child nodes, attributes, XElements. I tried few ways using XElements it is showing empty values, am not successful. Please help me in this. :(

I have this sample code in ".arxml" file.
I need to iterate the CLUSTER and get the SHORT-NAME (Note - In this there is only one cluster, but it might vary).

Under the one Cluster's "SCHEDULE-TABLES, there will be CON-SCHEDULE-TABLE. For each CON-SCHEDULE-TABLE I need SHORT-NAME, DELAY, TRIGGER values which are under table-entrys > application-entrys.

I need respective CON-SCHEDULE-TABLE values to be written in one row of Excel.

I need to write the details which is read from XML to Excel sheet as below which will contain the header as

Context

Sample Excel rows:

------------------------------------------------------------------------------------
TEST-ID | CLUSTER-SHORT-NAME | CON-SCHEDULE-TABLE-SHORT-NAME  | DELAY  |  TRIGGER 
------------------------------------------------------------------------------------
 1      |  LIN-1             |     DIAG1                      |  0.01  |  M_LIN
                                                              |  0.11  |  M_LIN_1

 2      |                    |     RUN_MAIN                   |  0.01  |  M_LIN_01

------------------------------------------------------------------------------------

   <?XML version = '1.0’ encoding='UTF-8'?>
<AUTOSAR xmlns=http://autosar.org>
   <AR-PACKAGES>
     <AR-PACKAGE>
       <SHORT-NAME>System</SHORT-NAME>
      <AR-PACKAGES>
         <AR-PACKAGE>
            <CLUSTER>
                <SHORT-NAME>LIN-1</SHORT-NAME>
                <LIN-CLUSTER-VARIANTS>
                    <LIN-CLUSTER-CONDITIONAL> 
                       <BAUDRATE>19200</BAUDRATE>
                       <PHYSICAL-CHANNELS>
                          <LIN-PHYSICAL-CHANNEL>
                             <SHORT-NAME>M_LIN_9</SHORT-NAME>
                             <ADMIN-DATA/>
                             <FRAME-TRIGGERINGS>
                                <FRAME-TRIGGERING>
                                    <SHORT-NAME>M_LIN_01</SHORT-NAME>
                                    <IDENTIFIER>60</IDENTIFIER>
                                    <CHECKSUM>ENHANCED</CHECKSUM>
                                </FRAME-TRIGGERING>
                                <FRAME-TRIGGERING>
                                    <SHORT-NAME>M_LIN_01</SHORT-NAME>
                                    <IDENTIFIER>60</IDENTIFIER>
                                    <CHECKSUM>ENHANCED</CHECKSUM>
                                </FRAME-TRIGGERING>
                             </FRAME-TRIGGERINGS>
                             <SCHEDULE-TABLES>
                                <CON-SCHEDULE-TABLE>
                                    <SHORT-NAME>DIAG1</SHORT-NAME>
                                    <RESUME-POSITION>START-FROM-BEGINNING</RESUME-POSITION>
                                   <TABLE-ENTRYS>
                                      <APPLICATION-ENTRY>
                                           <DELAY>0.01</DELAY>
                                           <TRIGGER>M_LIN</TRIGGER>
                                     </APPLICATION-ENTRY>
                                    <APPLICATION-ENTRY>
                                        <DELAY>0.11</DELAY>
                                        <TRIGGER>M_LIN_1</TRIGGER>
                                   </APPLICATION-ENTRY>
                             </CON-SCHEDULE-TABLE>
                           <CON-SCHEDULE-TABLE>
                              <SHORT-NAME>RUN_MAIN</SHORT-NAME>
                             <RESUME-POSITION>START-FROM-BEGINNING</RESUME-POSITION>
                             <TABLE-ENTRYS>
                                  <APPLICATION-ENTRY>
                                      <DELAY>0.01</DELAY>
                                     <TRIGGER>M_LIN_01</TRIGGER>
                                  </APPLICATION-ENTRY>
                                 <APPLICATION-ENTRY>
                                     <DELAY>0.01</DELAY>
                                    <TRIGGER>M_LIN_02</TRIGGER>
                                 </APPLICATION-ENTRY>
                             </TABLE-ENTRYS>
                        </CON-SCHEDULE-TABLE>
                        <CON-SCHEDULE-TABLE>
                             <SHORT-NAME>RUN_MAIN</SHORT-NAME>
                            <RESUME-POSITION>START-FROM-BEGINNING</RESUME-POSITION>
                           <TABLE-ENTRYS>
                               <ASSIGN-FRAME-ID-RANGE>
                               <DELAY>0.01</DELAY>
                               <ASSIGNED-SLAVE-CONFIG>SAMPLE</ASSIGNED-SLAVE-CONFIG>
                               <FRAME-IDS>
                                  <FRAME-ID>
                                     <INDEX>0</INDEX>
                                  </FRAME-ID>
                                    <FRAME-ID>
                                     <INDEX>0</INDEX>
                                  </FRAME-ID>
                               </FRAME-IDS>
                            </ASSIGN-FRAME-ID-RANGE>
                            <ASSIGN-FRAME-ID>
                                <DELAY>0.01</DELAY>                       
                                <TRIGGER>M_LIN_03</TRIGGER>
                            </ASSIGN-FRAME-ID>
                            <ASSIGN-FRAME-ID>
                                <DELAY>0.01</DELAY>                       
                                <TRIGGER>M_LIN_03</TRIGGER>
                            </ASSIGN-FRAME-ID>
                          </TABLE-ENTRYS>
                       </CON-SCHEDULE-TABLE>
                       <CON-SCHEDULE-TABLE>
                         <SHORT-NAME>RUNEVENT</SHORT-NAME>
                         <RESUME-POSITION>START-FROM-BEGINNING</RESUME-POSITION>
                         <TABLE-ENTRYS>
                            <APPLICATION-ENTRY>
                               <DELAY>0.02</DELAY>
                               <TRIGGER>M_LIN</TRIGGER>
                            </APPLICATION-ENTRY>
                         </TABLE-ENTRYS>
                       </CON-SCHEDULE-TABLE>
                     </SCHEDULE-TABLES>                    
      </LIN-PHYSICAL-CHANNEL>


                      </PHYSICAL-CHANNELS>
                    </LIN-CLUSTER-CONDITIONAL>


                </LIN-CLUSTER-VARIANTS>

         </AR-PACKAGE>
     </AR-PACKAGES>
     </AR-PACKAGE>


   </AR-PACKAGES>

Please find the script which i have tried for the logic

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Imports System.Text.RegularExpressions
Imports System.IO
Imports System.Xml
Imports System.Linq
Imports System.Xml.Linq
Imports System.Collections.Generic

Public Class frm_HexGen
    Private Sub Button1_Click1(Sender As Object, e As EventArgs) Handles Button1.Click
        Dim str_File_Name = “Schedule.xlsx”
        Dim str_File_Template As String
        Dim xlApp As Excel.Application
        Dim xlWB As Excel.Workbook
        Dim xlSH As Excel.WorkSheet
        Dim myRange As Excel.Range

        Dim Current_Directory As String

        Current_Directory = CurDir()

        str_File_Template = Current_Directory&”\_log”&str_File_Name

        xlWB=xlApp.Workbooks.Open(str_File_Template)
        xlApp.Visible=True
        xlApp.ActiveWindow.WindowState=XlWindowState.xlMaximized

        xlSH=xlWB.Worksheets(“Sheet1”)
        xlSH.Activate

        If NOT My.Computet.FileSystem.FileExists(str_File_Template) Then
            MsgBox(“error”)
        End If

        Dim iRow As Long = 2

        Dim bOK As Boolean
        Dim OXMLDOC As Object=Nothing

        oXMLDOC=CreateObject(“Microsoft.XMLDOM”)
        bOK=oXMLDOC.Load(“board.xml”)

        If not bOK Then
            MsgBox(“failed to load”)
        End If

        Dim nodes1,nodes2,nodes3,nodes4,nodes5
        Dim nodes1,node2,node3,node4,node5

        Dim str_Frame_Trigger As String = “”
        Dim str_Frame_Trigger_Name As String()
        Dim str_Trigger_Name As String
        Dim str_Length As Integer
        Dim str_Frame_Name As String
        Dim str_Identifier As Integer

        nodes1=oXMLDOC.SelectNkdes(“//CLUSTER”)

        For Each node1 In nodes1
            For Each node2 In node1.ChildNodes
                If node2.tagName =”SHORT-NAME” Then
                    iRow = iRow+1
                    xlSH.Cells(iRow,2)=node2.Text
                ElseIf node2.tagName = “LIN-CLUSTER-VARIANTS”
                    nodes3 = node2.SelectNodes(“//LIN-CLUSTER-VARIANTS/LIN-CLUSTER-CONDITIONAL/PHYSICAL-CHANNELS/LIN-PHYSICAL-CHANNEL/SCHEDULE-TABLES/SCHEDULE-TABLE/*)
                    For Each node3 in nodes
                        If node3.tagName=”SHORT-NAME” Then
                        testID = testID+1
                        xlSH.Cells(iRow,1)= testID
                        xlSH.Cells(iRow,3)= node3.text
                ElseIf node3.tagName=”Table-Entrys” Then
                    node4=node3.SectNodes(“(APPLICATION-ENTRY|ASSIGN-FRAME-ID|ASSIGN-FRAME-ID-RANGE)/*)
                    For Each node4 In nodes4
                        If node4.tagName = “DELAY” Then
                        If not Is nothing(xlSH.Cells(iRow,4) Then iRow=iRow+1
                        xlSH.Cells(iRow,4)=node4.text
                ElseIf node4.tagName = “TRIGGER” Then
                If not Is nothing(xlSh.Cells(iRow,5) Then iRow=iRow+1
                str_Frame_Trigger= node4.Text

                nodes5=node2.SelectNode(“//LIN-CLUSTER-VARIANTS/LIN-CLUSTER-CONDITIONAL/PHYSICAL-CHANNELS/LIN-PHYSICAL-CHANNEL/FRAME-TRIGGERINGS/FRAME-TRIGGERING/*)
                For Each node5 In nodes5
                    If node5.tagName=”SHORT-NAME”
                        str_Frame_Name=node5.Text
                        If str_Frame_Name = str_Frame_Trigger Then
                            str_Identifier = node5.tagName(“IDENTIFIER”).text
                            xlSH.Cells(iRow,6)=str_Identifier
                            Exit For
                        End If
                    End If
                Next

            End If

        Next
        iRow=iRow+1

    End If
Next

        End If
    Next

Next




    End Sub


End Class
Improve this question
2
  • Just to be clear. You want to read xml from some source, then parse it and save the output as a formatted Excel spreedsheet? Commented Feb 4, 2020 at 20:40
  • Ya I need to read the XML the particular values mentioned and write to the excel sheet Commented Feb 5, 2020 at 8:16

1 Answer 1

Reset to default
1

There are some errors in the XML which show up in Firefox

XML Parsing Error: XML declaration not well-formed
Location: file:///C:/temp/autosar.xml
Line Number 1, Column 22:
<?xml version = '1.0'encoding = 'UTF-8'?>    
---------------------^

XML Parsing Error: mismatched tag. Expected: </TABLE-ENTRYS>.
Location: file:///C:/temp/autosar.xml
Line Number 24, Column 26:
                   </CON-SCHEDULE-TABLE>
-------------------------^

If you fix those this might work for you 

Imports Microsoft.Office.Interop

Public Class frm_HexGen
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim xlApp As New Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlSH As Excel.Worksheet

    Dim str_File_Name = "Schedule.xlsx"
    Dim str_File_Template As String
    Dim Current_Directory As String

    Current_Directory = "C:\temp" 'CurDir()
    str_File_Template = Current_Directory & "\_log" & str_File_Name

    If Not My.Computer.FileSystem.FileExists(str_File_Template) Then
        MsgBox("Error - File not found " & str_File_Template, vbCritical)
    End If

    xlWB = xlApp.Workbooks.Open(str_File_Template)
    xlApp.Visible = True
    xlApp.ActiveWindow.WindowState = Excel.XlWindowState.xlMaximized

    xlSH = xlWB.Worksheets("Sheet1")
    xlSH.Activate()

    Dim header As String() = {"TEST ID", "AR-RANGE", "CLUSTER-SHORT-NAME", "CON-SCHEDULE-TABLE_SHORT-NAME",
        "DELAY", "TRIGGER", "TRIGGERING-INDENTIFIER", "TABLE-ENTRY"}
    xlSH.Range("A1:H1").Value = header

    Dim bOK As Boolean, sXMLFilename As String
    sXMLFilename = Current_Directory & "\" & "board.xml"
    Dim OXMLDOC As Object = Nothing

    OXMLDOC = CreateObject("Microsoft.XMLDOM")
    bOK = OXMLDOC.Load(sXMLFilename)

    If Not bOK Then
        MsgBox("XML file failed to load : " & sXMLFilename, vbCritical)
    Else
        xlApp.StatusBar = "Loaded " & sXMLFilename
    End If

    Dim nodes0, nodes1, nodes2, nodes3, nodes5, nodesFT
    Dim node0, node1, node2, node3, node4, node5, nodeFT
    Dim sFTname As String = ""
    Dim sFTident As String = ""

    Dim iRow As Long = 0
    Dim testID As Integer = 0

    ' process each package
    nodes0 = OXMLDOC.SelectNodes("/AUTOSAR/AR-PACKAGES/AR-PACKAGE/*")
    For Each node0 In nodes0
        If node0.tagname = "SHORT-NAME" Then
            iRow += 2
            testID += 1
            xlSH.Cells(iRow, 1) = testID
            xlSH.Cells(iRow, 2).Value = node0.Text
        ElseIf node0.tagname = "AR-PACKAGES" Then
            nodes1 = node0.SelectNodes(".//CLUSTER/*")

            'process each cluster
            For Each node1 In nodes1
                If node1.tagname = "SHORT-NAME" Then
                    xlSH.Cells(iRow, 3).Value = node1.Text
                ElseIf node1.tagname = "LIN-CLUSTER-VARIANTS" Then
                    nodes2 = node1.SelectNodes(".//LIN-PHYSICAL-CHANNEL/*")

                    For Each node2 In nodes2
                        ' get frame triggering name and ident
                        If node2.tagname = "FRAME-TRIGGERINGS" Then
                            For Each nodesFT In node2.ChildNodes
                                For Each nodeFT In nodesFT.ChildNodes
                                    If nodeFT.tagname = "SHORT-NAME" Then
                                        sFTname = nodeFT.Text
                                    ElseIf nodeFT.tagname = "IDENTIFIER" Then
                                        sFTident = nodeFT.Text
                                    End If
                                Next
                            Next

                        ElseIf node2.tagname = "SCHEDULE-TABLES" Then
                            nodes3 = node2.ChildNodes
                            For Each node3 In nodes3
                                For Each node4 In node3.ChildNodes
                                    If node4.tagname = "SHORT-NAME" Then
                                        If Len(xlSH.Cells(iRow, 3).value) = 0 Then iRow += 2
                                        xlSH.Cells(iRow, 4).Value = node4.Text

                                    ElseIf node4.tagname = "TABLE-ENTRYS" Then
                                        nodes5 = node4.SelectNodes(".//(DELAY|TRIGGER)")
                                        For Each node5 In nodes5

                                            If node5.tagname = "DELAY" Then
                                                If Len(xlSH.Cells(iRow, 5).value) > 0 Then iRow += 1
                                                xlSH.Cells(iRow, 5).Value = node5.Text
                                            ElseIf node5.tagname = "TRIGGER" Then
                                                If Len(xlSH.Cells(iRow, 6).value) > 0 Then iRow += 1
                                                xlSH.Cells(iRow, 6).Value = node5.Text
                                                If node5.Text = sFTname Then
                                                    xlSH.Cells(iRow, 7).Value = sFTident
                                                End If
                                            End If
                                            xlSH.Cells(iRow, 8).Value = node5.ParentNode.tagname ' TABLE-ENTRY
                                        Next
                                    End If
                                Next
                            Next
                        End If
                    Next
                End If
            Next
        End If
    Next
    xlApp.StatusBar = "Finished processing " & sXMLFilename
End Sub
End Class
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

In this line "Set oXMLDOc = CreateObject("Microsoft.XMLDOM")" getting 'Let' and 'Set' assignment statements are no longer supported.
I am able to assign objects or values without set property. But the a the delay and trigger values are getting displayed irrespective of particular " CON-SCHEDULE-TABLE". The node4.text for delay and trigger is showing all the delay and trigger values. Can we able to loop according to the "CON-SCHEDULE-TABLE" respective values?
Ya, but for the "CON-SCHEDULE-TABLE" - RUN-MAIN, there is no application entry but delay and trigger values are there. How it will work for that cases? Can you please in it?
I have 3 possible ways where DELAY and TRIGGER values will displayed. (1) APPLICATION-ENTRY (2) ASSIGN-FRAME-ID-RANGE (3) ASSIGN-FRAME-ID. As per your answer it is working for first case APPLICATION ENTRY. Is there any possible ways to check for FRAME-ID-RANGE and FRAME-ID under "TABLE-ENTRYS"
Thank you it is working. I need one more help please. The text which am getting in this part "node4.tagName = “TRIGGER”, I need to compare in another part of the XML and get the value which belongs to "IDENTIFIER" Tag. Please check the updated XML which I have kept in question . Under <FRAME-TRIGGERINGS><FRAME-TRIGGERING> I need to take the short name, if the short name is same as "node4.tagName = “TRIGGER""'s text I need to store the value of identifier under the FRAME-TRIGGERING. I have written the logic under the elsif part. Please check and help me.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.