Quick and dirty Circular Reference POI problem demonstration

gistfile1.scala

import org.apache.poi.ss.formula.{EvaluationWorkbook, EvaluationSheet, EvaluationCell, EvaluationName, FormulaParsingWorkbook, FormulaParser, FormulaType, WorkbookEvaluator, IStabilityClassifier}
import org.apache.poi.ss.formula.ptg.{Ptg, NamePtg, NameXPtg}
import org.apache.poi.ss.formula.udf.{UDFFinder, AggregatingUDFFinder}
import org.apache.poi.ss.formula.functions.FreeRefFunction
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.SpreadsheetVersion
import org.apache.poi.ss.formula.eval._

class DBUDFFinder extends UDFFinder {
	def findFunction(name: String): FreeRefFunction = {
		null
	}
}

class DBWorkbook extends EvaluationWorkbook with FormulaParsingWorkbook {

	val sheet = new DBSheet(this)

	/* from EvaluationWorkbook */

	def convertFromExternSheetIndex(externSheetIndex: Int): Int = 0
	def getExternalName(externSheetIndex: Int, externNameIndex: Int): EvaluationWorkbook.ExternalName = null
	def getExternalSheet(externSheetIndex: Int): EvaluationWorkbook.ExternalSheet = null
	def getFormulaTokens(cell: EvaluationCell): Array[Ptg] = {
		val _cell = cell.asInstanceOf[DBCell]
		var formulaText: String = _cell.getCellFormula()
		formulaText = formulaText.replaceAll("\\n", "").replaceAll("\\r", "")
		val result = FormulaParser.parse(formulaText, this, FormulaType.CELL, getSheetIndex(cell.getSheet()))
		result
	}
	def getName(namePtg: NamePtg): EvaluationName = {
		//int ix = namePtg.getIndex()
		//new DBEvaluationName(_uBook.getNameAt(ix), ix, this)
		null
	}
	def getName(name: String, sheetIndex: Int): EvaluationName = null
	def getSheet(sheetIndex: Int): EvaluationSheet = sheet
	def getSheetIndex(sheet: EvaluationSheet): Int = 0
	def getSheetIndex(sheetName: String): Int = 0
	def getSheetName(sheetIndex: Int): String = "Arkusz1"
	def getUDFFinder(): UDFFinder = null
	def resolveNameXText(ptg: NameXPtg): String = ""

	def getNameXPtg(name: String): NameXPtg = null

	/**
	 * gets the externSheet index for a sheet from this workbook
	 */
	def getExternalSheetIndex(sheetName: String): Int = 0
	/**
	 * gets the externSheet index for a sheet from an external workbook
	 * @param workbookName e.g. "Budget.xls"
	 * @param sheetName a name of a sheet in that workbook
	 */
	def getExternalSheetIndex(workbookName: String, sheetName: String): Int = 0
	
	def getSpreadsheetVersion(): SpreadsheetVersion = SpreadsheetVersion.EXCEL2007
}

class DBSheet(workbook: DBWorkbook) extends EvaluationSheet {
	def getCell(rowIndex: Int, columnIndex: Int): EvaluationCell = {
		val cell = new DBCell(rowIndex, columnIndex, this)
		cell
	}
}

class DBCell(rowIndex: Int, columnIndex: Int, sheet: DBSheet) extends EvaluationCell {
	def getValueFromDB: String = {
		println("%d, %d".format(rowIndex, columnIndex))
		(rowIndex, columnIndex) match {
			case (2,0) => "1"
			case (1,0) => "=A3+2"
			case (0,0) => "=A2+3"
			case (_, _) => "0"
		}
		//"1"
	}
	def getBooleanCellValue(): Boolean = {
		var value = getValueFromDB
//		value match {
//			case val2: Boolean => val2
//			case _ => false
//		}
		true
	}
	def getCellType(): Int = {
	val DBValue = getValueFromDB
		if(DBValue.startsWith("=")) {
		    Cell.CELL_TYPE_FORMULA
		} else {
		    Cell.CELL_TYPE_NUMERIC
		}
	}
	def getColumnIndex(): Int = columnIndex
	def getErrorCellValue(): Int = 0
	def getIdentityKey(): java.lang.Object = "1"
	def getNumericCellValue(): Double = {
		var value = getValueFromDB
		value.toDouble
//		value match {
//			case val2: Double => val2
//			case _ => new Double(0)
//		}
		//2
	}
	def getRowIndex(): Int = rowIndex
	def getSheet(): EvaluationSheet = sheet
	def getStringCellValue(): String = getValueFromDB
	
	//def getCellFormula(): String = "IF(B1+B2=2,30,2)"
	def getCellFormula(): String = {
	  val DBValue = getValueFromDB
	  if(DBValue.startsWith("=")) { //value.head == "="
	    DBValue.replaceFirst("^=", "") // .tail
	  } else {
	    ""
	  }
	}
}

object DBExcel {
  def main(args: Array[String]) {
    println("Hello, world!")
    val workbook = new DBWorkbook
    val workbookEvaluator = new WorkbookEvaluator(workbook, IStabilityClassifier.TOTALLY_IMMUTABLE, workbook.getUDFFinder())
    val eval = workbookEvaluator.evaluate(workbook.sheet.getCell(0,0))
    val value = eval match {
		case ne: NumberEval => ne.getNumberValue()
		case be: BoolEval => be.getBooleanValue()
		case se: StringEval => se.getStringValue()
		case ee: ErrorEval => ee.toString()
    }
    println("Should be 6, it is: %s" format value)
  }
}