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.
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.
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:
You need the "latest binary release" in .zip format, such as xalan-j_2_7_1-bin.zip
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.)
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:
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:
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.
The tabulated data can be converted into other formats. See tabulated_to_html.xsl for an example of creating a plain HTML document.