Search code examples
xmlvb.netdatagrid

binding xml data to dataGrid

I am exporting excel file using Datagrid.

my datagrid looks like this.which has one of the column has XML data as highlighted. enter image description here

Below is my code which i am using to bind and get the xml file.

 Sub ExportToExcel(ByVal ds As DataSet, ByVal response As HttpResponse, ByVal strTitle As String, Optional ByVal strFileName As String = "ExcelExport")
    'Clean up the response.object
    response.Clear()
    response.Charset = ""
    response.ClearContent()
    response.ClearHeaders()
    response.AppendHeader("content-disposition", "attachment; filename=" + strFileName + ".xls")
    response.ContentType = "application/vnd.ms-excel"
    'create a string writer
    Dim stringWrite As New System.IO.StringWriter
    'create an htmltextwriter which uses the stringwriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
    'instantiate a datagrid
    Dim dg As New DataGrid
    'If the datatable contains DateTime fields, add format handler
    If SetDateColumns(ds.Tables(0)) >= 1 Then
        AddHandler dg.ItemDataBound, AddressOf ItemDataBound
    End If

    dg.DataSource = ds.Tables(0)

    dg.HeaderStyle.Font.Bold = True
    dg.DataBind()
    'response.Write("<font size=4><b>" + strTitle + "</b></font><br>")
    response.Write("<style>.text { mso-number-format:\@; } </style>")
    'Need to make this more generic
    For intTemp As Integer = 0 To ds.Tables(0).Rows.Count - 1
        dg.Items(intTemp).Cells(0).Attributes.Add("class", "text")
        dg.Items(intTemp).Cells(1).Attributes.Add("class", "text")
    Next

    'tell the datagrid to render itself to our htmltextwriter
    dg.RenderControl(htmlWrite)
    response.Write(stringWrite.ToString)
    response.Flush()
    response.End()
End Sub

I am expecting XML as in datagrid. but when excel file upload I am only getting data inside the tag and not the xml tag as below: enter image description here

My XML looks like as below.Its may have different tabs

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Header>
<Action xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none" 
s:mustUnderstand="1" />
</s:Header>
<s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<rrcRequest xmlns="http://gpm.portrix-ls.de/schema/rrc/v1">
  <rrcViews>
    <rrcView>SALES</rrcView>
  </rrcViews>
  <combineResults>true</combineResults>
  <origins>
    <geoobject>
      <code>CNSHG CFS</code>
    </geoobject>
  </origins>
  <destinations>
    <geoobject>
      <code>USMCJ</code>
    </geoobject>
    <geoobject>
      <code>USCHI CFS</code>
    </geoobject>
  </destinations>
  <suppliers>
    <supplier>
      <code>PYRDX</code>
    </supplier>
  </suppliers>
  <dateOfShipment>2020-02-25</dateOfShipment>
  <transportModes>
    <transportMode>
      <name>LCL</name>
    </transportMode>
  </transportModes>
  <shipmentTypesOrigin>
    <shipmentType>
      <name>CFS</name>
    </shipmentType>
  </shipmentTypesOrigin>
  <shipmentTypesDestination>
    <shipmentType>
      <name>CFS</name>
    </shipmentType>
    <shipmentType>
      <name>DOOR</name>
    </shipmentType>
  </shipmentTypesDestination>
</rrcRequest>
</s:Body>
</s:Envelope>

Not sure what is missing. Please Advice !!

Solution

  • Using the Dataset method doesn't work on complicated XML files. The Dataset ReadXml() method fragmnets the data into multiple DataTables that cannot be used. I wrote code using XMl Linq below

    Imports System.Xml
Imports System.Xml.Linq
Module Module1
    Const FILENAME As String = "c:\temp\test.xml"
    Sub Main()
        Dim dt As New DataTable()
        dt.Columns.Add("Origin Door", GetType(String))
        dt.Columns.Add("Origin CFS", GetType(String))
        dt.Columns.Add("Destination", GetType(String))
        dt.Columns.Add("Destination CFS", GetType(String))
        dt.Columns.Add("Transportation Mode", GetType(String))
        dt.Columns.Add("Rate View", GetType(String))
        dt.Columns.Add("Shipment Type", GetType(String))
        dt.Columns.Add("RMS Request", GetType(String))
        dt.Columns.Add("RMS Response", GetType(String))
        dt.Columns.Add("RMS Status", GetType(String))
        dt.Columns.Add("Client Request", GetType(String))
        dt.Columns.Add("Client Response", GetType(String))
        dt.Columns.Add("Client Status", GetType(String))

        Dim doc As XDocument = XDocument.Load(FILENAME)

        Dim rrcRequest As XElement = doc.Descendants().Where(Function(x) x.Name.LocalName = "rrcRequest").First()
        Dim ns As XNamespace = rrcRequest.GetDefaultNamespace()

        Dim newRow As DataRow = dt.Rows.Add()

        newRow("Origin CFS") = CType(rrcRequest.Element(ns + "origins").Descendants(ns + "code").FirstOrDefault(), String)
        Dim destinationCodes As String() = rrcRequest.Element(ns + "destinations").Descendants(ns + "code").Select(Function(x) CType(x, String)).ToArray()
        newRow("Destination") = destinationCodes(0)
        newRow("Destination CFS") = destinationCodes(1)
        newRow("Transportation Mode") = CType(rrcRequest.Element(ns + "transportModes").Descendants(ns + "name").FirstOrDefault(), String)
        newRow("Rate View") = CType(rrcRequest.Descendants(ns + "rrcView").FirstOrDefault(), String)
        Dim shipmentTypes As String() = rrcRequest.Element(ns + "shipmentTypesDestination").Descendants(ns + "name").Select(Function(x) CType(x, String)).ToArray()
        newRow("Shipment Type") = String.Join(" TO ", shipmentTypes)

    End Sub