Bug 52158

Summary: Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula by ValueRecordsAggregate
Product: POI Reporter: jdaues
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED LATER    
Severity: normal    
Priority: P2    
Version: 3.8-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: source and data

Description jdaues 2011-11-08 23:37:57 UTC
Created attachment 27911 [details]
source and data

Attached is a zip file containing:

Three class files:
EventBasedExcelExtractor.java
TextHSSFListener.java
TestEventBasedExcelExtractor.java

Two data files:
oneSheet-good.xls - no exception, this file was produced manually (ie typed)
oneSheet-bad.xls - throws exception below, this file was created with this line of code
							excelSheet.setFormula(row, column, excelFormula.toString());

where excelSheet is org.apache.poi.ss.usermodel.Sheet, row and column are int, excelFormula is StringBuffer

To run, use one file as a program argument to TestEventBasedExcelExtractor

Exception in thread "main" java.lang.RuntimeException: Coding Error: Expected ExpPtg to be converted from Shared to Non-Shared Formula by ValueRecordsAggregate, but it wasn't
	at org.apache.poi.ss.formula.ptg.ExpPtg.toFormulaString(ExpPtg.java:64)
	at org.apache.poi.ss.formula.FormulaRenderer.toFormulaString(FormulaRenderer.java:96)
	at org.apache.poi.hssf.model.HSSFFormulaParser.toFormulaString(HSSFFormulaParser.java:83)
	at eventbasedexcelextractor.TextHSSFListener.processRecord(TextHSSFListener.java:92)
	
	at org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener.processRecord(FormatTrackingHSSFListener.java:82)
	at org.apache.poi.hssf.eventusermodel.HSSFRequest.processRecord(HSSFRequest.java:112)
	at org.apache.poi.hssf.eventusermodel.HSSFEventFactory.genericProcessEvents(HSSFEventFactory.java:147)
	at org.apache.poi.hssf.eventusermodel.HSSFEventFactory.processEvents(HSSFEventFactory.java:106)
	at org.apache.poi.hssf.eventusermodel.HSSFEventFactory.processWorkbookEvents(HSSFEventFactory.java:65)
	at eventbasedexcelextractor.EventBasedExcelExtractor.triggerExtraction(EventBasedExcelExtractor.java:159)
	at eventbasedexcelextractor.EventBasedExcelExtractor.getText(EventBasedExcelExtractor.java:127)
	at eventbasedexcelextractor.TestEventBasedExcelExtractor.<init>(TestEventBasedExcelExtractor.java:39)
	at eventbasedexcelextractor.TestEventBasedExcelExtractor.main(TestEventBasedExcelExtractor.java:76)
Comment 1 Nick Burch 2011-11-08 23:48:57 UTC
If you're using the HSSF event model, you have to take care of a lot of the low level formula stuff yourself. In the user model, this is all taken care of

Looks like there is some of this needed, or we'll just need to skip these
Comment 2 jdaues 2011-11-09 14:04:47 UTC
The problem seems to me that the cell types don't match. It would seem reasonable to me that when I write with a method called setFormula() and then read a FormulaRecord in HSSFListener that it would not crash.

That having been said ...
>>Looks like there is some of this needed
How would I do this?

>>or we'll just need to skip these
Not sure what you are saying here. Are you proposing to change POI code?
Comment 3 jdaues 2011-11-09 16:22:03 UTC
How about adding this to ExpPtg:

protected final String formatReferenceAsString() {
    // Only make cell references as needed. Memory is an issue
    CellReference cr = new CellReference(getRow(), getColumn(), false, false);
    return cr.formatAsString();
}
Comment 4 jdaues 2011-11-09 16:27:45 UTC
Oops, no, that's not it...
Comment 5 jdaues 2011-11-09 16:55:07 UTC
I should add that in our implementation of setFormula, we set the cell type:

Cell cell = getCell( row, column );
cell.setCellType( Cell.CELL_TYPE_FORMULA );
cell.setCellFormula( formula );
Comment 6 jdaues 2011-11-09 18:40:57 UTC
The solution for me maybe to use
    HSSFCell createCell(short columnIndex, int type) 
instead of
    HSSFCell createCell(short columnIndex)
Comment 7 Nick Burch 2011-11-09 22:54:51 UTC
In HSSF UserModel, the support for working with Shared Formulas is centred around FormulaRecordAggregate and SharedFormulaRecord

In EventUserModel, you have to do something similar, but it won't be the simplest of things. When you hit a shared formula, you'll have to track it somewhere, so we can refer to it from later cells that re-use the same one

(In this case, the event code is also POI, so that handling needs to be somewhere in POI itself, likely in the text extraction related code)
Comment 8 Dominik Stadler 2016-04-10 09:46:56 UTC
Unfortunately there was no real conclusion on this issue, as far as I see only some discussion about possible approaches, but no update for many years, so I am closing this for now as I don't see much use in looking at this further currently. 

Please reopen or raise new bug-entries if there is something that should be done in POI itself here.