3rd Dec 2014

Building a spreadsheet with AngularJS

A mini series covering my experiences of building a spreadsheet

Shortly into CommuSoft's estimate module I came across a use case that included the words "spreadsheet like". The requirement was simple; users needed to get data into the system as quickly as possible.

After exploring a few "non spreadsheet like" ideas I realised that nothing could match the efficiency and familiarity users already had with a good old spreadsheet. It's turned out to be one of the most interesting things I've builtyet :). And I'd like to share some thoughts and insights into building a spreadsheet of my own!

It wasn't long into writing this that I realised there was too much to say for one post. So I've split it up into a mini series to make it easier to digest. The topics I cover are:

  • Keyboard Shortcuts
  • Calculations
  • Dynamic Content
  • Miscellaneous (things too small to warrant a full section but are noteworthy)

Keyboard Shortcuts

Keyboard shortcuts turned out to be the hardest problem to tackle. The difficulty lay in features that aren't found in your conventional spreadsheet. Things such as different row types, and non-selectable cells.

Here we can see 2 types of row: normal rows (highlighted in red), and associated rows (highlighted in blue). Normal rows are pretty self explanatory. In terms of data they are just a one dimensional array containing their cell models.

Associated row is the term I coined to describe a row that contains other rows that relate to one-another. They introduce difficulties (especially when dealing with shortcuts) and have a more complex structure; being two dimensional.

Associated rows and non-selectable cells come hand in hand. Non-selectable cells are either empty, or contain data that can't be edited.

The cell with the value "#install" (in row 3) has 3 empty cells below it. Pressing down selects the cell with the value "#certificate" in row 4; skipping the 3 empty cells in the middle.

The cells in the second column contain values that can't be changed, and act as labels for the cells to their right. If you look carefully you'll see that when the "Homebase" and "Wickes" cells are selected their left border flashes red for a split second. This is because the left key was pressed, which is an invalid shortcut (more on this in part 4).

Now the scene is set lets talk about the implementation. Initially I thought a shortcut could be handled on the fly, i.e when an arrow key is pressed find the closest cell in that direction and select it. This worked perfectly for spreadsheets with normal rows, but struggled to handle those with associated rows. In pseudo code the logic began to look like this:

As you can see it gets complicated pretty quickly, and is far too much overhead just to find a cell to select. (Imagine this block firing every time you pressed an arrow key!) Not surprisingly this approach scored no points performance wise and was fairly error prone. After a few goes at optimising the logic I realised I needed to take a different angle.

An idea I started playing with was to pre-calculate a shortcut before a key was pressed. (Note: a spreadsheet is an object made up of lots of arrays). To move from one cell to another, all thats needed is the index of the cell we want to move to. If this index was already in memory performing the shortcut could be as easy as accessing an objects value, (i.e new active cell = dot.notation.pointing.to.new.cell). On paper this concept worked, it was now time to code!

Without giving a line by line account of what happened next lets skip to the important bits. Clicking on a cell assigned the following attributes to each cell model:

(Non-selectable cells were not assigned these attributes, as they would never be navigated to / from)

  • "up_ref" (index of closest cell above)
  • "down_ref" (index of closest cell below)
  • "left_ref" (index of closest cell to the left)
  • "right_ref" (index of closest cell to the right)

Which in the wild looks like this:

Equally important as the shortcut attributes is keeping track of the current active cell. (In the directive the current cell is saved to a variable called this.cell). Then when a shortcut is pressed, (lets say the right key) we can fulfil the shortcut with this.cell = this.rows[this.cell.right_ref]. (In actual fact the index has to be interpolated beforehand, but for demo purposes this will do).

Up until now we have only discussed how this solution works for pre-calculating shortcuts. But what happens when a new row is added, or an existing one is deleted? These changes to a spreadsheet would render the shortcut refs invalid. A ref could now point to a cell that no longer existed, or to one that lived in an associated row. The solution was simple: re-run the function that assigned the shortcut attributes to each cell model, and everything's hunky dory.

One thing to note is that shortcuts have different actions depending on the contexts in which they're used. For example if I'm editing a cell and press left, my intension is to move the cursor back 1 character. It shouldn't register as an attempt to select the cell on the left. As with jQuery, Angular provides an implementation for binding and un-binding events. This gives you full control over turning shortcut listeners on or off, as seen below:

And thats it! Hopefully the ideas covered have been interesting, and illustrate some useful techniques for dealing with shortcut intensive applications. Next time I cover the mathematical side of the spreadsheet.

Never Miss a Post!

If you have enjoyed this blog post I invite you to join my newsletter:

Emails are kept safe with MailChimp And I never ever send spammy emails (Or stay updated with RSS)

Coming Soon

Spreadsheet Calculations