• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 786
  • Last Modified:

Problem creating an XML file form a datatable

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.
           rootElement.AppendChild(rowElement)
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.

Code

        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()
                rowElement.AppendChild(colElement(i))
            Next i
            rootElement.AppendChild(rowElement)
        Next
        doc.WriteTo(xw)
        xw.Close()
0
ajaac
Asked:
ajaac
  • 6
  • 4
1 Solution
 
Bob LearnedCommented:
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()
  DataSet2.Tables.Remove("Table2")
  DataSet2.WriteXml("C:\temp\test.xml")

Bob
0
 
ajaacAuthor Commented:
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.
0
 
Bob LearnedCommented:
You set the root element to the DocumentElement, which is not set.  You need to call CreateElement to add a single root element.

Bob
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ajaacAuthor Commented:
TheLearnedOne, can you provide sample code so I know where and how to do this?  What I just tried did not work.
0
 
Bob LearnedCommented:
Are you trying to create an Excel XML file?

Bob
0
 
ajaacAuthor Commented:
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?
0
 
ajaacAuthor Commented:
I got the code to work by changing the line.

Dim rootElement As XmlElement = doc.DocumentElement()
to
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.

Thanks
0
 
Bob LearnedCommented:
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

    m_response.Clear()

    ' 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))
    m_response.BinaryWrite(Encoding.UTF8.GetBytes(exportContent))

    ' Stop execution of the current page.
    m_response.End()

  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.Clear()
    m_response.Buffer = True

    m_response.Clear()
    m_response.ClearHeaders()
    m_response.Cache.SetExpires(DateTime.Now.AddSeconds(60))
    m_response.Expires = 1
    m_response.Cache.SetCacheability(HttpCacheability.ServerAndPrivate)
    m_response.Cache.SetValidUntilExpires(False)
    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/vnd.ms-excel"
    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

    m_response.Write(workbook.Xml)

    m_response.Flush()
    m_response.Close()

  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

    resultBuilder.Append(ControlChars.Quote)

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

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

      resultBuilder.Append(sourceColumn.ColumnName)

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

      resultBuilder.Append(Separator)

    Next sourceColumn

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

    resultBuilder.Append(ControlChars.Quote)
    resultBuilder.Append(ControlChars.CrLf)

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

      resultBuilder.Append(ControlChars.Quote)

      For Each columnCurrent As DataColumn In sourceData.Columns

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

        resultBuilder.Append(rowCurrent(columnCurrent.ColumnName))

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

      Next columnCurrent

      resultBuilder.Length = resultBuilder.Length - 1
      resultBuilder.Append(ControlChars.Quote)
      resultBuilder.Append(ControlChars.CrLf)

    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.
0
 
Bob LearnedCommented:
Worker class for Excel--ExcelWorkbook:

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

' Xml Reference:
' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp

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_document.LoadXml(Me.GetXmlTemplate("Workbook.xml"))

    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
    Get

      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

      book.RemoveChild(sheet)

      Dim transformer As New XmlDsigC14NTransform
      transformer.LoadInput(m_document)
      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.
    table.RemoveChild(table.ChildNodes(0))

    ' 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_sheetTemplate.RemoveChild(m_sheetTemplate.ChildNodes(0))
      m_isFirstWorksheet = False
    End If

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

    ' Add the Worksheet to the list.
    m_listWorksheets.Add(sheet)

  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

    buildRow.Append(Me.AddColumns(grid))

    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

          Else

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

          End If

          ' Add the cell to the row.
          buildCells.Append(cell.OuterXml)

        End If

      Next index

      node.InnerXml = buildCells.ToString()

      buildRow.Append(node.OuterXml)

    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.
      buildCells.Append(cell.OuterXml)
    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 = ""

    Try

      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

      Throw

    Finally

      If Not stream Is Nothing Then
        stream.Close()
      End If

      If Not reader Is Nothing Then
        reader.Close()
      End If

    End Try

    Return xml

  End Function  'GetXmlTemplate

End Class
0
 
Bob LearnedCommented:
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"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author></Author>
  <LastAuthor></LastAuthor>
  <Created></Created>
  <Company>WPAFB</Company>
  <Version></Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8190</WindowHeight>
  <WindowWidth>11400</WindowWidth>
  <WindowTopX>360</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <ActiveSheet>1</ActiveSheet>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Tahoma"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="Text">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <NumberFormat ss:Format="@"/>
  </Style>
  <Style ss:ID="Date">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <NumberFormat ss:Format="Short Date"/>
  </Style>
  <Style ss:ID="Number">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <NumberFormat ss:Format="0;[Red]0"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table>
   <Row>
    <Cell ss:StyleID="Text"><Data ss:Type="String">1:A1</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Panes>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
<!--
    <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>
-->

Bob
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now