Creating Open XML (OOXML) Spreadsheet Documents

As Office Open XML (OOXML) gains more wide-spread adoption and popularity - and since it is now an ISO standard - developers will be interested in how easy it is to create Open XML documents directly in their applications, e.g. spreadsheet documents that are compatible with Excel 2007. Most approaches require quite a bit of hand-coding and worrying about the actual OpenXML specifications, but what I want to show you today on the XML Aficionado blog is a way to use MapForce to auto-generate all the source-code (for example in C#) that will produce the desired .xlsx document so that you can integrate it into your applications (and use it royalty-free within your organization).

I will use a very simple example to demonstrate how you can turn some raw sales data in an arbitrary XML format:

into a pretty business graph in Excel 2007:

For such a simple use-case you could, of course, simply open the XML file in Excel 2007 directly, but I am only using a simple example to illustrate the process. The true power of this approach is that you can easily work with very complex data in a visual and intuitive manner - and that you can auto-generate the source-code to implement this as part of your application to automate such processes.

So let's open MapForce and insert the XML data file into our working surface where we are going to define the mapping:

Next we are going to insert an OpenXML spreadsheet document into the work surface of our mapping project - we can either insert an empty spreadsheet, or we can use an example document that we have previously created in Excel to indicate what sheets and what data ranges or labels should be receiving our data:

Now it is time to define how the source XML data should be mapped to the target OpenXML document. This particular mapping is just one example - MapForce lets you map between any combination of XML, relational database, EDI, flat-file (e.g. legacy text files), and OpenXML spreadsheet documents. In our case we are going to convert from start-date/end-date ranges in the XML source to months in the OpenXML document and from states to regions:

Once you've defined the whole mapping, this is how your project will look in MapForce - note that underneath the blue-gradient working surface the "Mapping" tab is the one that is presently selected, because I've just defined my mapping between the input and output files:

To test my mapping - before I auto-generate my program code, I can click on the "Output" tab underneath the working surface, and MapForce opens up Excel 2007 embedded within the same application frame to show me the result that is produced by my mapping:

This Excel table is then used to produce the graph that I showed earlier.

Now I want to auto-generate code in C# for my data integration project that will automate this generation of Excel 2007 OpenXML documents, so the next step is to check the code-gen settings to ensure that I generate it for the correct development environment - in my case Visual Studio .NET 2008 - but MapForce supports many other environments and can also generate code in C++ or Java in addition to C#.

OK, now we are ready to generate code. All that is required is using the corresponding command on the File menu, and all the source-code files are placed in a designated output directory, and the corresponding solution file for Visual Studio is generated as well:

The auto-generated source-code can now be integrated into any application and can be used royalty-free within your organization to automate the creation of Open XML (OOXML) spreadsheet documents.

If you would like to experiment a bit more with this example yourself, you can find all the files used here in the MapForceExamples directory when you download the free 30-day evaluation version of MapForce.

Also, keep in mind that you can use Excel 2007 files (or any other OpenXML spreadsheet documents) in MapForce both as input and output files, so you can create data integration applications and mapping or conversion code for any possible scenario that involves OOXML spreadsheet data, XML, EDI, or relational databases.

WorldWide Telescope - pretty, but not revolutionary

Microsoft Research has launched a public beta of the WorldWide Telescope (WWT) this week, which has generated considerable buzz in the blogosphere - mainly because über-geek blogger Robert Scoble stated that it made him cry when he saw a preview earlier this year.

I just downloaded the beta version myself and it is indeed pretty. Think of Google Earth, but looking outward at the universe rather than at our planet here. You can scroll and zoom and explore and see the night sky in much more detail than most people have ever experienced in a planetarium. It has a detailed database of astronomical objects, including stars, planets, constellations, and galaxies. You can use a search function to find any celestial object, or you can use the locator pane that points out noteworthy objects in your current field of view.

And it comes with great guided tours - slides, pictures from different wavelength images, and narration - that give you expert insights into little corners of the universe you didn't know about. And it allows you to control your actual telescope to zoom in on the same object you are viewing on your computer. And it's free. So it's a nice educational tool, no doubt.

But does it make me cry?

Hardly, if you consider that it has all been done before. For the avid hobby astronomer such features have already been available for quite a while. Starry Night Software does exactly what the WWT does, i.e. it lets you explore the night sky and provides guided tours to various astronomical events, controls your telescope, and it has far more features than the WWT. The only difference is that you have to buy Starry Night on a CD/DVD and install it on your computer, whereas you use WWT like you are using Google Earth: with a thin client viewer and all the data resides on the web.

The one thing I do like about the WWT is that any tours created in the software are, of course, stored in XML format. Microsoft hasn't yet published the specifications or schema for them, but I was able to create a short tour myself and then edited it further with our XMLSpy XML Editor.

So what is so innovative about the WWT that it warrants such a buzz? All it does is apply the thin client viewer plus cloud database approach to an astronomy application. That is certainly not revolutionary - I'd say it's not even original. Why you need a research lab to do it, is beyond me. It sure is a pretty application, but it simply doesn't deserve the hype and attention.

Fifth undersea cable cut: Coincidence?

When I received an e-mail from my father a couple of weeks ago that a ship off the coast of Egypt had severed a fiber-optic undersea cable that carried Internet traffic between Europe, Africa, and Asia, I was, of course, curious to learn that it actually was the second cable being damaged in the area and that it already had a measurable effect on Internet traffic for some organizations: my father is editor-in-chief for Monatshefte Chemie (Chemical Monthly), and he told me that he had already noticed that Chemical Monthly was no longer receiving articles, submissions, or reviews from China, India, Iran, and Egypt.

Imagine my surprise, when I learned last week that a third cable was affected in the region. I was not the only one starting to think that a pattern might emerge here - especially after it became clear that no ships were in the area, which is clearly marked on charts as being a no-anchoring zone.

Even with three cables damaged there is still some value in applying reasonable doubt, and assuming that a natural phenomenon, increased dragging of anchors due to storms in the area, or other accidents might have been contributing factors.
Today, however, the count increased again, as we are learning that a fourth and fifth cable in the region have been damaged. The Khaleej Times has reported the following summary of all the cables involved in the outage:

"A total of five cables being operated by two submarine cable operators have been damaged with a fault in each.

These are SeaMeWe-4 (South East Asia-Middle East-Western Europe-4) near Penang, Malaysia, the FLAG Europe-Asia near Alexandria, FLAG near the Dubai coast, FALCON near Bandar Abbas in Iran and SeaMeWe-4, also near Alexandria."

For the locations of these undersea cables please see the map on the Engadget blog today, which also lists the date of the respective cuts that range from January 23 until today. FLAG also has an update on the status of their two cables on their website and a map showing the FALCON cable locations.

However, it appears that initial reports on Slashdot today that these outages have cut Iran entirely off the Internet are exaggerated and do not appear to be true.

So where does that leave us: the loss of five cables in just two weeks is an awful lot to be a coincidence. As a result, there is an explosion of conspiracy theories trying to explain this, and you can find some of them in the comments on Bruce Schneier's blog. However, as Robert Graham points out in the Errata Security blog today, there isn't necessarily a pattern here, because normally undersea cable outages are not reported widely, so what we are seeing might still be a normal statistical fluke.

So it remains to be seen over the next several days what is behind these cable failures, as we learn more about the repair of these cuts and get reports on what factors might have been contributed to the damage in the first place. Stay tuned to your favorite blog, news website, or check out TechMeme.

One thing is clear, however: the undersea cables are an important part of our global Internet infrastructure, as we are carrying about 90% of the international traffic over these fiber-optic submarine routes, whereas satellites account for just 10%. Nonetheless, traffic is presently being rerouted around those damaged areas and frequently has to take longer routes - sometimes via the US - resulting in lower connectivity ratings on the Internet Traffic Report.

Interesting, but of no commercial value

JP Rangaswami has written a really beautiful posting with the title “Interesting, but of no commercial value”: The problem with emerging social media tools on his confused of calcutta blog today.
The article is a well written tour through the history of computing and the resistance being put up against new and innovative technologies that have the potential to change the way we work, communicate, or do business.
I'll quote just the first few introductory paragraphs here:

I can remember a time when people thought e-mail was a complete waste of time. I can remember a time when spreadsheets and storyboarding software were similarly disdained. In fact, I can even remember a time when no senior executive would be seen dead near a computer. You know something? It wasn’t that long ago, maybe 20 years ago?

I can remember a time when people thought the internet was a complete waste of time. When browsers had no future, when search engines were nothing more than toys. It wasn’t that long ago that Google was something that a few people played with, and the rest thought…. that they were wasting time. I can remember a time when people thought eBay was a plaything, someplace
that people went….to waste time. I can even remember a time when packages marked Amazon or Fedex were unheard-of in enterprise mail trolleys. You know something? It wasn’t that long ago, maybe 10 years ago.

I can remember a time when people thought social media, software
and networks were a complete waste of time. When Facebookers were fools, Twitterers were twits, when even blogs and wikis and IM were viewed with deep suspicion, when everyone thought that the people who were using them…..were wasting time. You know something? It wasn’t that long ago. Maybe it’s still happening now.

I've been in software since 1983 and always was an early adopter, using "stuff" at the bleeding edge of technology. JP is absolutely spot on: at first these new technologies are being ridiculed by "the establishment" - and then they take over the world.
Read the full article on JP's blog.