AFC - Abacus Formula Compiler for Java

Generating An Initial Spreadsheet File

AFC supports generating initial spreadsheet files that, for instance, already setup cells for the most typically used inputs and outputs. Or, if you are using AFC without a spreadsheet file for formulas defined using a custom UI, you can jumpstart the switch to a full-blown spreadsheet file by generating a template for exactly the formula the user had defined so far.

Motivation

AFC has a set of default conventions for the layout of a spreadsheet which, when followed, ensure a certain consistency, and simplify the association of cells to inputs and outputs for your users (they do not have to use cell names). It will be much easier for them to get started with these conventions if you provide them with ready-made templates.

It also greatly helps your users if they can start with a spreadsheet file that implements the computation the system is currently configured to perform. (Of course, this is only possible if this computation can be expressed in terms of a spreadsheet.)

Now, while you could certainly create these initial files by hand in Excel and ship them with your application, AFC supports generating them at run-time. This has the following advantages:

  • The initial file can be generated in any of the spreadsheet file formats supported by AFC, as desired by the user.
  • If the current computation is already customizable (see using AFC without a spreadsheet file), then generating the initial file for this computation must be done at run-time.

Generating The Internal Model

AFC generates initial files from its internal spreadsheet model. So in order to generate one, we first need to build its model in memory. This is exactly the same process as is needed when using AFC without a spreadsheet file. See there for details.

Generating The File

Once you have the internal model set up, you can tell AFC to write out a spreadsheet file for it. There are two flavors of this API. The simpler version automatically deduces the the spreadsheet file type by the file name extension (.xls, .xsd, etc.), and always writes to a file:

Spreadsheet s = buildSpreadsheet();
SpreadsheetCompiler.saveSpreadsheet( s, GENFILE, null );

The other version lets you specify the extension explicitly and returns the generated spreadsheet as a stream:

Spreadsheet s = buildSpreadsheet();
ByteArrayOutputStream os = new ByteArrayOutputStream();

SpreadsheetSaver.Config cfg = new SpreadsheetSaver.Config();
cfg.spreadsheet = s;
cfg.typeExtension = ".xls";
cfg.outputStream = os;
SpreadsheetCompiler.newSpreadsheetSaver( cfg ).save();

As an example, the spreadsheet generated for the model described above (see here) looks like this:

A B
1 CustomerRebate 0.1
2 ArticleRebate 0.05
3
4 Rebate 4
=(B1+B2)

Formatting The File

A spreadsheet file generated by AFC is, by default, rather unappealing visually. You can improve its looks by supplying a template spreadsheet and annotating the internal model with style names. These names are matched to cell names defined in the template file. If there is a match, AFC uses a number of formatting attributes of the template cell to format the cell(s) generated for the corresponding internal model element.

More specifically, a styled row will pick up the row height. A styled cell will pick up the column width (last styled cell in a column wins) and general formatting like

  • font attributes,
  • fore- and background color,
  • borders,
  • alignment, and
  • indentation.

This list just gives an idea. The actual formatting transferred depends on the capabilities of the target file format.

Styles In The Model

To generate a templated initial file, you first need to build your spreadsheet model with styled elements (this code also demonstrates the use of call chaining to avoid having to repeat the builder variable all the time):

final String CAPTION = "Caption";
final String LBL = "Label";
final String IN = "InputValue";
final String IN_P = "PercentInputValue";
final String IN_D = "DateInputValue";
final String OUT_P = "PercentOutputValue";
final String INTER = "IntermediateValue";

SpreadsheetBuilder b = SpreadsheetCompiler.newSpreadsheetBuilder();

b.newCell().newCell( b.cst( "Styled" ) ).newCell( b.cst( "Plain" ) );

b.newRow().styleRow( CAPTION ).newCell( b.cst( "Inputs" ) ).styleCell( CAPTION ).newRow();
b.newCell( b.cst( "CustomerRebate" ) ).styleCell( LBL );
b.newCell( b.cst( 0.1 ) ).styleCell( IN_P );
b.newCell( b.cst( 0.1 ) );
SpreadsheetBuilder.CellRef cr = b.currentCell();

