In Relation To

Back to home

Spreadsheets in Seam 3

Posted by    |       Seam

While the community is pondering on the question as how to do JSF headless rendering for xhtml templates for content delivery, I started planing on how to make the Excel-centric spreadsheet module from Seam 2 more generic for the upcoming Seam 3 release. This blog is just a documented brainstorming session where I call for feedback so don't panic if you feel you didn't learn anything new once you finished reading.

The model

Excel-generation didn't really have that much of a model in Seam 2. The various UI tags worked pretty much straight against the rendering library, so the first step in a more generic solution would be a model for the spreadsheet. So, how does a spreadsheet application look like? The answer to that is of course It depends on the spreadsheet application but most of them have a collection of named worksheets that has a collection of cells that are formatted in various ways. Let's start with the workbook (I omit getters/setters for brevity): 

public class Workbook
{
   private List<Worksheet> worksheets = new ArrayList<Worksheet>();
   private List<FormattingRule> formattingRules = new ArrayList<FormattingRule>();
}

We'll talk about FormattingRules shortly. Now the worksheet: 

public class Worksheet
{
   private String name;
   private List<Cell> cells = new ArrayList<Cell>();
   private List<FormattingRule> formattingRules = new ArrayList<FormattingRule>();
}

and the cell: 

public class Cell
{
   private Coordinate coordinate;
   private CellSpan span;
   private CellFormatting formatting;
}

a Coordinate is a class for modeling a (column, row) tuple: 

public class Coordinate
{
   private int column;
   private int row;
}

and a CellSpan for modeling col/rowspans 

public class CellSpan
{
   private int columnSpan;
   private int rowSpan;
}

Formatting

Now for the formatting. This is modeled by the CellFormatting class: 

public class CellFormatting
{
   public enum Type { CASCADING, ABSOLUTE }
   public Type type = Type.CASCADING;
   // Lots of formatting objects for Fonts, Borders, Backgrounds etc...
}

Where the enum is used for marking if the formatting should be absolute or merged/cascaded with previous rules. What previous rules? That's what I said I'll get back to when I talked about the Workbook class. Workbooks and SpreadSheets can have a list of FormattingRule:s that are cascaded 

public interface FormattingRule
{
   public abstract boolean appliesTo(Cell cell);
   public abstract CellFormatting getCellFormatting();
}

So if you want all cells in the entire Workbook to have a particular font, you would place an implementation of a FormattingRule in the Workbook that appliesTo all cells and returns a formatting for that font. Then if you would like to have alternate rows with grey background in a certain worksheet, you would in that worksheet place a FormattingRule implementation that appliesTo cell.getCoordinate().getRow()s that are odd and return a formatting for grey background. For final tuning of a cell, it could also have some formatting that would be merged in for the final result.

Generating the model

You could of course assemble a Workbook model by hand but that would be a bit tedious so there would of course be builder classes that could transform a JSF datatable (with ICEfaces/RichFaces variants for bling-bling-support) to a Workbook. Another tool would be a Workbook from JavaBean builder that would take an Iterable<Foo> and a String[] of field names and generate a workbook from that (keeping track of Coordinates with an internal cursor) etc.

Importing and exporting

So what do we do with the model? We import and export then of course. We could read a workbook with 

public interface SpreadsheetReader
{
   public abstract Workbook readWorkbook(byte[] data);
}

and write one with 

public interface SpreadsheetWriter
{
   public abstract byte[] writeWorkbook(Workbook workbook);
   public abstract byte[] writeWorkbook(Workbook workbook, byte[] template);
}

This means that we could have different implementations that do different things. There could be implementations like 

@Inject SpreadsheetWriter write;

or 

@Inject @Excel SpreadsheetWriter write;

or 

@Inject @OpenOffice SpreadsheetWriter write;

or in the case of multiple implementations 

@Inject @Excel(implementation="jxl") SpreadsheetWriter write;

The output from the could then be put in a database, written to the user, mailed etc.

Open issues

Does it work? How about the EL-stuff in Seam 2 templates, is it all mappable to FormattingRules? How about usage of the rendered attribute? Any other stuff? Your feedback is appreciated!

Back to top