Bug 47358 - Could not resolve external workbook name. Workbook environment has not been set up
Summary: Could not resolve external workbook name. Workbook environment has not been s...
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.5-dev
Hardware: PC Windows Vista
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-06-11 12:58 UTC by kevinrmckee
Modified: 2009-06-11 13:18 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.