AFC - Abacus Formula Compiler for Java

Release Notes

Lists the new features, improvements, and fixes for each release of AFC. Note that the former name of AFC was SEJ (Spreadsheet Engine for Java). You may also want to look at:

Release 1.4

Changed!
  • All public APIs taking a String argument where they really should take a File have been dropped.
  • The internal implementation of Engine is no longer a subclass of ClassLoader. We warned you not to rely on this.
Better
  • During constant folding, AFC now caches values for cells it has already computed. This can speed up compilation times of certain sheets dramatically.
Fixed
  • Fixed parsing multisheet ranges in .xls files (issue 21).
  • Fixed processing of HLOOKUP and VLOOKUP fourth parameter (issue 24).
  • Fixed inferring type of IF and ExpressionNodeForSwitch (issue 25).
  • Fixed parsing .ods files with lots of trailing empty rows (issue 26).
  • Fixed a bug in rewriter/annotator interplay (issue 27).

Release 1.3

New
Better
Fixed
  • Fixed compatibility issues with OpenOffice.org Calc 3.0.
  • Fixed loading saved engines when AFC classes are loaded not by system classloader.
  • Fixed getting named ranges defined in the spreadsheet after defining an additional range name.
  • Fixed generating identifiers when compiling to readable code.
  • Fixed incorrect Row.getRowIndex() return value when parsing ODF spreadsheets.
  • Scripts now have UNIX executable file permisson in the build tools package.

Release 1.2

New
  • AFC can now load and save spreadsheets in the Microsoft Office Open XML .xlsx (MS Excel 2007) format.
Fixed
  • Fixed parsing off-sheet ranges.
  • Fixed parsing sheet names with hyphens and quotes.
  • Fixed displaying cell references in A1 format with more that 2 letters.
  • Fixed parsing percents on JDK 6 with de_CH locale.
  • Sheet names are displayed in cell and range names in error messages.

Release 1.1

New
Better
Fixed
  • Fixed parsing formulas without the oooc: prefix in OpenDocument spreadsheets. Such spreadsheets are saved by KOffice.
  • Fixed parsing OpenDocument spreadsheets with broken references.
  • OpenDocument spreadsheet loader now pays attention to Config.loadAllCellValues property.
  • INDEX() now does better range checking on 2-dimensional indexes, handles omitted indices better when set to constant 0, and can deal with bound column-index values for lookups into vertical vectors and vice versa.

Release 1.0.1

Fixed
  • Builds and tests properly on JDK 6.
  • Source .zip no longer contains .hg folder.
  • Fixed parsing quotes in string literals.
  • Fixed and improved site.
  • Shared formula parsing fixes for JExcelAPI.
Internal
  • Improved system tests.

Release 1.0

Changed!
  • You can no longer bind to input methods throwing checked exceptions. (We may re-enable this later when AFC checks that all output methods declare all of those checked exceptions.)
  • AFC no longer rounds final results to the precision specified in cell formats. If you want rounded results, use the ROUND() function. This only affects newly compiled engines.
  • AFC no longer treats errors conditions like #NUM! as zero. Instead, it signals an error.
  • Cell now implements Range. The contains(Cell) method was removed from Range, because the contains(Range) method can be used instead.
  • The Spreadsheet.NameDefinition, Spreadsheet.CellNameDefinition, and Spreadsheet.RangeNameDefinition interfaces were removed. The getDefinedName(String) method was removed from the Spreadsheet interface. Its functionality is now provided by the getDefinedNames() method which now returns Map<String, Spreadsheet.Range>.
