

Apache POI を用いてエクセルに情報を出力し、セルにあるキャッシュされた値を取り出すために以下のようなプログラムを作成しました。

[code]

    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();
        }
    }
    
[/code]

添付画像よりわかるようにExcel側では正常に動いているはずなのですが、その値を取り出すと、以下のように0と返ってきてしまいます。

[code]

    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
    
[/code]

[![出力したExcel](https://i.stack.imgur.com/yPsPW.png)](https://i.stack.imgur.com/yPsPW.png)

最初はT.INV.2T関数のせいではと思ったのですが、ほかの関数ではどうなのかと思い、SUM関数を入れてみましたが結果は変わらず0.0と表示されます。色々調べてみたところ、計算結果がキャッシュされていない場合は0が返るようだと以下のサイトが言っていたのでセルの値をキャッシュさせる方法を調べてみたのですが、なかなか見つかりません。  
<http://www.ne.jp/asahi/hishidama/home/tech/apache/poi/cell.html#h_getCachedFormulaResultType>  
どのようにしたらセルの値をキャッシュできるかどうかを教えていただけると助かります。


CC BY-SA 4.0
2020-03-02T21:51:05.710
63509
2020-03-04T09:10:53.763
36217
question
java excel apache-poi
Apache POIでセルの値をキャッシュする方法を教えてください


私自身が確認したのではないですが、[Java日記～ApachePOIで表計算処理が終わらない～](https://www.cresco.co.jp/blog/entry/473/)の記事によると、質問者さんのソースで再計算のために設定している

[code]

    sheet.setForceFormulaRecalculation(true);
    
[/code]

はExcelでファイルを開いた際に再計算させるための処理のようです。Excelで開く前に再計算させる方法として参考サイトに記載されいている

[code]

    book.getCreationHelper().createFormulaEvaluator().evaluateAll();
    
[/code]

を使用してみてはいかがでしょうか。


CC BY-SA 4.0
2020-03-03T05:11:18.830
63517
2020-03-03T05:11:18.830
9515
63509
answer


英語版のスタックオーバーフローで質問してみた結果、以下のようにすると求めることができるようです。

[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();
        }
    }
    
[/code]


CC BY-SA 4.0
2020-03-04T09:10:53.763
63558
2020-03-04T09:10:53.763
36217
63509
answer