CE4JCE4J

Adaptive Calculation Engine for Java


#1 Modern Formula Language

Supported Operators and Excel Functions

Arithmetic Operations

+, -, *, /, ^, %

Comparison Operations

=, <>, <, <=, >, >=

Logical Functions

IF(), IFERROR(), OR(), AND(), NOT(), IFS(), SWITCH()

Lookup Functions

VLOOKUP(), HLOOKUP(), MATCH(), INDEX(), XMATCH(), XLOOKUP()

Math/Statistical Functions

MAX(), MAXIFS(), MIN(), MINIFS(), AVERAGE(), AVERAGEIFS(), MEDIAN(), MODE.SNGL(), RANK.EQ(), RANK.AVG(), SUM(), SUMIF(), SUMIFS(), COUNT(), COUNTA(), COUNTBLANK(), COUNTIF(), COUNTIFS()

Rounding Functions

ROUND(), ROUNDUP(), ROUNDDOWN()

Text Functions

CONCATENATE(), CONCAT(), &, EXACT(), FIND(), SEARCH(), RIGHT(), LEFT(), TRIM(), MID(), LEN(), TEXTJOIN(), SUBSTITUTE()

Math Functions

ABS(), POWER(), SQRT()

-

Explore

Why You Should Move from Excel to the Web

Security Risks Associated with Excel

Spreadsheets make organizations vulnerable to security risks through unregulated circulation of data, such as emailing attachments without encryption or password protection in addition to risk of manual error.

Excel Is Data Loss Prone

Excel files can be compromised due to accidental end-user deletion, file corruption, or malware infection. Recovering from such an event could take time and potentially result in data loss. Even the latest Files Restore feature available in Office 365 has serious limitations.

Excel Data Protection Shortcomings

Excel-based applications use password protection to enable read-only or editing access for different users on a file-level, work-book level, or work-sheet level of a document. The appropriate passwords are shared by the document owner with team members depending on the access they should be given. This runs the risk of accidentally sharing a password with unintended users.

Lack of Change Tracking in a Workbook

Change tracking for a workbook can be enabled as part of the Excel Shared Workbook feature. This feature has many limitations and has been replaced by co-authoring. Co-authoring, however, cannot track changes.

Excel Is Unfit for Agile Business Practices

Spreadsheets are often created by team members with very little IT experience. Eventually, spreadsheet files grow into highly personalised user developed applications that do not integrate well with other IT system components.

Web Applications Advantages

Web applications customize users’ views of specific data according to their roles, which are clearly defined and stored in the Database. This provides total security and data protection. Streamlined workflow is another notable example from a long list of web application advantages over Excel components.

Overview

Java API

The Adaptive Calculation Engine (ACE4J) is a Java API used to evaluate the formulas written in Microsoft Excel style. This engine provides the most popular Microsoft Excel functions which are frequently encountered in applications that require heavy calculations. The number of available formulas and features increases with every Adaptive Calculation Engine release based on feedback from our customers.

The ComputeField Concept

Adaptive Calculation Engine operates based on the concept of a ComputeField. The ComputeField Java interface is a high level representation of input and formula fields analogous to the Microsoft Excel input and formula cells. The InputField class implements the ComputeField interface. It is used to create input fields based on their type and value. The FormulaField class is a subclass of the InputField class and is used to create formula fields based on their type and formula text. The formulas referencing the Adaptive Calculation Engine lookup functions must use the TableArray class, analogous to the Microsoft Excel range of cells which form a table.

No Microsoft Excel Workbook

Adaptive Calculation Engine is completely INDEPENDENT from the Microsoft Excel Workbook paradigm. The engine uses a simple Java HashMap as a ComputeFields container. This HashMap consists of key-value pairs formed by a ComputeField name key and a ComputeField value entered by the user. The ComputeField names are arbitrary and do not follow Microsoft Excel conventions as long as they do not contain special characters (underscore and $ sign excluded).

Data Types

The ComputeFields and TableArray elements can be numeric, boolean, and string-based. The ComputeFieldType Java enum constants NUMERIC, BOOLEAN and ALPHANUMERIC specify this for ComputeFields. ComputeFieldType.ALPHANUMERIC allows for the use of special characters while ComputeFieldType.NUMERIC allows the primitive numeric data types as well as the Double, BigDecimal and BigInteger types. In the current Adaptive Calculation Engine release numeric ComputeFields and TableArray elements are internally converted into doubles.

HybridFormulaEvaluator Class

Adaptive Calculation Engine API is available via the static methods of the abstract Java class HybridFormulaEvaluator. These methods are used to compile and evaluate the formulas. To ensure the formula text syntax is correct, each formula must be compiled without errors prior to its evaluation. This will allow for optimal evaluation performance.

Formulas Compilation

The main compilation Java method, compileAllFormulas(), compiles all formulas of the FormulaFields residing in a ComputeFields HashMap. It creates and returns a FormulaFieldsDescriptor compilation summary consisting of the ordered evaluation sequences of the formulas, formula dependencies and encountered errors. The primary methods of formula evaluation, evaluateDependentFormulas() and evaluateAllFormulas(), obtain a FormulaFieldsDescriptor object from the CalculationContext parameter and use it as a data structure, which directs their execution.

Advantages

Microsoft Excel Like

Adaptive Calculation Engine provides all basic arithmetical and logical operations while being completely independent from the Microsoft Excel spreadsheet paradigm. Using the concept of an abstract Compute Field instead of an Excel cell, it supports a number of the most popular Microsoft Excel functions, such as VLOOKUP(), SUMIF(), CONCATENATE(), IFERROR(), and optimized IF().

Robust, Flexible and Fast

Adaptive Calculation Engine is capable of evaluating thousands of formulas within seconds while maintaining precision comparable to Microsoft Excel. At the same time nothing limits its ability to add new functionality without compromising its great performance.

Tool of Choice

Adaptive Calculation Engine is a tool of choice that allows for an easy and smooth transition from Microsoft Excel based applications, which rely on heavy calculations, to Java based Web applications. In fact it is easily integrated with any Java application because it is no more than a Java library, offering simple and comprehensive API and efficient error processing.