Bug 46537

Summary: Cannot get cell formula.
Product: POI Reporter: Matthew <matthew.knl>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED WONTFIX    
Severity: normal    
Priority: P2    
Version: 3.5-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: excel file

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)

--

Thanks!
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.

Regards,
Yegor
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.

Yegor