How to cache the formula cell by using Apache POI

I created code as below to create excel file and calculate and get cell value after because I need to use function in excel.(this function is not supported by apache poi, so I think I have to read cached./formatted value)

package main;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Scanner;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WorkbookEvaluator
{
    public static void pop_mean()
    {
        System.out.println ("Test population mean in two different populations are same or not");
        System.out.println ("This program works only for two-tailed ");

        Scanner in = new Scanner(System.in);
        System.out.println ("What is population mean?:");
        double m = in.nextDouble();

        System.out.println ("How many samples are taken from population?:");
        double n = in.nextDouble();

        System.out.println ("What is Sample mean?:");
        double X = in.nextDouble();

        System.out.println ("What is unbiased variance for population:");
        double U = in.nextDouble();

        System.out.println ("What is Level of Significance (Type with %-value)");
        double L = in.nextDouble();
        double l = L/100;

            double a = n-1;
            double b = X-m;
            double c = Math.sqrt(n);
            double d = Math.sqrt(U);
            double f = d/c;
            double T = b/f;
            System.out.println ("Degree of freedom is " + a);
            System.out.println ("Test statistic is " + T);

            Workbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet();
            Row row1 = sheet.createRow(1);
            Row row2 = sheet.createRow(2);

            Cell cell1_1 = row1.createCell(1);
            Cell cell1_2 = row1.createCell(2);
            Cell cell1_3 = row1.createCell(3);
            Cell cell2_3 = row2.createCell(3);
            Cell cell2_4 = row2.createCell(4);

            cell1_1.setCellValue(l);
            cell1_2.setCellValue(a);
            cell2_3.setCellFormula("_xlfn.T.INV.2T(" + l +"," + a + ")");
            cell2_4.setCellFormula("SUM(" + l +"," + a + ")");

            FileOutputStream out = null;
            try {
                out = new FileOutputStream("T-inverse.xlsx");
                wb.write(out);
            } catch(IOException e) {
                System.out.println(e.toString());
            } finally {
                try {
                    out.close();
                } catch(IOException e) {
                    System.out.println(e.toString());
                }
            }
    }

    public static void read_excel() throws IOException
    {
        for (int q=3;q<5;q++)
        {
            XSSFWorkbook book = new XSSFWorkbook("C:\\Users\\shump\\Java\\Population mean Test\\T-inverse.xlsx");
            book.setForceFormulaRecalculation(true);
            XSSFSheet sheet = book.getSheetAt(0);
            sheet.setForceFormulaRecalculation(true);
            XSSFRow row = sheet.getRow(2);
            final DataFormatter dataFormatter = new DataFormatter(); 
            final double formtatedValue = row.getCell((short) q).getNumericCellValue(); 
            System.out.println(formtatedValue);
        }
    }

    public static void main(String[] args) throws IOException
    {    
        pop_mean();
        read_excel();
    }
}

As can be see from the attempted picture below, it works successfully in excel. However, consoled output will display 0.0. I thought this is because of function is not supported so I tried same thing by using sum function which is one of the basic function and this function is supported. However, it didn't work. In picture D3 is where I used T.INV.2T function.

Test population mean in two different populations are same or not
This program works only for two-tailed 
What is population mean?:
171.4
How many samples are taken from population?:
9
What is Sample mean?:
172.8
What is unbiased variance for population:
4
What is Level of Significance (Type with %-value)
5
Degree of freedom is 8.0
Test statistic is 2.1000000000000085
0.0
0.0

After few research, I found article which states when the formula value is not cached, output will be 0. So, please tell me how I can cache the value or is there any other way that I can take to get value of non-supported function.

You seems misunderstand what apache poi is made for. In terms of Excel it is made for creating Excel files. That meams it creates files which Excelis able to open then. When it opens workbook files, it opens them as apache poi Workbook only to be able to append content. Apache poi is not thought to be the same as Excel and it does not interact with any Excel application.

So the setForceFormulaRecalculation is not a apache poi setting but a setting for Excel when Excel opens the workbook. If setForceFormulaRecalculation is set true, then Excel recalculates all formulas when Excel opens the workbook file. It does not mean that apache poi recalculates all formulas.

To force apache poi calculating the formulas, FormulaEvaluator needs to be used.

For example if you extend your code as follows:

...
import org.apache.poi.ss.usermodel.FormulaEvaluator;
...
Workbook wb = new XSSFWorkbook();
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
...
cell2_4.setCellFormula("SUM(" + l +"," + a + ")");
try {
evaluator.evaluateFormulaCell(cell2_4);
} catch (org.apache.poi.ss.formula.eval.NotImplementedException notImplEx) {
notImplEx.printStackTrace();
}
...

then the SUM formula gets evaluated and cell2_4 contains the evaluated numeric value additional to the formula.

But of course

...
cell2_3.setCellFormula("TINV(" + l +"," + a + ")");
try {
evaluator.evaluateFormulaCell(cell2_3);
} catch (org.apache.poi.ss.formula.eval.NotImplementedException notImplEx) {
notImplEx.printStackTrace();
}
...

will lead to NotImplementedException since TINV is not yet implemented in apache poi.

