Bug 46537 - Cannot get cell formula.
Summary: Cannot get cell formula.
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.5-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2009-01-15 00:42 UTC by Matthew
Modified: 2009-01-15 23:06 UTC (History)
0 users

excel file (19.51 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2009-01-15 00:42 UTC, Matthew

Note You need to log in before you can comment on or make changes to this bug.
Description Matthew 2009-01-15 00:42:02 UTC
I run following codes cannot get the cell formula from attached excel file.

Sheet sheet = wb.getSheet("Test");
for(int i=1; i <= sheet.getLastRowNum(); i++)
	Row row = sheet.getRow(i);
	Cell cell = row.getCell(2);
	String formula = cell.getCellFormula();
	System.("formula: " + formula);

It throws "Not implemented yet" exception, I know it means POI project team is working on it. But the formula of first row of the formula cells can be retrieved, is it possible I can get the other cell formula as well? I don't need to evaluate the formula, just the formula string is enough.

java.lang.RuntimeException: Not implemented yet
	at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getNameXPtg(XSSFEvaluationWorkbook.java:92)
	at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:541)
	at org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:323)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:724)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:684)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:671)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:973)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1074)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1058)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1015)
	at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:994)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1116)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:191)
	at org.apache.poi.xssf.usermodel.XSSFCell.convertSharedFormula(XSSFCell.java:352)
	at org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:332)
	at com.dttn.excel.connector.TestPOI.printCellFormula(TestPOI.java:96)
	at com.dttn.excel.connector.TestPOI.main(TestPOI.java:71)


Comment 1 Matthew 2009-01-15 00:42:34 UTC
Created attachment 23128 [details]
excel file
Comment 2 Nick Burch 2009-01-15 03:23:37 UTC
What's the formula in that cell?

(There's something odd about that formula which means xssf can't process it, but someone like Josh will need to know what the formula is to figure out how hard it'd be to implement)
Comment 3 Yegor Kozlov 2009-01-15 05:52:27 UTC
The sheet contains a shared formula with a user-defined VBA function. 

In short, shared formula is a template. Excel stores it in the first cell of a group of cells, other cells just substitute column and row references as appropriate. 

In your example the shared formula is used as follows:

concatstring(D2:M2) //the template formula is in C2

concatstring(D3:M3) //C3 does not contain a formula, instead it takes the shared formula from above and sets rownum=3
concatstring(D4:M4) //the same but for rownum=4

To process shared formulas POI needs to parse them, update cell area arguments and convert the parsed expression back to String.  POI can do that for formulas with built-in Excel functions but not for custom VBA functions.

Comment 4 Matthew 2009-01-15 18:24:48 UTC
Is it later POI will support getting custom VBA function name? (no need to evaluate the formula result, just get the formula string only)
Comment 5 Yegor Kozlov 2009-01-15 23:06:51 UTC
(In reply to comment #4)
> Is it later POI will support getting custom VBA function name? (no need to
> evaluate the formula result, just get the formula string only)

I think yes, it will eventually, but I'm not sure how soon.