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.
