Representing SpreadSheet Formulas

Kathi Fisler

On Thursday, we will be using spreadsheet formulas as an example to illustrate a programming technique. That lecture will go FAR more smoothly if you familiarize yourself with how we represent formulas in Java prior to lecture.

1 Spreadsheet Formulas and Functions Over Them

Spreadsheets are formed of cells. Each cell can contain a concrete value or a formula that may reference other cells. References are specified by the row (labeled with numbers) and column (labeled with letters) of the desired cell. For example:

A | B | C

--------------------------------

1 | Hwk 1 | 120 |

2 | Hwk 2 | 100 |

3 | Hwk 3 | 115 |

4 | Total | =B1 + B2 + B3 |

The = in cell B4 indicates that the value of the cell is computed according to the given formula.

Let’s capture spreadsheet formulas through data structures. The following classes achieve this. The Examples class shows how to create formulas in this representation:

  interface IFormula {}

  class Num implements IFormula {
    int value;
    Num(int value) {
      this.value = value;
    }
  }

  class CellRef implements IFormula {
    String cellname;

    CellRef(String cellname) {
      this.cellname = cellname;
    }
  }

  class Plus implements IFormula {
    IFormula left;
    IFormula right;

    Plus(IFormula left, IFormula right) {
      this.left = left;
      this.right = right;
    }
  }

  class Examples {
    Examples(){}

    Num num2 = new Num(2);
    Num num5 = new Num(5);
    CellRef a10 = new CellRef("a10");
    Plus f1 = new Plus(num2, num5);
    Plus f2 = new Plus(a10, num5);
  }

For now, we are interested in two functions on spreadsheet formulas: one checks whether a formula contains any references to other cells, the other computes the value of a formula (assuming it has no references to other cells – a spreadsheet would have to compute values for all referenced cells before evaluating a formula). We add these to the IFormula interface as follows:

  interface IFormula {
    // does formula reference other cells?
    boolean noRefs();
    // compute value of formula; ASSUMES noRefs
    int valueOf() ;
  }

Implementations of these operations are shown in the following classes:

  class Num implements IFormula {
    int value;
    Num(int value) {
      this.value = value;
    }

    public boolean noRefs() {
      return true;
    }

    public int valueOf() {
      return this.value;
    }

  }

  class CellRef implements IFormula {
    String cellname;

    CellRef(String cellname) {
      this.cellname = cellname;
    }

    public boolean noRefs() {
      return false;
    }

    public int valueOf() {
      throw new RuntimeException("Unresolved cell reference");
    }

  }

  class Plus implements IFormula {
    IFormula left;
    IFormula right;

    Plus(IFormula left, IFormula right) {
      this.left = left;
      this.right = right;
    }

    public boolean noRefs() {
      return this.left.noRefs() && this.right.noRefs();
    }

    public int valueOf() {
      return this.left.valueOf() + this.right.valueOf();
    }

  }

  class Examples {
    Examples(){}

    Num num2 = new Num(2);
    Num num5 = new Num(5);
    CellRef a10 = new CellRef("a10");
    Plus f1 = new Plus(num2, num5);
    Plus f2 = new Plus(a10, num5);

    boolean test1(Tester t) {
      return t.checkExpect(num2.noRefs(),true);
    }

    boolean test2(Tester t) {
      return t.checkExpect(num2.valueOf(),2);
    }

    boolean test3(Tester t) {
      return t.checkExpect(a10.noRefs(),false);
    }

    boolean test4(Tester t) {
      return t.checkExpect(f1.noRefs(), true);
    }

    boolean test5(Tester t) {
      return t.checkExpect(f2.noRefs(), false);
    }

    boolean test6(Tester t) {
      return t.checkExpect(f2.noRefs(), false);
    }

  }

You should understand this problem and the code prior to class on Thursday.