poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Zalewski <zalew...@optonline.net>
Subject RE: POI HSSF Excel filter cells (Data -> Filter -> AutoFilter) break HSSF
Date Thu, 03 Jun 2004 02:52:11 GMT
I have managed to avoid the InvocationTargetException by replacing the
static createSubRecord() in SubRecord.java with the following:

public static Record createSubRecord( short subRecordSid, short size, byte[]
data, int offset )
  Record r = null;

  short adjustedSize = size;
  if( size < 0) {
    adjustedSize = 0;
  } else if( offset + size > data.length) {
    adjustedSize = (short) (data.length - offset);
    if( adjustedSize >= 4) {
      adjustedSize -= 4;
  switch ( subRecordSid )
    case CommonObjectDataSubRecord.sid:
      r = new CommonObjectDataSubRecord( subRecordSid, adjustedSize, data,
offset );
    case GroupMarkerSubRecord.sid:
      r = new GroupMarkerSubRecord( subRecordSid, adjustedSize, data,
offset );
    case EndSubRecord.sid:
      r = new EndSubRecord( subRecordSid, adjustedSize, data, offset );
      r = new UnknownRecord( subRecordSid, adjustedSize, data, offset );

  return r;

Basically, whenever there is a combo box, there is an OBJ record in the
sheet (which is now being parsed as of 2.5).

An OBJ record is composed of several subrecords, each of which looks like
another BIFF record (a two byte record type, followed by a two byte packet
length, followed by a variable length packet of data which is interpreted
according to the record type). The problem is for subrecord type 0x13, which
is identified in the MSDN Documentation as 'List Box Data'. In every case
that I have seen, the length field for this subrecord will have a value of
0x1fee, but the actual length of the 0x13 subrecord appears to be 16 bytes

The patch above checks the length field of each subrecord. If the value in
the length field exceeds the size of the OBJ record, the size is adjusted to
fit to the end of the OBJ record less 4 bytes. (The final 4 bytes of every
OBJ record should be a ftEnd subrecord, with a zero byte payload - 2 bytes
for the subrecord ID, and 2 bytes for the subrecord length, both values are

-----Original Message-----
From: news [mailto:news@sea.gmane.org]On Behalf Of Neil Clifton
Sent: Wednesday, June 02, 2004 8:32 PM
To: poi-user@jakarta.apache.org
Subject: POI HSSF Excel filter cells (Data -> Filter -> AutoFilter) break

Anyone out there have any comments or suggestions for me?
Apparently if the HSSF file has cells that are set as filters using Data ->
Filter -> AutoFilter in excel, when reading, I get:

org.apache.poi.hssf.record.RecordFormatException: Unable to construct record
instance, the following exception occured: null

Also I see:

java.lang.reflect.InvocationTargetException: java.lang.
        at org.apache.poi.hssf.record.Record.fillFields(Record.java:91)
        at org.apache.poi.hssf.record.Record.<init>(Record.java:56)
        at org.apache.poi.hssf.record.ObjRecord.<init>(ObjRecord.java:62)
        at java.lang.reflect.Constructor.newInstance(Native Method)

This is easy for me to reproduce with a very simple spreadsheet.
Anyone else get this?
I guess that the excel filter cell is not supported.
I cannot find much recent reference to it.
This is unfortunate because I have seen this filter cell used in many
spreadsheets. It is useful and easy to apply.
I am working a project that will be loading data from some complex
into a database. (I had trouble finding this problem when the filter cells
in a hidden worksheet).
I would love to find and contribute a fix myself if the project could spare
time. I cannot fix it in my own time ...
Any help?
Questions welcome.

To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org

To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org

View raw message