Computation Mode

Different spreadsheet applications calculate some expressions slightly differently. The computation mode tells AFC which particular spreadsheet application to emulate.

Spreadsheets Loaded from Files

When you load a spreadsheet from a file, the computation mode is set automatically to match the format of the loaded file. So .xls will turn on Excel compatibility, .ods will turn on OpenOffice Calc compatibility, etc.

Example

Both Excel and OpenOffice handle dates as numbers of days since a particular fixed date. For example, January 23, 2007 is represented as 39105. But Excel incorrectly assumes that the year 1900 is a leap year. OpenOffice does not. That is why January 1, 1900 is represented as 1 in Excel and as 2 in OpenOffice.

AFC converts dates before March 1, 1900 to numbers the same way as the application that created that spreadsheet.

For .xls:

EngineBuilder builder = SpreadsheetCompiler.newEngineBuilder();
builder.loadSpreadsheet( new File( PATH, "DateConversion.xls" ) );
builder.setFactoryClass( Factory.class );
builder.createCellNamesFromRowTitles();
builder.bindAllByName();
Engine engine = builder.compile();
Factory factory = (Factory) engine.getComputationFactory();
Output output = factory.newInstance( new Input() );

assertEquals( 1.0, output.getNumberFromDate() );
assertEquals( 2.0, output.getNumberFromMilliseconds() );
assertEquals( date( 1900, Calendar.JANUARY, 1 ), output.getDateFromNumber() );
assertEquals( date( 1900, Calendar.JANUARY, 1 ).getTime(), output.getMillisecondsFromNumber() );

For .ods:

EngineBuilder builder = SpreadsheetCompiler.newEngineBuilder();
builder.loadSpreadsheet( new File( PATH, "DateConversion.ods" ) );
builder.setFactoryClass( Factory.class );
builder.createCellNamesFromRowTitles();
builder.bindAllByName();
Engine engine = builder.compile();
Factory factory = (Factory) engine.getComputationFactory();
Output output = factory.newInstance( new Input() );

assertEquals( 2.0, output.getNumberFromDate() );
assertEquals( 3.0, output.getNumberFromMilliseconds() );
assertEquals( date( 1899, Calendar.DECEMBER, 31 ), output.getDateFromNumber() );
assertEquals( date( 1899, Calendar.DECEMBER, 31 ).getTime(), output.getMillisecondsFromNumber() );

Spreadsheets Created by the Spreadsheet Builder

If an engine was compiled from a spreadsheet created in memory, then it uses Excel computation mode by default. This mode can be overriden:

EngineBuilder builder = SpreadsheetCompiler.newEngineBuilder();
SpreadsheetBuilder b = SpreadsheetCompiler.newSpreadsheetBuilder();

// Input cells
b.newCell();
b.nameCell( "Number" );
SpreadsheetBuilder.CellRef numberCellRef = b.currentCell();
b.newCell();
b.nameCell( "Date" );
SpreadsheetBuilder.CellRef dateCellRef = b.currentCell();
b.newCell();
b.nameCell( "DateInMilliseconds" );
SpreadsheetBuilder.CellRef msCellRef = b.currentCell();

// Output cells
b.newCell( b.ref( dateCellRef ) );
b.nameCell( "NumberFromDate" );
b.newCell( b.ref( msCellRef ) );
b.nameCell( "NumberFromMilliseconds" );
b.newCell( b.ref( numberCellRef ) );
b.nameCell( "DateFromNumber" );
b.newCell( b.ref( numberCellRef ) );
b.nameCell( "MillisecondsFromNumber" );

builder.setSpreadsheet( b.getSpreadsheet() );
builder.setFactoryClass( Factory.class );
builder.createCellNamesFromRowTitles();
builder.bindAllByName();
builder.setComputationMode( ComputationMode.OPEN_OFFICE_CALC );
Engine engine = builder.compile();
Factory factory = (Factory) engine.getComputationFactory();
Output output = factory.newInstance( new Input() );

assertEquals( 2.0, output.getNumberFromDate() );
assertEquals( 3.0, output.getNumberFromMilliseconds() );
assertEquals( date( 1899, Calendar.DECEMBER, 31 ), output.getDateFromNumber() );
assertEquals( date( 1899, Calendar.DECEMBER, 31 ).getTime(), output.getMillisecondsFromNumber() );