Excel 365 in 100ish lines of F#
Following the tutorial
Step #1: Keeping cell state
We start with four cells and cell A1 is always selected, but you cannot actually edit the text in the cell! This is because we do not correctly store the state of the textbox that the user edits.
main.fsand go to
renderEditor. You can see that we already have an event handler for
OnInputwhich shows a message using
window.alert. Change the code to trigger the
UpdateValueevent using the
triggerfunction (the first parameter of
UpdateValueshould be the position
posand the second should be the value of the input, i.e.
main.fsand go to the
updatefunction. This needs to handle the
UpdateValueevent and calculate a new state. When we get an event
UpdateValue(pos, value), we need to create a new
state.Cellsmap and add a mapping from
main.fsand go to the
renderCellfunction. Right now, this passes
renderViewfunctions. Find a value for the current cell using
Map.tryFind pos state.Cells. You can handle
Option.defaultValue(just make the default empty) and pass it to
Now you should be able to edit the value in cell A1!
Step #2: Selecting a cell
Now, we need to allow the user to select another cell. To do this, we will need to track the active cell in our state and add events for selecting another cell.
Find the definition of
main.fsand add a new field
Position option(this keeps the selected cell position or
Noneif no cell is selected). In the
To change the selected cell, we need a new type of event. Find the
main.fs) and add a new case
StartEditthat carries a
main.fsto handle the new
StartEditevent. When the event happens with
posas the new position to be selected, return a new state with
renderCelland modify the condition
pos = ('A', 1). Rather than checking that we are rendering cell A1, we need to check whether we are rendering the cell specified in
state.Active(note that this is an option type so you need to compare against
Finally, we need code that will trigger our new event. Find the
main.fs. This creates a
<td>element with the cell. In the attributes of the element, add a handler for
OnClickthat triggers (using the
StartEdit(pos)event. (The code is similar to
OnInputthat we already have in
Now you can click on cells and change their values!
Step #3: Rendering the grid
So far, we only had 4 cells. Those are created by hand in the
view function. We want to change the code so that it generates cells dynamically, using the cell and row keys in
To do this, you can either use list comprehensions with
[ .. yield .. ] syntax or you can use
List.map function. The following steps describe how to use
List.map, which is easier if you are new to F# (but if you know F# already, feel free to use list comprehensions!)
You can generate headers using
state.Colsas the input. In the body of the map function, you can create a header using
header (string h). You also need to append the empty cell using
viewcode defines two rows using
let cells1 = ...and
let cells2 = .... First, modify the body to generate cell for each column in
state.Cols(just like for the headers). Next, modify the code to be a function that takes a row numbe
Finally, use your new
cellfunction to generate a row for every single row of the spreadsheet specified in
state.Rows. If you are using
List.map, the argument will need to generate a row using
tr  (cells r).
Step #4: Evaluating equations
Finally, we need to add an evaluator for spreadsheet formulas! The
parse function is already implemented (in
evaluator.fs) so you need to add the evaluator and put everything together.
renderCell, when we are handling a cell that is not selected, we want to parse and evaluate the code and pass the result to
renderView. First, run
parseon the cell value (when it is
Some value) and then format the result using
string. This way, you should see what the result of parsing looks like.
Next, modify the code to call
evaluate. Since parsing can fail, you'll need
Option.mapor pattern matching to do this. Also, the
evalautefunction takes all cells too, so you need to call it using
evaluate state.Cells parsed.
Finally, the code for
evaluator.fsjust returns 0, 1 or 2. Modify this to actually evaluate the expression! For
Number, just return the number; for
Binary, recursively evaluate
rand then apply the binary operator; for
Reference, you will need to find the value in
cells, parse it and evaluate that recursively. Do not worry about correct error handling. We'll fix that next!
Step #5: Add proper error handling
The evaluator can fail when you reference a cell without a value (it will crash) or when you reference a cell within itself (it will run into an infinite loop), so let's fix that!
evaluator.fsso that it returns
option<int>rather than just
int. You will need to return
Numbercase and propagate the
Nonevalues correctly - the easiest way to do this is using
Option.map, but you can also use pattern matching using
Once you modify
evaluate, you also need to modify
main.fsso that it calls it correctly. If you pass
renderView, it will display
#ERRin red just like Excel.
Handling recursive references is harder. We currently just get into an infinite loop and get a stack overflow. To handle this, you need to modify the
evaluatefunction so that it has an additional parameter of type
Set<Position>that keeps a set with all cells that we are evaluating. Then, when handling
Reference, you need to make sure that the referenced cell is not in this set.