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
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.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.