Monday, May 19, 2008

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:

SalesDataXML

into a pretty business graph in Excel 2007:

SalesDataGraph

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:

MapForceXMLfile

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:

MapForceExcelTarget

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:

SalesDataMapping

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:

MapForceScreenshot

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:

SalesDataExcelOutput

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#.

CSharpSetting

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:

MapForceCodeGen

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.

Microsoft/Yahoo/Facebook rumors

The blogosphere is buzzing today with rumors on Microsoft intending to buy just the Search portion of Yahoo and then buying Facebook. It's a strange world we are living in, but some argue that the move appears to make sense in a twisted sort-of way.

This should be an interesting week...

Sunday, May 18, 2008

Red Sox sweep Brewers 11-7

We got to see another great game of baseball at Fenway Park today. Josh Becket pitched seven innings followed by Delcarmen and Papelbon as the closer.

It was a great pleasure to watch the four home-runs: two by David Ortiz, one by Dustin Pedroia, and one by Kevin Youkilis. This performance by the Red Sox brought the inter-league series finale to a nice close.

We also had the great pleasure of being on the field during batting practice today, and were catching some balls while the Brewers warmed up. In doing so, we were close to the Red Sox bullpen and watched Clay Buchholz as he was working with the pitching coach:

It is the same Clay Buchholz who pitched a no-hitter in September 2007 as only his second start as a rookie for the Red Sox.

Tuesday, May 13, 2008

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.

Monday, May 12, 2008

Inbox Zero

This is a bit off-topic and might even be old news for some, but I recently stumbled across this video of a great e-mail productivity enhancing talk titled "Inbox Zero" by Merlin Mann. For further information, see his series of blog postings on the same topic on 43folders.com.

This very closely reflects my personal policy of dealing with e-mail, with the main difference being that once I'm done processing a message, I archive my e-mail into a variety of hierarchical folders instead of just one big archive folder - primarily for easier retrieval from a mobile device.

Another productivity tip for e-mail: keep your replies short and sweet. Maybe as short as five.sentenc.es? I haven't managed to adopt that one yet...

Sunday, May 11, 2008

Zoom H4 Portable Digital Audio Recorder Review

I recently bought the Zoom H4 Digital Audio Recorder for use both as a field stereo recorder for interviews during trade shows, and also as a 4-track recorder for a local a-cappella group.

H4_black This weekend I put the recorder through its paces and recorded a few different scenarios under varying background-noise levels and recording conditions, as well as different dynamic ranges. However, none were made in a studio setting, so these samples all reflect open spaces or a regular building without any sound-insulation or echo-cancellation. All recordings were done with the recorder held in hand. For even higher quality recordings, the device comes with a tripod mount.

Here are a few sample recordings that were done in 44.1 kHz sampling, 16-bit format and later converted to MP3 format after the editing process - use your favorite media player to listen to them. For the outdoor examples I walked through Marblehead this weekend and recorded a few different audio scenes: ocean waves on the beach and crashing against a seawall (MP3), docks at the town landing and harbor noises (MP3), patrons at the local Starbucks engaged in various conversations (MP3), children at the carnival in town (MP3), more carnival impressions (MP3). Some of these outdoor settings were especially difficult recording environments due to the wind today, which was quite strong at times. However, the noise guard that comes with the recorder worked well and reduced wind noises to a minimum.

For the indoor recordings, I have two samples of my son practicing on the drums: Seven Nation Army (MP3) as well as a drum pattern from some Linkin Park song (MP3). Those were particularly tricky to record because of the dynamic range, but in the end I found the right gain setting during recording and then the right mixing approach in the audio software. Last, but not least, to test the vocal recording-quality of the device my wife offered a short rendition of the chorus from the song Ukulele Lady (MP3) by Richard Whiting & Gus Kahn on her ukulele.

After returning from the field work, It was easily possible to transfer the audio data to the PC either using a USB cable, or by plugging the SD card into a card-reader attached to the PC - both methods worked fine.

Overall I'm very impressed with the results from this handy little audio recorder. It offers different quality settings, different gain levels, and has very nice built-in microphones in an X/Y pattern, so you get great spatial separation of the channels. From direct recording to MP3 for quick podcasting work to high-quality stereo recording with 96 kHz sampling in 24-bit resolution, the device has a broad range of applications and nicely delivers the corresponding quality.

There is just one glitch in the device that can at times be annoying: when turning the power-switch to the "On" position, the device normally takes a few seconds to boot, but sometimes the screen just stays dark. Another power cycle usually fixes the problem.

Also, the accompanying software that came with the recorder is an OEM-version of Cubase LE and it is over 2 years old, so it simply doesn't work on Vista. I tried to upgrade to the latest full version of Cubase 4, but the Steinberg website didn't offer any downloads or online upgrades - big mistake! However, I was able to simply use Audacity to edit the audio files produced and then encoded them to MP3 format using Lame

Thursday, May 1, 2008

Creating Open XML documents from XML and database data

The latest release 2008r2 of StyleVision gives users important new functionality for creating advanced stylesheets to publish XML and database data in Word 2007, which uses the new Open XML (OOXML) data format, as well as simpler processes for publishing the same source content in other formats. And, to further ease the transition for developers and designers working with OOXML, we have just reduced the price of StyleVision considerably. As adoption of Open XML increases, StyleVision developers will be ready with a powerful tool for publishing XML and database data in what is sure to be the most predominant end-user document format, now that Open XML has been approved as an ISO standard.

Here is how the process works:

  1. Open your existing XML document or connect to an existing relational database to populate the source pane in StyleVision:
    Sources
  2. Drag & drop elements from the source pane into the design pane and apply styles to them, thereby creating a meta stylesheet for producing the desired output formatting:
    DragDrop
  3. Click on one of the preview tabs underneath the design pane to preview the output in any of the supported output formats (Open XML for Word 2007, HTML, PDF, and RTF) - all outputs are automatically created from one and the same visual design:
    OpenXMLpreview
  4. Save the generated output file(s) as well as the specific stylesheets that have been auto-generated to render your data in the desired output formats again and again...

StyleVision can access data from database tables,views, or you can directly enter a SQL SELECT statement to query only for particular data from a database. This makes StyleVision ideal for flexible database reporting, too.

If you are interested in further details, you can read more about the new features of StyleVision 2008r2 here.