You are hereBlogs / luano's blog / Polishing Scala SCells

Polishing Scala SCells


By luano - Posted on 21 October 2009

CellReference FormulaParsers FormulaException RichChar FormulaException TableUpdate FormulaError JTableMixin TableCellRenderer TableCellEditor AnyRef ValueChanged SpreadSheet SplitPane JTableMixin ListView TableHeader EmptyBorder LineBorder SystemColor TextField TableHeaderRenderer EditDone SwingConstants

Martin Odersky's book Programming in Scala ends with a demo spreadsheet application SCells. The example ends with a number of suggestions for improvements:

  1. Make the spreadsheet resizable
  2. Add new kinds of formulae
  3. Prevent recursive cell references
  4. Enhance error handling
  5. Add formula entry at the top of the spreadsheet.
  6. Add more functions

In addition to these improvements the SCells application could use some fixes, as it looks a bit ugly. Specifically

  1. The LAF is not set
  2. The row header doesn't show the current selection
  3. The row header doesn't show the rows
  4. The cell references are zero based instead of one based as is normal for spreadsheets.
  5. Only single cell selection is supported, not ranges

SCells also creates cells and listeners for every possible cell whether or not those cells are actually used and this just seems wasteful.

Sparse storage

Typically very few cells within a spreadsheet are actually populated, so why create storage for each cell. Indeed why add listeners to these cells if nothing ever happens there? The original SCells application created cells and listeners for every cell in the spreadsheet and this made it straightforward to references cells by their coordinates.

The updated SCells uses a hash table instead of an array for storing data so that only those cells with data are actually stored. A hash table may not be the most efficient structure if a row or column of values is being accessed, but typically a spreadsheet uses references that jump around and do not just follow a column or row, so a hadh table should be ok. Once a hash table is employed we no longer have a way of directly referencing a cell by its row and column index, so a new class CellReference was added. The CellReference class acts as a key into the hash table.

  1. package scells
  2.  
  3. import runtime.RichChar
  4.  
  5. class CellReference( val row:Int, val column:Int ) {
  6. override def hashCode() = 41 * ( 41 + row ) + column
  7.  
  8. override def equals(other: Any) = other match {
  9. case that: CellReference =>
  10. (that canEqual this) &&
  11. (this.row == that.row) && (this.column == that.column)
  12. case _ =>
  13. false
  14. }
  15.  
  16. def canEqual(other: Any) = other.isInstanceOf[CellReference]
  17.  
  18. override def toString() : String =
  19. {
  20. val aint:Int = 'A'.asInstanceOf[Int]
  21. var buf = new Array[Byte](32)
  22. var charPos = 32
  23. var col = column + 1
  24. do {
  25. charPos -= 1
  26. buf(charPos) = (aint + (col-1) % 26).asInstanceOf[Byte]
  27. col /= 26
  28. } while (col != 0)
  29.  
  30. new String( buf.toArray, charPos, (32 - charPos)) + Integer.toString( row + 1 )
  31. }
  32. }

The array access was therefore replaced with hash table lookup, which for the most part was straightforward. However, in some cases we need to check the contents of the hash table. In the case of the TableUpdate a new method setValue is introduced that creates a new cell when needed and adds or removes the listeners as needed - for example when the contents of a cell is removed.

  1. private def setCellValue( ref:CellReference, data:String) =
  2. {
  3. val cell:Option[Cell] = cells.get(ref)
  4. if (cell == None) {
  5. val cell = new Cell(ref.row, ref.column)
  6. cells(ref) = cell
  7. listenTo(cell)
  8. }
  9.  
  10. if (data == "") {
  11. if ( cell != None )
  12. deafTo( cell.get )
  13. cells -= ref
  14. }
  15. else {
  16. try {
  17. cells(ref).formula = FormulaParsers.parse(data)
  18. }
  19. catch {
  20. case ex: FormulaException => cells(ref).formula = new Error( ex.message )
  21. }
  22. }
  23. }

One quirk of this use of the hash tables is that Scala's normal hash table access will throw an exception if the key is not found and therefore the get method must be called so that the return value can be checked. If no return value is found then a new entry needs to be added.

Fixing the range

The original scells only allowed a single letter for the column selection and the rows were zero based. Fixing the row index is simply a matter of adding an offset, but correction the column settings required a little extra work, so that references such as AA22 could be parsed. The FormulaParser class was therefore modified as follows:

  1. def cell: Parser[Coord] =
  2. """[A-Za-z][A-Za-z]*\d\d*""".r ^^ { s =>
  3. val su = String.valueOf(s).toUpperCase
  4. var len = su.length
  5.  
  6. var column = 0
  7. var idx:Int = 0
  8. while ( idx < len ) {
  9. val c:Char = su.charAt(idx)
  10. if ( Character.isDigit(c))
  11. len = 0
  12. else {
  13. if ( idx > 0 )
  14. column += 1
  15. column *= 26
  16. column += c - 'A'
  17. idx += 1
  18. }
  19. }
  20. val row = s.substring(idx).toInt -1
  21. Coord(row, column)
  22. }

