RE: Formulas don't throw exceptions but show up "#NAME"

[Justin Flowers]

OK, it was a sanity issue on my part. Apparently you can get this issue if a 
UDF is not defined. And of course I wrote a Java UDF here that it cannot find 
on its side. The functions I'm implementing already exist in Excel, just not 
yet in POI ("STDEV.P" and "T.TEST"). I think I'll fall back on just filling the 
cells with hardcoded values calculated in Java. If anyone has any clever 
solutions or sees any issues with my code please don't hesitate!

Info on :#NAME?" error and UDFs:
https://support.office.com/en-us/article/Correct-a-NAME-error-13416b02-2a46-4677-817f-608d0b98c734

From: Justin Flowers
Sent: September-13-16 10:28 AM
To: 'user@poi.apache.org' <user@poi.apache.org>
Subject: 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