19 August 2008

Can I have it in Excel?

By Andrew Clifford

The latest formats make it much easier to export data and reports to Excel, even if you do not use Microsoft technology and do not have the latest versions of Microsoft Office.

I am often critical of Microsoft because of the incompatibilities between one version of their software and the next. I always encourage people to look at free and open source alternatives, because in the long run they offer greater freedom at lower cost.

Despite this habitually negative tendency, I am impressed at the latest version of Excel, Excel 2007.

Most of the reviews that I have read dismiss Excel 2007 as "New interface, but no new functionality." I can not comment on this, because I have hardly used the new version at all. What has impressed me, is that Microsoft have redesigned the underlying file formats.

The file formats of earlier versions of Microsoft Office are very difficult to produce unless you use Microsoft software. The latest versions are based on XML (known as Office Open XML), and are available as published standards (ECMA 376 and ISO/IEC DIS 29500). This allows any software to write files that can be read easily in Microsoft Office. Microsoft's compatibility pack allows the new formats to be read in earlier versions of Microsoft Office, and to be imported into other products such as OpenOffice.org.

XML-based standards for office documents are not new. OpenOffice.org and many other office suites use the OpenDocument standard (ISO/IEC 26300). There has been criticism of how the formats from Microsoft were adopted as standards. However, in a corporate IT environment most people use Excel and we in IT are often asked to deliver data and reports in Excel. The new formats make this much easier.

We had this requirement in our Metrici Advisor product. The system provides standard reports, but a number of customers wanted to export data to Excel to integrate with other reporting.

Metrici Advisor is Java-based web application. It runs on Linux, and uses no components from Microsoft. The Microsoft components for producing Excel spreadsheets were not an option. Because Metrici Advisor's reporting is all XML-based, we thought we would try to produce Excel data to the new formats directly from the system.

Without going into technical details, this was relatively easy. We create the Excel data in three steps: extract data into a tabular format, convert the tabular data to Office Open XML, and then compress this to produce the final files. (This approach makes it easy to extract different data sets, and to support other formats such as the OpenDocument standard.)

Setting aside the technical details, and glossing over how Microsoft got their formats adopted as a standard, the new Microsoft Office formats are significant for corporate IT:

So, yes, you can have it in Excel.

Note. See XML to Excel sample code for more technical details.