The range can now be several characters wide and it is now case insensitive.

Being new to Scala, this probably isn't the most efficient way to process this pattern, so please comment if you have a better solution.

Preventing circular references

The Scala book suggests setting allowing just one iteration to be computed, but some spreadsheet users program recursive solutions for problem solvers (even claiming that the likes of Excel are high performance computing engines), so stopping at one iteration seems a bit short sighted (Excel defaults to 100 iterations).

In order to implement this iteration counting the Model class and its setters need to be modified:

  1. private var MAX_ITERATION_COUNT = 100
  2. private var updateCount: Int = MAX_ITERATION_COUNT
  3. def resetIteration {
  4. updateCount = MAX_ITERATION_COUNT
  5. }
  6.  
  7. ...
  8.  
  9. def value_=(w: Double) {
  10. if (!(v == w || v.isNaN && w.isNaN)) {
  11. if ( updateCount > 0 ) {
  12. updateCount -= 1
  13. v = w
  14. publish(ValueChanged(this))
  15. }
  16. else {
  17. updateCount = MAX_ITERATION_COUNT
  18. this.f = new Error("Circular reference")
  19. throw new FormulaException(this, "Circular reference detected")
  20. }
  21. }
  22. }
  23.  
  24. ...
  25.  
  26. def formula_=(f: Formula) {
  27. if ( updateCount > 0 ) {
  28. updateCount -= 1
  29. for (c <- references(formula)) deafTo(c)
  30. this.f = f
  31. for (c <- references(formula)) listenTo(c)
  32. value = evaluate(f)
  33. }
  34. else {
  35. updateCount = MAX_ITERATION_COUNT
  36. this.f = new Error("Circular reference")
  37. throw new FormulaException(this, "Circular reference detected")
  38. }
  39. }
  40. }

If the iteration threshold is exceeds the counter is reset and an exception is thrown. The FormulaException is a new exception.

Better warnings

The new exception is created to carry some extra information about the cause of the error (and the same pattern could be followed for other exceptions):

  1. package scells
  2.  
  3. import swing._
  4.  
  5. class FormulaException(val cell:Publisher, val message: String)
  6. extends Exception(message)
  7. {
  8.  
  9. /**
  10.   * uses <code>null</code> as its error detail message.
  11.   */
  12. def this() = this(null, null)
  13.  
  14. private var rootCause: Throwable = null
  15.  
  16. /**
  17.   *
  18.   * @param nestedException the underlying exception which caused the
  19.   * FormulaException to be thrown
  20.   */
  21. def setRootCause(nestedException: Throwable )
  22. {
  23. this.rootCause = nestedException
  24. }
  25.  
  26. /**
  27.   *
  28.   * @return Throwable the underlying exception which caused the
  29.   * <code>FormulaException</code> to be thrown
  30.   */
  31. def getRootCause(): Throwable = this.rootCause
  32. }

In order to make use of this exception it is necessary to catch the exception and output the message as illustrated in the setValue method above. The trick here is to create a case class to hold the error information rather than output the NaN as before, and without loosing the input text. We simple define a Error:

  1. case class Error(msg: String) extends Formula

Improving the appearance

The appearance of the SCells application is a bit basic and an easy fix is to set the Look and Feel, and this can be done as follows within the Main class

  1. UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName())

The rendering of the cells is also a bit basic, but this can be fixed with ease by any experienced Swing developer, however one thing stands out and this is that the SCells application creates a new renderer component each the renderer is acquired and this just seems wasteful. The revised SCells therefore saves the renderer as a member and customizes it with the UIManager values:

  1. override def rendererComponent(
  2. isSelected: Boolean,
  3. hasFocus: Boolean,
  4. row: Int,
  5. column: Int): Component =
  6. {
  7. if ( hasFocus ) {
  8. focusRowIdx = row;
  9. rowHeader.repaint
  10.  
  11. focusCellRef = new CellReference(row,column)
  12. nameField.text = focusCellRef.toString
  13. // No selection Background in accordance with Excel.
  14. // if ( isSelected )
  15. // editField.background = UIManager.getColor( "Table.selectionBackground" )
  16. // else
  17. // editField.background = UIManager.getColor( "Table.background" )
  18.  
  19. val rawContents = userData(row, column)
  20. inputField.text = rawContents
  21. editField.text = rawContents
  22. editField
  23. }
  24. else {
  25. val cell:Option[Cell] = cells.get(new CellReference(row,column))
  26. val contents = if ( cell != None ) cell.get.toString else ""
  27.  
  28. if ( isSelected )
  29. labelRenderer.background = UIManager.getColor("Table.selectionBackground")
  30. else
  31. labelRenderer.background = UIManager.getColor("Table.background")
  32. labelRenderer.text = contents
  33. labelRenderer
  34. }
  35. }

