← BACK

Building a Spreadsheet in 20 Minutes with Angular.js

Written by David Graunke

In this post, we’re going to use Angular.js to build a miniature spreadsheet.

Angular makes it incredibly easy to bind model data to your HTML, and to propagate changes to your view. This, it turns out, makes developing something like a spreadsheet pretty straightforward. Cells in the spreadsheet can depend on each other, and Angular will handle propagating the necessary changes.

I’m going to show how I created the spreadsheet step by step. At each step, you’ll see the complete source code on the left, with new code, removed code, and unchaged code indicated. On the right, you’ll see an iFrame with the live output of the source.

This post was also an experiment — I created it using a tool I made called Step by Step, which turns git repos into HTML tutorials. Each version below is a commit in the tutorial repo, and each explanation is a commit message. The repo used to create this post is here here, and you can see just the commits for this post here.

If you like this, you should follow me on Twitter.

Let’s get started!

We’ll start with two simple cells: one for input, and one for output. We’ll use <input> tags for both of them, so they’re styled the same

<div>
    <input></input>
    <input disabled=disabled></input>
</div>

Let’s add our Angular.js dependency, and wire up the two cells. We put an ng-app attribute on our container div, which tells Angular to compile and parse that element and its children as an Angular app.

We also bind the value of out input cell to the model value cell. We haven’t set up a controller with ng-controller, but that’s ok — Angular will put the value cell in its global scope, so we can refere to it anywhere in our app. We’’ll do that by binding the contents of the output element to cell. Any changes in the first input element will change the value of cell, which should be mirrored in the output element.

<div>
  <input></input>
  <input disabled=disabled></input>
  <script src="lib/angular.js"></script>
  <div ng-app>
    <input ng-model="cell"></input>
    <input ng-binding="cell" disabled=disabled></input>
  </div>

Whoops! Looks like ng-bind isn’t going to work for us — Angular’s ng-bind implementation uses jQuery’s .text() method, which won’t work with an input element.

That’s ok: we’ll use a div instead. We’ll style it resembles our input somewhat.

<script src="lib/angular.js"></script>
<div ng-app>
  <input ng-model="cell"></input>
  <input ng-binding="cell" disabled=disabled></input>
  <div ng-bind="cell" class="output"></div>
</div>

<style>
  .output {
    display: inline-block;
    border: 1px solid #EEE;
    padding: 2.3pt; 
  }
</style>

Let’s start wiring this up! We’ll add an ng-controller attribute to our container div, and set it to spreadsheet. Now, rather than using the global scope, it will look in the global window object for a function named spreadsheet, and use that as a factory function to build our controller object.

To test that our factory function is working, we’ll have it set the starting value of $scope.cell to “hello spreadsheet!”, and see if it shows up in the view.

<script src="lib/angular.js"></script>
<script>
  sheet = function($scope) {
    $scope.cell = "hello spreadsheet!";
  }
</script>

<div ng-app ng-controller="sheet">
  <input ng-model="cell"></input>
  <div ng-bind="cell" class="output"></div>
</div>

<style>
  .output {
    display: inline-block;
    border: 1px solid #EEE;
    padding: 2.3pt;
  }
</style>

Alright — let’s start evaluating expressions. We’re going to do that using Angular’s built-in $parse service.

$parse takes an Angular expression and transforms it into a function. When the expression refers to a value like cell, angular will transform it into a function that looks up that field on a controller object that’s passed in, like this: function(scope) { return scope['cell'];};. If the expression is a function call like f(), Angular will turn it into a function that looks up the value on the controller object, and then calls it: function(scope) { return scope['f']();}.

Angular uses $parse to let users create context-sensitive bindings and callbacks in the view, but we’re going to use $parse for our own purposes. We’ll parse the contents of the input cell, and turn it into a function we can evaluate.

We do that in our compute() method with $parse($scope.cell) to create a function from the expression in the cell. We call that imediately, passing in our current scope like so: $parse($scope.cell)($scope). By passing in our $scope object to the function, we make values on our scope available to the expression, which will be important later.

