microtables

Microtables

A spreadsheet software with limited functionality, designed for quick calculations. The goal is to have an app that is lightweight and quick to load, but can handle many simple use cases - something in between the calculator app, and an office suite spreadsheet program.

Installation

Clone the repo, go into frontend, and run lein dev. Then navigate to localhost:8280 in a browser.

Currently, only the frontend works - but all the spreadsheet functionality works on the frontend side. The server will provide saving and collaboration features.

Concept

Data Structure

The original data structure was simply a list of column,row,value objects (which I called a "datum"). This is less efficient - since it would guarantee that virtually every operation would require cycling through the whole data set - but the implementation would be a series of simple map functions, and it was intended that the data set would be manageably small. Ultimately, I decided to refactor it to a map of maps - the first one using the column (letter) as keys, and the inner maps using the row (number) as keys.

{"A" {1 {:value 78}
      3 {:value 5}}
 "C" {3 {:value 42}}

Formula Evaluation

Embedded in this data set is another data structure: a set of disconnected doubly-linked trees, which represent references in formulae. When evaluating the new final value of a cell that was just modified, the program uses the final values of all referenced cells. It then re-evaluates all the cells that reference this one, using its "back-references" (or "inbound" references). The evaluated value is held in the datum under the :display property. When a cell is being edited, its view mode is set to :value. Otherwise, it reverts to the default :display mode.

{"A" {1 {:value 78 :inbound '({:col "B" :row 2})}
      3 {:value "=B2 * 4" :refs '({:col "B" :row 2})}}
 "B" {2 {:value "=A1 + 3" :refs '({:col "A" :row 1}) :inbound '({:col "A" :row 3})}}
 "C" {4 {:value 42 :inbound '({:col "D" :row 2})}
      6 {:value 33 :inbound '({:col "D" :row 2})}}
 "D" {2 {:value "=C4 + C6" :refs '({:col "C" :row 4} {:col "C" :row 6})}
      7 {:value 3}}}

All formulae (denoted by the equals sign "=" as the first character) are handled by the math.js library. This library also supports various functions, such as sum and average (mean). Microtables has support for range syntax, denoted by a colon ":" (ex: A5:C7), which allows for formulae like this: =sum(A1:A10).