ecently, I found myself in need of a way to share selected information from a spreadsheet with several distinct groups of people. This was a personal project, and I didn’t want to get too fancy. I thought about creating a web application, but decided that would be overkill. All I really wanted was a way to generate two or three static HTML pages every few days as new information came in.
I had created the spreadsheet on my home computer using StarOffice, Sun’s office suite software. The nice thing about StarOffice (besides the price) is that it’s based on OpenOffice, free, open-source software that supports the OpenDocument Format. In other words, the file format is not proprietary; it’s standard. Even better, it’s all based on XML, which means you can use XSLT to convert OpenOffice documents to other formats.
Here’s an overview of the conversion process for StarOffice:
- Create the XSL stylesheet. (This article assumes you’re familiar with XSL.)
- In StarOffice, add an entry for your stylesheet in the XML Filters list.
- Run the transformation by selecting your filter from the “File – Export” dialog.
|Figure 1. StarOffice XML Files: Open a StarOffice file using a utility such as 7-Zip to view the XML files inside.
Creating the XSL Stylesheet
Using the editor of your choice, create your XSL stylesheet. To do that, you’ll need to familiarize yourself with the document structure. You can find official documentation at OpenOffice.org. I found it easier just to look at the XSL stylesheets that came with StarOffice and poke around inside the document itself.
In my case, the document was a spreadsheet with an .ODS extension. An ODS file is actually a compressed file containing several XML files (see Figure 1), the most important of which is content.xml. If you open content.xml in a text editor or XML editor, the first thing you see is a long list of namespaces. Copy those into your stylesheet, as shown in Listing 1.
Next, take a look at the elements that make up your document. In a spreadsheet, there’s a table:table element for each sheet, a table:table-row element for each row, and a table:table-cell element for each cell.
How you select the cells you want will vary. There’s no easy way to select a defined range of cells. To get around that, I created a “row group” in my spreadsheet that included all the rows I was interested in (see Figure 2). A row group is represented as a table:table-row-group element, which is easy to select in your stylesheet.
When you’ve selected the correct rows, you can do all the usual things with your data that make XSL so useful. For example, my stylesheet does the following:
- Sorts by date rather than title
- Excludes some columns from the display
- Uses a different background color for some rows, based on a column value
- Creates an HTML link for each row, if a URL is available
- Displays row numbers
The resulting HTML file provides all the information the original spreadsheet does, but in two columns instead of four. Figure 3 shows the output HTML.
Adding an XML Filter Entry
When you’ve created your stylesheet, you need to add it to StarOffice’s list of XML filters. Select “Tools – XML Filter Settings” from the menu and click “New” to display the “New Filter” dialog. Fill in the information about your filter. The field for the filename of your stylesheet is on the Transformation tab. When you’re done, click OK. Figure 4 shows the output HTML.
Running the Transformation
Now you’re ready to export your StarOffice file to another format using XSLT. Select “File – Export” from the menu. You should see your new file type (for example, “Poems By Date”) in the dropdown list.
But What About Testing?
Exporting again and again is not the most efficient way to test your code. It’s better to test your stylesheet in an XML editor, if you can. I loaded my stylesheet, along with a copy of content.xml, into XMLSpy. In XMLSpy, you can easily assign a stylesheet to an XML file and press F10 to test the transformation (see Figure 5).
The example stylesheet I’ve included with this article is a bit simpler than the ones I actually used for my project, but it’s a starting point.