Fishing for POI

Creating Excel or Word files from Java...

Reading existing Excel files

You can also read Excel files. If I wished to read an existing Excel file and add the data defined in Figure 1 to that spreadsheet I would need to add the lines 15 to 25 presented in Figure 3. Notice we now create a new HSSFWorkbook using a constructor that takes a POIFSFileSystem object. This means the workbook will be initialised with the data held in the "test1.xls" Excel file.

Formatting cells

We can also add formatting to cells within a spreadsheet using POI. For example, Figure 4 illustrates a simple program used to format one of the cells created in the program presented in Figure 1. Note we are reading the newly created Excel file into our workbook at line 18 (as described above). Once we have done that, we use the workbook to create a new font and a new style object in Lines 21 and 26. The font and style objects can be configured as required. In our case we are making the text centred (horizontally and vertically), making the foreground text dark blue and the background area light blue. Making the text bold and using the "Times New Roman" font.

The end result of running this second program is that the file created with the first program is modified so the first cell is now formatted as illustrated in Figure 5.

POI Limitations

POI is extremely powerful and allows the formatting and configuration of almost all Excel file formats. The only real limitations are that it currently does not support images; and that Excel has a limit on the number of styles defined within an Excel file (which means that it may be necessary to associate the styles with the workbook rather than the individual cell - the approach used in this example).

Uses of POI

POI is also used elsewhere within the Apache projects, most notably within Cocoon. It is also used within OpenOffice.org.

[Ed: In connection with OpenOffice 2.0, remember that this supports the Open Document format as well as using POI, so you may have the choice of not using MS Office formats at all. From the OpenOffice web site referenced:

“Microsoft's announcement in November last year that it recognises the market pressure for open-standard data formats is a welcome development. But why opt for a new standard?

“OpenDocument, which OpenOffice.org uses, is approved by OASIS - the standards body for XML data formats in business. OASIS is sponsored by all the leading names in IT, including Microsoft. In addition, OpenDocument was submitted to the International Organisation for Standardisation (ISO) on September 30, 2005, for ratification. OpenDocument is a genuine vendor-neutral, open-standard specification free from intellectual property encumbrances. All developers are free to work with it.”]