b.newRow();
b.newCell( b.cst( "ArticleRebate" ) ).styleCell( LBL );
b.newCell( b.cst( 0.05 ) ).styleCell( IN_P );
b.newCell( b.cst( 0.05 ) );
SpreadsheetBuilder.CellRef ar = b.currentCell();

final Calendar cal = new GregorianCalendar();
cal.clear();
cal.set( 2006, Calendar.OCTOBER, 29 );
Date orderDateSampleValue = cal.getTime();

b.newRow();
b.newCell( b.cst( "OrderDate" ) ).styleCell( LBL );
b.newCell( b.cst( orderDateSampleValue ) ).styleCell( IN_D );
b.newCell( b.cst( orderDateSampleValue ) );

b.newRow();
b.newCell( b.cst( "IsKeyAccount" ) ).styleCell( LBL );
b.newCell( b.cst( true ) ).styleCell( IN );
b.newCell( b.cst( true ) );

b.newRow().styleRow( CAPTION ).newCell( b.cst( "Outputs" ) ).styleCell( CAPTION ).newRow();
b.newCell( b.cst( "Rebate" ) ).styleCell( LBL );
b.newCell( b.op( Operator.PLUS, b.ref( cr ), b.ref( ar ) ) ).styleCell( OUT_P );

b.newRow().styleRow( CAPTION ).newCell( b.cst( "Intermediate Values" ) ).styleCell( CAPTION ).newRow();
b.newCell( b.cst( "(sample only)" ) ).styleCell( LBL );
b.newCell().styleCell( INTER );
b.newCell();

return b.getSpreadsheet();

Template Spreadsheet

Then, create a template spreadsheet. For every style you used, create a named cell in the spreadsheet that defines the formatting.

A B
1 Input Values
(Caption)
2 InputValue
(Label)
123.45
(InputValue)
3 DateInputValue 1/1/70
(DateInputValue)
4 PercentInputValue 25%
(PercentInputValue)
5 Output Values
6 OutputValue 123.45
(OutputValue)
7 PercentOutputValue 30%
(PercentOutputValue)
8 Intermediate Values
9 IntermediateValue 123.45
(IntermediateValue)

The rendering above is not a screenshot of the real Excel spreadsheet, but an HTML version generated automatically from the original spreadsheet. This rendering conveys only the essentials, like formulas, values, and – the important bit here – names. Since we are concerned about actual visual appearance, let’s look at a real screenshot of the test template spreadsheet (this image may be outdated as it is not regenerated with every build of AFC):

Screenshot of Template.xls

When applying a cell style, AFC applies as much as possible of the original style. In particular, it usually applies number and date formatting options as well. This means that if you apply a generic style, like InputValue below, to a date value, it will be shown as its internal numeric value rather than a date on the spreadsheet.

Saving With A Template

To save this model using a template, pass the name of the template spreadsheet file to the saveSpreadsheet method:

Spreadsheet s = buildTemplatedSpreadsheet();
SpreadsheetCompiler.saveSpreadsheet( s, GENTEMPLATEDFILE, TEMPLATEFILE );

Again, this also works using streams:

Spreadsheet s = buildTemplatedSpreadsheet();
ByteArrayOutputStream os = new ByteArrayOutputStream();
InputStream ts = new BufferedInputStream( new FileInputStream( TEMPLATEFILE ) );

SpreadsheetSaver.Config cfg = new SpreadsheetSaver.Config();
cfg.spreadsheet = s;
cfg.typeExtension = ".xls";
cfg.outputStream = os;
cfg.templateInputStream = ts;
SpreadsheetCompiler.newSpreadsheetSaver( cfg ).save();

Here’s a screenshot of the result (this image may be outdated as it is not regenerated with every build of AFC):

Screenshot of Result.xls

Note that unstyled cells use a default font and format, which depends on the spreadsheet format being written.