Writing Spreadsheets in Elm

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 }

17 Likes

@jxxcarlson, I enjoyed this tour of your work. Where do you see this going next? Do you want to create a graphical spreadsheet app, or keep it as text?

Are you familiar with EtherCalc? It’s a browser-based spreadsheet app that supports multi-user simultaneous editing. I have used it to track of housing expenses shared between housemates, it’s not as polished as Excel but it works pretty well. I’m imagining using your library for this purpose, but I’d be afraid to impose a text-based spreadsheet format on people who are less computationally literate (i.e. familiar with Excel but not programmers).

Hi @cmart, thanks so much for your interest and comments. I’m not familiar with EtherCalc – looks nice though.

My motivation for creating this library was to make possible small calculations in documents written in some kind of markup language. It is still very much an experiment and could easily change – in fact, the current version uses a different formula language that v1.0.

My current plans are to work on making the library more capable – it has few functions / formula types now, and lacks an internal language for doing more complicated things.

Regarding a graphical interface, that is something that one should be able to do with any library that can represent spreadsheets and compute with them. The trick is to translate back and forth between whatever representation is used for the UI and the Spreadsheet representation.

The current setup is as below, where I’ve erased the semicolons, imagining that instead I am looking at the spreadsheet as presented in some graphical UI in a view mode where one sees all of the formulas:

   100        1.1          mul A1,A2
    120       1.4          mul B1,B2
    140       0.8          mul C1,C2
    -      add A2:C2       add A3:C3

But it is easy to use this format instead:

   100          1.1          =A1*A2
    120         1.4          =B1*A2
    140         0.8          =C1*C2
    -      =sum(A2:C2)    =sum(A3:C3)

To do so, you would change the function CellParser.parse. That is, you use the existing internal representation of a spreadsheet as Array2D Cell but you change the parser responsible for translating the external representation to the internal one. And of course you also change the function Cell.render that translates from the internal to the external representation.

I’ve had in mind to add such a parser, which would be more useful to the kind of thing that you envisage. In any case, my plans for the foreseeable future are to make the library better, perhaps (or perhaps not) going as far as building demo app to better show what it can do. If you have thoughts on the design of the library or the input language, I’d very much like to hear them.

Most people who have used a spreadsheet will be familiar with the Excel formula language, so if you can become compatible with its syntax (e.g. sum(A2:C2) instead of add A2:C2), the learning curve becomes shallower for a lot of people. That said, if you have ideas of how to make a fundamentally better input language which requires breaking from Excel’s syntax, then please don’t let me stop you!

Excel Format

Following the conversation above with @cmart , I’ve improved the API for the package so as to handle Excel format. There are now two parsers for mapping an external to an internal representation of a spreadsheet:

   CellParser.parse      : String -> Cell    -- Vanilla version
   CellParserExcel.parse : String -> Cell    -- Excel version

Suppose, for example, that we have a spreadsheet in Excel format:

    ss2 = """
    100;     1.1;      =A1*A2
    120;     1.4;      =B1*B2
    140;     0.8;      =C1*C2
    -;   =sum(A2:C2);  =sum(A3:C3)
    """

Then the pipeline

ss2  |> read CellParseExcel.parse |> eval  |> print

will evaluate the spreadsheet to

    100; 1.1; 110
    120; 1.4; 168
    140; 0.9; 126
    -;   3.4; 404

The functions read, eval, and print are defined in module Spreadsheet.

Remark. The intro to the package has some remarks on how one would go about using it for a GUI spreadsheet app. I have no plans to do this myself. My intention is to work to improve the library.

Oops, I forgot to expose the CellParser and CellParserExcel modules. Fixed now.

This topic was automatically closed 10 days after the last reply. New replies are no longer allowed.