Importing XML Maps, XML Lists, and Dynamic Chart Sources in Excel 2003

John R. Durant
Microsoft Corporation

June 2003

Applies to:
    Microsoft® Office Excel 2003

Summary: The Web article, Web Queries and Dynamic Chart Data in Microsoft Excel 2002 demonstrates how to import data into Microsoft Excel 2002 using Web queries and then create a chart that dynamically responds to changes in the source data. This article describes how Microsoft Office Excel 2003 delivers the same result only with less code, less fuss, and with greater possibilities. Along the way you will become more broadly acquainted with the XML capabilities of the Excel 2003, most importantly, XML maps, XML lists, new routines for importing XML, and how to use dynamically populated XML lists as data sources for charts. (8 printed pages)

Contents

The Business Scenario
XML Mapping in Excel 2003
Excel Lists
Getting XML Data into Excel
Dynamic Chart Sources
Conclusion

The Business Scenario

Imagine a sales manager who regularly looks at sales data. She wants to specify a range of dates and then get aggregate sales data. In particular, she wants to see sales figures summarized by day within the specified range. She wants to see the raw data as well as a chart showing the summary sales trend for the specified date range.

It is not practical to create explicit reports for each of her requests. To do this, someone must sift through input data and create separate spreadsheet reports for the vast number of scenarios that could exist in the organization. The burden on the developers and IT staff s too large, especially when considering that the sales manager for one department is not the only person in the organization that has a request of this nature. A better possibility is to have an Excel spreadsheet that could adapt itself to deliver the various reports the sales manager needs as well as one that other departments could reuse and adjust for their similar needs.

This article shows you how to use features in Microsoft Office Excel 2003 to import data and dynamically bind the chart to the data. We will also dress up the user interface a little so that the user can configure settings for the report, and then import data for the report. The overall solution is shown in Figure 1.

Figure 1. Business graphical view

XML Mapping in Excel 2003

Excel 2003 has improved its support for operations with native XML. Microsoft Excel 2002 added the ability to open and save Excel spreadsheets in an XML vocabulary designed specifically for Excel called the "XML Spreadsheet" format. This vocabulary conforms to a specific schema, so any system can produce an Excel-ready spreadsheet in XML, as long as it conforms to the schema. What would be even better is if Excel could handle other schemas in the same intelligent way. Excel 2003 makes this possible by letting users and developers add their own XML schemas to a workbook. This creates an XML map that you can use to make data immediately more meaningful upon import or export. Thus, you can import data conforming to a custom, business-specific XML vocabulary (or schema), and then write the data back out by using the same XML vocabulary.

In summary then, with the new XML mapping features, you can associate an XML schema with a workbook. This allows for easier and more reliable importing and exporting of data into and out of Excel. What this means is that data, whether internal or external to a spreadsheet, has greater meaning to Excel and to a user's specific tasks. Because spreadsheets in Excel 2003 can be designed with an underlying XML structure, it is possible to create more adaptable connections between a user's desktop and data stored on servers. In our solution, we are going to be importing data for a report, but, through use of an XML map, the data will also conform to a schema.

XML Maps

Figure 2 shows one of these XML maps or schemas in a workbook. When you add an XML schema to your workbook, Excel creates an object called an XML map. These XML maps let you map cells or ranges to elements in the XML schema. Excel also uses these maps to relate the contents of a mapped range to elements in the schema when you import or export XML data. A workbook can contain many XML maps and each one is independent of all others. However, many maps can refer to the same underlying schema.

As you can see in Figure 2, what Excel displays is a hierarchical representation of the way data should be structured in a spreadsheet. You can then map the different elements in the representation to actual cells in the spreadsheet. The schema can merely constrain data hierarchically, or it can further constrain data types and other aspects of the data that you can extract from or import into the spreadsheet.

Figure 2. An XML map for a workbook

To see how mapping in this way occurs in an actual workbook, look at Figure 3. There are two key elements in the XML map, one for OrderDay and the other for Sales. As you select the elements in the map in the XML Structure pane, Excel selects the respective cell range in the worksheet itself. Similarly, selecting a cell in the range causes Excel to select the corresponding element from the map in the Task Pane.

Figure 3. Mapping between elements and cells

