Creating Spreadsheet Documents Using ODFDOM

The input for the program in this tutorial is a text file that contains weather observations for a 24-hour period. Each line contains an observation date, time of day, and temperature in degrees Celsius. The program’s output will be an OpenDocument spreadsheet file. Here is a partial screenshot of what the result will look like.

The program is written to be run from the command line program; you invoke it with a command like this:

The Structure of a Spreadsheet Document

In order for the following code to make sense, you need to know how a spreadsheet document is represented in OpenDocument. An ODF spreadsheet’s content.xml file contains an <office:spreadsheet> element. Each of its <table:table> children represents one worksheet within the document. Tables and their styles are built in this hierarchy:

• The <table:table> may have a style that describes its borders and page-break properties (this is because tables can appear in text documents as well as spreadsheets).
  • A series of <table:table-column> elements specifies the width of each of the table columns and the default style for cells in that column.
  • The column specifications are followed by a series of <table:table-row> elements that specify the height of each row.
    • Each row consists of <table:table-cell> elements. A cell has attributes that specify:
      • Its style name
      • A value attribute that tells what type of data the cell contains (string, numeric, date, time, formula, etc.)
      • The cell’s value in an internal format
      The cell may have a <text:p> child that gives the presentation form of the cell’s value. If none is given, some applications such as OpenOffice.org will evaluate the cell value and display it.

Variables

The program starts out by declaring variables that you need to accomplish the task. First, you need a variable to hold the name of the input file and output file, and the spreadsheet document.

As described in the previous tutorial, you also need variables for the main “access points” to the document:

Unlike the previous tutorial, this spreadsheet does not create any named styles, so it does not need the stylesOfficeStyles variable; it is just here for the sake of completeness.

In this program, you will be creating automatic styles. The end user of the document does not need to see the names of these styles, and the ODFDOM toolkit automatically generates unique style names for you. In order for the program to apply those styles to elements, though, it must keep track of their names:

These styles correspond to the styles mentioned in the table hierarchy. The last three styles are prefixed with noaa because the data came from the National Oceanic and Atmospheric Administration’s (NOAA) XML feed. It also helps to distinguish the names from ODF’s date and time format styles, which you will see in a while.

As mentioned previously, the content.xml file for a spreadsheet document has all the tables as a child of an <office:spreadsheet> element. That element wil be stored in:

This having been done, here is the main() method, which creates an application and runs it via the run() method:

Creating the Output Document

The setupOutputDocument() method starts by calling newTextDocument() to create an ODF text document from a template that is built into the library. Once you have the document, the method gets the the Document Object Model (a subclass of Document) for the content.xml and styles.xml file.

setupOutputDocument() then retrieves the automatic styles in content.xml and the named styles in styles.xml (or creates them if they don’t exist yet). It finishes by retrieving the <office:spreadsheet> element from the content DOM. All of the headings and paragraphs that make up the document’s content will be children of this element.

Clearing Content from the Output Document

The templates included in the ODFDOM toolkit have content in them; a newly-created spreadsheet document contains at least one worksheet. The cleanOutDocument() method gets rid of any existing worksheets, and is built along the same lines as described in the previous tutorial, so we won’t duplicate the code here.

How Table Styles Work

Before continuing with the part of the program that creates the styles, you need to know a bit about how styles and tables interact. In the previous tutorial, you created a style by doing these steps:

1. Create a style with a name and family.
2. Set the display name
3. Set the style’s properties.

For automatic styles, the sequence is similar:

1. Create a style; the name is generated by the toolkit.
2. Retrieve the internally generated name for future use.
3. Set the style’s properties.

This sequence works for setting up styles for table columns, table rows, and table cells that don’t require any formatting of their data. For cells that need data formatting, you have a couple of additional steps:

1. Create a “data style” that tells how the data should be formatted.
2. Create a style; the name is generated by the toolkit.
3. Retrieve the internally generated name for future use.
4. Specify that this new style should use the data style created in step 1.
5. Set the style’s properties.

Adding Styles

Let’s this process in action. The addAutomaticStyles() method starts by setting up styles for table columns (2.5cm wide), rows (0.5cm tall), and header cells (bold and centered).

The last line in the method calls the setFontWeight() method. This utility code, described in the previous tutorial, sets the font weight for western, asian, and complex scripts.

Now the method creates the data formatting styles and appends them to the automatic styles. The format strings for date and time are the same as those used in Java’s SimpleDateFormat class, and the format strings for numbers are the same as those used in Java’s DecimalFormat class.

The method concludes by creating the styles for the date, time, and temperature cells, linking them to the data styles with the setStyleDataStyleNameAttribute() call:

Processing the Input Document

As before, setting up the styles is the most difficult part of the program. Once they are set up, processing the input document is relatively simple.

1. Create the table object.
2. Add the table column descriptions.
3. Add a row with the headers.
4. For each line in the file:
   1. Separate the date, time, and temperature
   2. Create a row with three cells containing that data.
5. Insert a blank row.
6. Insert a row with a label and a formula for the average temperature.

Here is the code for steps 1 and 2:

Here is step 3. The createCell() method is a utility routine that avoids a lot of duplicated code.

And now, the file manipulation and loop. To save space, we present it here without the enclosing try/catch. The code is similar for each cell: (a) create the cell, (b) give it a style name, (c) set the data as either a date, time, or decimal value, and (d) specify what kind of value the cell contains.

The time of day needs some extra processing. Times in ODF are not really times of day; they are stored as a duration, in the ISO 8601 extended format PnYnMnDTnHnMnS. The convertToOdfTime() utility method converts from the hours/minutes/seconds format to the ODF duration format.

After the file is processed, the method adds a blank row and a row with the label “Avg. Temp” and the formula for the average temperature. The formula is given in ODF1.1 format.

Saving the Output Document

This is the easiest part of the program: only one line of actual code, surrounded by error handling.

The Entire Program

You may download the src directory for this program. This directory comes from a NetBeans project.