Automating XML Maps in Excel 2003
As readers of my blog know, I am a huge supporter of the XML features in Word and Excel version 2003. Is the implementation ideal? No. Are there things that I would have loved to see added in before we shipped? Of course. I bet the program managers for the products feel the same. But, shipping software is not a simple thing as it amounts to a lot more than just coming up with the ideal feature set. But, I digress.
In Excel, you can map cells to an XML Map (schema) that underlies the workbook. For example, if you have a bunch of cells for sales figures in a different months, you can map these to a schema with an element for SalesMonths. You do this by loading the schema and doing some clicking and dragging between cells and the XML map (see Map XML elements to a worksheet and Creating XML Mappings in Excel 2003).
Maps conceptually work like this:
But, this is a tedious effort in many cases. It would be better to automate this, to try and infer where the cells map to in the schema. Also, it would be great to infer a schema from the workbooks that we have built up over the years in previous versions of Excel.
We just published an article on MSDN with some code that automates the creation of a map and the mapping of cells to that map. See Part 1: Automating the XML Data Mapping Process in Excel 2003. This is the first of a three part deal. The second part has also been published, and I'll make a mention of that either later today or early tomorrow.
The writers of the article state, "Our goal is to create a flexible XML schema that is self-maintaining. We take a nontraditional approach and allow the spreadsheet layout to determine the structure of the XML schema."
The code works pretty well in that you highlight a table in your spreadsheet and run a procedure BuildGrid. Then, run another procedure called ProcessSheet. You will get a full XML Map in your workbook, and the cells in your worksheet will already be mapped correctly.
The article is OK writing, and it doesn't spell out for our users, "here's how you take this idea and implement it in your workbooks". With a little thought and a couple of tweaks, I had it all working. I noticed that it makes some assumptions about how the workbook cells are arranged as well. This ought to be addressed at some point. In all, though, it is an example of some creativing thinking around our XML support in these applications.
Rock Thought for the Day: I stumbled on this Seattle band, Nevermore. Their last album Enemies of Reality shows a maturity of thrash metal- reaching out in different ways with attempts to be more melodic and provide richer textures. Sure, there are speed solos throughout, and that is nearly always satisfying. The typical themes of loss, isolation, despair, confusion, yaddah, yaddah can be detected without much trouble. Truth be told, I can't listen to an entire album of pretty much any metal band because...frankly, I'm too happy. That said, this LP is a pleaser.
Rock On