Due to Angular’s change-watching system, any changes to the contents of input cell will cause $scope.compute() to re-evaluate, and update our div. Try it out with a few arithmetic expressions: e.g., 52163 / 16604.

<script src="lib/angular.js"></script>
<script>
  sheet = function($scope) {
    $scope.cell = "hello spreadsheet!";
    sheet = function($scope, $parse) {
      $scope.cell = "";
      $scope.compute = function() {
        return $parse($scope.cell)($scope);
      };
  }
</script>

<div ng-app ng-controller="sheet">
  <input ng-model="cell"></input>
  <div ng-bind="cell" class="output"></div>
  <div ng-bind="compute()" class="output"></div>
</div>

<style>
  .output {
    display: inline-block;
    border: 1px solid #EEE;
    padding: 2.3pt;
  }
</style>

Alright, let’s start turning this into a spreadsheet. We’ll use position: absolute to place the input and output on top of each other. The input is pink, and the output is light purple.

When the cell is not focused, we want to show the computed result of the expression. We do that by placing the output cell on top of the input cell. We also want to show the unevaluated expression when we click on the cell. To do that, we’ll pass all click events through the out using pointer-event: none. Any clicks to the output will be passed through to the input cell behind it, allowing the input element to get focus. We’ll then use a CSS sibling selector to hide the output when the input is selected like so: input:focus + .output { display: none; }.

<script src="lib/angular.js"></script>
<script>
  sheet = function($scope, $parse) {
    $scope.cell = "";
    $scope.compute = function() {
      return $parse($scope.cell)($scope);
    };
  }
</script>

<div ng-app ng-controller="sheet">
  <input ng-model="cell"></input>
  <div ng-bind="compute()" class="output"></div>
</div>

<style>
  .output {
    display: inline-block;
    border: 1px solid #EEE;
    padding: 2.3pt;
  }
  .output, input {
    font-family: sans-serif;
    font-size: 10pt;
    position: absolute;
    height: 1.2em;
    width: 50pt;
    height: 16pt;
    overflow: hidden;
    padding-left: 4pt;
  }
  input { background: rgba(255, 0, 0, 0.2); border: none;}
  .output {
    background: rgba(200, 200, 255, 1);
    pointer-events: none;
    padding-top: 2pt;
    height: 14pt;
  }
  input:focus + .output { display: none; }
</style>

Let’s turn this into a spreadsheet!

Instead of having a single cell, we’re going to have an object called $scope.cells that maps names of cells like ‘A1’ or 'C4’ to their current expression.

We also have a list of our columns ($scope.columns) and rows ($scope.rows). We use ng-repeat to create a <tr> element for row, and a <td> element to each column. Inside that <td> we have the same input and output div as before, except that they’re now bound to a value in the $scope.cells object.

Because we pass in our $scope object when evaluating our expressions, you can reference other cells within an expression by calling compute manually — try setting the value of cell 'A1’ to 2, and the value of cell 'A2’ to compute('A1') * 5.

<script>
  sheet = function($scope, $parse) {
    $scope.cell = "";
    $scope.compute = function() {
      return $parse($scope.cell)($scope);
      $scope.columns = ['A', 'B', 'C', 'D'];
      $scope.rows = [1, 2, 3, 4]; 
      $scope.cells = {};
      $scope.compute = function(cell) {
      return $parse($scope.cells[cell])($scope);
    };
  }
</script>

<div ng-app ng-controller="sheet">
  <input ng-model="cell"></input>
  <div ng-bind="compute()" class="output"></div>
  <table>
    <tr ng-repeat="row in rows">
      <td ng-repeat="column in columns">
        <div>
          <input ng-model="cells[column+row]"></input>
           <div ng-bind="compute(column+row)" class="output"></div>
        </div>
      </td>
    </tr>
  </table>
</div>

<style>
  .output, input {
    font-family: sans-serif;
    font-size: 10pt;
    position: absolute;
    height: 1.2em;
    width: 50pt;
    height: 16pt;
    overflow: hidden;
    padding-left: 4pt;
  }
  input { background: rgba(255, 0, 0, 0.2); border: none;}
  input { border: none;}
  .output {
    background: rgba(200, 200, 255, 1);
    background: white;
    pointer-events: none;
    padding-top: 2pt;
    height: 14pt;
  }
  input:focus + .output { display: none; }
  table { border-collapse: collapse; }
  td > div {
    width: 55pt;
    height: 16pt;
  }
  td { border: 1px solid #EEE; }
</style>

Let’s add some row and column labels. We add a <tr> before our row ng-repeat to create the column labels, and adding a <td> on each row for the row labels.

<script>
  sheet = function($scope, $parse) {
    $scope.columns = ['A', 'B', 'C', 'D'];
    $scope.rows = [1, 2, 3, 4];
    $scope.cells = {};
    $scope.compute = function(cell) {
      return $parse($scope.cells[cell])($scope);
    };
  }
</script>

<div ng-app ng-controller="sheet">
  <table>
    <tr class="column-label">
      <td></td>
      <td ng-repeat="column in columns">{{column}}</td>
    </tr>
    <tr ng-repeat="row in rows">
      <td class="row-label">{{row}}</td>
      <td ng-repeat="column in columns">
        <div>
          <input ng-model="cells[column+row]"></input>
          <div ng-bind="compute(column+row)" class="output"></div>
        </div>
      </td>
    </tr>
  </table>
</div>

<style>
  .output, input { font-family: sans-serif; }
  body { font-family: sans-serif; }
  .output, input {
    font-size: 10pt;
    position: absolute;
    height: 1.2em;
    width: 50pt;
    height: 16pt;
    overflow: hidden;
    padding-left: 4pt;
  }
  input { border: none;}
  .output {
    background: white;
    pointer-events: none;
    padding-top: 2pt;
    height: 14pt;
  }
  input:focus + .output { display: none; }
  table { border-collapse: collapse; }
  td > div {
    width: 55pt;
    height: 1pt;
  }
  td { border: 1px solid #EEE; }
  .column-label > td, .row-label {
    text-align: center;
    background: #EEE;
  }
  .row-label { width: 2em; }
</style>

Rather than make users type compute(A1), you should be able to just type 'A1’. We’ll add a preprocessor that finds anything that looks like a cell reference, and turns it into a call to compute. Try it out!

<script>
  sheet = function($scope, $parse) {
  $scope.columns = ['A', 'B', 'C', 'D'];
  $scope.rows = [1, 2, 3, 4];
  $scope.cells = {};
  process = function(exp) {
    return exp.replace(/[A-Z]\d+/g, function(ref) {
      return 'compute("' + ref + '")';
    })
  }
  $scope.compute = function(cell) {
    return $parse($scope.cells[cell])($scope);
      return $parse(process($scope.cells[cell]))($scope);
    };
  }
</script>

<div ng-app ng-controller="sheet">
  <table>
    <tr class="column-label">
      <td></td>
      <td ng-repeat="column in columns">{{column}}</td>
      </tr>
    <tr ng-repeat="row in rows">
      <td class="row-label">{{row}}</td>
      <td ng-repeat="column in columns">
        <div>
          <input ng-model="cells[column+row]"></input>
          <div ng-bind="compute(column+row)" class="output"></div>
        </div>
      </td>
    </tr>
  </table>
</div>

<style>
  body { font-family: sans-serif; }
  .output, input {
    font-size: 10pt;
    position: absolute;
    height: 1.2em;
    width: 50pt;
    height: 16pt;
    overflow: hidden;
    padding-left: 4pt;
  }
  input { border: none;}
  .output {
    background: white;
    pointer-events: none;
    padding-top: 2pt;
    height: 14pt;
  }
  input:focus + .output { display: none; }
  table { border-collapse: collapse; }
  td > div {
    width: 55pt;
    height: 16pt;
  }
  td { border: 1px solid #EEE; }
  .column-label > td, .row-label {
    text-align: center;
    background: #EEE;
  }
  .row-label { width: 2em; }
</style>

That’s all it takes to build a the beginnings of a spreadsheet in Angular — two dozen lines of Javascript and HTML, and a few dozen lines of CSS.

I hope you liked this post! We use Angular.js everyday at Thomas Street to build fast, testable web apps for our clients. Get in touch!