Bug 44958

Summary: Unknown Ptg in Formula: 0x2 (2) --- Data Tables not Supported
Product: POI Reporter: Brandon Hudgeons <brandon>
Component: HSSFAssignee: POI Developers List <dev>
Severity: enhancement    
Priority: P2    
Version: 3.0-dev   
Target Milestone: ---   
Hardware: Macintosh   
OS: Mac OS X 10.4   
Attachments: Excel Spreadsheet Containing Data Tables

Description Brandon Hudgeons 2008-05-08 23:44:51 UTC
Created attachment 21940 [details]
Excel Spreadsheet Containing Data Tables

Using the HSSFWorkbook(InputStream) constructor with the attached excel spreadsheet (containing data tables) yields:

WARN. Unread 170 bytes of record 0x1b8
Exception in thread "main" org.apache.poi.hssf.record.RecordFormatException: Unable to construct record instance
	at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java:208)
	at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.java:126)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:226)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:168)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:278)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:259)
	at HSSFTest.main(HSSFTest.java:29)
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:494)
	at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java:196)
	... 6 more
Caused by: org.apache.poi.hssf.record.RecordFormatException: java.lang.UnsupportedOperationException:  Unknown Ptg in Formula: 0x2 (2)
	at org.apache.poi.hssf.record.FormulaRecord.fillFields(FormulaRecord.java:109)
	at org.apache.poi.hssf.record.Record.<init>(Record.java:55)
	at org.apache.poi.hssf.record.FormulaRecord.<init>(FormulaRecord.java:89)
	... 11 more
Caused by: java.lang.UnsupportedOperationException:  Unknown Ptg in Formula: 0x2 (2)
	at org.apache.poi.hssf.record.formula.Ptg.createPtg(Ptg.java:361)
	at org.apache.poi.hssf.record.formula.Ptg.createParsedExpressionTokens(Ptg.java:98)
	at org.apache.poi.hssf.record.FormulaRecord.fillFields(FormulaRecord.java:107)
	... 13 more

I found this as well:

ptgTbl - data table (ptg = 0x02)
  This ptg indicates a data table.  It only occurs in a FORMULA record,
  never in an ARRAY or NAME record.  When ptgTbl occurs in a formula,
  it is the only token in the formula.  This indicates that the cell
  containing the formula is an interior cell in a data table; the table
  description is found in a TABLE record. Rows and columns which
  contain input values to be substituted in the table do not contain

  The token value for ptgTbl consists of the row and column of the
  upper left corner of the table's interior.

  Offset  Name    Size Contents
  ------  ----    ---- --------
  0   rwFirst  2  row number of upper left corner
  2   colFirst 1  column number of upper left corner
Comment 1 Nick Burch 2008-07-13 05:38:30 UTC
I've added support for the ptg, and some basic support for the TableRecord, to svn trunk.

However, there's no support yet for tables or arrays in the formula parser, or for tieing table records and formula records together in the usermodel, so you won't be able to do that much with these formulas yet
Comment 2 Josh Micich 2008-08-30 21:52:55 UTC
Some more progress as of svn r690636.

POI now does round trip re-serialization of workbooks with ArrayRecords and/or TableRecords without introducing errors.

However, POI is still not able to create/edit Array or Table formulas.