New
  • AFC can now load spreadsheets in the OpenDocument .ods format (as written, for example, by OpenOffice.org Calc).
  • COVAR() is now supported.
  • SUMIF(), and COUNTIF() are now supported.
  • CONFIDENCE(), BETADIST(), BINOMDIST(), CHIDIST(), CHITEST(), FDIST(), GAMMADIST(), NEGBINOMDIST(), NORMDIST(), NORMSDIST(), LOGNORMSDIST(), NORMINV(), NORMSINV(), LOGINV(), POISSON(), TDIST(), BETAINV(), CHIINV(), EXPONDIST(), FINV(), FISHER(), FISHERINV(), FTEST(), GAMMAINV(), GAMMALN(), HYPGEOMDIST(), PEARSON(), RSQ(), STANDARDIZE(), TINV(), TTEST(), WEIBULL() and ZTEST() are now supported.
  • MEDIAN(), CORREL(), CRITBINOM(), LARGE(), MODE(), PERCENTILE(), PERCENTRANK(), PROB(), QUARTILE(), SMALL(), STDEVPA(), STEYX(), SUMX2MY2(), SUMX2PY2(), SUMXMY2(), TRIMMEAN(), INTERCEPT(), SLOPE(), FORECAST() and VARA() are now supported.
  • ACOSH(), ASINH(), ATANH(), SINH(), COSH(), TANH(), GEOMEAN(), HARMEAN(), PERMUT(), RAND(), and SIGN() are now supported.
  • VDB() is now supported
  • DCOUNTA(), DAVERAGE(), DVAR/P(), DSTDEV/P(), DGET() are now supported.
  • CLEAN() is now supported
  • ROMAN() and FIXED() are now supported
  • VALUE() and DSUM() et al. now handle date/time strings.
  • You can now tell AFC to compile to more readable code when decompiled.
  • DAYS360() is now supported.
  • CHAR(), CODE(), DOLLAR(), DATEVALUE(), and TIMEVALUE() are now supported
Better
  • We now support binding unboxed numeric types as inputs (double, long, int, etc.) to string-valued cells.
  • MATCH() is now supported for string comparisons.
  • When creating cell names from row titles, AFC now filters out typical illegal characters (spaces, for instance).
  • A few convenience variants of defineInputCell et al. make it easier to bind cells to methods. You no longer have to construct the CallFrame objects yourself, except for advanced binding.
Internal
  • The folding support (used to implement SUM etc.) has been rewritten completely. It can now handle multiple parallel vectors, multiple aggregators, and has much more streamlined code in the implementation.
  • We now verify the spreadsheet model loaded from our test spreadsheets against an external description (.yaml files). This ensures that test sheet changes are reflected in a diffable format.
  • The reference test sheets now have columns that check whether the expected and actual values truly match (in Excel). These columns are verified during reference tests (formulas and values).
  • You can set test-ref-update-yaml: true in build.properties to make reference tests silently update the .yaml files in-place. More streamlined when you detect changes using hg stat later on anyway.

Release 0.10.1

Changed!
New
Better
  • You can query the named cells still unbound to warn users about potential automatic by-name binding problems.

Release 0.10.0

Changed!
  • Changed the names of the runtimes to Runtime..._v2 and removed deprecated features.
    Your engines won’t run anymore. Recompile.
  • Changed the names of the predefined scaled BigDecimal and long numeric types.
  • Changed the way internal caching is controlled.
  • Changed the API for manual value conversions using a given locale. Now uses a Computation.Config instead.
New
  • BigDecimal with MathContext now supported. Older scaled BigDecimal no longer recommended and may be phased out in the future.
  • KURT(), SKEW(), STDEV(), and STDEVP() are now supported.
  • COUNTA() is now supported.
  • N() and T() are now supported.
  • ISTEXT(), ISNONTEXT(), and ISNUMBER() are now supported.
  • CEILING(), FLOOR(), ROUNDDOWN(), and ROUNDUP() are now supported.
  • CHOOSE(), LOOKUP(), HLOOKUP(), and VLOOKUP() are now supported.
  • You can optionally configure symbols (such as the decimal separator, the grouping separator, and so on) needed to format numbers.
  • Added option to load cell values of all cells, even computed ones, into the internal spreadsheet model. This is mainly useful to write automated tests (see EngineBuilder.setLoadAllCellValues and Spreadsheet.Cell.getValue).
Better
Fixed
  • Number to string conversions now all use the configured runtime locale. This means they are no longer computed at compile-time, even if constant

Release 0.9.1

New
Better
Fixed
  • The hacking docs have been updated to reflect the new project layout.
  • NOW() and TODAY() functions now return the same value for the same computation. This value can be reset when output class implements Resettable by invoking reset().
Internals
  • New build target compile added.
  • Pass a root pointer instead of the environment to subengines. This is in preparation for having the root engine contain things like the current computation’s value of NOW().

Release 0.9.0

Changed!
  • The packages have been renamed. The prefix sej. has become org.formulacompiler.. Old compiled engines are not compatible with this release!
  • The central API has changed. sej.runtime.SEJRuntime has become org.formulacompiler.runtime.FormulaRuntime. sej.SEJ has been split up into org.formulacompiler.compiler.FormulaCompiler and, derived from this, org.formulacompiler.spreadsheet.SpreadsheetCompiler.
  • The folder layout has changed. The code is now split up into a separate component per .jar file (despite the earlier reasons for not splitting the project up into modules). The components live in the components/ folder and each has its own lib/ and build/ folder. This shows clearly which AFC .jars need which supporting external .jars.
