How to Use ACE4J Library

Moving Away from Spreadsheets

Suppose we need to transition a legacy Excel-based application to the Web and we want its web successor to be a table driven Java-based application with a Javascript-based UI. This is easily accomplished with the ACE4J library as shown below.

Excel-Based Car Inventory Application

Web Car Inventory Application

Example of Web Car Inventory front end, all calculations made through ACE4J web calcualtion engine, no excel

Excel-Based Car Inventory Application

Car Inventory is a fictitious Excel-based application that locates an available car model by specifying a desirable car make and using the XLOOKUP formula from the XLOOKUP Example published on our website. The selected car make and car model pairs can be stored in the Database.

This application is for illustration only.

Web Car Inventory Application Architecture

UI

The web Car Inventory application UI is represented by a screen with one input field and one output field. The selected UI topology aims to deliver the best possible user experience, which is relatively simple since HTML-based screen layouts are free from rigid Excel screen layout limitations.

Backend

Database

The web Car Inventory application uses two static tables to store its metadata and one transactional table to store its data. Input and output (formula) field names, along with their immutable attributes (such as field type and formula text), are stored in the first static table. Table array elements that form the car inventory are stored in the second static table. Car make and car model values are stored in the transactional table so that legacy data points created by the Excel-based Car Inventory application can be reused.

Services

The web Car Inventory application provides three services. They interact with the application UI by means of JSON objects serialization from, and deserialization into, the ACE4J CalculationContext class instances.

fiber_manual_record Metadata service

This service has no parameters.

It creates and returns a CalculationContext object based on the metadata it reads from the above mentioned static tables. In our case, the returned CalculationContext object will contain one InputField, one FormulaField, two TableArray objects, and an empty FormulaFieldsDescriptor object.

The corresponding JSON object is shown below:

JSON CalculationContext {6}
- computeFields {2}
  - car {1}
    - InputField {7}
      - computeFieldType : ALPHANUMERIC
      - value : null
      - errorCode : 0
      - error : false
      - displayValue :
      - rangeName : null
      - rangePosition : null
  - model_formula {1}
    - FormulaField {11}
      - computeFieldType : ALPHANUMERIC
      - formulaText : XLOOKUP(car, \"car_make\", \"model\")
      - value : null
      - errorCode : 0
      - operandInErrorName : null
      - formulaIterator : null
      - formulaFieldOverridden : false
      - error : false
      - displayValue :
      - rangeName : null
      - rangePosition : null
- tableArrays {2}
  - car_make {1}
    - table {8}
      - 0 [1]
        0 : PEUGEOT
      - 1 [1]
        0 : BMW
      - 2 [1]
        0 : ALFA ROMEO
      - 3 [1]
        0 : TOYOTA
      - 4 [1]
        0 : KIA
      - 5 [1]
        0 : VOLVO
      - 6 [1]
        0 : LAND ROVER
      - 7 [1]
        0 : FORD
  - model {1}
    - table {8}
      - 0 [1]
        0 : 1007
      - 1 [1]
        0 : X3
      - 2 [1]
        0 : GIULIA
      - 3 [1]
        0 : CAMRY
      - 4 [1]
        0 : SELTOS
      - 5 [1]
        0 : V90
      - 6 [1]
        0 : DEFENDER
      - 7 [1]
        0 : RANGER
- formulaFieldsDescriptor {7}
  - formulaFieldsDependencies [0]
    - (empty array)
  - formulaFieldsEvaluationOrder [0]
    - (empty array)
  - missingComputeFields [0]
    - (empty array)
  - computeFieldsInError [0]
    - (empty array)
  - error : false
  - missingComputeFieldsIndicator : false
  - computeFieldsInErrorIndicator : false
- udfClassName : null
- appName : MyApp
- recompileFormulas : false

fiber_manual_record Calculation service

This service uses a CalculationContext object as a parameter.

This parameter should contain InputField values (just one in our case) entered from the application UI. The Calculation service passes this object to the ACE4J evaluateAllFormulas() method, which proceeds with formula calculations.

It returns a CalculationContext object containing calculated formula field values (just one in our case).

The corresponding JSON object is shown below:

JSON CalculationContext {6}
- computeFields {2}
  - car {1}
    - InputField {7}
      - computeFieldType : ALPHANUMERIC
      - value : VOLVO
      - errorCode : 0
      - error : false
      - displayValue : VOLVO
      - rangeName : null
      - rangePosition : null
  - model_formula {1}
    - FormulaField {11}
      - computeFieldType : ALPHANUMERIC
      - formulaText : XLOOKUP(car, \"car_make\", \"model\")
      - value : V90
      - errorCode : 0
      - operandInErrorName : null
      - formulaIterator : null
      - formulaFieldOverridden : false
      - error : false
      - displayValue : V90
      - rangeName : null
      - rangePosition : null
- tableArrays {2}
  - car_make {1}
    - table {8}
      - 0 [1]
        0 : PEUGEOT
      - 1 [1]
        0 : BMW
      - 2 [1]
        0 : ALFA ROMEO
      - 3 [1]
        0 : TOYOTA
      - 4 [1]
        0 : KIA
      - 5 [1]
        0 : VOLVO
      - 6 [1]
        0 : LAND ROVER
      - 7 [1]
        0 : FORD
  - model {1}
    - table {8}
      - 0 [1]
        0 : 1007
      - 1 [1]
        0 : X3
      - 2 [1]
        0 : GIULIA
      - 3 [1]
        0 : CAMRY
      - 4 [1]
        0 : SELTOS
      - 5 [1]
        0 : V90
      - 6 [1]
        0 : DEFENDER
      - 7 [1]
        0 : >RANGER
- formulaFieldsDescriptor {7}
  - formulaFieldsDependencies [2]
    - car [1]
      - 0 : model_formula
    - model_formula [0]
      - (empty array)
  - formulaFieldsEvaluationOrder [1]
    - 0 : model_formula
  - missingComputeFields [0]
    - (empty array)
  - computeFieldsInError [0]
    - (empty array)
  - error : false
  - missingComputeFieldsIndicator : false
  - computeFieldsInErrorIndicator : false
- udfClassName : null
- appName : MyApp
- recompileFormulas : false

fiber_manual_record Save data service

This service uses a CalculationContext object as a parameter from which it obtains InputField and FormulaField values and stores them in the transactional table.

Application Workflow

As part of the application initialization phase, the application UI invokes the Metadata service to receive a JSON representation of a CalculationContext object containing the application metadata.

The first car make value entered by a user will be embedded into this object and passed to the Calculation service. A calculated car model value will be obtained from the Calculation service response and displayed on the screen. At this point, the application UI has at its disposal a JSON object representing a CalculationContext object in its complete configuration. which includes a FormulaFieldDescriptor component along with compiled formulas (just one in our case) .

The car make values entered by the user from this point on will be received by the Calculation service inside the CalculationContex objects configured for optimal evaluation performance.

When a user hits the SAVE button, the current car make and car model values will be embedded into the stored JSON object and passed to the Save data service to be saved into the Database.