Apache Jakarta and Beyond: A Java Programmeramp;#039;s Introduction [Electronic resources]

15.3. Creating Spreadsheets

The API to create spreadsheets is somewhat different from the one that reads spreadsheets. In some sense the creation API is a higher level API because it deals with conceptual entities such as cells and fonts instead of underlying implementation entities such as Records. The general technique for creating a spreadsheet is as follows:

Step 5 can be repeated to create multiple cells in a row, step 4 can be repeated to create multiple rows in a sheet, and so on. Listing 15.6 shows these ideas in action.

Listing 15.6. Creating a simple spreadsheet

package com.awl.toolbook.chapter15;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.*;
public class MakeSpreadsheet {
public static void main(String args[])
throws Exception
{
FileOutputStream out =
new FileOutputStream("sample.xls");
HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.createSheet("Sheet 1");
HSSFRow row   = null;
HSSFCell cell = null;
// Create the first row, with some headers
row = sheet.createRow((short) 0);
cell = row.createCell((short) 0);
cell.setCellValue("Name");
cell = row.createCell((short) 1);
cell.setCellValue("Value");
// Create the second row with some values
row = sheet.createRow((short) 1);
cell = row.createCell((short) 0);
cell.setCellValue("A");
cell = row.createCell((short) 1);
cell.setCellValue(88.8);
// Create the third row with some values
row = sheet.createRow((short) 2);
cell = row.createCell((short) 0);
cell.setCellValue("B");
cell = row.createCell((short) 1);
cell.setCellValue(67.90);
// Create the fourth row with a total
row = sheet.createRow((short) 3);
cell = row.createCell((short) 0);
cell.setCellValue("Total");
// Not yet implemented
// cell = row.createCell((short) 1);
// cell.setCellFormula("b2 + b3");
// Save and close
book.write(out);
out.close();
}
}

This is a bit tedious but straightforward and effective. After running Listing 15.6, sample.xls will contain the spreadsheet shown in Figure 15.2

It is of interest to note that when SimpleReader from Listing 15.4 is run on this spreadsheet, 100 records are reported. This dramatically illustrates the fact that records are a lower-level structure than cells, and it highlights the advantages of using Listing 15.2 reports a RecordFormatException after reading the last EOFRecord, possibly indicating some skew between the underlying reading and writing code.

15.3.1. Formatting

All of the formatting features available through Excel are also available through the APIs, although using them often requires a lot of code. Most formatting is done through the creation of HSSFCellStyle instances that contain information about fonts, colors, borders, and so on. These are then assigned to cells.

For example, to make the "name" and "value" labels created by Listing 15.6 bold, blue, 16 points, and centered both vertically and horizontally, the style would first be created with the following code:

HSSFCellStyle style = book.createCellStyle();
HSSFFont font       = book.createFont();
font.setFontHeightInPoints((short) 16);
font.setColor(HSSFColor.BLUE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setAlignment(style.ALIGN_CENTER);
style.setVerticalAlignment(style.VERTICAL_CENTER);

Both labels would then be set to use this style with

cell.setCellStyle(style);

In addition, to accommodate the larger font, the row should be made taller. This can be done with an operation on the row that takes a size in a unit called "twips," equivalent to 1/20th of a point.

row.setHeight((short) 0x249);

It is also possible to add borders, although once again the process is somewhat tedious. To enclose the whole region, the "name" cell needs borders at the top and left, and the "value" cell needs borders at the top and right. This means they can no longer use the same HSSFCellStyle, even though their styles are otherwise identical. Likewise, the "A" and "B" cells need borders only on the left and the numbers only on the right. The bottom cells need borders on the left and bottom, and right and bottom, respectively. This means different HSSFCellStyles must be created. Borders are added to styles with various setBorder() methods; for example, the following specifies the borders for the "name" cell:

style.setBorderLeft(style.BORDER_THICK);
style.setBorderTop(style.BORDER_THICK);

With these additions to Listing 15.6 the generated spreadsheet will resemble Figure 15.3.