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
Figure 2. Row Group: One way of selecting rows in a stylesheet is to reference a row group defined in the spreadsheet.
Figure 3. Output HTML from XSLT: In this case, the output format was HTML, but XSLT can be used for other formats, too.
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.