通过

Intro to Excel XML Part 3: Displaying your Data

  • 项目

At this point, we're getting really close to PDC and I can't wait. At PDC, I'm going to go through some examples of the new formats in all three applications (Word, Excel, and PowerPoint). I'll continue to talk about Office 2003 as well, but there will be more focus on the 12 formats from that point on. That's still a few weeks away though, so I figured today I would still focus on Office 2003. I want to write really quickly about Excel's ability to map XML structures as both the input and output of a spreadsheet. In the Intro #2, I showed how you could use an XSLT to transform your data into SpreadsheetML for rich display. Most folks who read that and knew about the XML support in Excel 2003 realized that there was a much easier way to do this. You can use the XML mapping functionality to completely skip the XSLT step, which makes it a lot easier.

Let's start with that same example we used in Part 2. Take this XML and save it to a file on your desktop:

<?xml version="1.0"?>
<p:PhoneBook xmlns:p="urn:somePhoneBook">
<p:Entry>
<p:FirstName>Brian</p:FirstName>
<p:LastName>Jones</p:LastName>
<p:Number>(425) 123-4567</p:Number>
</p:Entry>
<p:Entry>
<p:FirstName>Chad</p:FirstName>
<p:LastName>Rothschiller</p:LastName>
<p:Number>(425) 123-4567</p:Number>
</p:Entry>
</p:PhoneBook>

Map your schema

Open up a blank Excel spreadsheet (you need to be using a version of Excel 2003 that supports custom defined schema), and go to the Data menu. Find the XML flyout, and choose XML Source. The XML Source task pane should now be up on the side. It's currently blank because we haven't specified an XML schema to map yet. Click on the XML Maps... button and it will bring up a dialog that let's you specify the XML schema that you want to map. Click the Add... button and find the XML file you saved to your desktop. You will be notified that there isn't a schema but that Excel can infer a schema for you. In this example we're starting with an XML instance, so we want Excel to infer a schema. We could have also just started with a schema file if we had that. Go ahead and press OK, and you will now have a tree view of the inferred schema in the XML Source task pane.

Click on the node for the Entry element and drag it out onto the spreadsheet. This will map the child nodes and give them titles. After doing this, you've told Excel where you want the elements to be mapped to in the grid. You can change the titles of the columns if you want so that they have a more user friendly title. By default they have the namespace prefix and element name in the title.

Import your data

Now that the nodes have been mapped, you can tell Excel to import the data. Right click on the mapped region, navigate to the XML fly-out menu, and select Refresh XML data. That will import the data from your XML file. The region that the data was imported into has a blue border around it. This is a new feature in Excel 2003 called a "list". A list is a structured region in Excel that consists of repeating content. The list was automatically generated for us when we mapped the Entry element into the spreadsheet.

Now that we have our list mapped to the XML schema, we can also choose to import multiple XML files at once if you have a couple XML files that adhere to your schema. Just make a copy of the XML file you saved to the desktop, open it in notepad and make some changes. Now let's import both of the files. Right-click on the list and under the XML flyout choose Import... Now just select both of your XML files and hit OK. Now both sets of data are imported into the list.

Export the results

If you want to export your data, it's just as easy. Right click on the list again and this time under the XML flyout choose Export... You can choose to export to a brand new XML file, or to overwrite one of the files you imported.

This example shows how easy it is to bring your own XML data into Excel, work on it, and then output it back into it's original XML schema. Once common use I've seen of this functionality is that people will have two schemas. The first schema is used to import a huge data set that comes form a web service or some other external data source. Using the XML mapping functionality you can bring that data into Excel, and then run whatever models you want to on the data. The 2nd schema is used to map the results of the model in Excel. Map the result regions with the 2nd schema, and use that to export the results as XML. This allows Excel to serve as a very powerful transformation tool with rich UI. It's pretty cool

-Brian

Comments

  • Anonymous
    May 12, 2006
    It was very useful. Just met my requirements in time.
    Thanks a ton!
  • Anonymous
    May 15, 2006
    Is it possible to automate using macros or vbeditor, instead of manually dragging the elements from the XML Source pane?
  • Anonymous
    June 24, 2006
    PingBack from http://importersnow.info/microsoft-excel-500-point-question-on-importing-data-from-xml-or
  • Anonymous
    August 17, 2006

    hi
    is it possible to declare a cell formula in a xsd file ?
    if you insert a formula in a xml file
    (i.e    <Amount>=1000+2000</Amount>
    Excel recognize the formula with xml mapping ?

    best regards

    denis.benichou@ca-sam.com