So we need to do what is shown in Developing Formula Evaluation. In terms of the TINV function this would be:

...
import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.formula.eval.*;
import org.apache.poi.ss.formula.functions.*;
import org.apache.commons.math3.distribution.TDistribution;
...
static Function TINV = new Fixed2ArgFunction() {
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg1, ValueEval arg2) {
try {
ValueEval ve1 = OperandResolver.getSingleValue(arg1, srcRowIndex, srcColumnIndex);
double dArg1 = OperandResolver.coerceValueToDouble(ve1);
ValueEval ve2 = OperandResolver.getSingleValue(arg2, srcRowIndex, srcColumnIndex);
double dArg2 = OperandResolver.coerceValueToDouble(ve2);
TDistribution t = new TDistribution(dArg2);
double result = t.inverseCumulativeProbability(1d - dArg1/2d);
if (Double.isNaN(result) || Double.isInfinite(result)) {
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
return new NumberEval(result);
} catch (EvaluationException e) {
return e.getErrorEval();
}
}
};
...

and then

...
WorkbookEvaluator.registerFunction("TINV", TINV);
...

Note, I have implemented TINV instead of _xlfn.T.INV.2T since the latter is not able to be implemented that way because of it's strange name. All Excel versions I know also support TINV instead of _xlfn.T.INV.2T.

Complete example extending your code:

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Scanner;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.formula.eval.*;
import org.apache.poi.ss.formula.functions.*;
import org.apache.commons.math3.distribution.TDistribution;
public class WorkbookEvaluatorTest {
static Function TINV = new Fixed2ArgFunction() {
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg1, ValueEval arg2) {
try {
ValueEval ve1 = OperandResolver.getSingleValue(arg1, srcRowIndex, srcColumnIndex);
double dArg1 = OperandResolver.coerceValueToDouble(ve1);
ValueEval ve2 = OperandResolver.getSingleValue(arg2, srcRowIndex, srcColumnIndex);
double dArg2 = OperandResolver.coerceValueToDouble(ve2);
TDistribution t = new TDistribution(dArg2);
double result = t.inverseCumulativeProbability(1d - dArg1/2d);
if (Double.isNaN(result) || Double.isInfinite(result)) {
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
return new NumberEval(result);
} catch (EvaluationException e) {
return e.getErrorEval();
}
}
};
public static void pop_mean() {
WorkbookEvaluator.registerFunction("TINV", TINV);
System.out.println ("Test population mean in two different populations are same or not");
System.out.println ("This program works only for two-tailed ");
Scanner in = new Scanner(System.in);
System.out.println ("What is population mean?:");
double m = in.nextDouble();
System.out.println ("How many samples are taken from population?:");
double n = in.nextDouble();
System.out.println ("What is Sample mean?:");
double X = in.nextDouble();
System.out.println ("What is unbiased variance for population:");
double U = in.nextDouble();
System.out.println ("What is Level of Significance (Type with %-value)");
double L = in.nextDouble();
double l = L/100;
double a = n-1;
double b = X-m;
double c = Math.sqrt(n);
double d = Math.sqrt(U);
double f = d/c;
double T = b/f;
System.out.println ("Degree of freedom is " + a);
System.out.println ("Test statistic is " + T);
Workbook wb = new XSSFWorkbook();
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
Sheet sheet = wb.createSheet();
Row row1 = sheet.createRow(1);
Row row2 = sheet.createRow(2);
Cell cell1_1 = row1.createCell(1);
Cell cell1_2 = row1.createCell(2);
Cell cell1_3 = row1.createCell(3);
Cell cell2_3 = row2.createCell(3);
Cell cell2_4 = row2.createCell(4);
cell1_1.setCellValue(l);
cell1_2.setCellValue(a);
cell2_3.setCellFormula("TINV(" + l +"," + a + ")");
try {
evaluator.evaluateFormulaCell(cell2_3);
} catch (org.apache.poi.ss.formula.eval.NotImplementedException notImplEx) {
notImplEx.printStackTrace();
}
cell2_4.setCellFormula("SUM(" + l +"," + a + ")");
try {
evaluator.evaluateFormulaCell(cell2_4);
} catch (org.apache.poi.ss.formula.eval.NotImplementedException notImplEx) {
notImplEx.printStackTrace();
}
FileOutputStream out = null;
try {
out = new FileOutputStream("T-inverse.xlsx");
wb.write(out);
} catch(IOException e) {
System.out.println("Write: " + e.toString());
} finally {
try {
out.close();
wb.close();
} catch(IOException e) {
System.out.println("Close: " + e.toString());
}
}
}
public static void read_excel() throws IOException {
for (int q=3;q<5;q++) {
XSSFWorkbook book = new XSSFWorkbook("T-inverse.xlsx");
//book.setForceFormulaRecalculation(true);
XSSFSheet sheet = book.getSheetAt(0);
//sheet.setForceFormulaRecalculation(true);
XSSFRow row = sheet.getRow(2);
final DataFormatter dataFormatter = new DataFormatter(); 
final double formtatedValue = row.getCell((short) q).getNumericCellValue(); 
System.out.println(formtatedValue);
}
}
public static void main(String[] args) throws IOException {    
pop_mean();
read_excel();
}
}