Today I published a new version of the library jxxcarlson/elm-spreadsheet. While still rudimentary, it has an improved API in which formulas are more like the ones we are familiar with from Excel. For a demo, see cayatex.lamdera.app. The package is here.
Here is an example of the raw text of a spreadsheet
100; 1.1; mul A1,A2
120; 1.4; mul B1,B2
140; 0.8; mul C1,C2
-; add A2:C2; add A3:C3
When parsed, evaluated and rendered, it gives you this:
100; 1.1; 110
120; 1.4; 168
140; 0.9; 126
-; 3.4; 404
The parse-eval-render pipeline can be run like this: ss |> read |> eval |> print where ss is the text of the spreadsheet.
Evaluating a spreadsheet
A spreadsheet is an Array2D of Cell, where a Cell consists of either data or a formula. A spreadsheet is fully evaluated if it has no formulas. The function below will evaluate the cell
at given row and column if it contains a formula, returning the updated spreadsheet. If the cell
contains data, the spreadsheet is returned unchanged.
evalCell : Int -> Int -> Cell -> Spreadsheet -> Spreadsheet
By mapping this function over a spreadsheet, we obtain a function
evalOnce : Spreadsheet -> Spreadsheet
Because formulas may refer to cells which themselves contain formulas, a single application of evalOnce may not fully evaluate a spreadsheet. As a remedy, one can apply evalOnce
repeatedly, keeping track of the number of unevaluated cells. When this number does not
change from one step to another, we call it quits, returning the result of the last iteration. The code
for eval is in the appendix.
This strategy – seeking a fixed point – was suggested to me by Brian Hicks. One can do more with this approach, for example, seeking approximate fixed points in the case of (for example) financial computations that are solved by iteration (or finding roots of functions, for something more geeky).
Not all spreadsheets can be fully evaluated. This is the case, for example, when there are circular references.
Note. One can imagine a more sophisticated evaluation strategy based upon the dependency graph of the formulas. However, the iterative method described here seems to work rather well.
Appendix: Code
type alias State = { notEvaluated : Int, sheet : Spreadsheet }
eval : Spreadsheet -> Spreadsheet
eval sheet =
eval_ { notEvaluated = Array2D.length sheet
, sheet = evalOnce sheet } |> .sheet
eval_ : State -> State
eval_ { notEvaluated, sheet } =
let
sheet2 =
evalOnce sheet
notEvaluated2 =
countNotEvaluated sheet2
in
if notEvaluated2 == notEvaluated then
{ notEvaluated = notEvaluated, sheet = sheet }
else
eval_ { notEvaluated = notEvaluated2, sheet = sheet2 }