Changing the row header

Initially I tried setting the properties of the renderer for the row header so that it would look like the table header, but this did't work and it didn't match the look and feel. On top of this the initial approach fell short of what was needed as the renderer didn't follow the mouse selection - something I wanted to add. Therefore I borrowed so Java code from the Aria framework and added it as the renderer (and ultimately I changed the table header to match).

  1. val rowHeader = new ListView((0 until height) map (_.toString))
  2. {
  3. fixedCellWidth = col_width
  4. fixedCellHeight = row_height
  5. background = UIManager.getColor( "TableHeader.background" )
  6.  
  7. peer.setCellRenderer( new TableHeaderRenderer( peer ))
  8. }

Adding an input field

Adding the input field and a cell reference is straightforward Swing. The one trick is to have the edit field echo its content to the input field, and this can be accomlished by making the text fields share the same document model.

  1. val nf = new TextField
  2. nf.border = null
  3. nf.peer.setHorizontalAlignment( javax.swing.SwingConstants.CENTER )
  4.  
  5. val tf = new TextField
  6. tf.border = Swing.EmptyBorder(0, 6, 0, 0)
  7.  
  8. val editField = new TextField
  9. editField.border = Swing.LineBorder(SystemColor.controlShadow)
  10. editField.peer.setDocument( tf.peer.getDocument )
  11.  
  12. val inputBar = new SplitPane( Orientation.Vertical, nf, tf ) {
  13. dividerSize = 1
  14. dividerLocation = 40
  15. }
  16. val sheet = new SpreadSheet(100, 100, nf, tf, editField)
  17. contents = new SplitPane( Orientation.Horizontal, inputBar, sheet)
  18.  
  19. reactions += {
  20. case EditDone(source) => {
  21. sheet.setValue(source.text)
  22. }
  23. }
  24.  
  25. listenTo(tf)

Selecting the edit value

One of the biggest problems I faced in revising SCells was in selecting the cell value for editing. Normally when the user selects a cell and starts typing the old value is erased. The original SCells just appended the new characters.

The long and short of it is that the cell contents needs to be selected when the cell starts editing. I tried to find an alternative solution but couldn't get one that worked consistently and therefore I had to go to the extreme of providing a patched version of the Table class.

The solution is to use the prepareEditor method to select the cell contents, but since the Table class didn't wrap this method and since it doesn't subclass JTable directly I was left with no option but to create the patched class. The modification is as follows:

  1. override lazy val peer: JTable = new JTable with Table.JTableMixin {
  2. def tableWrapper = Table.this
  3. override def getCellRenderer(r: Int, c: Int) = new TableCellRenderer {
  4. def getTableCellRendererComponent(table: JTable,
  5. value: AnyRef,
  6. isSelected: Boolean,
  7. hasFocus: Boolean,
  8. row: Int,
  9. column: Int) =
  10. Table.this.rendererComponent(isSelected, hasFocus, row, column).peer
  11. }
  12. override def getCellEditor(r: Int, c: Int) = editor(r, c)
  13. override def getValueAt(r: Int, c: Int) = Table.this.apply(r,c).asInstanceOf[AnyRef]
  14. override def prepareEditor(editor:TableCellEditor, r: Int, c: Int) = {
  15. val e = super.prepareEditor(editor, r, c)
  16. prepare(e, r, c)
  17. e
  18. }
  19. }

What next?

There are still a few minor details to work out but the SCells application now looks and feels a bit more like the real thing. Of course it's still a long way off of being an Excel class application but it no longer looks as throwaway as the original SCells.

Lessons?

So what did I learn from this Scala learning exercise? Well Scala is very nice for some features - certainly the formula parsing and evaluation is nice, but I'm not so convince by the Swing support. Sure it saves some code, but it just seems to get in the way when trying to do things like prepare the editor. Granted that it isn't terribly common, however most applications use some such methods sooner or later./p>

I can't help feeling that the use of wrappers and peers is wrong. I've never seen this idiom work well and I've tried it myself before. Wrappers may allow for a clean API, but that leaves an awful lot of work if you want to cover a large API like Swing in any breath. I wasn't doing anything that wild with this approach and I ran into some blocking issues. Maybe I've missed something?

Source Code

Source Code for the SCells application