Irina  Pavlushko Irina Pavlushko - 3 months ago 22
Vb.net Question

Export from Excel into XML using XML Literals + VB.NET

I need help with this....
I have Excel file with data.
And I need to export it into XMLfile, using XML Literals in VB.NET

I need some XML-file a a result:

<?xml version="1.0" standalone="no"?>
<soapenv:Envelope xmlns:soapenv="http://...">
<soapenv:Header/>
<soapenv:Body xmlns:wsu="http://....xsd">
<Header>
<Verb>cancel</Verb>
</Header>
<Request>
<ID>VALUE-1</ID>
<ID>VALUE-2</ID>
<ID>VALUE-3</ID>
......
</Request>
</soapenv:Body>
</soapenv:Envelope>


Where VALUE-1, VALUE-2,....and so on - I get them from Cells with Ron Numer i and column Number = 2.
In VB.NET in Loop I did this like:

While Not this condition
While Not (String.IsNullOrEmpty(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet.Cells(iRow, 1).Value))
'Check next condition with IF
If LCase(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet.Cells(iRow, 25).Value) = "y" Then
'Add XElement object into another one XML Element from main XDocument
objRequestCANCEL.Add(objIDCANCEL)
'Add Value for this new Element
objIDCANCEL.Value = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet.Cells(iRow, 2).Value
End If
'Go to next Row and do the same - add XELement if condition is OK here
iRow = iRow + 1
End While


Here objIDCANCEL As XElement in main XDocument
I used in this case XElements like Variables.But Now I need something like creation document without variables like this:

Dim XDoc As XDocument = <?xml version="1.0" standalone="no"?>
<soapenv:Envelope xmlns:soapenv="http://...">
<soapenv:Header/>
<soapenv:Body xmlns:wsu="http://....xsd">
<Header>
<Verb>create</Verb>
<ID><%= varValue %> </ID>
.....HERE I NEED ADD IN LOOP ALL VALUES FROM ALL ROWS IN EXCEL FILE.....
</Header>
<Request>
</Request>
</RequestMessage>
</soapenv:Body>
</soapenv:Envelope>


Write me please, how can I re-write this Loop here ?

Answer

Here is a simple example using LINQ and a worksheet to read out the first column (1) of the rows 1 .. 3 and populate some XML literals:

    Dim excel As Excel.Application = New Excel.Application()
    excel.Workbooks.Open("sheet1.xlsx")
    Dim sheet As Excel._Worksheet = excel.ActiveWorkbook.ActiveSheet
    Dim cells As Excel.Range = sheet.Cells

    Dim doc As XDocument = <?xml version="1.0"?>
                           <root>
                               <values>
                                   <%= From i In Enumerable.Range(1, 3) Select <value>
                                                                                   <%= cells(i, 1).Value %>
                                                                               </value> %>
                               </values>

                           </root>

    doc.Save(Console.Out)

Output then is e.g.

<root>
  <values>
    <value>1</value>
    <value>2</value>
    <value>3</value>
  </values>
</root>

Adapt that to your XML format and Excel sheet contents as needed.

Comments