asked on

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()

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()
DataSet2.Tables.Remove("Table2")
DataSet2.WriteXml("C:\temp\test.xml")

Bob

ajaac

ASKER

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.

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.

Bob

ajaac

ASKER

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

Bob Learned

Are you trying to create an Excel XML file?

Bob

ajaac

ASKER

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?

ajaac

ASKER

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

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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:
' 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

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"
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>


Bob