Java apache poi setting the cell formula

Question

I am try to set a cell formulat that references cells from other workbooks. However, when I open the programmatically generated workbook, the formula cells show up as #REF!. I print out the formulas that were generated in a log. If I cut and paste those into the cells, the numbers from the external workbooks is pulled in.

        String formula = "'C:\\tmp\\ForecastAggregate\\Total Products\\[ForecastWorksheet.xls]2010 Budget'!C10"; 
        HSSFCell cell = row.createCell(0); //row was created above
        cell.setCellFormula(formula);

Can anybode help?

Answer 1

Tried looking around, but the only information I could find for this issue is this apache bug report, which shows that it is a long standing bug:

https://issues.apache.org/bugzilla/show_bug.cgi?id=46670

Although referencing other sheets within the same workbook seems to work without a problem

Answer 2

I gave a quick test reading a formula from a worksheet, and it came out without the single quotes, as:

[test.xls]testsheet!A1

When I tried setting that as a formula, however, I got an error:

Exception in thread "main" java.lang.RuntimeException: No external workbook with name 'test.xls' Links seemed to suggest, when you're reading a formula that uses an external reference, you need to load the workbook into a formula evaluator and setup the environment. However, I tried this, but it still didn't work... but it may be something to look at:

 // Set up the workbook environment for evaluation
 HSSFFormulaEvaluator ev = new HSSFFormulaEvaluator(testwb); 
 String[] workbookNames = { "test.xls", };
 HSSFFormulaEvaluator[] evaluators = { ev, };
 HSSFFormulaEvaluator.setupEnvironment(workbookNames, evaluators); 

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.html

Answer 3

You can work around this problem by using dynamic evaluation ot the reference, via the INDIRECT function.

The INDIRECT function evaluates the value of a cell, where the cell to evaluate is fetched from the value of another cell.

For example,

1. In Cell A1, put your cell reference as plain text (not formula), e.g. "'C:\tmp\ForecastAggregate\Total Products\[ForecastWorksheet.xls]2010 Budget'!C10"

2. In A2, the cell where you want value of that referenced cell to appear, put the formula

   =INDIRECT(A1)

This will fetch the target cell from A1 and show its value in A2.

In Java, you could set up a pattern where you create a separate worksheet (e.g. "indirectRef") to manage indirects. Whenever you write an external reference to a cell, you instead write INDIRECT(xx) and put the reference in cell xx in the "indirectRef" worksheet. Given that Poi is mostly interfaces, you may be able to do this transparently with no code changes in your java spreadsheet builder code.

Excel also has an EVALUATE macro that dynamically evaluates formulas, which would be even easier to get working, but this is officially undocumented (but extensively documented elsewhere!) and it may not be supported on all versions of Office.

Hope this helps!