July 7, 2016

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 [#" "][#"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 [#" "][#"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! ;-)

14 comments:

  1. The shortest one is the "S-" spreadsheet of the K2 language, clocking at 2 lines. The K2 interpreter executable weighed at 240KB at the time, with no dependencies (other than Win32/Xlib .so), but alas is no longer available for download. Under http://nsl.com/papers/spreadsheet.htm , look for "Retractions: S-" near the end of the page.

    It "cheats" in that it uses the K2 interpreter to recompute on dependencies and parse formulas, but it also had no grid component. At the time, I compared K2 and Rebol, and K2 was about a thousand time faster on any benchmark - but nowadays, I suspect Red might actually be in the same ballpark.

    ReplyDelete
    Replies
    1. Thank you for the info and the link. I'm familiar with column-oriented databases, but I've never used K. Its database and vector processing oriented nature seems to qualify it as a DSL, rather than a general-purpose language, though, I don't know enough of it to have a conclusive opinion on that matter.

      For the spreadsheet you referenced, the advertised "minimal" version seems to take more than 2 lines: http://nsl.com/k/s/s_minus.k

      Moreover, the linking of input fields and variables is an implicit feature of K, so that's cheating a bit too. ;-)

      Also, as you mentioned, that "S-" spreadsheet only accepts expressions in K notation, not using the familiar Excel-style notation for formulas, as our demo does.

      Anyway, K is a powerful language with an impressive implementation, but I'm not fan of its extremely terse syntax (which is a total opposite of the Rebol/Red approach).

      Delete
    2. Here's the original two-line version)

      S..t:".[`D;(;);{. y};S[]];S[.;`f]:9$D[]"
      S:D:.+(`$'_ci 97+!26;26 99#,"");`show$`S

      (If you want one expression per line, it's 3 lines.) The s_minus.k script you link to also contains the original two-liner in a comment at the bottom.

      The "linking" in the K version is essentially the same as your reactive framework and the face auto-update feature; so that's hardly cheating in relation to Red.

      The only "cheat" in there is that a 'show' of a table automatically creates a grid-like UI, so no need to manually create and align text widgets for each cell.

      Finally, K is definitely a general purpose programmin language. It is geared towards data-intensive use-cases, so that's where it excels. But you can do your garden-variety programming as well. (Just have a look around Stevan Apter's nsl.com site to see all kinds of examples.)

      Yes, it is terse :)

      Delete
  2. Boy, this reminds of the examples which first drew me to Rebol. Keep up the great work Doc!

    ReplyDelete
  3. > plus an Excel-like expressions parsing/evaluating library
    > called expr.

    Tcl's EXPR is just like your use of MATH -- a way to have more standard math syntax with common math precedence rules. I suggest you take the remark that this is "cheating" out, since then you'd "cheat" as well ;-)

    ReplyDelete
    Replies
    1. You are misunderstanding what `math` function does. It simply ensures that multiplication and division get the priority over addition/subtraction, that's all. OTOH, Tcl's `expr` is a full C-like expressions evaluator, supporting dozens of features, including adding extra spaces around arguments, which we have to do manually in the above demo code. `expr` could qualify as a DSL (a very interesting one anyway).

      https://en.wikibooks.org/wiki/Tcl_Programming/expr
      http://www.tcl.tk/man/tcl/TclCmd/expr.htm

      So, nice try, but no, my remark is relevant and stays. ;-)

      Delete
    2. I have reformulated the sentence about the Tcl demo, to remove any unwanted negative connotation. Hope it looks better now.

      Delete
    3. I think I understand both MATH and EXPR fairly well and I am still convinced, that they fulfil the same purpose.

      Tcl's EXPR fits nicely into your description of MATH too: it rewrites infix to Tcl's regular prefix syntax (https://www.tcl.tk/man/tcl8.5/TclCmd/mathop.htm) and ensures common mathematical precedence rules. The Tcl spreadsheet demo also manually prepends variable references with $ for expr. Comparable to your whitespace pre-processing.

      Thanks for the reformulation. Better and fairer now.

      Delete
  4. I did not try rebol or red before but pasting your code gives me this: Script Error: rejoin has no value
    version used 0.6.1

    ReplyDelete
    Replies
    1. `rejoin` function is a recent addition, not present in 0.6.1. Please download the version on Download page under "Automated builds, master branch" section, or even better, the version linked under the demo source code:

      http://static.red-lang.org/dl/win/gui-console.exe

      Delete
  5. Reminds me of a project by Resolver One Systems which has since been retired. It was a python spread sheet written in python. It was also capable of publishing your spread sheets as a web app. Could see Red doing something like this.
    https://www.youtube.com/watch?v=t0fnPsc1s2c

    ReplyDelete

Fork me on GitHub