Spread Sheet Plugin

The spread sheet plugin (SSP) provides users with a simple 10 x 10 spread sheet. Each cell contains a numeric value. The value may be determined by the user (see the "modify cell value" use case) or it may be the weighted sum of the values of other cells (see the "define cell formula" use case).

Specification Model

Modify cell value

The user types a new value into a cell. Dependent cells are automatically updated.

Priority: high

Define cell formula

The user enters a formula into a cell.

Priority: high

Main Scenario

User: selects the Sheet/Define Formula menu item.

SSP: Displays a dialog box prompting the user for the cell that will contain the formula and the formula.

Note that cells shall be referenced using a letter (A – J) indicating the column followed by an integer (1 – 10) indicating the row. For example: A3, C6, etc.

The syntax of a formula will be:

<Number> * <Cell> + <Number> * <Cell> + <Number> * <Cell> ...

For example:

3.14 * A5 + -2.78 * B6 + 14 * E2

Domain Model

Design Model

A TableView is a JPanel containing a TableController and a JTable control. The spread sheet functions as the JTable's table model.

A table consists of 10 rows of 10 cells, but this should be flexible. Each cell contains a value (which could be a Double). A cell may also contain a formula, which is invoked by the cell's update method.

Consider using the Publisher-Subscriber pattern to handle cell updates:

Implementation

One idea is to think of a formula as a table. For example, the formula

3*A1 + 4 * B2 + 5*C6

Might be represented as the table:

3 -> A1
4 -> B2
5 -> C6

Here's a sketch of the implementation:

class Formula extends Hashtable<Double, Cell> {
public Double apply() {
// multiply each key by the associated value
// return the sum of the products
}
}

If a cell depends on other cells, then its value must be updated any time one of the values in its provider cells changes. Here's a sketch:

class Cell extends Observable implements Observer {
private Double value;
private Formula formula;
public void update() {
if (formula != null) {
setValue(formula.apply());
}
}
public void setValue(Double newValue) {
value = newValue;
notifyObservers();
}
public void setFormula(Formula newFormula) {
formula = newFormula;
// add this cell as an observer to each cell
// in the formula.
}
}