New
Better
  • Reorganized build targets to make build.xml more manageable.
  • Cited examples now serve as tripwires in the documentation.

Release 0.8.4

New
  • Mathematical functions: ACOS(), ASIN(), ATAN(), ATAN2(), COS(), DEGREES(), EVEN(), EXP(), INT(), LN(), LOG(), LOG10(), MOD(), ODD(), PI(), POWER(), RADIANS(), SIN(), SQRT(), TAN(), and TRUNC() are now supported.
  • Financial functions: DB(), DDB(), SLN(), SYD(), FV(), NPER(), PMT(), PV(), and RATE() are now supported.
  • String functions: PROPER(), REPT(), and TRIM() are now supported.
Better
  • For the ^ operator, as for the new POWER() function, invocations with non-integer exponents are converted to double operations.
  • Moved from hardwired service implementor constructors (in, for example, the class SEJ) to dynamically configured implementors. This mimics Java 6’s java.util.ServiceLoader, but also works on Java 5. For details, see the discussion in the dev journal and also the reasons for not splitting the project up into modules.
  • Updated build script so the bootstrap dummy implementations for code that gets generated later on is no longer necessary.
  • Macker dependency checking rules are now generated from a central component description in YAML using Ruby. This description is also used to generate a graph of the allowed component dependencies.
  • The build now runs dot and the Ruby scripts to regenerate graphics and config files.
  • INDEX() no longer generates a backing constant array if there are no constants.
  • Replaced the JFlex/CUP-based parser with one generated by JavaCC to work around a parser limit.

Release 0.8.3

New
Fixed
  • SEJ generates the InnerClass attribute needed for proper decompilation and required by the JVM spec when a factory or computation is based on a public static inner class.
Documented
  • Function reference now links to decompiled engines for example formulas.
  • Tutorial topics now contain decompiled engines to better show what SEJ does behind the scenes.

Release 0.8.2

Changed!
  • Dropped support for Operator.MIN, Operator.MAX, and Operator.NOOP. Use Function.MIN and Function.MAX instead.
  • Renamed internal rewrite functions _FOLD_1ST to _REDUCE and _FOLD_1STOK to _FOLD_OR_REDUCE. Simplified _REDUCE.
  • Dropped support for automatically aggregating outer sibling sections. You need to use an outer cell which aggregates the sibling section explicitly. This will probably be enabled more fully again later on.
New
  • NPV(), MIRR(), and IRR() are now supported (not yet over dynamic sections, though).
  • DSUM(), DPRODUCT(), DCOUNT(), DMIN(), DMAX() are now supported.
  • _MAKE_ARRAY(), _FOLD_ARRAY() added to internal rewriting language.
  • Added _DFOLD() to the internal expression AST, but not the rewriting language. This is because rewriting a database fold like DSUM() is too complex for a simple rewrite. The rewriting is done in a specialized Java class.
Better
  • The constant folder now uses the template methods in sej.internal.templates to evaluate templated operators and functions. This got rid of a lot of redundant code, and it ensures that newly added templates automatically get constant folding support.
  • Moved sej.internal.templates to sej.internal.model.templates.
  • Cleaned up the implementation of COUNT().
  • BigDecimal exponentiation now fails with an internal ArithmeticException “Rounding necessary” if the exponent is not an integer.
  • Introduced sej.util.New which provides generic static getters to leverage Java 5 type inference (as suggested by Josh Bloch in his new slides on “Effective Java Reloaded”).
Documented

Release 0.8.1

Better
  • Added support for naive string comparisons. By naive I mean that they do not handle the fancy code-page stuff in Excel yet. It’s basically String.compareToIgnoreCase().
  • Redid the developer’s journal so it indexes automatically. Added two entries.

Release 0.8.0

Changed!
  • Dropped support for JRE 1.4. This could be re-enabled with some effort, if anyone really needs it.
  • Dropped support for Operator.AND and Operator.OR. Use Function.AND and Function.OR instead.
New
Better
  • SpreadsheetBuilder.newCell() added for creating blank cells.
  • A number of automated tests compare generated .jar files against baseline versions to detect unwanted changes.
  • Documentation source now in the project in Rextile format. Used to be in a custom format and only resulting HTML files were versioned in the project.
  • Build script greatly streamlined and documented. Made it properly configurable/portable.
  • Added download for build tools package to the main page.
  • Source distribution now includes Eclipse project setup with custom Java formatting rules and all.
  • Added internal logging package for structured (indented) debug log output.
  • Temporary build artefacts now get written to temp/ rather than build/.
  • Constant folding support for AND and OR.
  • Eliminated redundant definitions of A1- and R1C1-style expression scanners.
