Problem creating an XML file form a datatable

Posted on 2006-04-21
Last Modified: 2012-05-05
I'm missing something that I've spent the last few hours stuck on, hopefully someone can point me in the right direction.

I'm trying to create an xml document to be opened later by excel.
I can do this with dataset.writexml, but I only want one table written to the xml file.

I get an error on this line.
The error is Object reference not set to an instance of an object.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

I suspect the problem to be with this line.
        Dim rootElement As XmlElement = doc.DocumentElement
I just don't see what i need to do to get this to work.


        Dim dsBrowse As DataSet = CType(Session("dsBrowse"), DataSet)
        Dim strFilename As String
        strFilename = "C:\Customers2.xml"
        Dim dt As New DataTable
        Dim row As DataRow
        Dim rowElement As XmlElement
        dt = dsBrowse.Tables("tblContact")
        Dim doc As XmlDocument = New XmlDocument
        Dim xw As XmlTextWriter = New XmlTextWriter(strFilename, System.Text.Encoding.UTF8)
        Dim rootElement As XmlElement = doc.DocumentElement()
        Dim i As Integer, cnt As Long
        Dim colElement(dt.Columns.Count) As XmlElement
        xw.WriteProcessingInstruction("xml", "version='1.0'")
        xw.WriteProcessingInstruction("mso-application", "progid='Excel.Sheet'")
        For Each row In dt.Rows
            rowElement = doc.CreateElement("Table")
            For i = 0 To dt.Columns.Count - 1
                colElement(i) = doc.CreateElement(dt.Columns(i).Caption)
                colElement(i).InnerText = row(i).ToString()
            Next i
