You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Justin Flowers <ju...@nexj.com> on 2016/09/13 14:27:42 UTC
Formulas don't throw exceptions but show up "#NAME"
Hi everyone!
I'm a new user for this mail group so let me know if I'm making any mistakes here!
So I'm having some issues getting user defined functions running. I've defined a STDEV.P and T.TEST custom functions and attached them using:
private static void setupCustomFormulas(Workbook wb){
String[] functionNames = { "STDEV.P", "T.TEST" } ;
FreeRefFunction[] functionImpls = { new STDEVP(), new TTEST() } ;
UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ;
UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ;
wb.addToolPack(udfToolpack);
}
Here's the evaluate method of the STDEV.P class:
class STDEVP implements FreeRefFunction {
@Override
public ValueEval evaluate(ValueEval[] arg0, OperationEvaluationContext arg1)
{
// Pull out values in the range provided
String firstPoint = arg0[0].toString().replace("]", "").split("!")[1].split(":")[0];
String secondPoint = arg0[0].toString().replace("]", "").split("!")[1].split(":")[1];
String sheetName = arg0[0].toString().split("\\[")[1].split("!")[0];
EvaluationWorkbook wb = arg1.getWorkbook();
EvaluationSheet sheet = wb.getSheet(wb.getSheetIndex(sheetName));
double value = 0;
char curCol = firstPoint.charAt(0);
char curRow = firstPoint.charAt(1);
char finalCol = secondPoint.charAt(0);
ArrayList<Double> values = new ArrayList<Double>();
while(curCol < finalCol){
value = getNumericCellValue(sheet, Character.toString(curCol) + Character.toString(curRow));
values.add(value);
curCol++;
}
// Calculate standard deviation for data set and return
return new NumberEval(getStdDev(values));
}
Here's the TTEST evaluate method:
class TTEST implements FreeRefFunction{
@Override
public ValueEval evaluate(ValueEval[] arg0, OperationEvaluationContext arg1)
{
for(int i = 0; i < arg0.length; i++){
System.out.println("ARGS: " + arg0[i]);
}
String start1 = arg0[0].toString().replace("]", "").split("!")[1].split(":")[0];
String end1 = arg0[0].toString().replace("]", "").split("!")[1].split(":")[1];
String start2 = arg0[1].toString().replace("]", "").split("!")[1].split(":")[0];
String end2 = arg0[1].toString().replace("]", "").split("!")[1].split(":")[1];
String sheetName = arg0[0].toString().split("\\[")[1].split("!")[0];
EvaluationSheet sheet = arg1.getWorkbook().getSheet(arg1.getWorkbook().getSheetIndex(sheetName));
double[] set1 = getValuesInRange(start1, end1, sheet);
double[] set2 = getValuesInRange(start2, end2, sheet);
TTest ttest = new TTest();
double t_statistic = ttest.t(set1, set2);
return new NumberEval(t_statistic);
}
How I write the formula cells:
for(int i = 0; i < grid.length; i++){
Row row = sheet.createRow((short)i);
for (int j = 0; j < grid[i].length; j++){
Cell cell = row.createCell(j);
try{
cell.setCellValue((Double)grid[i][j]);
}
catch(Exception e){
String val = (String) grid[i][j];
if (val != null && val.startsWith("=")){
val = val.replaceAll("=", "");
cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(val);
}
else{
cell.setCellValue(val);
}
}
}
}
So when I run my code and hit the evaluateAll() method no exceptions are thrown but when I open the workbook generated the default formulas are correctly calculated but the UDF formulas report back "#NAME?" as the value. When I click on the cell the contained formula is correct, though. Any ideas what I'm doing wrong here?
Thanks a lot!
Justin