Using Ado.net Datasets in Microsoft Office System (v)

ado| data to import a dataset as XML into an Excel worksheet

Microsoft Office Excel 2003 provides very powerful support for XML, which allows you to save a workbook as XML or import an XML data source into a workbook. Because the dataset is inherently serialized as XML, you can easily import its data into Excel. The import step requires an XML schema so that Excel can map the data to the appropriate cells in the workbook, and the dataset automatically provides this functionality. In fact, this is the function of the code added at the end of the GetDataSet method described earlier in this article, which writes the data and schema of the DataSet object to disk.

If Bsaveschema Then

' Places ' The file in this app ' s bin directory

Ds. WriteXmlSchema ("Customers.xsd")

Ds. WriteXml ("Customers.xml")

End If

This code creates the XML and schema files in the application's Bin folder. Figure 5 shows the results of the XML file.



Figure 5: Customers.xml file generated using the WriteXml method of the dataset

Figure 6 shows the XML schema, which is also displayed in Internet Explorer.



Figure 6: Customers generated using the WriteXmlSchema method of the DataSet. XSD file

This technique can also be used in a new feature in Excel, called ListObject, which is a new type of list structure, which in essence provides a complete view of the data. The results list is shown in Figure 7. You can filter and sort the entire list of data by clicking the Drop-down list in the header row. The XML source panel in the figure shows the schema of the data, and you can modify the schema to determine which data can be included in the list.



Figure 7 Importing XML programmatically using XML Schema mapping

The BUILDXMLMAP process performs similar functions as Buildworksheet, but it uses XML import and mapping to move data from the dataset to Excel. This procedure receives a DataSet as its unique parameter, then instantiates Excel, adds a workbook, and colleagues perform other UI (user interface) tasks. The workbook name in the example is Northwind Customers. Of course, you can replace it with the name of your own workbook.

Private Sub Buildxmlmap (ByVal ds as DataSet)

' Create An instance of Excel 2003, add a workbook,

' And let the user know what ' s happening

Dim XL as New Excel.Application

Xl. Workbooks.Add ()

Xl. Activesheet.name = "Northwind Customers"

Xl. Visible = True

Xl. Range ("A1"). Value = "Loading the DataSet ..."

This process loads XML data directly from the DataSet object, but there is no way to read its schema directly from memory. So this code obtains a full path and file name for the. XSD schema file using the GetFullPath method of the. NET Framework Path object.

Try

Dim SMap as String = System.IO.Path.GetFullPath ("customers.xsd")

The code then adds the XML map schema to the XmlMaps collection of the currently active workbook. The first parameter of the Add method is the location of the file that will be mapped, and the second parameter is the name of the map stored in the collection. The code sets the name of the mapping so that the mapping is referenced later.

' Add ' map to the active workbook

' Can only add a map from a disk file.

Xl. ActiveWorkbook.XmlMaps.Add (SMap, _

"Northwindcustomerorders"). Name _

= "Northwindcustomerorders_map"

The code gets a reference to this new XML map, saves it as a mapped variable, and then adds a ListObject to the currently active worksheet. The list panel columns you select, in this example columns A through J, and many rows below the header row are used to hold the data.

' Specify the cells where the mapped data should go upon import

Dim Map as Excel.xmlmap = _

Xl. Activeworkbook.xmlmaps ("Northwindcustomerorders_map")

Xl. Range ("A1", "J1"). Select ()

Dim list as Excel.listobject = _

CType (XL. ActiveSheet, Excel.Worksheet). Listobjects.add

The next step is to map the specific elements of the XML data to each column in the list. The SetValue method holds a reference to the mapping used, and an XPath expression is used to indicate which elements are saved in which column. The following code sets column A to accommodate the CustomerID element, and then sets the column heading to "Customer ID." You can set the contents and headings of other columns in the same way.

List. ListColumns (1). Xpath.setvalue (Map, _

"/northwindcustomerorders/customers/customerid")

Xl. Range ("A1"). Value = "Customer ID"

Now you want to set the structure of the ListObject, because the next step is to import the XML data. The import method reads the disk file and uses the ImportXML method to read the XML directly from the dataset's Getxml method and does not save the XML data to disk. The process opens the XML Source task pane at the end to allow you to view it.

' Import the XML data

Xl. Activeworkbook.xmlmaps ("Northwindcustomerorders_map"). _

ImportXml (ds. GETXML)



' Open ' XML Source Task Pane

Xl. Displayxmlsourcepane (map)



Catch ex as Exception



End Try



End Sub