Creating Excel 2007 spreadsheets from XML

This directory contains sample code which shows how to create Excel 2007 spreadsheets from an XML input document.

The majority of the code was originally developed by Metrici Limited, as part of their Metrici Advisor product. These samples are significantly cut down from the original, and some errors may have crept in during this process.

The samples are offered as illustrations only, with no warranrt that they are fit for purpose.

If you require consultancy assistance to create similar solutions, please contact Andrew Clifford at andrew.clifford@minimalit.com. If you have any comments or suggestions for improvements to these samples, please also contact Andrew.

Comments

The code converts any XML file into a spreadsheet workbook in Office Open XML format that can be read natively in Excel 2007. You can read Excel 2007 in other versions of Excel by using the Microsoft Office Compatibility Pack. The compatibility pack can also be used with other products which can read earlier versions of Excel, such as OpenOffice.org.

The code only supports simple tabular data. Each sheet in the workbook represents a table of data, with rows and columns. If you want a more conventional spreadsheet, you will need a different approach, though this code may still provide some useful reference.

The structure of the code allows the tabular data to be formatted in other ways also. An example of formatting it as plain HTML is given.

Running the code

To run the code, you must have Java installed on your machine, and the Xalan XSLT processor. (Other XSLT should work. The first step uses some of the EXSLT functions, and you will need a XSLT processor that supports these to run the step.)

To install Xalan:

• Download the Xerxes-J distribution by following the instructions at http://www.apache.org/dyn/closer.cgi/xml/xalan-j

  You need the "latest binary release" in .zip format, such as xalan-j_2_7_1-bin.zip

• Unzip the downloaded file, and then copy the xalan.jar and serializer.jar files into this directory.

See run.bat for instructions on how to execute the code. (run.bat is a simple Windows batch file; Linux users should be able to work out how to run the code from this.)

Step 1 - Tabulate

The first step in the process is to convert an arbitrary XML file into an XML file of tables, each with rows and columns.

The tabulate.xsl stylesheet achieves this. It takes a specification of the output file in the tableSpecificationFileName parameter.

See Tabular Export for a description of the format of the tabulated data and its specification.

Notes:

• You can define significantly complicated tabulate specifications, with multiple tables. The sample is only very simple.
• You can add extension functions to tabulate.xsl to provide additional capabilities in the extraction.
• This generic process is relatively slow. If you are producing a large amount of data, you might want a more specific stylesheet.

Step 2 - Format as Excel data

This step formats tabular data into a single XML file that contains all the parts of the target Excel file. (The Excel 2007 file is a compressed collection of files. The output from this step is the content of those files, wrapped with <directory> and <file> elements.

Things to look out for in the stylesheet:

• The use of a limited number of styles: normal, bold, multi-line, and timestamp.
• The use of "inline strings" for text data. To save space, Excel creates a shared string table so that two occurences of the same string are stored only once. This is difficult to reproduce through XSLT (though it could be done). This stylesheet uses inline strings to avoid this.
• No attempt is made to calculate the numeric representation of timestamps. Timestamps are converted to a formula so that Excel itself calculates them. (This does mean that as soon as the file is opened, it is marked as "Changed", and the user will be asked to save the file when they exit.)

Step 3 - Compress

To produce the final file, you need to compress the data in the zip format.

You can do this in a number of different ways. The xml_to_dir.xsl stylesheet expands the Excel data to its constituent files, which you can then compress using a zip utility.

You can compress the Excel data file more directly using the ZIP file classes in the Java API. See XMLToZip.java for an example.

Other formats

The tabulated data can be converted into other formats. See tabulated_to_html.xsl for an example of creating a plain HTML document.