Figure 35.2 · Cells displaying themselves.

35.3 Formulas

In reality, a spreadsheet cell holds two things: An actual value and a formula to compute this value. There are three types of formulas in a spreadsheet:

1.Numeric values such as 1.22, -3, or 0.

2.Textual labels such as Annual sales, Deprecation, or total.

3.Formulas that compute a new value from the contents of cells, such as “=add(A1,B2)”, or “=sum(mul(2, A2), C1:D16)”

A formula that computes a value always starts with an equals sign and is followed by an arithmetic expression. The SCells spreadsheet has a particularly simple and uniform convention for arithmetic expressions: every

Cover · Overview · Contents · Discuss · Suggest · Glossary · Index

expression is an application of some function to a list of arguments. The function name is an identifier such as add for binary addition, or sum for summation of an arbitrary number of operands. A function argument can be a number, a reference to a cell, a reference to a range of cells such as C1:D16, or another function application. You’ll see later that SCells has an open architecture that makes it easy to install your own functions via mixin composition.

The first step to handling formulas is writing down the types that represent them. As you might expect, the different kinds of formulas are represented by case classes. Listing 35.5 shows the contents of a file named Formulas.scala, where these case classes are defined:

package org.stairwaybook.scells

trait Formula

case class Coord(row: Int, column: Int) extends Formula { override def toString = ('A' + column).toChar.toString + row

}

case class Range(c1: Coord, c2: Coord) extends Formula { override def toString = c1.toString +":"+ c2.toString

}

case class Number(value: Double) extends Formula { override def toString = value.toString

}

case class Textual(value: String) extends Formula { override def toString = value

}

case class Application(function: String, arguments: List[Formula]) extends Formula {

override def toString =

function + arguments.mkString("(", ",", ")")

}

object Empty extends Textual("")

Listing 35.5 · Classes representing formulas.

The root of the class hierarchy shown in Listing 35.5 is a trait Formula. This trait has five case classes as children:

Cover · Overview · Contents · Discuss · Suggest · Glossary · Index

Each case class overrides the toString method so that it displays its kind of formula in the standard way shown above. For convenience there’s also an Empty object that represents the contents of an empty cell. The Empty object is an instance of the Textual class with an empty string argument.

35.4 Parsing formulas

In the previous section you saw the different kinds of formulas and how they display as strings. In this section you’ll see how to reverse the process: to transform a user input string into a Formula tree. The rest of this section explains one by one the different elements of a class FormulaParsers, which contains the parsers that do the transformation. The class builds on the combinator framework given in Chapter 33. Specifically, formula parsers are an instance of the RegexParsers class explained in that chapter:

package org.stairwaybook.scells

import scala.util.parsing.combinator._

