Help

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!

10 comments:
 
30. Jun 2010, 23:44 CET | Link
Arbi Sookazian

@Excel(implementation="jxl") is not type-safe? Reminds me of @Name("foo") in Seam 2...

 
01. Jul 2010, 08:13 CET | Link
Arbi Sookazian wrote on Jun 30, 2010 17:44:
@Excel(implementation="jxl") is not type-safe? Reminds me of @Name("foo") in Seam 2...

OK. It should essentially be an enum at that level but when used in xhtml-templates it would have to be a string (that is then converted to the enum-equivalent before entering the processing stage)

 
08. Jul 2010, 14:34 CET | Link
Cloves

Whey the Coordinate class? Since by definition spreadsheets are tabular, isn't int row; int col enough?

There should be an option to expose the implementation object (something like getDelegate()) to do advanced manipulation.

A JSF independent declarative way to building such would also be nice for those that don't use JSF (Wicket, Vaadin, etc.) with CDI. Maybe just annotate the model with JAXB. One could do wonders with pure XML+Velocity.

 
08. Jul 2010, 22:13 CET | Link
Cloves wrote on Jul 08, 2010 08:34:
Whey the Coordinate class? Since by definition spreadsheets are tabular, isn't int row; int col enough? There should be an option to expose the implementation object (something like getDelegate()) to do advanced manipulation. A JSF independent declarative way to building such would also be nice for those that don't use JSF (Wicket, Vaadin, etc.) with CDI. Maybe just annotate the model with JAXB. One could do wonders with pure XML+Velocity.

Coordinate is one parameter to pass along, row-col pair is two ;-) There is also the IteratingCoordinate which encapsulates the nextRow() so that column jumps to a certain col and not 0 etc...

Yep, there will be a delegate exposed.

Still working on the UI-interface options.

 
09. Jul 2010, 07:26 CET | Link

We're missing the contents of a cell. Does it hold plain text, is it a hierarchy of objects (to support functions, images, etc)? The rest of it seems pretty good at first read. The JAXB suggestion is a good idea too (I imagine it would a different implementation).

 
16. Jul 2010, 17:06 CET | Link

I don't understand why we need this API, it seems remarkably similar to the JExcelAPI API for modeling spreadsheets.

 
19. Jul 2010, 08:28 CET | Link
Pete Muir wrote on Jul 16, 2010 11:06:
I don't understand why we need this API, it seems remarkably similar to the JExcelAPI API for modeling spreadsheets.

It serves as a neutral model without the JExcelAPI dependency. This way you can have different input (JavaBean model builders, xhtml pages, Wicket, JSF DataTables) that produce a model that can be freely modified and then have that model transformed to Excel, CSV, OpenOffice Spreadsheets or whatever.

11. Sep 2013, 07:30 CET | Link

Click HELP for text formatting instructions. Then edit this text and check the preview.

 
30. Aug 2014, 03:45 CET | Link
polo

Best friend successive good years, Gucci Shoes Factory, winter spent in Sanya, Monster Beats Outlet, so winter home, seems to be nowhere in sight, North Jackets Outlet, both a little rusty, Nike Air Max Shoes, but also very cordial, Burberry Outlet Online, forward to tonight, feather-like snow, MCM Bags Outlet, drifts underground, Coach Black Friday, looking at that the floc, Louis Vuitton Outlet, fly like snow Ling, Michael Kors Outlet Online, eloquent, Polo Outlet Online, flying in the sky, Cheap Canada Goose, limp walk downs, strange things, Marc Jacobs Outlet Online, I float thinking lianpian, North Outlet Online, poetic heart wenqing, not help surging surging, Coach Factory Outlet, so I borrowed the winter, Ralph Lauren UK, snows research ink, Longchamp Sacs Sortie, into the mind of inactivity, Michael Kors Outlet, the white snow everywhere, Monster Beats By Dre, wrapped in some different, Ralph Lauren Outlet, kind of scenery, Oakley Sunglaases Outlet, wherever the wind in the faces, of the people, Michael Kors Bags Outlet, who are very Huddled, could not help.

 
20. Nov 2014, 12:56 CET | Link
store