Bug 9908

Summary: UnsupportedOperationException
Product: POI Reporter: Rudolf Ziegaus <Extern.Rudolf.Ziegaus>
Component: HSSFAssignee: POI Developers List <dev>
Status: CLOSED FIXED    
Severity: normal    
Priority: P3    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: ZIP-file containing trouble-causing spreadsheet

Description Rudolf Ziegaus 2002-06-17 06:27:24 UTC

 I am using the most recent nightly build. When I try to create a workbook using

  HSSFWorkbook wb = new HSSFWorkbook(fs);

where fs refers to an existing file, I get the following exception:

java.lang.UnsupportedOperationException: 3c (60)
org.apache.poi.hssf.record.formula.Ptg.createPtg(byte [], int)
org.apache.poi.hssf.record.NameRecord.getParsedExpressionTokens(byte [], short, 
int, int)
	void org.apache.poi.hssf.record.NameRecord.fillFields(byte [], short, 
	void org.apache.poi.hssf.record.Record.fillFields(byte [], short)
	org.apache.poi.hssf.record.Record(short, short, byte [])
	org.apache.poi.hssf.record.NameRecord(short, short, byte [])
	java.lang.Object java.lang.reflect.Constructor.newInstance
(java.lang.Object [])
	org.apache.poi.hssf.record.Record [] 
org.apache.poi.hssf.record.RecordFactory.createRecord(short, short, byte [])
	java.util.List org.apache.poi.hssf.record.RecordFactory.createRecords
	void de.rz.demos.poi.ExcelReaderTest.main(java.lang.String [])
Comment 1 Rudolf Ziegaus 2002-06-17 06:30:05 UTC
Sorry, I forgot to mention that I am using the latest nightly build from 
version 1.6.0.

Comment 2 Avik Sengupta 2002-06-17 09:26:32 UTC
Thanks, the stack trace is all i needed. Why oh why doesnt netbeans have a "Find
Use" button?? :)
Will fix in a couple of hours. 
Comment 3 Avik Sengupta 2002-06-17 17:26:25 UTC
Fixed. Namerecord is now resilient against unknown ptgs. Will write back exactly
what is read, unless record changed from usermodel, in which case new (correct)
rec ord will be written back. getReference on namerecord will yeild "#REF!" in
these cases. The Ptgs that are missing as far as namerecord is concerned is
primarily the  error ptgs (RefErr, AreaErr, RefErr3d and AreaError3d). While we
will add these ptgs later, we are still pretty much functionally complete at the

Rudolf, will you please cross check with you sheet? thanks. (get CVS HEAD,  or
get a new nightly tomorrow)

Even better, if it is possible for you to send us the sheet causing the error,
we can add it to our unit tests. 
Comment 4 Rudolf Ziegaus 2002-06-18 06:35:17 UTC
Created attachment 2112 [details]
ZIP-file containing trouble-causing spreadsheet
Comment 5 Rudolf Ziegaus 2002-06-18 06:38:05 UTC
The problem has gone away with yesterday's nightly build, so the error seems to 
be fixed. However, I do not get any rows out of my spreadsheet. It says, it has 
0 rows in it. Shall I open a new bug for this problem?
Comment 6 Avik Sengupta 2002-06-18 06:51:23 UTC
Well, your sheet seems to have lots of relational operations which we dont
support at the moment. However, the idea is that POI gives you all the other
stuff as it is, irrespective of what it cant understand. So this would be a bug.
Hold on for a moment, let me check. 
Comment 7 Avik Sengupta 2002-06-18 07:47:29 UTC
I can successfully run you file through formula viewer. So the problem must be
elsewhere. investigating. 
Comment 8 Avik Sengupta 2002-06-18 18:17:37 UTC
your formula has some kind of hidden sheet which is what you get at
wb.getSheetAt(0). try wb.getSheetAt with 1,2,or 3 which will give you rows 88,48
and 44 respectively. 
Comment 9 Rudolf Ziegaus 2002-06-19 09:32:45 UTC
I have tried as suggested (reading sheets 1, 2 or 3) but end with a range check 
exception from ArrayList, since the workbook has only one sheet listed (the 
size property equals 1).

Did you write any code to show my sheets or did you use another tool? 

Please verify again.

Comment 10 Avik Sengupta 2002-06-19 09:55:18 UTC
The follwoing test passes with the file you have attached above!

  public void testReadComplicated()
        throws java.io.IOException
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/Ziegaus.xls";
        java.io.FileInputStream stream   = new java.io.FileInputStream(filename);
        org.apache.poi.poifs.filesystem.POIFSFileSystem fs       = new
        HSSFWorkbook    workbook = new HSSFWorkbook(fs);
        HSSFSheet       sheet    = workbook.getSheetAt(1);

        assertTrue("sheet has more than one row",sheet.getLastRowNum()>0);
        sheet = workbook.getSheetAt(2);
        assertTrue("sheet has more than one row",sheet.getLastRowNum()>0);
        sheet = workbook.getSheetAt(3);
        assertTrue("sheet has more than one row",sheet.getLastRowNum()>0);

Please remember that excel can apply strange optimisations each time you save
the file (unlike poi, its not consistent :) so ensure that you are testing with
exactly the same file. 
Comment 11 Avik Sengupta 2002-06-19 10:02:16 UTC
Forgot to add, the test FAILS if i do 

sheet = workbook.getSheetAt(0);
assertTrue("sheet has more than one row",sheet.getLastRowNum()>0);
Comment 12 Rudolf Ziegaus 2002-06-19 10:19:04 UTC
Hm, very strange. What version do you use for your test? My testing code is 
very similiar to yours, I just do not use assert statements.

I used the latest nightly build and still get this problem, that I have only 
one sheet. If I try to access sheet 1 (getSheetAt(1)), I get an exception.
I verified that the file is in fact identically with the one I sent to you. I 
did not touch the file with Excel since June, 3rd.

Can I check which version I do effectively use? Is there an internal version 


PS: My test code is as follows:

  POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream
  HSSFWorkbook wb = new HSSFWorkbook(fs);
  HSSFSheet sheet = wb.getSheetAt(1);  // here I get an exception, because only 
one sheet is found
  HSSFRow row = sheet.getRow(0);
  HSSFCell cell = row.getCell((short) 0);

Comment 13 Avik Sengupta 2002-06-19 18:06:06 UTC
hmm.. very strange indeed. You must be using the the correct version, because my
last commit fixed the exception on reading files with name records (the orig
bug). So we are using the same versions.  All i can think of (apart from a
gremlin :-) is that the file you have attached here is not the same as the one
you are using. The sheet u have attached here is called Ziegus.xls , and shows
three sheets called Zeiterfassung , Beispiel , HILFESTELLUNG. 

Can u try debugging thru the HSSFWorkbook object. 
Comment 14 Rudolf Ziegaus 2002-06-20 07:06:09 UTC
Sorry, I found a problem in my code. During all the tests I ran, I switched to 
another file. Unfortunately I did not throw out the "ziegaus.xls", but only 
made a comment to eliminate it. 

So I did not realize that I was in fact using a COMPLETELY WRONG file.

Sorry for the inconvenience I made and thanks a lot for the bug you fixed.

Comment 15 Avik Sengupta 2002-06-20 09:43:20 UTC
Thanks. closing.