Hi there,
first of all - I really appreciate the new spreadsheet component - great job. That helps soooo much!.
I am wondering if there exists a way to access the POI interface that is used underneath. Is it possible to add user-defined functions? Or is it possible to do some “forced recalculation” on the server side?
Any help is highly appreciated
Hi,
You can access the full POI interface through spreadsheet.getWorkbook(). There’s also a shortcut to ge the currently active sheet: spreadsheet.getActiveSheet(). Many of the Spreadsheet API functions actually port directly to POI.
If you change the sheet from the POI interface, spreadsheet will not know about these changes so you’ll have to refresh the cells.
I haven’t played around with the excel functions myself yet, but as far as I understand, the spreadsheet will render what ever is in the currently active sheet. You can refresh specific cells with spreadsheet.refreshCells() or refresh all cells with spreadsheet.refreshAllCellValues(). From what I gather, if you change the formula, refreshing the cell should refresh the value in the UI.
Hope that helps a little.
Hi,
yes - this helps a lot. thanks for the quick reply.
I wanted to dig a little further as to how this might work.
The following article describes how to add a user defined formula to poi
http://poi.apache.org/spreadsheet/user-defined-functions.html
Need to see if the vaadin spreadsheet exposes all of the necessary interfaces.
What I’m trying to work out is how to propergate dynamic values to the UI based on a custom formula.
We plan on implementing a formula that essentially hooks the spreadsheet to a number of dynamicly generated values from our application. We build call centre systems so thinks like the number of people waiting in a queue.
It looks like we can simply call getCell and then update the value each time the value gets updated but I’m wondering how we know what cells the formula is currently used in.
It does look like each time the cell is evaluated we are passed a
OperationEvaluationContext
which I’m guessing keeps the cells location row/col.
If thats the case I guess we could simply cache the set of cells that are reported as using the formula and each time the underlying datasource changes simple force an update on each of the cached cells.
I’m a little concerned that poi (excel) caches calculated value and restores from the cache on load but I think the cache gets refreshed shortly after the workbook loads.
I will run some tests and report back the results.
In the mean time if you have any suggestions on how to do this they would be greatly appreciated.
OK, so wow that just worked.
I’ve just added a new formula to a vaadin spreadsheet and now I’m using it to caclulate interest payments.
Two classes
package au.com.noojee.dashboard;
import org.apache.poi.ss.formula.OperationEvaluationContext ;
import org.apache.poi.ss.formula.eval.ErrorEval ;
import org.apache.poi.ss.formula.eval.EvaluationException ;
import org.apache.poi.ss.formula.eval.NumberEval ;
import org.apache.poi.ss.formula.eval.OperandResolver ;
import org.apache.poi.ss.formula.eval.ValueEval ;
import org.apache.poi.ss.formula.functions.FreeRefFunction ;
/**
* A simple function to calculate principal and interest.
*
* @author Jon Svede
*
*/
public class CalculateMortgage implements FreeRefFunction {
@Override
public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
if (args.length != 3) {
return ErrorEval.VALUE_INVALID;
}
double principal, rate, years, result;
try {
ValueEval v1 = OperandResolver.getSingleValue( args[0]
,
ec.getRowIndex(),
ec.getColumnIndex() ) ;
ValueEval v2 = OperandResolver.getSingleValue( args[1]
,
ec.getRowIndex(),
ec.getColumnIndex() ) ;
ValueEval v3 = OperandResolver.getSingleValue( args[2]
,
ec.getRowIndex(),
ec.getColumnIndex() ) ;
principal = OperandResolver.coerceValueToDouble( v1 ) ;
rate = OperandResolver.coerceValueToDouble( v2 ) ;
years = OperandResolver.coerceValueToDouble( v3 ) ;
result = calculateMortgagePayment( principal, rate, years ) ;
checkValue(result);
} catch (EvaluationException e) {
e.printStackTrace() ;
return e.getErrorEval();
}
return new NumberEval( result ) ;
}
public double calculateMortgagePayment( double p, double r, double y ) {
double i = r / 12 ;
double n = y * 12 ;
//M = P [ i(1 + i)n ]
/ [ (1 + i)n - 1]
double principalAndInterest = //p + r + y;
p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1)) ;
return principalAndInterest ;
}
/**
* Excel does not support infinities and NaNs, rather, it gives a #NUM! error in these cases
*
* @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt>
*/
static final void checkValue(double result) throws EvaluationException {
if (Double.isNaN(result) || Double.isInfinite(result)) {
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
}
}package au.com.noojee.dashboard;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
import org.apache.poi.ss.formula.udf.DefaultUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.Workbook;
public class DataSource {
public static void initFormula(Workbook workbook) {
String[] functionNames = { "calculatePayment" };
FreeRefFunction[] functionImpls = { new CalculateMortgage() };
UDFFinder udfs = new DefaultUDFFinder(functionNames, functionImpls);
UDFFinder udfToolpack = new AggregatingUDFFinder(udfs);
workbook.addToolPack(udfToolpack);
}
}And to make it available to your spreadsheet:
// create the spreadsheet
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.setHeight("400px");
// inject the new formula evaulator
DataSource.initFormula(spreadsheet.getWorkbook());
layout.addComponent(spreadsheet);
// you can now use the formula '=calculatePayment(principle, rate, years)'It was so simply it was almost magic.
Now just need to work out how to live update the cells using the formula.