Bug 47199

Another bug besides bug 46840 was fixed recently: bug 46953.  That fix was made on May 11, and if you were running that code, it would be impossible to get that exception from line 241.  In the most recent version of Sheet.java (since May 11) this exception would have been thrown from line 236: 
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/Sheet.java?annotate=773438

The top pair of frames in your stacktrace is matched by revisions 757520 to 773411 (Mar 23 bug 46840 - May 11 bug 46953)
I am closing this bug on the assumption that that you're running a slightly older version of POI than May 14.  If you experience a similar problem please re-open with the new details.

Nightly builds can be found here http://encore.torchbox.com/poi-svn-build/
The fix for bug 46953 found from this file: poi-source-3.5-beta6-20090511.zip onwards.


Thanks for doing the extra investigation using BiffViewer.  I am guessing that the changes Excel made to the TABIDs  is unrelated to your problem.

Hopefully you don't need to troubleshoot further, but if Excel 'fixed' the file for your problem, it's likely that it shuffled a few records around, and that information would be interesting.  If you pipe the BiffViewer output through "grep Offset=0x", that will extract only the record headers.  The resulting file will not have any proprietary data, but the ordering of biff records will be preserved and that would be enough to diagnose this bug.

*** This bug has been marked as a duplicate of bug 46953 ***

Hi, 

Sorry, the error is coming in line number 236 now. I have taken the latest jar files: 
poi-contrib-3.5-beta6-20090518.jar
poi-ooxml-3.5-beta6-20090518.jar
poi-scratchpad-3.5-beta6-20090518.jar
poi-3.5-beta6-20090518.jar

Following exception is throwing in the console. 
 
Exception in thread "main" java.lang.RuntimeException: two Page Settings Blocks found in the same sheet
	at org.apache.poi.hssf.model.Sheet.<init>(Sheet.java:236)
	at org.apache.poi.hssf.model.Sheet.createSheet(Sheet.java:162)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:288)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:202)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:318)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:299)
	at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:60). 

This is coming with my old file also. The step to recreate is same as above.

Thanks for re-verifying the bug with the most recent POI build.

In general it is difficult to fix any problem without sample the code/data to reproduce it.  Can you please upload the BiffViewer summary as mentioned above (pipe the BiffViewer output through "grep Offset=0x").  It will help to have BIFF summaries for both files (the '1 year old' file and the one re-saved by Excel).

Created attachment 23687 [details]
utput file which is giving PSB problem

Created attachment 23688 [details]
new output file without PSB problem

PFA the information needed .

The files you uploaded are incomplete.  It appears that the output of BiffView has been truncated.  None of the sheet sub-streams are present, and even the workbook stream is incomplete.  I can see 49 BOUNDSHEET records which implies 49 sheet sub-streams are missing.

I am pretty sure that BiffView will not silently quit in the middle of a file. So either it crashed and you didn't mention that, or you have deliberately truncated the output hoping that we only needed to consider the record order in the first 5% of the workbook.  Perhaps you are concerned that even these BIFF header summaries constitute information that you must keep private.  I can't convince you otherwise, but at least let us know that's what you are thinking.


If you are not able to upload a complete BIFF summary, you could try single stepping the code in a debugger:
 - First (since there are many sheets), find out which sheet the error occurs on.  When the bug occurs, take at "this._sheets.size" 2 stack frames up (HSSFWorkbook.java:288). 
 - Next re-run your app and progress over all the previous sheets and stop at HSSFWorkbook.java:288 before reading the sheet with the problem.
 - Put a breakpoint on line Sheet.java:219, let the app execute to there.
 - Observe carefully how many and which records are read from the RecordStream in the PageSettingsBlock constructor.
 - Let the app run to the Sheet.java:219 breakpoint again.  Pay attention to which records (non-PSB) have been read from the RecordStream since creating the PageSettingsBlock, and the current record (which seems to belong to the PSB)
 - Make sure that execution _immediately_ proceeds to the exception at Sheet.java:236 (otherwise you haven't observed the exact conditions for the bug yet)

When you find out specifically what POI is doing wrong (and what Excel is doing 'right'), please reply here so we can incorporate this knowledge back into POI.

Hi, 

How do I get the sheet name? I have searched in the code but not able to get the sheet.

In Sheet.java : 
I have tried to print the "recSid" the value is coming as 41 (Dec) and 0x0029 (hex).

the value for " _psBlock != records.get(prevPsbIx) " is coming as true in my case, Is it correct or we have to use equals method for comparing the objects.
Please advise.

It seems like you are going with the debugging approach.

(In reply to comment #8)
> How do I get the sheet name? I have searched in the code but not able to get
> the sheet.

At stack frame (HSSFWorkbook.java:288), you can see the number of already loaded sheets with "_sheets.size()".  This also happens to be the index to the sheet currently being constructed, so that sheet name can be observed with:
workbook.getSheetName(_sheets.size());


> In Sheet.java :
> I have tried to print the "recSid" the value is coming as 41 (Dec) and 0x0029
> (hex).

That sid corresponds to BottomMarginRecord.  We are going to need more information than this (see Comment 7).  Basically we need the list of records (found within RecordStream rs) starting from the records that the _psBlock has, all the way through to the current record (seems to be BottomMarginRecord) and a little further, just to make sure there are no more PSB records after that.  I guess that execution was at line 234 when you made this observation.  That's an important detail to clarify too.


> the value for " _psBlock != records.get(prevPsbIx) " is coming as true in my
> case, Is it correct or we have to use equals method for comparing the objects.

Did you try out your suggestion "!_psBlock.equals(records.get(prevPsbIx))"?

Created attachment 23710 [details]
Giving PSB problem 


I have attached the file which is giving PSB problem. 

Please use this file directly, if you open and change any of the value then this wont throw exception.



Test Case :
================

	
	public void testImportMacroPSB() throws Exception {
		String filename = "C:\\poi-src-3.5-beta5-20090219\\poi-3.5-beta5\\testOLD\\excel.xls";
		InputStream macro = new FileInputStream(filename);
		Workbook oldWb = new HSSFWorkbook(macro);
		assertNotNull(oldWb);
		  FileOutputStream out = new FileOutputStream("c:/testPSB.xls");
		  oldWb.write(out);
	        out.close();
	}


Please let me you if anything else is required. 

Thanks 
Naveen Kumar

Hi

Please let  me know if any one is working on this.. 

Waiting for your reply . 

Thanks 
Naveen

I hit this problem today on a XLS (Excel 2003) file when reading it with HSSF from POI 3.5 beta 5 of Feb 19, 2009.  I too am working with proprietary data that I cannot upload, and I bet that any modification will cause the problem to vanish.  The one bright spot: I tried reading the file with the streaming HSSF approach (i.e., eventusermodel, HSSFListener, and friends), and that worked very well.

Hi Chris,

Thanks for your repley . 

I dont know how to use the event model and all. 

Please suggest me how to resolve the error, as my release is pending due to this error. 

Thanks
Naveen

Fixed in svn r780774

junits added

Thanks for taking the time to upload something that would reproduce the error.  The problem in the sample file (attachment id=23710) was that the margin records are not found with the rest of the PageSettingsBlock.  If Excel re-saves the file, it corrects that problem.  POI now does the same.

I'm not sure if if is of concern to you, but my Excel (2007) reports the data loss in the sample file ("Shared workbook revisions and custom workbook views may have been lost"). Same warning message from Excel after re-writing with POI.

Thanks a lot for the solution. 

Cheers!!!
Naveen