Java apache poi sets cell formula

Question

I am trying to establish a cell formula that refers to cells from other books. However, when I open a program-generated workbook, the formula cells display as #REF !. I print out the formulas that were generated in the magazine. If I cut and paste them into cells, numbers from external books are pulled.

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

I quickly read the formula from the sheet, and it came out without single quotes, since:

[test.xls] testsheet! A1

However, when I tried to set this as a formula, I received an error message:

Exception in thread "main" java.lang.RuntimeException: there is no external workbook named "test.xls". Links seemed to prompt when you read a formula using an external link, you need to load the book into the formula evaluator and set up the environment. However, I tried this, but it still didn't work ... but maybe 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 2

You can get around this problem by using dynamic link estimation using INDIRECT .

The INDIRECT function evaluates the value of the cell, where the cell to be evaluated is extracted from the value of another cell.

For instance,

• In cell A1, place the cell reference in plain text (not formulas), for example. "C: \ tmp \ ForecastAggregate \ Total Products \ [ForecastWorksheet.xls] 2010 Budget !! C10"

• In A2, the cell where you want the value of this reference cell to display, put the formula

  = INDIRECT (A1)

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

In Java, you can customize a template in which you create a separate sheet (for example, "indirect Ref") to control indirect ones. Whenever you write an external link to a cell, you instead write INDIRECT (xx) and place the link in cell xx on the "indirect Ref" sheet. Given that Poi are basically interfaces, you can do this transparently without any code changes in the code of the java table.

Excel also has an EVALUATE macro that dynamically evaluates formulas that will make it even easier to work, but this is officially undocumented (but widely documented elsewhere!), And it may not be supported in all versions of Office.

Hope this helps!

Answer 3

I tried looking around, but the only information I could find for this problem was the apache error report, which shows that this is a long-standing error:

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

Although links to other sheets in the same book seem to work without problems