Adding an XML Map

Before you can use an XML map, you must add it to the workbook.

To add an XML map to a workbook using the user interface

1. On the Data menu, point to XML and then click XML Source.
2. On the XML Source pane, click XML Maps... and then click Add.
3. Select a valid XML, XSD, or UDC file, and then click Open.
4. Click OK to add the XML map.

Mapping cells to elements in an XML map can be as simple as highlighting the cell or a range of cells and dragging the element from the map to the selected cells. However, you can also perform these mappings programmatically, even when importing data from an external source as outlined in the business solution.

You can specify an existing XML map when executing the import routine. This lets you reuse an existing XML map. In contrast, if you do not specify an XML map when importing XML data then Excel generates one for you, effectively mapping the imported data elements to the elements in the XML map.

Our business solution makes it possible to import data repeatedly from the same source, each time with different parameters. For example, the first time the user may want report data from July 1 2000 to July 15 2000, and the next time a user requests the report, she may want to see the last 16 days of the month. Because Excel imports the data repeatedly, it doesn't make sense to force Excel to re-map the cells each time. Therefore, you can apply an existing map and merely overwrite data in the mapped cells.

One of the things that makes the solution even easier to develop is the advantage of Excel list functionality. This functionality, combined with the XML mapping technology makes it possible for Excel to organize the mapped XML into groups and handle it more intelligently.

Excel Lists

You can use a list (Figure 4) to organize data into groups within an Excel spreadsheet, thus making them more meaningful. If you look closely at the cells, you can see a blue border around them. This is not a formatting style. Rather, Excel places an automatic border around cells it has identified as a list. (In case you are wondering, you can tell Excel not to place the border around lists that are not active.) Because a list is just a group of data in the spreadsheet, you can have many lists in a single spreadsheet, and you can refer to each list as a cohesive unit. In Excel 2003, there is also a type of list called an XML list. What distinguishes an XML list from the more general list type is that, in addition to being a list or a group of data within a spreadsheet, the list has supplementary information, or metadata, associated with it, all of it articulated as XML.

Figure 4. A list in an Excel worksheet

Lists include distinct columns, column headings, and an insert row for adding more data. If you look near the bottom of Figure 4, you can see a large asterisk indicating the location of the insert row. You can also see the column headings. In an XML list, the columns are associated with an XML schema element. To do this, set the XPath property of the column implicitly or explicitly. The business solution sets this implicitly by using the XML map that we have put in place.

Our business solution dynamically loads data into an XML list when the user clicks a button. However, the list must already be in place for the operation to work successfully. To make this work, first add the list.

To add the list to a worksheet

1. Select a cell that is in the upper-left cell in the XML list.
2. Select the root element in the XML Structure pane to highlight it in the XML map.
3. Drag the highlighted root element from the XML Structure pane to the selected cell.

You should see a list like the one in Figure 5.

Figure 5. Sample of a new XML list

You can create XML lists by deliberately mapping cells in this way, or you can let Excel create a list for you based on user activities. For example, Excel automatically creates XML lists when one or more of the elements dragged onto the worksheet are repeating elements. In our business solution, the XML list is created ahead of time so that it can be populated with data when it is imported.

Getting XML Data into Excel

There are a number of ways to get XML into an Excel spreadsheet. You can use three key methods of the Workbook class to import XML into Excel:

• OpenXML method
• XMLImport method
• XMLImportXML method

You can use the OpenXML method when loading data from a file into a workbook. This is the programmatic equivalent of using Open from the File menu to open an XML file in Excel. The following table lists the different options for opening XML files in Excel and their purpose.

Importing XML is different in that, rather than opening an XML file as a separate workbook, Excel places the data in an existing workbook, either creating a worksheet or creating an XML list in an existing one. The XMLImport method lets you target a URL as the source for the XML similar to how Web queries use the QueryTable object in Excel 2002 and earlier. This method accepts four parameters:

Note   To use an existing XML map do not pass a Destination parameter.

The last method, XMLImportXML, is similar to XMLImport, except that instead of importing data based on a URL, the method accepts the actual data string as its first parameter. All other parameters are the same.

Importing the Data

