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, getOutputFile(), 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 = getSpreadsheetExtension(); // .xls or .ods
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. In the template spreadsheet you define style names for particular cell formats. Then you annotate the internal model with these style names to indicate how to format the generated output.

Template Spreadsheet

The first step is to create the template spreadsheet and assign the style names. AFC uses different mechanisms to assign style names for Excel and OpenOffice spreadsheets.

Excel .xls Template Spreadsheet

While Excel supports proper named cell styles (see Format → Style in Excel), AFC does not (it uses JExcelAPI to read .xls files, and JExcelAPI does not support named cell styles).

To work around this, AFC matches your internal model’s style names up with range names in the template spreadsheet. If there is a match, AFC uses a number of formatting attributes of the top left cell in the range to format the generated cell(s):

• 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
  • font attributes,
  • fore- and background color,
  • borders,
  • alignment,
  • indentation,
  • number and date formatting.

Beware: 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 because AFC applies the number and date formatting settings from the template cell.

Example (screenshot may be outdated as it is not regenerated with every build of AFC):

Here’s the example template again, this time showing the range names:

	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)

OpenOffice .ods Template Spreadsheet

With OpenOffice (and .ods in general), AFC supports proper cell styles. So instead of defining range names, you define proper named cell styles. This style information is copied from the template to the generated spreadsheet.

Example (screenshot may be outdated as it is not regenerated with every build of AFC):

Style Names In The Model

The second step is to annotate your internal spreadsheet model with style names (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();

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, getTemplatedOutputFile(), getTemplateFile() );

Again, this also works using streams:

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

SpreadsheetSaver.Config cfg = new SpreadsheetSaver.Config();
cfg.spreadsheet = s;
cfg.typeExtension = getSpreadsheetExtension(); // .xls or .ods
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):

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