This exception is thrown in HSSFSheet when dealing with some spreadsheets: Exception caught java.lang.RuntimeException: Unexpected missing row when some rows already present at org.apache.poi.hssf.usermodel.HSSFSheet.setPropertiesFromSheet(HSSFSheet.java:171) at org.apache.poi.hssf.usermodel.HSSFSheet.<init>(HSSFSheet.java:118) at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:289) at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:202) at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:184) Assumptions: Relates to bug 41187 Row is null - correct rowRecordsAlreadyPresent = true At this point in time _rows contains: [ROW] .rownumber = 0 .firstcol = 0x0000 .lastcol = 0x0000 .height = 0x0000 .optimize = 0x0000 .reserved = 0x0000 .optionflags = 0x0160 .outlinelvl = 0 .colapsed = false .zeroheight = true .badfontheig= true .formatted = false .xfindex = f [/ROW] I can't see anything out of the ordinary with the spreadsheet, if I comment our the 41887 workaround (starting on line 170) that throws a runtime exception the spreadsheet is output sucessfully, starting as follows: SP041808 effective 04/18/08... Please let me know if you need any more info.
Please attach the problem file. Yegor
I can't provide you with the initial file because of client confidentility reasons. I'll take a look at this bug myself on Monday. Many thanks.
(In reply to comment #2) > I can't provide you with the initial file because of client confidentility > reasons. I'll take a look at this bug myself on Monday. Many thanks. Do you know the origin of this file? Which version of Excel produced it? Open the file in Excel and do "save as". Does the problem still persist? Yegor
Created attachment 28594 [details] Pricelist file from http://www.gigatronshop.com/download/Cenovnik.xls Here is one more file that produces the same reported error.
Commenting these lines fixes the issue (as has already been stated): // if (rowRecordsAlreadyPresent) { // // if at least one row record is present, all should be present. // throw new RuntimeException("Unexpected missing row when some rows already present"); // } The question is that I don't see the reason why if one row record is present, all should be. Cells already contain row and column indexes, so creating the missing rows should be no problem, and if there's a conflict between an existing row record and a row record created internally from an existing cell, probably the exception should be thrown from HSSFRow.createCellFromRecord or HSSFRow.addCell if the cell being added already exist.
*** Bug 49312 has been marked as a duplicate of this bug. ***
I don't know why this runtime exception was thrown; maybe the assumption is to correctly assume this case should never happen - but appears to do with Business Objects. As I ran into this issue, I've removed the error - not understanding it really - to see how POI behaves. This unblocked my project; see here how I did it: http://scn.sap.com/message/15236988#15236988
I am also facing the above mentioned issue. I tried using the workaround specified above and it works for me. Does commenting this line can lead to any future complication? Please advise. The link from which the file creating the error can be downloaded is http://www.bursamalaysia.com/market/derivatives/market-statistics/historical-data/ The file creating the problem is under Daily Trading Summary section of the webpage. Thanks and Regards, Deepesh Ramrakhyani
We have had that fix working for some time now (almost 2 years) and haven't found any issue with it.
It would be great if someone could confirm what the problematic section of records are. BiffViewer should be able to help with this It would also be good if someone could run one of these problematic files through the Microsoft Binary File Format Validator to see if Microsoft consider them valid or not
The file provided (Cenovnik.xls) gives a different exception when using BiffViewer. I'm gonna attach one of the files metioned by Deepesh.
Created attachment 31927 [details] Problematic file to test This file contains the bug, but runs properly with BiffViewer. What I found after debugging is that in HSSFSheet.setPropertiesFromSheet there is a line where it looks for the row 149 and the file contains 149 RowRecords. This line: hrow = getRow(cval.getRow()); cval is the CellIterator. The first problematic cval is a LabelSSTRecord with row number 149. From the javadoc: HSSFRow org.apache.poi.hssf.usermodel.HSSFSheet.getRow(int rowIndex) Returns the logical row (not physical) 0-based. If you ask for a row that is not defined you get a null. This is to say row 4 represents the fifth row on a sheet. So, since there are only 149 RowRecords, and getRow(149) represents the 150th row on the sheet, which doesn't exists, then hrow becomes null and then the exception is thrown. Maybe it's as easy as changing that line to: hrow = getRow(cval.getRow() - 1); [LABELSST] .row = 0x0095 .col = 0x0002 .xfindex= 0x0045 .sstIndex = 0x0053 [/LABELSST]
Hi Triqui, Please Note that poi jar is also used for loading other .xls and .xlsx files also so how will this code change affect other files. Because after doing the code changes you mentioned above and trying to access zeroth row by using Sheet s=workBook.getSheetAt(0); Row r=s.getRow(0); Iterator<Cell> ci=r.cellIterator(); while(ci.hasNext()){ Cell c=ci.next(); System.out.println("Data => "+c); } Please Advise. Thanks and Regards, Deepesh Ramrakhyani.
Hi Triqui, The file attached has 150 rows so 149th(Logically) exists Plus after doing the changes you mentioned while i am trying to access the Zeroth row its giving me the content of the second row in the file. Please have a look into it. Thanks and Regards, Deepesh Ramrakhyani.
Sorry for the similar comment twice. Thanks and Regards, Deepesh Ramrakhyani.
First comment i.e. Comment 13 is incomplete Please consider Comment no 14. Sorry again.
Well, I stated my opinion before (comment 5). If no RowRecords are present, POI creates them, but if some are present and some not, then this exception says it won't do it. I can't see the logic behind this. So, to me, it looks like the best solution is to comment out that exception. On the other hand, since I've been trying to find out what is wrong with those files, I've noticed some problems with the row number and the row index. You are right. Adding -1 is wrong. But the file has 149 RowRecords (from 0 to 148). They are stored in a TreeMap with a 0-based KeySet. When I debug it it has the following KeySet [0 ... 148]. Some keys may be missing if a row is empty. But, please note that there is no row 149, which is the one this cell belongs to: [LABELSST] .row = 0x0095 .col = 0x0002 .xfindex= 0x0045 .sstIndex = 0x0053 [/LABELSST] Again, I think this is a problem with the file and somewhat unrelated to the bug. The real question is what to do when a row records is missing? If no row records exists it's ok, POI creates them. But if there are some row records but not all of them, then it's a problem. Is it really a problem? I think POI can create the missing row records and carry on, but I would like it if someone with more knowledge would comment on this.
The excel file on BFFValidation gives positive result so the file is proper hence according to me the fix is correct. And I don't think that if a file has some row records but not all then it should be a problem. Thanks and Regards, Deepesh Ramrakhyani.
*** Bug 54392 has been marked as a duplicate of this bug. ***
The bug 41187 was what led to the modification being discussed here. The patch can be seen here: https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java?r1=655278&r2=655277&pathrev=655278 It's nice, and it's working. Except for the fact it throws an exception when everything suggest that it shouldn't. The problem appears when there is one or more RowRecords [ROW] but then there are also one or more cells for a row which has not been defined/declared. For instance, in the document attached there is a single row record: [ROW] .rownumber = 3 .firstcol = 0x0000 .lastcol = 0x0000 .height = 0x0960 .optimize = 0x0000 .reserved = 0x0000 .optionflags = 0x0140 .outlinelvl = 0 .colapsed = false .zeroheight = false .badfontheig= true .formatted = false .optionsflags2 = 0x000F .xfindex = f .topBorder = false .bottomBorder = false .phoeneticGuide= false [/ROW] And then there are several LABELSST records for different rows without a corresponding row record. [LABELSST] .row = 0x0000 .col = 0x0000 .xfindex= 0x0016 .sstIndex = 0x0000 [/LABELSST] [LABELSST] .row = 0x0001 .col = 0x0000 .xfindex= 0x0016 .sstIndex = 0x0001 [/LABELSST] Every comment in this bug and duplicates say that removing the line throwing the exception fixes the problem with no side effects. I really can't see a reason to keep that exception. Maybe Josh Micich could shed some light on this issue. I don't know if he is still involved in the project, though.
The problem still persists in 3.14-beta1
I made a mistake yesterday, sorry. The attachment I was referring to in comment #20 is the file from bug 54392, not the one attached here. That file contains only one [ROW] record but there are several [LABELSST] records with a different row number. The file I attached in this bug some years ago is a bit different. There is only one error in the first sheet. It contains 149 [ROW] records, but there is a [LABELSST] record for the 150th row. As mentioned in the code if there is at least one [ROW] record, all of them should be present too, otherwise it throws an exception. This is what we are challenging here, and I say that there is no need for that exception. When that throw is removed, the code goes on to create a RowRecord for the 'orphan' cell and everything works perfectly. In fact, if you open the file with LibreOffice (haven't had a chance to try it in Excel) it shows 150 rows, and I have been working with that line commented out for some years now and haven't seen a single error caused by that change. I keep patching HSSFSheet for every release, but I would like to stop already. Is there anything I can do to get this fixed?
Thanks for the thorough review of the problem, I have now commented out the exception and verified that our full test-suite still runs fine, so I think it is ok to change this as it makes POI behave more like LibreOffice/Excel do. So this is applied as r1732235 now.