Our business solution collects user input to assemble the target URL of the data. When the user clicks a button, in this case there is a button embedded in the worksheet, the event procedure runs. It is in this procedure where the data import occurs. Because the ImportXML method has a parameter requiring a reference to an XMLMap object, we acquire a reference like this:

  Dim xmpCustomMap as XmlMap
  Set xmpCustomMap = ActiveWorkbook.XmlMaps("ROOT_Map")
Then, we call the ImportXML method this way:
  ActiveWorkbook.XmlImport URL:= _
  & "http://localhost/NorthWindWeb/" _  
  & "sales.xml?BeginDate=07/01/2000&EndDate=07/14/2000, _
  ImportMap:=xmpCustomMap, Overwrite:=True

In this example, the URL is hard-coded so that the method is clearly understood. However, the business solution builds the URL in a separate procedure so that it can be constructed based on user input and settings in the workbook. Excel then passes resulting URL back as a string so that it can be used as a parameter in the ImportXML method. A real-world business solution should use error handling and other programming best practices.

Once you import the data from the target URL, Excel places it in the XML list that is mapped to the XML map. Because you specified the existing XML map when calling the ImportXML method, you do not need to worry about where the resulting data is placed or what the data means in the context of the spreadsheet as Excel configures these settings as part of the XML mapping process.

Dynamic Chart Sources

Just looking at some basic data is only part of the overall pleasure of Excel. A nice chart to give a visual representation of the data is an added bonus. Our business solution features a chart whose source is the XML list associated with the XML map with data dynamically loaded into the XML list. The benefit to the user is that as the criteria for the report change, the data in the XML list change after the import process, and so does the chart.

To add the chart to the workbook

1. On the Insert menu, click Chart.
2. Using the Chart Wizard, follow the prompts and choose a Line chart.
3. Click Next to specify a source for the data or click Finish to place a blank chart on the worksheet.

After adding the chart, you must configure the XML list as the data source, but you will not do so with the Chart Wizard dialog box. You will do it the easy way. By immediately clicking Finish, the resulting chart is blank because it does not yet have any data.

To link the source of the chart to the XML List

1. Hover on the XML list to select the XML list.
2. Click the border of the XML list to drag and drop it onto the chart. A Paste Special dialog appears.
3. Click OK to accept the default settings and add the data from the XML list to the chart.

After completing these steps, the resulting chart should look like the one in Figure 6.

Figure 6. View of the final chart with XML list

You can change the chart type to something other than a Line chart, such as Bar, Pie, Doughnut (these are not snacks). You can also change the format of the chart to something other than the default. However, the source data for the chart can remain the same. As Excel repopulates the XML list with new data, the chart adjusts its display accordingly because its source is the dynamically loaded XML list.

As an added bonus, a user can add their own data to the spreadsheet directly and watch the chart reflect their changes. Because the XML list has an insertion point, the user can click in the list and add new rows of data as needed. As a user adds new rows, the chart is automatically updated. This gives users the power of not only creating reports from immutable source data but also the flexibility of adding their own data. Programmatically, you can add data from an entirely separate source and then append it to the XML list data. This is the programmatic equivalent of the user adding more data by typing directly in the cells. The result is a system that lets you aggregate data from potentially multiple sources, and placing them in spreadsheet for more careful analysis.

Conclusion

Improvements in Microsoft Office Excel 2003 make it easier to work with native XML source data. You can import raw XML into a spreadsheet, place it in a specific location, and dynamically update the data for a chart. The power of XML maps is that they enable you to map a hierarchical XML schema with elements and attributes to specific ranges in a spreadsheet. These ranges then become more meaningful based on the schema that underlies the XML map. XML lists also make the data more meaningful by enabling Excel organize cell ranges by groups within the worksheet itself. You can manipulate these groups, more properly called lists, independently, and refer to them programmatically.

Furthermore, Excel 2003 includes new routines for importing XML that let you take advantage of the other advances. You can send newly imported data to an existing XML list by using the XML mapping technology. The imported data can overwrite or append the existing data.

Finally, you can designate an XML list as the data source for a chart. In this way, as the data in the XML list change, so does the display in the chart. These key features, XML maps, XML lists, XML importing, and XML lists as chart sources make it possible to do more with existing data and let's you reach out farther and deeper in the enterprise with Excel.