Native reactive spreadsheet in 17 LOC

After the release of our reactive framework a few days ago, we though it would be a good idea to implement the, often mentioned, spreadsheet model as a small demo, just to see how much it would take to do it in Red, with its current feature-set. Well, despite not having a grid component, it turns out that 17 LOC (of packed, but still readable code; down to 14 LOC and 1053 bytes if minified) is enough to make a spreadsheet-like demo with native widgets and realtime updating of dependent cells as-you-type! ;-)

Red [] L: charset "ABCDEFGHI" D: union N: charset "123456789" charset "0" 
repeat y 9 [repeat x 9 [col: either x = 1 [#"^(2028)"][#"A" + (x - 2)]
  append p: [] set ref: (to word! rejoin [col y - 1]) make face! [size: 90x24
    type:    pick [text field] header?: (y = 1) or (x = 1)
    offset:  -20x10 + as-pair ((x - 1) * size/x + 2) ((y - 1) * size/y + 1)
    text:    form case [y = 1 [col] x = 1 [y - 1] 'else [copy ""]]
    para:    make para! [align: pick [center right] header?]
    extra:   object [name: form ref formula: old: none]
    actors:  context [on-create: on-unfocus: function [f e][f/color: none
      if rel: f/extra/old [react/unlink rel 'all]
      if #"=" = first f/extra/formula: copy text: copy f/text [parse remove text
          [any [p: L N not ["/" skip not N] insert p " " insert "/data "
          | L skip | p: some D opt [dot some D] insert p " " insert " " | skip]]
        f/text: rejoin [f/extra/name "/data: any [math/safe [" text {] "#UND"]}]
       if f/data [any [react f/extra/old: f/data do f/data]]]]
      on-focus: func [f e][f/text: any [f/extra/formula f/text] f/color: yello]
]]]] view make face! [type: 'window text: "PicoSheet" size: 840x250 pane: p]

You can copy/paste the above code into the Red console for Windows, using the latest toolchain build (950 KB), or, better, using this prebuilt console version (247 KB, requires Windows 7+). Yeah, we still count in KB. ;-)

Features:

• 100% native widgets using our built-in GUI engine (no third-party libraries, Windows only for now, OSX and GTK are coming).
• Support for arbitrary Excel-style formulas (=A1+2*C3).
• Support for arbitrary Red code in formulas.
• Realtime updating of dependent cells as you type.
• While editing a formula, dependent cells will display #UND (for "undefined").
• If a formula is syntactically incorrect, #UND is displayed in the cell.
• Code is packed to reduce the LOC number, but limited to 82 characters per line (could fit on 77 if indentation is removed).
• It takes about 6 LOC to build the spreadsheet and 3 LOC to compile the formulas to Red expressions.
• One expression per line (expressions can have nested expressions), Red's header not counting as an expression for the purpose of this demo, nor the last expression at line 16 for setting the yellow color on focus, it is just there to make the animated captures easier to follow.
• Not using our VID dialect for GUI, such version is left as an exercise to the reader. ;-)

Here is a capture of how it works:

If you want to play with the same dataset, use this script.

This other session shows how to leverage the rich datatypes of Red, to play with, in the spreadsheet. It also shows that you can access the face objects properties from within the cells and modify them directly:

If you want to play with the same dataset, use this script.

Those captures were done on Windows, which is currently the most advanced GUI backend we have, our OSX and GTK backends are still a work in progress.

This demo was inspired by a similar one written in Tcl/tk which weights 30 LOC only, but takes advantage of a built-in grid component, and a C-like expressions parsing/evaluating library called expr. Though, it is still impressive to see what Tcl/tk can achieve. But the real king there, is the JS 220 bytes demo, even if it is more a testimony to the DOM capabilities (with a 100MB+ runtime behind) than JS expressivness. Nevertheless, Red's demo is the smallest one we could find in the native GUI category. Even the executable footprint is minimal. Once compiled (just insert Needs: View in the header in such case), it weights 655 KB, which can be further compressed down to just 221 KB, and as usual, zero dependency.

The above source code is very packed to fit in as less lines as possible, though it is still readable, as it is really hard to obfuscate Red code, even when you want to (mandatory spaces between tokens prevent from reaching C-like extremes). Therefore, you will hardly win a codegolf competition where each byte counts...unless you leverage Red's DSL abilities and write one optimized towards such goal.


How does it work?

