Bug 53178

Summary: RunTimeException: "Unexpected tAttr" in getCellFormula()
Product: POI Reporter: npaetsch
Component: HSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: major    
Priority: P2    
Version: 3.8-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Small example for the bug

Description npaetsch 2012-05-02 14:43:00 UTC
Created attachment 28709 [details]
Small example for the bug

Hi,

I got the following problem, I try to read the cell formula from an HSSFCell, of cell type "HSSFCell.CELL_TYPE_FORMULA":

if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
  String formula = cell.getCellFormula();
}


This all works well, unless in the attached excel sheet, where I get the following excpetion:

Exception in thread "main" java.lang.RuntimeException: Unexpected tAttr: org.apache.poi.ss.formula.ptg.AttrPtg []
        at org.apache.poi.ss.formula.FormulaRenderer.toFormulaString(FormulaRenderer.java:87)
        at org.apache.poi.hssf.model.HSSFFormulaParser.toFormulaString(HSSFFormulaParser.java:83)
        at org.apache.poi.hssf.usermodel.HSSFCell.getCellFormula(HSSFCell.java:628)


I don't really see the point. I assume, this is because the formula in the cell is rather long:

=WENN('E:\Berichtswesen\Programme\pis_esm.xla'!Twert(#BEZUG!C320;"h";#BEZUG!;#BEZUG!)/1000<LTC_ENTRY_FLEX_MIN/(23+ABS(SOWI-2))*1000-1;1;WENN('E:\Berichtswesen\Programme\pis_esm.xla'!Twert(#BEZUG!C320;"h";#BEZUG!;#BEZUG!)/1000>LTC_ENTRY_FLEX_MAX/(23+ABS(SOWI-2))*1000+1;1;0))
Comment 1 npaetsch 2012-05-03 08:14:35 UTC
I have to add, that the same exception occurs for the following formula:

=WENN(('E:\Berichtswesen\Programme\pis_esm.xla'!Tmark($Q$12;"STD";$B$4;$B$4)) = "N";"No Allocation";"")

So, somehow this exception occurs due to the nested functions.
Comment 2 npaetsch 2012-05-03 09:06:11 UTC
The exception is thrown in org.apache.poi.ss.formula.ForumlaRenderer.

When stepping through the source code while parsing the Excel-formula 

{=WENN(('E:\Berichtswesen\Programme\pis_esm.xla'!Tmark($Q$12;"STD";$B$4;$B$4)) = "N";"No Allocation";"")}

as indicated in my second comment, the following stack is already set up

stack	Stack<E>  (id=135)	
	capacityIncrement	0	
	elementCount	2	
	elementData	Object[10]  (id=139)	
		[0]	"(Tmark(#REF!,"STD",#REF!,#REF!))="N"" (id=143)	
		[1]	""No Allocation"" (id=1046)	
		[2]	null	
		[3]	null	
		[4]	null	
		[5]	null	
		[6]	null	
		[7]	null	
		[8]	null	
		[9]	null	
	modCount	18	

The next element ptgs[13] which is of instance AttrPtg then causes the exception.
Comment 3 Dominik Stadler 2016-02-17 21:41:39 UTC
Still reproducible in 3.14-beta1 with the following unit-test

    @Test
    public void test53178() {
        Workbook wb = HSSFTestDataSamples.openSampleWorkbook("53178.xls");
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);

        assertEquals(Cell.CELL_TYPE_FORMULA, cell.getCellType());
        assertEquals("", cell.getCellFormula());
    }