object FormulaParsers extends RegexParsers {

The first two elements of object FormulaParsers are auxiliary parsers for identifiers and decimal numbers:

def ident: Parser[String] = """[a-zA-Z_]\w*""".r def decimal: Parser[String] = """-?\d+(\.\d*)?""".r

As you can see from the first regular expression above, an identifier starts with a letter or underscore. This is followed by an arbitrary number of “word” characters represented by the regular expression code \w, which recognizes letters, digits or underscores. The second regular expression describes decimal numbers, which consist of an optional minus sign, one or more digits that are represented by regular expression code \d, and an optional decimal part consisting of a period followed by zero or more digits.

Cover · Overview · Contents · Discuss · Suggest · Glossary · Index

The next element of object FormulaParsers is the cell parser, which recognizes the coordinates of a cell, such as C11 or B2. It first calls a regular expression parser that determines the form of a coordinate: a single letter followed by one or more digits. The string returned from that parser is then converted to a cell coordinate by separating the letter from the numerical part and converting the two parts to indices for the cell’s column and row:

def cell: Parser[Coord] =

"""[A-Za-z]\d+""".r ˆˆ { s =>

val column = s.charAt(0).toUpper - 'A' val row = s.substring(1).toInt Coord(row, column)

}

Note that the cell parser is a bit restrictive in that it allows only column coordinates consisting of a single letter. Hence the number of spreadsheet columns is in effect restricted to be at most 26, because further columns cannot be parsed. It’s a good idea to generalize the parser so that it accepts cells with several leading letters. This is left as an exercise to you.

The range parser recognizes a range of cells. Such a range is composed of two cell coordinates with a colon between them:

def range: Parser[Range] = cell~":"~cell ˆˆ {

case c1~":"~c2 => Range(c1, c2)

}

The number parser recognizes a decimal number, which is converted to a Double and wrapped in an instance of the Number class:

def number: Parser[Number] =

decimal ˆˆ (d => Number(d.toDouble))

The application parser recognizes a function application. Such an application is composed of an identifier followed by a list of argument expressions in parentheses:

def application: Parser[Application] = ident~"("~repsep(expr, ",")~")" ˆˆ {

case f~"("~ps~")" => Application(f, ps)

}

Cover · Overview · Contents · Discuss · Suggest · Glossary · Index

The expr parser recognizes a formula expression—either a top-level formula following an ‘=’, or an argument to a function. Such a formula expression is defined to be a cell, a range of cells, a number, or an application:

def expr: Parser[Formula] =

range | cell | number | application

This definition of the expr parser contains a slight oversimplification because ranges of cells should only appear as function arguments; they should not be allowed as top-level formulas. You could change the formula grammar so that the two uses of expressions are separated, and ranges are excluded syntactically from top-level formulas. In the spreadsheet presented here such an error is instead detected once an expression is evaluated.

The textual parser recognizes an arbitrary input string, as long as it does not start with an equals sign (recall that strings that start with ‘=’ are considered to be formulas):

def textual: Parser[Textual] =

"""[ˆ=].*""".r ˆˆ Textual

The formula parser recognizes all kinds of legal inputs into a cell. A formula is either a number, or a textual entry, or a formula starting with an equals sign:

def formula: Parser[Formula] = number | textual | "="~>expr

This concludes the grammar for spreadsheet cells. The final method parse uses this grammar in a method that converts an input string into a

Formula tree:

def parse(input: String): Formula = parseAll(formula, input) match {

case Success(e, _) => e

case f: NoSuccess => Textual("["+ f.msg +"]")

}

} //end FormulaParsers

The parse method parses all of the input with the formula parser. If that succeeds, the resulting formula is returned. If it fails, a Textual object with an error message is returned instead.

Cover · Overview · Contents · Discuss · Suggest · Glossary · Index

package org.stairwaybook.scells import swing._

import event._

class Spreadsheet(val height: Int, val width: Int) ... { val table = new Table(height, width) {

...

reactions += {

case TableUpdated(table, rows, column) => for (row <- rows)

cells(row)(column).formula = FormulaParsers.parse(userData(row, column))

}

}

}

Listing 35.6 · A spreadsheet that parses formulas.

That’s everything there is to parsing formulas. The only thing that remains is to integrate the parser into the spreadsheet. To do this, you can enrich the Cell class in class Model by a formula field:

case class Cell(row: Int, column: Int) { var formula: Formula = Empty

override def toString = formula.toString

}

In the new version of the Cell class, the toString method is defined to display the cell’s formula. That way you can check whether formulas have been correctly parsed.

The last step in this section is to integrate the parser into the spreadsheet. Parsing a formula happens as a reaction to the user’s input into a cell. A completed cell input is modeled in the Swing library by a TableUpdated event. The TableUpdated class is contained in package scala.swing.event. The event is of the form:

TableUpdated(table, rows, column)

It contains the table that was changed, as well as a set of coordinates of affected cells given by rows and column. The rows parameter is a range

Cover · Overview · Contents · Discuss · Suggest · Glossary · Index

Figure 35.3 · Cells displaying their formulas.

value of type Range[Int].2 The column parameter is an integer. So in general a TableUpdated event can refer to several affected cells, but they would be on a consecutive range of rows and share the same column.

Once a table is changed, the affected cells need to be re-parsed. To react to a TableUpdated event, you add a case to the reactions value of the table component, as is shown in Listing 35.6. Now, whenever the table is edited the formulas of all affected cells will be updated by parsing the corresponding user data. When compiling the classes discussed so far and launching the scells.Main application you should see a spreadsheet application like the one shown in Figure 35.3. You can edit cells by typing into them. After editing is done, a cell displays the formula it contains. You can

2Range[Int] is also the type of a Scala expression such as “1 to N”.

Cover · Overview · Contents · Discuss · Suggest · Glossary · Index