he first part of this article described how to build the Nano-Sheets spreadsheet engine, and then extended it by adding the following features:

• Dynamic generation and sizing of cells
• Automatic data types in cells
• Formulas including REBOL code
• Loading and saving spreadsheet files

This part extends Nano-Sheets even further.

Adding Pretty Printing
The basic Nano-Sheets engine doesn’t supply users with a way to print the spreadsheet. This section of the article extends it by emitting HTML, so users can print from the browser. Of course, developers can also use the generated HTML for other purposes.

You create a context for this bit of code. A context in REBOL is basically an object, and is a handy way to create namespaces and group code.

The HTML-export context contains some boilerplate HTML, with replaceable tags where the specific values and generated data from the spreadsheet should go. The emit helper function makes the generation loop cleaner. The generation loop works very much like the one that built the UI; but it emits HTML rather than a REBOL VID layout. For testing purposes, the following code just writes the HTML to a file and opens the browser to display it. Note how the code uses REBOL’s tag data type directly; you don’t need to wrap tags in quoted strings. In REBOL, tags are a first class data type?a special type of string?so REBOL functions that work on strings generally also work on tags.

   ctx-html-export: context [       out-buff: make string! 10'000   html-template: {                  $table      }   emit: func [data] [repend out-buff       [reduce data newline]]   set 'emit-html func [/to file /local val] [       clear out-buff       emit        repeat row 1 + sheet-size/y [           emit []           repeat col sheet-size/x [              emit either 1 = row               [[]] [                   []               ]           ]           emit        ]       emit 
 either 1 =               row [""] [form row - 1]
 col-lbl col 
 any                    [get/any mk-var col row - 1 ""] 
       out-buff: replace copy html-template           "$table" out-buff       write %rebolcalc-out.html out-buff browse          %rebolcalc-out.html   ]   ]

Adding Keyboard Navigation
Although the basic engine has mouse controls, and the tab key moves between cells, it’s worth adding some extra keyboard navigation; the up and down arrow keys are very helpful if you expect to do much editing. To avoid adding code to every cell to handle keyboard events, you can use a nice feature in REBOL/View: insert-event-func.

