Abarna Abi
Reputation: 27
XML reading and writing to Excel using VB.Net
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
Upvotes: 0
Views: 962
Answers (1)
CDP1802
Reputation: 16174
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
Upvotes: 1
Related Questions
- Parsing XML using Excel VBA
- Import data from XML file into excel
- How to read in data from XML file into a Excel file using VBA
- Reading XML and Save into Excel C#
- Importing XML Content to Excel
- Export data from Excel file to different format
- How to parse an XML file and write in data then save it
- How read certain XML data into Excel via VBA
- Parse XML with VBA
- XML to Excel Mapping