Friendly language for data manipulation inspired by Microsoft Excel ™


Keywords
clojure, clojurescript, excel, expression-evaluator, expression-parser, javascript
License
EPL-1.0

Documentation

Axel-f codecov CircleCI cljdoc badge

Expressions language for Clojure(Script) and JavaScript inspired by Microsoft Excel ™

Rationale

In some applications, the lion's share of business logic is concentrated in dynamic expressions. Often they are not even part of the code base and are stored in a database, files, or somewhere in the cloud. And sometimes these expressions need to be accessed non-technical personnel, like managers or call centers operators. Making them learn clojure or python is not fair. This library is designed to combine the best of two completely different worlds: the most understandable syntax from Excel and the speed of Clojure.

Installation

Clojure(Script)

  • Leiningen : [io.xapix/axel-f "2.0.11"]
  • Boot: (set-env! :dependencies #(conj % [io.xapix/axel-f "2.0.11"]))
  • deps.edn: {:deps {io.xapix/axel-f {:mvn/version "2.0.11"}}}

JavaScript

Please checkout the documentation for JavaScript package

TL;DR

(require '[axel-f.excel :as axel-f])

;; Compile Excel-like formula into executable function
(def foo (axel-f/compile "AVERAGE({1, 2, 3})"))

(foo)
;; => 2

((axel-f/compile "SUM(1, 2, AVERAGE({4,5,6}), foo.bar, foo.baz[*].x)")
            {:foo {:bar 1
                   :baz [{:x 5}
                         {:x 7}
                         {:x 8}]}})
;; => 29

;; metadata of compiled functions has information about used variables
(meta (axel-f/compile "SUM(1, 2, AVERAGE({4,5,6}), foo.bar, foo.baz[*].x)"))
;; => {:free-variables (("foo" "bar") ("foo" "baz" "*" "x")) ... }

Difference from Excel

  • No cell-references or reference operations.
  • Extra functions to work with lists such as MAP, FILTER, SORT, LENGTH, CONCAT
  • Lambda functions! FN(x, y, x + y) where all but last arguments are arglist, last - lambda's body with local bindings.

Object references

In addition to a formula, the run function can accept execution context as a second argument. Context can be any valid Clojure(Script) object. In the formula you can select the data from context by using object reference operators:

  • Dot reference operator for access nested data: foo.bar.baz
  • Single string can be used as a reference by using dot character as a prefix .'some string with spaces'
  • Array reference operator for access data in vector: foo[*].bar
    • foo[*][*] we support nested vectors (vector of vectors of vectors ...)
    • and objects in vectors foo[*].bar[*].baz
    • it is possible to use indexes to get the data inside of arrays: foo[1].bar
    • index can be computed on the fly: foo[SUM(x, 10)].bar
  • field reference can have any character except space, single/double quote, dot, comma, opening/closing square/round brackets and operators
    • fields with mentioned symbols inside must be quoted by wrapping into string literals: 'bar > baz'[0].foo or 'foo -> bar'.baz
    • some functions such as FILTER or SORT possibly can return nested data structure and this data can be used as a root reference object: FILTER(FN(item, item.x), _)[0].x with context [{:x 2} {:x 1} {:x 3}] returns 1 as expected.

Data types

  • Null as in Excel (NULL).
  • Booleans as in Excel (TRUE/FALSE). In addition axel-f understands True/False/true/false
  • Numbers (Integers, Floats, Exponential form)
  • Strings in double or single quotes. ('Some String', "Some String")
  • Arrays. Any data in curly brackets ({1, 2, TRUE})
  • Date
  • Excel Error types
  • Geospatial Data

Operators

Any expression can be used as an operand for any operator. axel-f has the same operator precedence as in Excel. To change a precendence of operators you can use round brackets ((2+2)*2 => 8)

Unary

  • Sign operator (-/+, eg. "-1") Can be used for coercing boolean into number (--TRUE => 1)
  • Percent operator (2.4%) axel-f uses float as replacement (2.4% => 0.024).

Binary

  • Additive operators (+/-)
  • Multiplicative operators (* and /)
  • Comparison operators
    • more (>)
    • more or equal (>=)
    • less (<)
    • less or equal (<=)
    • equal (=)
    • not equal (<>)
  • Exponential operator (^)
  • Concatenate operator (&)

Implemented functions

Please check the wiki page

Changes between 1.0.6 and 2.0.0

Namespaces (!BREAKING!)

  • axel-f.core => axle-f.excel , for ability to have separate (not just excel-like) extensions for axel-f core
  • axle-f.analyze was replaced by metadata attached to compiled AST

Working with collections (!BREAKING!)

  • MAP, FILTER, SORT no longer accept reference as a first argument. Use lambda function instead.
  • New function CONCAT to concatenate elements of multiple collections.

Extensions (!BREAKING!)

To be more data-driven def-excel-fn was replaced by providing extra context to compile function. It must be a map of token -> fn pairs.

Local scope

New special function WITH can be used to create local bindings.

E.g.

WITH(x, 1,
     y, 2,
     x + y)
=> 3

Lambdas also supported here

WITH(foo, FN(x, x + 2),
     MAP(foo, {5, 6, 7}))
=> [7, 8, 9]

Copyright and License

Copyright © 2018 Xapix GmbH, and contributors

Distributed under the Eclipse Public License, the same as Clojure.