It relies on our Red/View GUI engine, the reactive framework, the Parse DSL and the core Red language, which is, for those hearing about it for the first time, a Rebol language descendent, with one of the highest expressiveness among programming languages.

For the ones interested in the details of the above code, you can find a more readable version here and what follows is a detailed explanation. This is actually much simpler than it looks, here we go:

Line 1

 L: charset "ABCDEFGHI" D: union N: charset "123456789" charset "0" 

Skipping the Red [] header, it starts by defining a few bitsets, which will be used for the parsing operations. We create the D charset by combining N and "0", which save space.

Line 2

 repeat y 9 [repeat x 9 [col: either x = 1 [#"^(2028)"][#"A" + (x - 2)] 

A double loop is used to produce all the widgets needed. col is set to a space character if the column is a header, or to a letter starting from A to G. It will be used to create the cell names and the first row labels.

Line 3

 append p: [] set ref: (to word! rejoin [col y - 1]) make face! [size: 90x24 

Here we start building the faces which will be accumulated in p block. p: [] is a static allocation that conveniently avoids using a separate line to define p. The set ref: (to word! rejoin [col y - 1]) part is transparent, and let the face produced by make face! be appended to the p list. That transparent expression creates the cell name (in form of a capital letter denoting the column, and a number for the row), which is converted to a word, that gets set to the newly created face. Those words are necessary for supporting the spreadsheet formulas. Last, the opening block for the face definition leaves an option to append a nested expression, size definition being the shortest of all the other property definitions, is a good fit for that.

Line 4

 type:    pick [text field] header?: (y = 1) or (x = 1)

The face type can be a text for the first row/column and a field otherwise. The header? word will be useful further in the code, to indicate if the cell is a just label or a field. If you wonder why the use of or instead of the idiomatic any, it is to avoid an expensive conversion to logic!, as required by pick in such use-case.

Line 5

 offset:  -20x10 + as-pair ((x - 1) * size/x + 2) ((y - 1) * size/y + 1) 

The face position is calculated using the x and y values to set up a grid, which is sligtly moved to the left for (subjective) minor look improvement.

Line 6

 text:    form case [y = 1 [col] x = 1 [y - 1] 'else [copy ""]] 

The face content is set to col which contains column's label, or row number, or otherwise an empty string for input cells.

Line 7

 para:    make para! [align: pick [center right] header?] 

The face para object is just used there to center the header labels while keeping the cell content right-aligned.

Line 8

 extra:   object [name: form ref formula: old: none] 

The extra field is populated with an object which holds the state of the cell, namely:

• name: name of the cell, in string format for easier usage in the formulas compiler.
• formula: keeps a reference to the last entered formula, in text format, as typed by the user.
• old: keeps a reference of the last reaction set by the cell's formula (or none).

Line 9

 actors:  context [on-create: on-unfocus: function [f e][f/color: none 

The cell definition is almost done, just remain the event handlers, which we start defining from this line. on-create is called when the cell is created, ensuring that the preset content will be properly processed (in case of a formula) before showing it for the first time. on-unfocus is the main way to trigger the user's input processing. on-enter was not used, as the tabbing support is not working currently, so pressing Enter key will keep the focus on the same cell, causing unwanted side-effects which would take several lines to workaround. Once proper tabbing will be there, we could add it too. Last, as the function's body block is opening, we can squeeze in a short expression, which just resets the background color of the cell to its default.

Line 10

 if rel: f/extra/old [react/unlink rel 'all] 

We start with the hot stuff now. If a previous formula did produce a reaction, we first destroy it.

Line 11

 if #"=" = first f/extra/formula: copy text: copy f/text [parse remove text 

If a formula is detected, we copy first the content in text, which will be used for the transformation to a Red expression. As series are owned by deep reactors (a face! object is one), the copy will ensure that no object events are produced during the transformation steps. A second copy creates another instance of the input string to be referenced by extra/formula. In case it is not a formula (all that is done before the test succeeds, it will have no effect on the cell content (just wasting some memory, but that's not what we optimize for, in this exercise). Last, we start the transformation of the input text if it's a formula, using a Parse rule, applied to text with the leading equal sign removed.

Line 12

 [any [p: L N not ["/" skip not N] insert p " " insert "/data " 

The rule starts with a loop, the goal is to spot all the cell names and insert a space before it and /data just after it ("A1" becomes " A1/data "). The not ["/" skip not N] rule is there to avoid transforming cell names followed by a face property (e.g. A1/color). It works by ensuring that the second character after the slash is not a number, allowing to still transform inputs like A1/B2 (A1 divided by B2).

Line 13

 | L skip | p: some D opt [dot some D] insert p " " insert " " | skip]] 

If the input is not a cell name, we search for numbers (some D) including number with decimals (opt [dot some D]), so we can insert a space before and after (e.g "1+2" become " 1 + 2 "), in order to enforce Red's syntactic rules (as we will LOAD that string later). The | L skip part is there to avoid injecting spaces to numbers with leading signs ("-123" would not be touched). The final skip rule just skips every other character we are not interested in.

Line 14

 f/text: rejoin [f/extra/name "/data: any [math/safe [" text {] "#UND"]}] 

The transformation is almost done, the last step is decorating properly the text to generate the Red expression we are aiming for. First we enclose the resulting expression from last step in a math/safe [...] block. The math function just ensures that math precedence rules are enforced, while /safe option evaluates the code using attempt internally, so any error will be returned as a none value (and in such case, the "#UND" string is used). The result of that evaluation is set the the current cell. So for an input formula like: "=A1+B1" in C1 cell, we get as result of the transformation process:
 "C1/data: any [math/safe [ A1/data + B1/data ] "#UND"]", which is a LOADable expression in string format. But LOAD is not used in the demo code? Well, it is, thanks to a new feature in 0.6.1 release: by default the /text property of a field is synchronized in realtime with its /data property, using a LOAD call. If it fails, /data is set to none value. Conversely, setting /data will change /text value at once using a FORM call. Well, that's what the resulting expression is meant to leverage. ;-)

Line 15

 if f/data [any [react f/extra/old: f/data do f/data]]]] 

Now take a deep breath as we reach the crux of the matter. The previous line set f/text, which, at once created a LOADed version of that string, referred by f/data. If the LOADing failed, f/data would be set to none and then we just exit the event handler. Otherwise, we have something we can use as the input to REACT for trying to set up a new reactive relation for that cell. That's where the "/data" injection for the cell names in previous steps, becomes useful. Those path! values are statically analyzed by REACT to determine the reactive sources. Though, if no reactive source has been found in the expression (e.g. "=1+2" which would give [C1/data: any [math/safe [ 1 + 2 ]]] in f/data), REACT returns none and we then can simply evaluate the expression, which would assign the result to the current cell /data (hence to /text, making it visible to the user). If REACT succeeded, we have set a new reactive relation for that cell, and by default, the reaction is run once on creation, ensuring that our cell gets the correct visual value (by indirectly setting /data, as usual now). Moreover, we save in extra/old a reference to the expression we used for creating the reactive relation, as we'll need to destroy if the user inputs a new formula. If you're still following, at this point, congrats, you can consider yourself a master of both View and the reactive framework. ;-)

Line 16

 on-focus: func [f e][f/text: any [f/extra/formula f/text] f/color: yello] 

The second event handler is used to restore the saved formula (if any) in the cell, when the user gives it the focus. We also then set the background color to yellow, which is...well, like yellow color, but a bit less yellow...hence the truncated name for an otherwise anonymous color. (Carl, if you're reading this, I hope you appreciate my tap-dancing around your, sometimes, creative naming schemes. ;-))

Line 17

 ]]]] view make face! [type: 'window text: "PicoSheet" size: 840x250 pane: p] 

This last line is just creating a window, assigning the list of previously created labels and fields to the /pane property (face's children), then displaying it while entering an event loop using view call. That's all folks!

Last thoughts

We hope this demo and explanations were both entertaining and informative. Spreadsheet applications are not your common app, they are special. They are a unique combination of incredibly useful and powerful features, while at the same time being usable by pretty much anyone with basic computer skills. Many consider them as the culmination of our industrial software world, Microsoft's CEO itself declared a few days ago, that Excel was the best product his company ever made.

As Red allows you to create such software in a convenient and straightforward way, using native technologies, we hope this will inspire some of you to invest more time learning Red and to create some amazing software with it!

Beyond the simple fun provided by this demo, it also shows the potential of Red in the native GUI apps domain (we're just at 0.6.1, we have many more features planned and platforms to support). In the big struggle between native vs web solutions, you can expect Red to become, someday, an option to account for.

In the meantime... have fun with Red, as much as we do! ;-)