The insert-event-func word lets you specify a callback function that the main parent calls whenever it “sees” an event. Using this feature, you can filter and act on events in one central location. First, define a callback function:

   event-func: func [face event /local f] [       ; The face we get passed is *not* the cell, it's        ; the main layout.       ; We use system/view/focal-face to get the cell        ; being edited.       ; The ALL function is a shortcut for ANDing        ; clauses together, e.g.       ;   if (('key = event/type) and (in-cell?))       if all ['key = event/type  in-cell?] [           switch event/key [               F2    [if in-cell? [show-formula                         system/view/focal-face]]               up    [move up]               down  [move down]               ; left/right conflict with edit                ' navigation, need a more               ; complex system to track edit mode if we                ' want to do this. Use Tab/Shift+Tab to                ; move L/R in the meantime.               ;left  [move left]               ;right [move right]           ]       ]       event   ]

Then you install the completed callback function in the event chain using insert-event-func.

   insert-event-func :event-func

Note that the code also handles an F2 key press, so you can edit the formula for a cell as you would in a traditional spreadsheet. The function that actually moves the focus is called move. If you’re new to REBOL, it may not be clear how move works, so here’s some commented code that should help explain it.

   move: func ['way /local pos] [       ; Find the location of the current cell in the        ; list of cells       pos: find cells cur-cell          ; "enter cur-cell" triggers the recalc; the tab        ; key does that automatically, arrow keys don't       if find [up down] way           [enter cur-cell] ; left right          ; Now we shift the position in the list of cells,        ; relative to the current cell, based on the       ; direction they're moving, and pick the cell at        ; that location so we can move to it.       ; Left and Right arrows conflict with in-cell        ; editing, and need more code to support changing       ; between navigation and edit modes.       cell: pick switch way [           up    [skip pos negate sheet-size/x * 2]           down  [skip pos sheet-size/x * 2]           ;left  [back pos]           ;right [next pos]       ] 1          ; Keep from falling off the top of the list due to        ; a negative skip.       if not object? cell [cell: none]          ; Set the current cell       if cell [focus cell]   ]

The move code illustrates one other advanced trick?the use of a lit-word! (notice the tick-mark/apostrophe in the word way in the first line of preceding code) as a parameter. That tells REBOL not to evaluate the argument it gets. In this case way is going to be a word! value, something like up or down. Normally you would have to use a lit-word! when making the call, for example:

   move 'up   move 'down

That’s not terrible, but it’s not quite as nice as this.

   move up   move down

The goal here is to make the higher-level logic look less like “code,” and is an important concept when you think in terms of REBOL dialects. The use of a lit-word! as a parameter shows how much control REBOL gives you over when evaluation occurs. That’s important because, in REBOL, there is no code, there is only data that gets evaluated (it takes a while for that concept to sink in, especially if you’re an experienced programmer used to other languages).

Extending Nano-Sheets with Macros
Adding macro capability is easy?Nano-Sheets already has the powerful REBOL engine under the hood. For example, simply type the following example formula into a cell and press the Enter key (thanks and credit to Allen Kamp from REBOL Forces for this example).

   =length? read http://www.rebol.com

The formula displays the size of the REBOL home page in the cell. Remember that URLs are a native data type in REBOL.

Traditional spreadsheets have specialized worksheet functions tailored for use in formulas such as SUM, or AVG. By including your own REBOL functions in the Nano-Sheets engine you can make them available to end users, just as if they were native. Here are some examples:

   avg: average: func ["Arithmetic mean" block       [any-block!]] [      divide  sum block  length? block   ]      gcd: func ["Greatest common denominator"        m [integer!] n [integer!]] [       either (m // n) = 0 [n] [gcd n (m // n)]         ; Euclid's algorithm   ]      geo-mean: func ["Geometric mean" block [any-block!]] [       either empty? block [0]           [(product block) ** (1 / length? block)]   ]      median: func [       "Returns the number in the middle of a set of           numbers sorted by value"       block [any-block!] /local len mid   ] [       block: sort copy block       len: length? block       mid: to integer! len / 2       either odd? len [           pick block add 1 mid       ][           (block/:mid) + (pick block add 1 mid) / 2       ]   ]      mode: func [       "Returns the most frequently occurring value in            the block"       block [any-block!]       /local last-item result high-count count   ][       block: sort copy block       result: last-item: first block       count: high-count: 1       foreach item next block [           either item = last-item [count: count + 1] [               if count > high-count [                   high-count: count                   result: last-item               ]               last-item: item               count: 1           ]       ]       if count > high-count [result: last-item]       result   ]      product: func [       "Multiplies all the values in the block"       block [any-block!] /local result   ][       result: 1       foreach value reduce block           [result: result * value]       result   ]      sum: func [       "Adds all the values in the block"       block [any-block!] /local result   ][       result: 0       foreach value reduce block           [result: result + value]       result   ]

Multi-user Collaboration and Interacting with Other Programs
Adding multi-user collaboration capability to Nano-Sheets is easy in a secure X-Internet environment such as REBOL/IOS. With just a little code, you publish spreadsheets to an IOS server, which then syncs it to all the other clients that have access to the file (folders can be private, for example). IOS also maintains a historical list of published versions, so you can roll back to an earlier one if you want.

Nano-sheets files are small, thanks to their dialect-based format, and sharing published files works well in most cases, but you can reduce the network traffic required for collaboration even further. Rather than publishing an entire file, you can send messages to IOS that contain just part of a spreadsheet?for example, a single cell, or range of cells?in other words, just the changes made to that spreadsheet. IOS syncs those messages and the listening Nano-Sheet?if it had the same file open?can easily read that message and apply the changes to the live spreadsheet. This works because (remember the file format) cells are named; you just send the name of the cells that have changed and their new contents. That gives you interactive spreadsheet collaboration

In the next sections you’ll see how to extend Nano-Sheets to publish spreadsheets in REBOL/IOS, and display the data to other users. To do that, you make to create a fileset in IOS and make two distinct changes to Nano-Sheets.

Creating the Fileset
Before extending Nano-Sheets so it can publish spreadsheets and display changed data to other users, a user with administrative rights must create a fileset named “nano-sheets” for the above example.

You can find more detailed information about filesets including both an overview and detailed programming information.

The administrator can create the fileset using the App-Admin tool provided with REBOL/IOS, or programmatically using the following line of code:

   send-server new-app compose/deep [nano-sheets [] [] ]

The flexibilities of filesets allow you to automate nearly any workflow process. This example was designed to show a simple, fundamental application of distributed messaging. Note that when the file is “saved” via the X-Internet, the local copy is saved only after the distribution sync is complete. Also note that both “save” and “save-as” use the save-sheet function.

With the fileset created, you can continue with the Nano-Sheet collaboration extensions.

Publishing Spreadsheet Data with Send-server
To publish spreadsheet data collaboratively, you first modify the save-sheet function by adding the REBOL/IOS send-server function when the IOS Serve dialect is present by modifying these original two lines at the end of the save-sheet function:

   save file buffer   current-file: file

Here’s a simple modified version that uses absolute file paths so you can see the structure clearly:

   ; For collaboration, see if IOS is connected through    ; the X-Internet   either all [link? connected?] [       send-server add-file reduce [           'nano-sheets           file           compress mold/only buffer       ]   ][       save file buffer ;otherwise, store locally   ]   current-file: file

Here’s a more advanced modified version that uses flexible cross-platform-capable relative file paths:

   ; For collaboration, see if IOS is connected    ; through the X-Internet   either all [link? connected?] [       base-dir: any [ ( find/match what-dir link-root )           what-dir]       pub-file: second split-path file       send-server add-file reduce [           'nano-sheets           base-dir/:pub-file           compress mold/only buffer       ]   ][       save file buffer ;otherwise, store locally   ]   current-file: file

How does the code above publish a spreadsheet? First, you check to be sure it’s using REBOL/IOS with the link? test and then determine whether it’s connected to the X-Internet with the connected? test. Next it determines the relative base-dir. pub-file contains the filename only?without the path. These are joined by the base-dir/:pub-file line that determines where the file will be synced. The send-server dialect tells REBOL/IOS to add the file to the nano-sheets fileset, and the file contents will be whatever buffer holds.

Using relative file paths you can install Nano-Sheets anywhere on the REBOL/IOS desktop without needing to modify the Nano-Sheets program.

Receiving and Loading Data with an insert-notify Callback
To display the changed data to other users, you extend the load-sheets function in this second extension of Nano-Sheets so that when the send-server action above results in a new or updated spreadsheet, the insert-notify callback function will load the data. Here’s the entire load-sheet file so you can see where to add the new code (after the ;— comment).

   last-file: none   load-sheet: func [file [file! url!] /local data] [       collab-file: second split-path file       clear-sheet       parse load/all file sheet==          ;---Add this code to receive and load synced        ;---spreadsheets        if all [ link? (last-file <> collab-file )] [           base-dir: any [ ( find/match what-dir               link-root ) what-dir]           remove-notify 'file-downloaded               base-dir/:last-file           last-file: collab-file           insert-notify 'file-downloaded                base-dir/:collab-file func [a data][               load-sheet collab-file           ]       ]          current-file: file       show lay       compute   ]

After adding this new code, all users who are given access to the nano-sheets fileset will receive the new file the next time their REBOL/IOS client connects to the X-Internet. This simple example assumes that users who want to see the spreadsheet when it is synced have Nano-Sheets running; if they do, it will load the file if you added the preceding code.

This example simply uses Nano-Sheets to receive and load the file, but you could add a management program to the nano-sheets fileset that loads the data and uses it to automate workflow.

Note that it requires only about 500 bytes to extend Nano-Sheets for collaboration with either itself, or with other programs.

The link? test lets Nano-Sheets operate with any version of REBOL, even without the X-Internet, or without any connection at all.

We hope you now appreciate the revolutionary design of Nano-Sheets as shown in the examples. REBOL enables concise, readable code that’s tiny compared to other software technologies. The Nano-Sheets application is just an appetizer of what’s possible with REBOL; and we hope these servings were palatable. You don’t need a huge team of programmers to produce professional results using this example as a starting point.

Please refer to the sidebar “Major Benefits of REBOL” to see a further analysis of how REBOL functions under the hood of Nano-Sheets.