Bug 57798 - Getting java.lang.IllegalStateException: Only formula cells have cached results for array formulas
Summary: Getting java.lang.IllegalStateException: Only formula cells have cached resul...
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.12-dev
Hardware: PC All
: P2 major with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-04-08 08:24 UTC by ftz
Modified: 2016-06-02 20:11 UTC (History)
1 user (show)



Attachments
xlsx version (10.18 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-04-08 08:24 UTC, ftz
Details
xls version (28.00 KB, application/vnd.ms-excel)
2015-04-08 08:25 UTC, ftz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description ftz 2015-04-08 08:24:21 UTC
Created attachment 32637 [details]
xlsx version

The method cell.getCachedFormulaResultType() returns an exception when the cell is part of an array formula and is not the first cell of the array formula.

This exception is not occurring with a HSSFWorkbook.

See sample code below and file attached.
You'll need to save the .xlsx version as .xls in Excel to run the working test case.



import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;

public class TestXssf {

    public static void main(String[] args) throws Exception {

        String fileName = "Test.xlsx";
//        String fileName = "Test.xls";

        FileInputStream inputStream = new FileInputStream(fileName);
        Workbook workbook = WorkbookFactory.create(inputStream);

        Sheet sheet = workbook.getSheet("Sheet1");

        // *******************************
        // First cell of array formula, OK
        int rowId = 0;
        int cellId = 1;
        System.out.println("Reading row " + rowId + ", col " + cellId);

        Row row = sheet.getRow(rowId);
        Cell cell = row.getCell(cellId);

        System.out.println("Formula:" + cell.getCellFormula());
        if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
            int formulaResultType = cell.getCachedFormulaResultType();
            System.out.println("Formual Result Type:" + formulaResultType);
        }

        // *******************************
        // Second cell of array formula, NOT OK for xlsx files  
        rowId = 1;
        cellId = 1;
        System.out.println("Reading row " + rowId + ", col " + cellId);

        row = sheet.getRow(rowId);
        cell = row.getCell(cellId);
        System.out.println("Formula:" + cell.getCellFormula());

        if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
            int formulaResultType = cell.getCachedFormulaResultType();
            System.out.println("Formual Result Type:" + formulaResultType);
        }

        workbook.close();

    }
}
Comment 1 ftz 2015-04-08 08:25:10 UTC
Created attachment 32638 [details]
xls version
Comment 2 Dominik Stadler 2016-05-22 20:30:50 UTC
I could not reproduce this with the latest trunk, can you try if it was actually fixed by some change already now?
Comment 3 Dominik Stadler 2016-06-02 20:11:11 UTC
I have added a uni-test which verifies that this works for us, see r1746624 for the test. Please reopen this bug if this is still a problem for you with the latest version of POI.