Question by:ajaac
    LVL 96

    Expert Comment

    by:Bob Learned
    If you want to use WriteXml to write the file, you could copy the DataSet, remove any table that you don't want to write:

      Dim DataSet2 As DataSet = DataSet1.Copy()


    Author Comment

    I know I could do it that way, but I would perfer to get the code I posted to work.
    To understand what I'm missing, and as my perferred method to address this issue.
    LVL 96

    Expert Comment

    by:Bob Learned
    You set the root element to the DocumentElement, which is not set.  You need to call CreateElement to add a single root element.


    Author Comment

    TheLearnedOne, can you provide sample code so I know where and how to do this?  What I just tried did not work.
    LVL 96

    Expert Comment

    by:Bob Learned
    Are you trying to create an Excel XML file?


    Author Comment

    Yes, but more importantly I want to know why I cannot get my code to work.
    Your reply to create a root element is what I'm stuck on right now, how do I do that?

    Author Comment

    I got the code to work by changing the line.

    Dim rootElement As XmlElement = doc.DocumentElement()
    Dim rootElement As XmlElement = doc.CreateElement("Contact")

    Now I need to get the format right for excel, if you have any suggestions, I would appreciate it.

    LVL 96

    Accepted Solution

    Here is a class that I use to create XML files the same way Excel does when you 'Save As' XML:

    Imports System.IO
    Imports System.Text
    Imports System.Text.RegularExpressions
    Imports System.Web.HttpContext

    Public Class Export

      Private m_response As HttpResponse = Nothing

      ' Exports data to a custom format
      Public Sub ExportCSV(ByVal data As DataTable, ByVal fileName As String)

        m_response = HttpContext.Current.Response


        ' Add the header that specifies the default filename
        ' for the Download/SaveAs dialog
        m_response.AddHeader("Content-Disposition", "attachment;filename=" & fileName)

        Dim delimiter As String = String.Empty

        m_response.ContentType = "text/csv"

        Dim exportContent As String = ConvertDataTableToString(data, delimiter)

        If exportContent.Length <= 0 Then
          exportContent = "Error converting data"
        End If

        m_response.AddHeader("Content-Length", Encoding.UTF8.GetByteCount(exportContent))

        ' Stop execution of the current page.

      End Sub  'ExportCSV

      Public Sub ExportExcel(ByVal fileName As String, ByVal ParamArray grids As DictionaryEntry())

        m_response = Current.Response

        ' Write the file to a temporary path on the client.
        Dim temporaryPath As String = Path.GetTempPath()
        fileName = MakeValidName(fileName)

        m_response.Buffer = True

        m_response.Expires = 1
        m_response.CacheControl = "private"
        m_response.AddHeader("pragma", "cache")

        ' Add the header that specifies the default filename
        ' for the Download/SaveAs dialog
        m_response.AddHeader("Content-Disposition", "attachment;filename=" & fileName)
        m_response.ContentType = "application/"
        m_response.ContentEncoding = Encoding.UTF8

        Dim workbook As New ExcelWorkbook
        For Each entry As DictionaryEntry In grids
          workbook.AddWorksheet(entry.Value, Me.MakeValidName(entry.Key))
        Next entry



      End Sub  'ExportExcel

      Public Function ConvertDataTableToString(ByVal sourceData As DataTable, Optional ByVal Delimiter As String = Nothing, Optional ByVal Separator As String = ",") As String

        Dim resultBuilder As StringBuilder
        resultBuilder = New StringBuilder
        resultBuilder.Length = 0


        ' Add column headers.
        For Each sourceColumn As DataColumn In sourceData.Columns

          If Not Delimiter Is Nothing AndAlso (Delimiter.Trim.Length > 0) Then
          End If


          If Not Delimiter Is Nothing AndAlso (Delimiter.Trim.Length > 0) Then
          End If


        Next sourceColumn

        If resultBuilder.Length > Separator.Trim.Length Then
          resultBuilder.Length = resultBuilder.Length - Separator.Trim.Length
        End If


        ' Add data rows.
        For Each rowCurrent As DataRow In sourceData.Rows


          For Each columnCurrent As DataColumn In sourceData.Columns

            If Not Delimiter Is Nothing AndAlso (Delimiter.Trim.Length > 0) Then
            End If


            If Not Delimiter Is Nothing AndAlso (Delimiter.Trim.Length > 0) Then
            End If

          Next columnCurrent

          resultBuilder.Length = resultBuilder.Length - 1

        Next rowCurrent

        Return resultBuilder.ToString()

      End Function  'ConvertDataTableToString

      Private Function MakeValidName(ByVal name As String) As String

        Return Regex.Replace(name, "[*?\:;/'<>|" & Chr(34) & "]", "_").Replace("  ", " ")

      End Function  'MakeValidName

    End Class

    Bonus is exporting to CSV.
    LVL 96

    Expert Comment

    by:Bob Learned
    Worker class for Excel--ExcelWorkbook:

    Imports System.IO
    Imports System.Xml
    Imports System.Text
    Imports System.Reflection
    Imports System.Security.Cryptography.Xml

    ' Xml Reference:

    Public Class ExcelWorkbook

      Private m_listWorksheets As New ArrayList
      Private m_document As XmlDocument

      Private m_bookTemplate As XmlNode
      Private m_sheetTemplate As XmlNode
      Private m_tableTemplate As XmlNode
      Private m_rowTemplate As XmlNode
      Private m_cellTemplate As XmlNode

      Private m_nsmgr As XmlNamespaceManager
      Private m_isFirstWorksheet As Boolean = True

      Public Sub New()

        m_document = New XmlDocument

        m_nsmgr = New XmlNamespaceManager(m_document.NameTable)
        For Each attr As XmlAttribute In m_document.SelectSingleNode("/*").Attributes
          If attr.Prefix = "xmlns" Then
            m_nsmgr.AddNamespace(attr.LocalName, attr.Value)
          End If
        Next attr

        m_bookTemplate = m_document.SelectSingleNode("//ss:Workbook", m_nsmgr).Clone()

        If m_bookTemplate Is Nothing Then
          Throw New ArgumentNullException("Workbook", "Workbook not found")
        End If

        m_sheetTemplate = m_document.SelectSingleNode("//ss:Worksheet", m_nsmgr).Clone()

        If m_sheetTemplate Is Nothing Then
          Throw New ArgumentNullException("Worksheet", "Worksheet not found")
        End If

        m_tableTemplate = m_document.SelectSingleNode("//ss:Table", m_nsmgr).Clone()

        If m_tableTemplate Is Nothing Then
          Throw New ArgumentNullException("TableTemplate", "Table template not found")
        End If

        m_rowTemplate = m_tableTemplate.SelectSingleNode("//ss:Row", m_nsmgr).Clone()

        If m_rowTemplate Is Nothing Then
          Throw New ArgumentNullException("RowTemplate", "Row template not found")
        End If

        m_cellTemplate = m_rowTemplate.SelectSingleNode("//ss:Cell", m_nsmgr).Clone()

        If m_cellTemplate Is Nothing Then
          Throw New ArgumentNullException("CellTemplate", "Cell template not found")
        End If

      End Sub    'New

      Public ReadOnly Property Xml() As String

          Dim book As XmlNode = m_document.SelectSingleNode("//ss:Workbook", m_nsmgr)
          Dim sheet As XmlNode = book.SelectSingleNode("//ss:Worksheet", m_nsmgr)

          For Each node As XmlNode In m_listWorksheets
            book.InsertAfter(node, sheet)
          Next node


          Dim transformer As New XmlDsigC14NTransform
          Dim strm As Stream = transformer.GetOutput(GetType(Stream))
          Dim reader As New StreamReader(strm)
          Dim output As String = reader.ReadToEnd()
          output = "<?xml version=""1.0""?>" & output

          Return output
        End Get
      End Property  'Xml

      Public Sub AddWorksheet(ByVal grid As DataGrid, ByVal name As String)

        ' Check conditions.
        If grid Is Nothing Then
          Throw New ArgumentNullException("Grid")
        End If

        If grid.DataSource Is Nothing Then
          Throw New ArgumentNullException("Grid.DataSource")
        End If

        ' Get the data source from the data grid.
        Dim data As DataTable = CType(grid.DataSource, DataSet).Tables(0)

        ' Copy the Worksheet element.
        Dim sheet As XmlNode = m_sheetTemplate.Clone()
        sheet.Attributes("ss:Name").Value = name

        ' Copy the Table element.
        Dim table As XmlNode = m_tableTemplate.Clone()

        ' Remove the Row and Cell elements.

        ' Add Row and Cell elements to the Table.
        Me.AddRows(grid, table, data)

        If m_isFirstWorksheet Then
          ' Remove the table, rows, cells, etc. from the template.
          m_isFirstWorksheet = False
        End If

        ' Add the Table to the Worksheet.
        sheet.InnerXml = table.OuterXml

        ' Add the Worksheet to the list.

      End Sub  'AddWorksheet

      Private Sub AddRows(ByVal grid As DataGrid, ByVal tableNode As XmlNode, _
       ByVal table As DataTable)

        Dim buildRow As New StringBuilder(2000)
        Dim index As Integer
        Dim cell As XmlNode
        Dim data As XmlNode


        For Each row As DataRow In table.Rows

          Dim node As XmlNode = m_rowTemplate.Clone()
          Dim buildCells As New StringBuilder(1000)

          For index = 0 To grid.Columns.Count - 1

            cell = m_cellTemplate.Clone()
            data = cell.SelectSingleNode("ss:Data", m_nsmgr)

            Dim gridColumn As DataGridColumn = grid.Columns(index)
            If TypeOf gridColumn Is BoundColumn AndAlso gridColumn.Visible Then
              Dim columnName As String = Me.FindColumnName(gridColumn)
              Dim column As DataColumn = table.Columns(columnName)
              Dim type As String = column.DataType.Name

              Dim value As String = row(columnName).ToString()

              If value.Length > 0 Then

                Dim cellStyle As String = Me.DataTypeToStyleID(type)

                ' Set the cell style.
                cell.Attributes("ss:StyleID").Value = cellStyle
                data.Attributes("ss:Type").Value = type
                data.InnerText = value


                cell.RemoveChild(cell.SelectSingleNode("ss:Data", m_nsmgr))

              End If

              ' Add the cell to the row.

            End If

          Next index

          node.InnerXml = buildCells.ToString()


        Next row

        tableNode.InnerXml = buildRow.ToString()

      End Sub    'AddRows

      Private Function DataTypeToStyleID(ByRef type As String) As String
        Dim cellStyle As String = type

        ' Convert the data type to a cell style
        Select Case cellStyle
          Case "String"
            cellStyle = "Text"

          Case "DateTime"
            cellStyle = "Date"

          Case "Double", "Single", "Decimal"
            cellStyle = "Number"
            type = "Number"

          Case "Integer", "Short", "Byte", "Int32", "Int16"
            cellStyle = "Number"
            type = "Number"

          Case Else
            Throw New ArgumentException("Unknown data type:  " & type)

        End Select      'cellStyle

        Return cellStyle
      End Function

      Private Function FindColumnName(ByVal gridColumn As DataGridColumn) As String

        If TypeOf gridColumn Is BoundColumn Then
          Dim column As BoundColumn = gridColumn

          Return column.DataField
        End If
      End Function  'FindColumnName

      Private Function AddColumns(ByVal grid As DataGrid) As String

        Dim gridColumn As DataGridColumn

        Dim node As XmlNode = m_rowTemplate.Clone()
        Dim buildCells As New StringBuilder(1000)
        For index As Integer = 0 To grid.Columns.Count - 1
          gridColumn = grid.Columns(index)
          Dim cell As XmlNode = m_cellTemplate.Clone()
          Dim data As XmlNode = cell.SelectSingleNode("ss:Data", m_nsmgr)
          cell.Attributes("ss:StyleID").Value = "Text"
          data.Attributes("ss:Type").Value = "String"
          data.InnerText = gridColumn.HeaderText
          ' Add the cell to the row.
        Next index

        node.InnerXml = buildCells.ToString()
        Return node.OuterXml

      End Function  'AddColumns

      Private Function GetXmlTemplate(ByVal fileName As String) As String

        Dim stream As Stream
        Dim reader As StreamReader
        Dim xml As String = ""


          Dim assy As [Assembly] = [Assembly].GetExecutingAssembly()
          For Each name As String In assy.GetManifestResourceNames()
            If name.IndexOf(fileName) <> -1 Then
              stream = assy.GetManifestResourceStream(name)
            End If
          Next name

          reader = New StreamReader(stream)
          xml = reader.ReadToEnd()

        Catch ex As Exception



          If Not stream Is Nothing Then
          End If

          If Not reader Is Nothing Then
          End If

        End Try

        Return xml

      End Function  'GetXmlTemplate

    End Class
    LVL 96

    Expert Comment

    by:Bob Learned
    ExcelWorkbook.GetXmlTemplate retrieves template text from an embedded XML file (Name = Workbook.xml, Build Action = 'Embedded Resource'):

    <?xml version="1.0"?>
    <?mso-application progid="Excel.Sheet"?>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
     <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
     <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
      <Style ss:ID="Default" ss:Name="Normal">
       <Alignment ss:Vertical="Bottom"/>
       <Font ss:FontName="Tahoma"/>
      <Style ss:ID="Text">
       <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
       <NumberFormat ss:Format="@"/>
      <Style ss:ID="Date">
       <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
       <NumberFormat ss:Format="Short Date"/>
      <Style ss:ID="Number">
       <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
       <NumberFormat ss:Format="0;[Red]0"/>
     <Worksheet ss:Name="Sheet1">
        <Cell ss:StyleID="Text"><Data ss:Type="String">1:A1</Data></Cell>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
        <Cell ss:StyleID="Numeric"><Data ss:Type="Number">12.34</Data></Cell>
        <Cell ss:StyleID="Date"><Data ss:Type="DateTime">2006-12-12T00:00:00.000</Data></Cell>


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now