XML Import

MSXML

ExcelWriter can import into a spreadsheet data parsed from an XML file, allowing you to use an XML file on the Web server as a data source. To parse data and make it available for importing into the ExcelWriter generated spreadsheet, you will need an XML parser, such as Microsoft's free MSXML.

To make MSXML available for use in the ASP page that contains ExcelWriter, create an instance of the parser as follows:

	Set XML = Server. CreateObject("MSXML2.DOMDocument")

Once the XML object is available, the XML source file can be accessed via the Load method (with error handling):

	Xml_file = Server.MapPath("my_xml.xml") 

	Dim bRet
	bRet = XML.Load(xml_file)

	If (bRet = False) Then
		Response.Write Err.Description
	End If

Top

Excel XML DOM

The XML "Document Object Model" (DOM) is a programming interface for XML documents. It defines the way a document can be read and manipulated. Once the XML file is loaded in the ASP page that includes the ExcelWriter object, this DOM provides a means for the server-side scripting to navigate its structure in order to import the data contained in document's various "nodes" into the Excel worksheet on a cell-by-cell basis.

The DOM represents a tree view of the XML document. The documentElement is the top-level of the tree. This element has one or many childNodes that represent the branches of the tree.

A Node Interface is used to read and write the individual elements in the XML node tree. The childNodes property of the documentElement can be accessed with a for/each construct to enumerate each individual node.

Top

Example: Importing data from an XML File

The following script, continued from above, demonstrates how to "walk" the XML DOM in order to retrieve the data to be imported into the Excel file being created by ExcelWriter.

Once the Excel file in memory on the server has successfully imported the data, the worksheet can then be formatted, manipulated and saved or streamed to the browser using the various options available with the ExcelWriter's Save method.

Private Sub BuildWorksheet(ByRef parent, ByRef sheet)
Dim node
Dim row
Dim cell
Dim nRow
Dim nCol
For Each node In parent.childNodes
	If node.nodeName = "Table" Then
		For Each row In node.childNodes
			If Not row.Attributes.getNamedItem("ss:Index")  & _
							Is Nothing Then
				nRow =  & _
					row.Attributes.getNamedItem("ss:Index").nodeValue
			End If
			For Each cell In row.childNodes
				If Not cell.Attributes.getNamedItem("ss:Index")  & _
							Is Nothing Then
					nCol =  & _
					cell.Attributes.getNamedItem("ss:Index").nodeValue
				End If
				If Not cell.firstChild Is Nothing Then
					sheet.Cells(nRow, nCol) =  & _
							cell.firstChild.firstChild.nodeValue
				End If
				nCol = nCol + 1
			Next
			nRow = nRow + 1
		Next
	End If
Next
End Sub

Dim XLW, XML
Set XLW = Server.CreateObject("SoftArtisans.ExcelWriter")
Set XML = Server. CreateObject("MSXML2.DOMDocument")
Xml_file = Server.MapPath("my_xml.xml")

Dim bRet
bRet = XML.Load(xml_file)
If (bRet = False) Then
	Response.Write Err.Description
End If

Set doc = XML.documentElement
Dim strSheetName
Dim node
For Each node In doc.childNodes
	If node.nodeName = "Worksheet" Then
		strSheetName =  & _
				node.Attributes.getNamedItem("ss:Name").nodeValue
		XLW.Worksheets.Add strSheetName
		Set tempSheet = XLW.Worksheets(CStr(strSheetName))
		BuildWorksheet node, tempSheet

		'--- Create Worksheet 1 to contain the data table
		Set WKS = tempSheet 
		Set Cells = WKS.Cells
	End If
Set tempSheet = Nothing
Next

Top

Copyright © 2003, SoftArtisans, Inc.