his article explores the design of a GUI spreadsheet, called Nano-Sheets. In its most basic form, the code for Nano-Sheets requires fewer than 1,500 bytes of code, which prints easily on a single sheet of paper. It is a very basic, but fully functional, spreadsheet engine with a working GUI.

Why is it so small?

• It is built using REBOL, a compact, portable, messaging language designed for the semantic exchange of information between people and machines.
• The code generates the main GUI worksheet dynamically.
• The REBOL language is available for direct use in cell formulas. That means you don’t have to write your own expression evaluator or macro language, yet you can still easily extend the worksheets with custom code.
• There are no features beyond basic spreadsheet functionality. This is an important design aspect. As you’ll see, Nano-Sheets can be extended with specific features as necessary in an application, but also inherits the rich macro language that REBOL provides for cell formulas.

Many modern software developers think a spreadsheet has to be a multi-Megabyte program whose empty data files are 10K or more in size. Even after adding features such as the ability to load and save files, HTML export, and some custom worksheet functions, Nano-Sheets will still be only about 10K of code (3.5K in “.rap” form?that is, compressed and encrypted?on REBOL/IOS. IOS stands for “Internet Operating System,” which provides secure distributed messaging between Reblets similar to Nano-Sheets. You do need to install REBOL/View (about a 300K download) to run the samples.

The goal here is not to rail against mainstream software bloat, or to write the smallest, most cryptic code possible; instead, it’s to create a useful tool. With Nano-Sheets, you can send the entire application, along with the custom worksheet, in an email. For clients that use REBOL IOS, you can publish Nano-Sheets once; subsequently, the clients can can all use the shared copy. Fixes or extensions to the engine require a download of only a few kilobytes, which makes the application accessible to users on dial-up or wireless connections as well as those using broadband or LAN connections. And because REBOL is a multi-platform tool, Nano-Sheets will run wherever REBOL/View is available.

Nano-Sheets?the Complete Code
Pinch yourself. Here it is?a fully functional, cross-platform GUI spreadsheet in one page of code.

   REBOL [Title: "Nano-Sheets"]      csize: 100x20   size: 8x16      col-lbl: func [col] [form to char! 64 + col]   cell-name: func [x y] [join col-lbl x y]   mk-var: func [x y] [to lit-word! cell-name x y]      sheet: copy [      space 1x1 across      style cell field csize edge none          with [formula: none]         [enter face  compute  face/para/scroll: 0x0]      style label text csize white black bold center   ]      repeat y 1 + size/y [      repend sheet ['label (csize / 2x1)         either 1 = y [""] [form y - 1]]      repeat x size/x [         append sheet compose/deep         either 1 = y [            [label (col-lbl x)]         ][            [cell with [var: (mk-var x y - 1)]]         ]      ]      append sheet 'return   ]      enter: func [face /local data] [      if empty? face/text [exit]      set face/var face/text      data: either #"=" = face/text/1 [ next face/text ][         face/text      ]      if error? try [data: load data] [exit]      if find [integer! decimal! money! time!         date! tuple! pair!]   type?/word :data [            set face/var data exit      ]      if face/text/1 = #"=" [face/formula: :data]   ]      compute: does [      unfocus      foreach cell cells [         if cell/formula [            if error? cell/text: try [do cell/formula]                [cell/text: "ERROR!"]            set cell/var cell/text            show cell         ]      ]   ]      lay: layout sheet   cells: copy []   foreach face lay/pane [      if 'cell = face/style [   append cells face]   ]   focus first cells   view lay 

The preceding code is the complete Nano-Sheets engine, in fewer than 1,500 bytes. If you “.rap” Nano-Sheets, it shrinks even further?down to 1,051 bytes. The “.rap” process compresses and protects code for professional projects.

Analyzing the Code
The Nano-Sheets code begins with a few definitions that control the size of the cells and the overall sheet itself. The cell size is in pixels; the sheet size in cells. They are both REBOL pair! values.

   csize: 100x20   size: 8x16

There are a few helper functions to make things easier and clearer in the main generation loop. The meaning of col-lbl and cell-name should be pretty obvious, but mk-var might not be; it creates a variable name dynamically so you don’t have to declare variables statically for every cell.

   col-lbl: func [col] [form to char! 64 + col]   cell-name: func [x y] [join col-lbl x y]   mk-var: func [x y] [to lit-word! cell-name x y]

The sheet definition starts out with some static elements that set the spacing and layout orientation, and then define a couple of styles used in the UI, much as you might use CSS to define styles for use in an HTML document.

   sheet: copy [       space 1x1 across       style cell field csize edge none with [formula:           none]           [enter face  compute  face/para/scroll: 0x0]       style label text csize white black bold center   ]

Next is the real magic. The two nested loops add elements to the sheet layout spec. If you’ve ever written a CGI app to dynamically generate a table in HTML, you’ll recognize that this is the same concept. The big difference is that these items will become active cells when they are rendered, and will be bound to a variable that represents them for use in formulas.

   repeat y 1 + size/y [       repend sheet ['label (csize / 2x1) either 1 = y           [""] [form y - 1]]       repeat x size/x [           append sheet compose/deep either 1 = y [[label              (col-lbl x)]] [               [cell with [var: (mk-var x y - 1)]]           ]       ]       append sheet 'return   ]

Setting Cell Values and Triggering Recalculations
The spreadsheet calls the enter function when a cell loses focus. Its job is to evaluate what a user entered, decide if it’s a formula, and set the value for the variable bound to the cell. Formulas, as in other spreadsheets, are identified by a leading equal sign (=). The spreadsheet coerces text entered in the cell to the most appropriate internal data type, so calculations operate in an intuitive way (e.g. $100 * 3 = $300).

   enter: func [face /local data] [       if empty? face/text [exit]       set face/var face/text       data: either #"=" = face/text/1 [next          face/text][face/text]       if error? try [data: load data] [exit]       if find [integer! decimal! money! time! date!           tuple! pair!] type?/word :data [           set face/var data exit       ]       if face/text/1 = #"=" [face/formula: :data]   ]

Recalculating Cell Values
You should note a few important things regarding recalculations:

1. Computation moves from top to bottom.
2. Recalculation is non-iterative.
3. Recalculation is triggered whenever a cell loses focus.
4. The spreadsheet displays any error text in the offending cell if it can’t execute the formula successfully.
5. The spreadsheet executes formulas using the REBOL do command. This means you can run any REBOL code as a formula. The good news is, that means formulas are all-powerful; but that’s also the bad news. The simple model discussed here has no security limitations on what formulas can or can’t do, so be careful and remember that formulas execute every time a cell loses focus?this version doesn’t check to see if the cell value has changed. If you write a formula that reads data from a web site, this version will execute that formula every time the spreadsheet recalculates the cell.

   compute: does [       unfocus       foreach cell cells [           if cell/formula [               if error? cell/text: try [do cell/formula]                  [cell/text: "ERROR!"]               set cell/var cell/text               show cell           ]       ]   ]

Common Questions You Might Have
Q: How do I enter formulas?

A: Just as you would in a traditional spreadsheet; put an equal sign (=) as the first character. For example, typing =now/time into a cell will print the current time. Typing =checksum “any message” will yield a CRC checksum. Formulas can combine cell data and REBOL Language expressions.

Q: How is the macro language implemented?

A: The formulas and macros are REBOL code that the spreadsheet evaluates from the cell. The most important thing to keep in mind, in this context, is that REBOL evaluates left-to-right; there is no inherent operator precedence. If you need to control the order of evaluation, use parentheses to group items.

Q: How does Nano-Sheets handle data types?

A: If you type $1000000 into cell A1, and 1.2531 into cell A2, and then enter the formula =A1 * A2 in cell A3, you’ll see the way REBOL handles values like money. No messy, time-consuming fussing with formats. REBOL supports all the following scalar data types directly: integer, decimal, money, time, date, tuple, and pair.

Q: Nano-Sheets doesn’t like money values with comma separators in them. Why?

A: The lexical format for money values in REBOL was designed to allow foreign currencies to be entered in the same manner as U.S. currency. That means the decimal point can be either a period (.) or a comma (,) and you normally wouldn’t use grouping separators. If you want to use them, the correct character is the apostrophe (‘). For example, $1234567.89, $1’234’567.89, and $1’234’567,89 are all equivalent.

Extending Nano-Sheets for Loading and Saving
Saving the data from a sheet is simple. All you need to do is walk the list of cells, emitting the name and contents for each cell. That data all goes into a block, which is then saved with one function call. REBOL’s native data serialization support is terrific for this kind of thing.

   foreach [id cell] cells [       if not empty-cell? cell [           repend buffer [               cell/var reduce [any [cell/formula  get                   cell/var]]           ]       ]   ]   save file buffer

Loading data is a little more involved than saving it, because the load process must allow for future extensions. The actual file format is a REBOL dialect designed to represent spreadsheet data. To read the file, you need to parse the data, according to the grammar for that dialect, and take actions when the incoming content matches production rules. The REBOL documentation has detailed information on parsing in REBOL.

You could just load a serialized block of values and let the index of each item implicitly define the cell it goes to, but that would mean storing an empty value (a marker of some kind) for every empty field, an approach which is neither scalable nor extensible. Fortunately, REBOL makes it easy to use the dialected approach, which lets the spreadsheet use a scalable, extensible data format that’s easily editable by humans using any plain text editor. Here’s an example data file:

   A1 [7-Feb-2005] B1 [[system/version]] C1 [100] D1 ["Gregg"] E1 [[head reverse copy d1]]    C2 [200]    C3 [300]    C4 [[C1 + C2 + C3]] D4 [[now/date]]

Figure 1 shows what the preceding spreadsheet file looks like when loaded into Nano-Sheets.

Figure 1. Nano-Sheets: The figure shows how the sample data file looks when loaded into Nano-Sheets.

The following lines of code provide graphical buttons and an integrated macro language in the Nano-Sheets spreadsheet project.

   use [       ; rules       buttons== cell== sheet-code==       ; vars       id val text action face style   ] [       id: val: text: action: face: none       style: 'btn  ; 'button       buttons==: [           'buttons into [               any [                   set val word! (id: val) 2 [                       set val string! (text: val)                       | set val block! (action: val)                   ] (                       repend sheet-buttons [id text                          action]                       append lay/pane face: make-                         face/size/offset style                           cell-size                           cells/:id/offset                       if 'button = style                          [face/edge/size: 1x1]                       face/text: text                       face/action: action                       face/style: style                   )               ]           ]       ]       cell==: [           set id word! into [               opt 'formula set val [block! | path!]                   (cells/:id/formula: :val)               | opt 'value set val                    [string! | scalar-types] (                   set cells/:id/var cells/:id/text: val               )           ]       ]       sheet-code==: ['do set sheet-code block!          (do sheet-code)]              sheet==: [           (sheet-code: none  clear sheet-buttons)           opt sheet-code==           any [buttons== | cell==]       ]   ]   

Here’s an example spreadsheet that provides buttons, an integrated macro language, and uses the preceding extensions to Nano-Sheets:

   do [       right-now: does [now/time/precise]        circle-area: func [diameter]           [diameter / 2 ** 2 * pi]   ] buttons [       A10 "Random-test" [         set-cell 'a11 circle-area random 10 1E-2]       C10 "test-2" [set-cell 'c11 right-now]       A5 "Check" [print "check"]       F16 "Done" [quit]]       A1 ["test"]       C1 [[now/date]]       D1 [3]       E1 [$200.00]       F1 [1x2]       E2 [[d1 * e1]]       A11 [19.63]       C11 [0:46:00.171]      C12 [[c11 + 1]]

Figure 2. Nano-Sheets Extended: Here’s the preceding code example that provides buttons running in Nano-Sheets.

When loaded into Nano-Sheets, the result looks like Figure 2.

By this point, you’re probably aware of some of the benefits of designing small Reblets in REBOL. These examples were constructed in a few days’ time. This article itself took more time to write than the software. An upcoming article will discuss extending Nano-Sheets to include pretty-printing capabilities, adding keyboard capabilities, and explore how you can use the spreadsheet as a component of more complete applications.