Apache Jakarta and Beyond: A Java Programmeramp;#039;s Introduction [Electronic resources] نسخه متنی

15.4. Templating

Doing formatting from within code can require a great deal of effort, as the preceding section illustrated. A better solution would be to take the formatting from an existing spreadsheet and have the program populate any necessary data. This idea is called

templating. An end user can create a template from within Excel, adding a few special values that the program will use to determine how data should be formatted. When this template is processed, all formatting will be copied from the rows and cells with these special values, and data will be provided from an external source.

The most useful place from which data could be drawn is a database, and the easiest way to access a database is through OJB, the object-relational mapping tool discussed in Chapter 10. To use OJB the user must specify a class to populate. The program will allow the name of this class to be specified on either the command line or in the first cell of the first row of the template sheet.

Chapter 10. Specifying

com.awl.toolbook.chapter10.Artist

as the class name will populate the sheet with information about all artists, and adding name= "Unto Ashes" on the command line will restrict the data retrieved to just that artist, his or her albums, the tracks on those albums, and so on.

Next, syntax is needed to allow users to indicate how retrieved data should be placed in the spreadsheet. The convention will be that a string preceded and followed by two octothorps (#) will be considered a

token ^[5] and will be taken to be the name of a property of the object. So using the Artist bean ##name## would refer to the artist's name.

^[5] 5This convention follows one that was used at CapitalThinking and is due to Raphael Thiney.

Using this scheme, ##albums## would refer to the whole array of albums. Although this doesn't make sense as the value for a single cell, it makes perfect sense as a collection over which to repeat a set of rows. To clarify the scope of such iterations, start and end markers must be used and must be placed in the first column of rows. In this case these markers will be ##BEGIN:albums## and ##END:albums##. Every row between the rows with these markers will be repeated once for each album. Within that scope tokens will refer to the current album, so ##name## will refer to the album name, not the artist name. In addition, within this region ##TRacks## will be available to iterate over the set of tracks. A sample template is shown in Figure 15.4.

Because the template engine is large, it will presented over several Listings. Listing 15.7 shows the prelude, including the all-important imports.

Listing 15.7. Imports

package com.awl.toolbook.chapter15;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Iterator;
import java.beans.Beans;
// BeanUtils
import org.apache.commons.beanutils.PropertyUtils;
// POI
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.hssf.usermodel.*;
// OJB
import org.apache.ojb.broker.PersistenceBroker;
import org.apache.ojb.broker.PersistenceBrokerFactory;
import org.apache.ojb.broker.query.Criteria;
import org.apache.ojb.broker.query.Query;
import org.apache.ojb.broker.query.QueryByIdentity;
import org.apache.ojb.broker.query.QueryByCriteria;
// CLI
import org.apache.commons.cli.*;
// Log4J
import org.apache.log4j.*;
public class TemplateProcessor {
private Logger logger =
Logger.getLogger(
"com.awl.toolbook.chapter15.TemplateProcessor");

Chapter 8 provides this functionality exactly. In addition, CLI will be used as in previous chapters to handle command-line arguments and help messages. Finally, this program will handle many complex situations, so it's a good idea to log its progress so log4j is also imported. The first thing this class will do when constructed is get a logger for use throughout the class.

Listing 15.8 shows the option-handling code.

Listing 15.8. Option handling

private static Options makeOptions() {
Options options = new Options();
options.addOption(OptionBuilder
.withDescription(
"New sheet name (default=result)")
.hasArg()
.withArgName("sheet name")
.withLongOpt("sheet-name")
.create('n'));
options.addOption(OptionBuilder
.withDescription("Input file")
.hasArg()
.withArgName("input file")
.isRequired()
.withLongOpt("input-file")
.create('i'));
options.addOption(OptionBuilder
. withDescription(
"Output file (default=output.xls)")
.hasArg()
.withArgName("output file")
.withLongOpt("output-file")
.create('o'));
options.addOption(OptionBuilder
.withDescription("name of db class")
.hasArg()
.withArgName("class name")
.withLongOpt("class-name")
.create('c'));
options.addOption(OptionBuilder
.withArgName("property=value")
.hasArg()
.withValueSeparator()
.withDescription(
"Set a constraint in the DB object")
.create("D"));
return options;
}
public static void usage(Options options) {
HelpFormatter formatter = new HelpFormatter();
formatter.printHelp(
"TemplateProcessor",
options,
true);
}
private String inputFileName;
public String getInputFileName() {
return inputFileName;
}
public void setInputFileName(String inputFileName) {
this.inputFileName = inputFileName;
}
private String outputFileName = "output.xls";
public String getOutputFileName() {
return outputFileName;
}
public void setOutputFileName(String outputFileName) {
this.outputFileName = outputFileName;
}
private String sheetName = "result";
public String getSheetName() {return sheetName;}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
private String className = null;
public String getClassName() {return className;}
public void setClassName(String className) {
this.className = className;
}
private String[] constraints = new String[0];
public String[] getConstraints() {return constraints;}
public void setConstraints(String[] constraints) {
this.constraints = constraints;
}
public void processArgs(Options options,String args[])
throws Exception
{
CommandLineParser parser = new BasicParser();
CommandLine cmd = null;
try {
cmd = parser.parse(options,args);
} catch (Exception e) {
logger.error("Unable to parse command line",e);
throw e;
}
if(cmd.hasOption('n')) {
String val = cmd.getOptionValue('n');
logger.info("Set sheet name from args: " +
val);
setSheetName(val);
}
if(cmd.hasOption('c')) {
String val = cmd.getOptionValue('c');
logger.info("Set class name from args: " +
val);
setClassName(val);
}
if(cmd.hasOption('o')) {
String val = cmd.getOptionValue('o');
logger.info("Set output file from args: " +
val);
setOutputFileName(val);
}
if(cmd.hasOption('i')) {
String val = cmd.getOptionValue('i');
logger.info("Set input file from args: " +
val);
setInputFileName(val);
}
if(cmd.hasOption('D')) {
setConstraints(cmd.getOptionValues('D'));
}
}

Listing 15.8 follows patterns established previously. makeOptions() creates an Options object, and usage() uses this object to display a help message to the user. Next there are a number of properties that will be set from the command line. These are exposed as bean properties to make it easier for other programs to invoke TemplateProcessor. processArgs() processes the arguments and builds a CommandLine. Options are also logged.

Next, Listing 15.9 shows main() and other top-level processing.

Listing 15.9. Top-level processing

public TemplateProcessor() {}
public static void main(String args[])
throws Exception
{
Options options = makeOptions();
TemplateProcessor t = new TemplateProcessor();
try {
t.processArgs(options,args);
} catch (ParseException e) {
usage(options);
System.exit(-1);
}
t.process();
System.exit(0);
}
public void process() throws Exception {
logger.info("starting processing");
FileInputStream in =
new FileInputStream(inputFileName);
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook book  = new HSSFWorkbook(fs);
HSSFSheet sheet    = book.getSheetAt(0);
HSSFSheet newSheet = book.createSheet(sheetName);
short firstRow   = (short) sheet.getFirstRowNum();
short lastRow    = (short) sheet.getLastRowNum();
copySheetProperties(sheet,newSheet);
if(className == null) {
className = getDBClassName(sheet,firstRow++);
logger.info("Got class name from sheet" +
className);
}
Iterator iter    = getDBObjectIterator();
while(iter.hasNext()) {
logger.info("Processing block");
processBlock(iter.next(),
sheet,
newSheet,
(short) (firstRow),
lastRow);
}
FileOutputStream out =
new FileOutputStream(outputFileName);
book.write(out);
out.close();
}
private String getDBClassName(HSSFSheet sheet,
short firstRow)
{
HSSFRow row = sheet.getRow(firstRow);
return getFirstCellText(row);
}
private void copySheetProperties(HSSFSheet oldSheet,
HSSFSheet newSheet)
{
logger.debug("Copying sheet properties");
try {
for(int i=0;
i<oldSheet.getNumMergedRegions();
i++)
{
newSheet.addMergedRegion(
oldSheet.getMergedRegionAt(i));
logger.debug("Copied merge region" + i);
}
}   catch (Exception e) {
logger.warn("getNumMergedRegions threw null");
}
newSheet.setDefaultColumnWidth(
oldSheet.getDefaultColumnWidth());
newSheet.setDefaultRowHeight(
oldSheet.getDefaultRowHeight());
// newSheet.setHeader(oldSheet.getHeader());
// newSheet.setFooter(oldSheet.getFooter());
// Copy the column widths
HSSFRow row = oldSheet.getRow(
oldSheet.getFirstRowNum());
short firstCell = row.getFirstCellNum();
short lastCell = row.getLastCellNum();
for (short cellNum=firstCell;
cellNum<lastCell;
cellNum++)
{
HSSFCell cell = row.getCell(cellNum);
if(cell == null) {
continue;
}
short width = oldSheet.getColumnWidth(cellNum);
logger.debug("Set width of field" + cellNum +
" to " + width);
if(width > 100) {
newSheet.setColumnWidth(
cellNum,
width);
}
}
}

main() constructs a TemplateProcessor, has it evaluate the arguments, and then starts everything off by calling process().

process() opens the input file and gets the HSSFWorkbook, and from there the first sheet that will be assumed to be the template. process() also creates a new sheet, which will hold the populated template. Note that the output file will contain both the template sheet and the result. This is because a great deal of formatting and other information is stored with the workbook, and rather than copy all this into a new book, it is easier to create a new sheet that will automatically be able to use all this information.

Various properties of the template sheet are then copied into the new one. As can be seen from copySheetProperties(), these consist of such things as the merged regions and default sizes. The size of each cell is also copied, although this requires examining each cell in the first row. The header and footer are commented out in this method because they do not exist, despite being listed in the POI javadocs. Presumably they will be added in a future release.

Returning to process(), after the sheet has been set up, the set of objects is obtained with a call to getdBObjectIterator(), which will be shown following. For now just note that this method returns an iterator over a set of the class specified, matching any constraints that were specified. For each of these objects, processBlock() is called to populate a segment of the sheet. processBlock() is shown in Listing 15.10.

Listing 15.10. Processing a block

private void processBlock(Object obj,
HSSFSheet sheet,
HSSFSheet newSheet,
short firstRow,
short lastRow)
{
for(short rowNum=firstRow;
rowNum<=lastRow;
rowNum++)
{
HSSFRow row = sheet.getRow (rowNum);
if(row == null) {
logger.warn("Row"   + rowNum +" is null");
continue;
}
String iterateStart = getStartFlag (row);
boolean endMarker     = isEndMarker (row);
if(endMarker) {
// do nothing
} else if(iterateStart != null) {
logger.debug(
"Found start of an iteration:  " +
iterateStart);
short endRow =
findLastIterateRow(sheet,
iterateStart,
rowNum,
lastRow);
logger.debug("End of iteration at" +
endRow);
Object iterateObjects = null;
try {
iterateObjects =
PropertyUtils.getProperty(
obj,
iterateStart);
logger.debug(
"Found iteration objects");
}   catch (Exception e) {
logger.error(
"Unable to get iteration objects",
e);
}
try {
Object objArray[] =
(Object[]) iterateObjects;
for(int i=0;i<objArray.length;i++) {
processBlock(objArray[i],
sheet,
newSheet,
(short) (rowNum+1),
(short) (endRow-1));
}
rowNum = (short) (endRow);
}   catch (ClassCastException e) {
logger.info(
"Object collection is not an array");
}
try {
List objList =
(List) iterateObjects;
Iterator iter = objList.iterator();
while(iter.hasNext()) {
processBlock(iter.next(),
sheet,
newSheet,
(short) (rowNum+1),
(short) (endRow-1));
}
rowNum = (short) (endRow);
}   catch (ClassCastException e) {
logger.info(
"Object collection is not a list");
}
} else {
HSSFRow newRow =
newSheet.createRow(nextRow++);
processRow(obj,row,newRow);
}
}
}

processBlock() iterates over a set of rows from the original sheet. First, it uses getStartFlag() to determine whether the current row starts an iterationthat is, whether the first cell in the row looks like ##BEGIN:##. If it is, then iterateStart will be set to the name of the variable over which to iteratefor example, tracks. Similarly, isEndMarker() determines whether the current row is the end of an iteration.

End markers are simply skipped, so they do not end up in the result sheet.

If the current row is a start marker, then the corresponding end row is found. Then PropertyUtils from BeanUtils is used to obtain the set of objects. Currently this may take the form of either an array or a List, and both these options are examined and handled. Note that in either case proccessBlock will be called recursively. Because iterations can be nested arbitrarily, this is appropriate.

If the current row is neither the start nor the end of an iteration block, then it is a normal row and is handled by processRow(), which is shown in Listing 15.11.

Listing 15.11. Processing a single row

public short nextRow=0;
private void processRow(Object obj,
HSSFRow row,
HSSFRow newRow)
{
logger.debug("Processing a row");
newRow.setHeight(row.getHeight());
short firstCell = row.getFirstCellNum();
short lastCell = row.getLastCellNum();
for(short cellNum=firstCell;
cellNum<=lastCell;
cellNum++)
{
HSSFCell cell = row.getCell(cellNum);
if(cell == null)
continue;
HSSFCell newCell = newRow.createCell(cellNum);
processCell(obj,cell,newCell);
}
}
private void processCell(Object obj,
HSSFCell cell,
HSSFCell newCell)
{
logger.debug("Processing a cell");
newCell.setCellStyle(cell.getCellStyle());
switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ FORMULA:
logger.warn("Formula found");
break;
case HSSFCell.CELL_TYPE_ NUMERIC:
logger.debug("Copying numeric value");
newCell.setCellValue(
cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
String val = cell.getStringCellValue();
if(val.startsWith("##") &&
val.endsWith("##"))
{
int pos = val.indexOf(':');
if(pos == -1) {
pos = val.length()-2;
}
String prop = val.  substring(2,pos);
logger.debug("Processing variable: " +
prop);
Object value = null;
try {
value =
PropertyUtils.getProperty(obj,
prop);
logger.debug("Found value: " +
value);
} catch (Exception e) {
logger.error("Unable to get value",
e);
newCell.setCellValue("ERR");
}
if(value == null)  {
newCell.setCellValue(");
}   else if(value instanceof Number) {
newCell.setCellValue(
((Number) value).doubleValue());
}   else {
newCell.setCellValue(
value.toString());
}
}   else {
newCell.setCellValue(val);
}
break;
default:
System.out.println(
"Unknown or unhandled type");
break;
}
}

processRow() itself is simple; it just iterates over the set of cells by calling processCell(). processCell() determines the type of the cell and copies simple types into a new cell in the new row. If a cell contains a string, and the string looks like a token, then PropertyUtils is once again used to obtain the value.

That completes the major functionality of TemplateProcessor. Each of a set of objects is handled by processBlock(), which will repeatedly call processRow() to manage row data, which in turn will call processCell() to copy or populate each cell. The only remaining major piece is to to get the initial data used by process(), and this is shown in Listing 15.12.

Listing 15.12. Loading data

private Iterator getDBObjectIterator()
throws Exception
{
PersistenceBroker broker = null;
try {
broker =
PersistenceBrokerFactory.
defaultPersistenceBroker();
} catch (Exception t) {
logger.fatal("Unable to get broker");
throw t;
}
Class klass = null;
try {
klass = getClass() .
getClassLoader().
loadClass(className);
} catch (Exception e) {
logger.fatal("Unable to build DB object",e);
throw e;
}
Criteria criteria = new Criteria();
for(int i=0;i<constraints.length;i++) {
int pos      = constraints[i].indexOf('=');
String name = constraints[i].substring(0,pos);
String value = constraints[i].substring(pos+1);
logger.debug("Setting constraint" +
name + "to" + value);
criteria.addEqualTo(name,value);
}
Query query = new QueryByCriteria(klass,
criteria);
return broker.getIteratorByQuery(query);
}

Listing 15.12 is where all the OJB code lives. First, a PersistenceBroker is obtained in the usual way. Then the specified class is loaded. Next a Constraints object is constructed and populated from any constraints provided on the command line. In principal it would not be hard to extend this code to handle constraints such as greater-than and less-than in addition to equality. Finally, a QueryByCriteria is constructed, and the broker uses this to obtain the matching objects.

Finally, Listing 15.13 shows a number of utility methods.

Listing 15.13. Miscellaneous utilities

private String getStartFlag(HSSFRow row) {
HSSFCell cell = row.getCell((short) 0);
if(cell == null)
return null;
if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
String val = cell.getStringCellValue();
if(val.startsWith("##BEGIN:") &&
val.endsWith("##"))
{
return val.substring(8,val.length()-2);
}
}
return null;
}
private boolean isEndMarker(HSSFRow row) {
String text = getFirstCellText(row);
if(text == null) {
return false;
}
if(text.startsWith("##END:") &&
text.endsWith("##"))
{
return true;
}
return false;
}
private String getFirstCellText(HSSFRow row) {
HSSFCell cell = row.getCell((short) 0);
if(cell == null)
return null;
if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
return cell.getStringCellValue();
}
return null;
}
private short findLastIterateRow(HSSFSheet sheet,
String property,
short start,
short end)
{
String lookFor = "##END:" + property +"##";
for(short rowNum=start;rowNum<=end;rowNum++) {
HSSFRow row = sheet.getRow(rowNum);
if(row == null) {
continue;
}
HSSFCell cell = row.getCell((short) 0);
if(cell == null) {
continue;
}
if(cell.getCellType() ==
HSSFCell.CELL_TYPE_STRING)
{
String val = cell.getStringCellValue();
if(val.equals(lookFor)) {
return rowNum;
}
}
}
return end;
}

When TemplateProcessor is run on the sample template from Figure 15.4, the result is the spreadsheet shown in Figure 15.5.