Fixed
  • Excel spreadsheet saver now properly formats dates and booleans (added tests, too). Fixed issue with columns widths and default fonts not being copied from the template properly.
  • Code coverage ant targets no longer run the caching speed test as the speed varies too much when coverage tracking is enabled.
  • Use of _FOLD fixes a number of bugs with aggregation over nested sections.
  • SEARCH with wildcards had bugs.

Release 0.7.3

New
Better
  • SEJ now supports multiple sheets in an Excel workbook and resolves references across sheets properly.
  • The interface Spreadsheet.Cell now gives access to the formula text using getExpressionText().
  • Call chaining for spreadsheet builder methods (they return this instead of void).

Release 0.7.2

Better

Release 0.7.1

Better
  • Much improved (and tested) error reporting. Error messages should now contain all information for users to be able to pinpoint the source of a problem in their original spreadsheet.
  • Code coverage analysis added using cobertura and emma. Use ant cover and ant emma to run them, then check build/coverage/index.html or build/coverage/coverage.html for the results.
Fixed
  • MATCH now properly handles type arguments other than 1, 0, and -1.

Release 0.7.0

New
Better
  • Much improved Excel function tests, used as base for reference docs.
  • Improved error messages should give clearer indication of the source of a problem.
Fixed
  • Fixed-scale BigDecimal engines now return all values with proper scale.
  • Boolean values are treated as numbers in constant folder, too.
  • Enabled proper constant folding for AND and OR when they are aggregators.

Release 0.6.3

Better

Release 0.6.2

Better
Fixed
  • The Ant build script now runs the tutorial tests for both the current JRE and JRE 1.4. They fell through so far because their class names do not end in Test for easier citability.
  • As a consequence of the above, I have fixed the automatic type conversions so that they run on JRE 1.4 (which lacks, for instance, static Byte valueOf(byte) in class Byte).
  • Also as a consequence of the above, unscaled BigDecimal division is no longer supported on JRE 1.4.
  • Give a clearer error message when an outer cell references an inner cell of a section without aggregating it.

Release 0.6.1

Better
  • The sample spreadsheets in the documentation are now cited from the real spreadsheet files used in the automated tests (using JCite again). This ensures their correctness and improves the formatting.
  • The function MATCH is supported again. However, the last argument (the match type) must be constant, and the second argument (the match range) cannot reference a repeating section yet.
  • The function INDEX is now supported for one- and two-dimensional lookup. However, the range argument cannot reference a repeating section yet.

Release 0.6.0

Changed
  • In ComputationFactory, renamed newInstance() to newComputation().
New
  • Implemented input-only repeating sections. AND and OR are not yet supported as aggregators on them, though.
  • Implemented support for Excel functions TODAY() and COUNT(). (Although COUNT does not yet handle empty cells properly – this is because SEJ generally does not.)
Better
  • Documented, tested and completed the handling of the full set of allowed input method parameters.
  • Refactored the binding tutorial into more separate pages.
  • Added support for defining and referencing ranges to the SpreadsheetBuilder.
Fixed
  • Added some missing switch break statements discovered by Eclipse’s new check (should not ever trigger anyway, but still…)
  • Unbound output parameter combinations now throw IllegalArgumentException when there is now default implementation of the output method.

Release 0.5.4

New
  • Parametrized output methods are supported now. The interactive demo thus runs again, but does not yet look good because there are some info methods missing on the spreadsheet interface.
Better

Release 0.5.3

Better
  • SEJ now ships the new sej-<ver>-srcjar.zip, containing sej-src.jar and sej-doc.jar which contain, respectively, the entire source code and documentation for SEJ. Not included are the examples and test cases. These two .jar files make attaching source and docs in modern IDEs very easy.
  • Clarified behaviour differences between Excel and SEJ with respect to null input values. See tutorial on type conversions.
  • Implemented AND, OR, NOT, and comparisons outside of the test of an IF.
Fixed
  • Fixed parsing of cell/range names containing dots (.) in the R1C1-style parser. Added unit test for parsing names (only had a system test before which did not test the R1C1 parser).

Release 0.5.2

Fixed
  • SEJ should now accept all valid cell and range names according to Excel’s online help. In particular, Excel names may now contain dots (.) and do no longer have to start with an underscore or at least two alphabetic characters.

