Bug 47358

Summary:	Could not resolve external workbook name. Workbook environment has not been set up
Product:	POI	Reporter:	kevinrmckee
Component:	HSSF	Assignee:	POI Developers List <dev>
Status:	RESOLVED INVALID
Severity:	normal
Priority:	P2
Version:	3.5-dev
Target Milestone:	---
Hardware:	PC
OS:	Windows Vista

Description kevinrmckee 2009-06-11 12:58:54 UTC

I'm trying to run the sample code found at the bottom of http://poi.apache.org/spreadsheet/eval.html for re-calculating all formulas in a workbook (I had to modify the code to the following to get it to compile).

FileInputStream fis = new FileInputStream(filename);
        FileOutputStream out = new FileOutputStream(filename.replaceAll(".xls","temp.xls"));
        org.apache.poi.ss.usermodel.Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(sheetNum);
            RoadmapGUIView.updateProgressText("Updating sheet " + sheetNum);
            for(org.apache.poi.ss.usermodel.Row r : sheet) {
                for(org.apache.poi.ss.usermodel.Cell c : r) {
                    if(c.getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
        }
        wb.write(out);
        out.close();

When I run the program, I'm getting the error java.lang.RuntimeException: Could not resolve external workbook name 'rm0509.xls'. Workbook environment has not been set up.

rm0509.xls is a linked spreadsheet within the spreadsheet I'm trying to update.

I can't find any information on this error.

Can you help?

Comment 1 kevinrmckee 2009-06-11 12:59:33 UTC

P.S. I've tried poi-3.5-beta6-20090402.jar and poi-3.5-beta7-20090611.jar

Comment 2 Josh Micich 2009-06-11 13:18:06 UTC

You need to tell POI about all workbooks involved in the evaluation.  This is done with the method HSSFFormulaEvaluator.setupEnvironment()

You can get away with not doing this if the cell you are evaluation does not depend on the other workbook(s).  However, in your case the other workbook is required (hence the error).

----    ----
Here is some sample code:

String dirName = "c:/somedir/";
String bookNameA = "test.xls";
String bookNameB = "rm0509.xls";


HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(dirName + bookNameA));
HSSFWorkbook wbB = new HSSFWorkbook(new FileInputStream(dirName + bookNameB));

HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator feB = new HSSFFormulaEvaluator(wbB);

// Set up the workbook environment for evaluation
String[] workbookNames = { bookNameA, bookNameB, };
HSSFFormulaEvaluator[] evaluators = { evaluator, feB, };
HSSFFormulaEvaluator.setupEnvironment(workbookNames, evaluators);

// do an evaluation
HSSFCell cell = wb.getSheetAt(0).getRow(0).getCell(0);
evaluator.evaluateFormulaCell(cell);

----    ----

This feature is currently only implemented for HSSF.