Release 0.5.1

Better
  • Type conversion is now documented.
  • All exceptions are now called ...Exception, not ...Error.
  • Can call static input methods.
Fixed
  • Removed the superfluous Engine*.jar files from the binary distribution.
  • Can use non-static local classes as inputs again.

Release 0.5.0

New
  • The new EngineBuilder supports the most typical use-case out of the box. The SpreadsheetByNameBinder and SpreadsheetNameCreator give more fine-grained control over this. This use-case is used to provide the motivation for using SEJ in the tutorial.
  • Separated the concept of a computation engine (collection of computation classes) from the concept of a computation factory. Engine is now the thing being loaded and saved. Factory creates now computation instances. The factory can be forced to implement an application-specific interface or base class, which makes SEJ even more non-intrusive on application code (see use-case above).
  • SEJ now converts between its internal numeric type and basically all numeric Java types in the input and output method result types. To support this, scaled long values in the interface must now be annotated with ScaledLong. (While already tested fairly rigorously, the full documentation for this feature is still missing.)
  • Saved engines are now normal .jar files, which can be inspected like any other .jar file. The generated classes are normal .class entries in the .jar file.
Better
  • I have reorganized the packages. Everything internal is now in sej.internal. Everything need for the loading and running of compiled engines is in sej.runtime, sej.internal.runtime, and sej.internal.bytecode.runtime. The compile-time public API is in sej.
  • I have rigorously trimmed the API again. Every non-trivial entity is now represented by an interface in the API, not a class. The central class sej.SEJ provides factory methods for all of these.
  • The fairly monolithic low-level interface sej.Compiler has been broken up to reflect the processing chain: sej.Spreadsheet, sej.SpreadsheetBinder, sej.SpreadsheetBinding, sej.SpreadsheetCompiler. Most of these components have a consistent instantiation protocol with a factory method in sej.SEJ and a configuration class called XY.Config which supplies parameters to new instances. This is in preparation of being able to expose the internal computation model as a lightweight alternative to the spreadsheet model.
  • New base error class, sej.runtime.SEJError, from which all others are derived. Better separation of errors into sej.SpreadsheetError, sej.CompilerError, and sej.runtime.EngineError.

Release 0.4.2

New
  • You can now generate engines that support internal caching of inputs, outputs and multiply referenced intermediate values.
Better
  • The BigDecimal numeric type now generates vastly more efficient constant values. They are preconstructed in private static final BigDecimal fields, using BigDecimal.valueOf( long, int ) wherever possible.

Release 0.4.1

New
Better
  • sej.NumericType now has support routines for value conversions.
  • sej.runtime is a new package holding the runtime support classes. They used to be in sej.engine.
  • Added automated tests for serialization and deserialization of engines, as well as the use of the runtime-only jar for deserialization.

Release 0.4.0

New
Better
  • Removed ASM dependency from the base Operator class.
  • Removed general expression interpreter support. Introduced special constant folder support instead (much faster).
Fixed
  • Exponentiation (A1^A2) and taking a percentage (A1%) now work when applied to input cells. Added this to the test suite.

Release 0.3.2

Better
  • SEJ ships with two new .jars, sej-jre1.4.jar and sej-runtime-jre1.4.jar, which have been converted from Java 5 to Java 1.4 by Retrotranslator. Use these .jars if you plan to deploy SEJ on the JRE 1.4. All unit tests are now run automatically using both the JDK 1.5 and a JRE 1.4 during builds to ensure compatibility.
  • I dropped Engine.Computation. You no longer have to subclass this thing when defining an output type. As a consequence, you can now define an output as an interface instead of a class.
  • I moved the saveTo() functionality from EngineFactory to Compiler. That way, compiled engines don’t need the interface to the compiler, making sej-runtime.jar a little smaller still.
  • SEJ accepts boolean input values and can return both boolean and Date values.
  • Moved all test and sample data to folders named org/formulacompiler for better compatibility with the source layout at Abacus.
Fixed
  • I fixed the contents of the distribution packages so the documentation style-sheets are included.
  • The run-time .jar now works (that is, contains all the required stuff).

Release 0.3.1

This minor release changes the name Compiler.NameSpace to Compiler.Section. Since we are no longer binding by name, the term NameSpace is no longer appropriate.

In addition Compiler.getRootNameSpace() has been shortened to Compiler.getRoot().

Release 0.3.0

This is the first release with the new API based on binding cells to native types. The interpreter has been dropped and replaced by a byte code compiler producing very fast code.