getting the same error for similar code...

The problem is not in POI. It is JDK that can't read the file. The hang can be reproduced with the following code:

  ZipFile zip =  new ZipFile("TestHang.xlsx");

So, something is wrong with the underlying zip archive.  We rely on JDK to open OOXML files and I don't see a problem on the  POI side.

Do you know any details how the problematic file was produced? Version of Excel, OS, etc. Was the file unzipped / modified and zipped back ? 

PS. I'm testing on Windows 7 and JDK 1.6.26. 

Yegor

I have the same problem as described. WorkbookFactory.create() hangs with a file I have localy and the sample file provided in this bug report.

I am able to parse the underlying zip files and read their contents without any issue, so the problem is NOT related to ZIP corruption.

try {
	ZipFile zip =  new ZipFile("C:\\TestHang.xlsx");
	List<ZipEntry> entries = (List<ZipEntry>) Collections.list(zip.entries());
			  
	for ( ZipEntry ze:entries){
		System.out.println("-------> "+ze.getName());
		System.out.println("\t -------> "+ze.getComment());
		System.out.println("\t -------> "+ze.getCompressedSize());
		System.out.println("\t -------> "+ze.getCrc());
		System.out.println("\t -------> "+ze.getMethod());
		System.out.println("\t -------> "+ze.getSize());
		System.out.println("\t -------> "+ze.getTime());
				
		InputStream is = zip.getInputStream(ze);
		
		StringWriter writer = new StringWriter(); 
		IOUtils.copy(is, writer);  

		System.out.println(writer.toString());
				
	}
		
} catch (IOException e) {
	e.printStackTrace();
}

Ok after some debugging, I can see that the code hangs on some very inffecient looping code centered at ColumnHelper.cleanColumns() and ColumnHelper.addCleanColIntoCols(CTCols, CTCol)

The attached example xlsx for example has some 10000 column definitons in its xml structure. The ridiculous looping code, starts to loop over 10000 records, and then calls some inner loop(s) which have the potential to loop 10000 times themselves, and as well as for every iteration of the loop, it also tries to sort that same list of columns which can have upto 10000 entries. 

There is no infinite loops or anything of the sort. Just some poorly designed iteration code. The call would eventually finish, but who knows how long it will take. The complexity and size of the list being iterated grows exponentially, and after about the first 1100 columns it slow to a crawl, Im guessing the complexity of this looping process is something like n^3

I have worked out some changes to ColumnHelper coupled with Proxy classes for CTCol and CTCols and was able to complete workbook creation in seconds. Will post sample code later. Probably needs a patch in the codebase.

Created attachment 29462 [details]
Altered ColumnHelper, that uses the CTCol and CTCols proxies

-slight modification to the ColumnHelper class, so that it uses proxies for classes CTCol and CTCols (also attached) significantly speeding up the looping code that tries to handle column overlaps

Created attachment 29463 [details]
proxy for CTCols that maintains max and min values

-original CTCols just maintains a list of CTCol objects, so if u everytime u need to tell the min col and max col ranges, you needed to iterate over all the columns, this is what was so time consuming. This CTColsProxy maintains min and max which is expected to be updated everytime a setMax or setMin is called on the container columns

Created attachment 29464 [details]
- proxy for CTCol that maintain a reference to the CTCols its contained in and updates min and max

Original CTCol had no knowledge of the CTCols object its mainted in, ie one directional relationship. This proxy adds a CTColsProxy property which is expected to be the parent CTCols object. Whenever a setMin or setMax is called on the CTCol[Proxy] the CTCols[Proxy] min/max value is also maintained

Attached 3 classes, that significantly sped up my processing, example xlsx as well my problematics files now all parse in about a second.

ColumnHelper is a modified version of org.apache.poi.xssf.usermodel.helpers.ColumnHelper while the other two classes, CTColProxy and CTColsProxy are brand new.

Thanks for the patch. I added it to my punch list.

Yegor

(In reply to comment #8)
> Attached 3 classes, that significantly sped up my processing, example xlsx
> as well my problematics files now all parse in about a second.
> 
> ColumnHelper is a modified version of
> org.apache.poi.xssf.usermodel.helpers.ColumnHelper while the other two
> classes, CTColProxy and CTColsProxy are brand new.

The patch needs some work.

3 tests failed:

org.apache.poi.xssf.usermodel.TestXSSFSheet.testGroupUngroupColumn
org.apache.poi.xssf.usermodel.TestXSSFSheet.testSetColumnGroupCollapsed
org.apache.poi.xssf.usermodel.helpers.TestColumnHelper.testAddCleanColIntoCols

Please re-visit your code and ensure that all tests pass or give sensible explanation why the new logic produces different results.

Yegor


(In reply to comment #8)
> Attached 3 classes, that significantly sped up my processing, example xlsx
> as well my problematics files now all parse in about a second.
> 
> ColumnHelper is a modified version of
> org.apache.poi.xssf.usermodel.helpers.ColumnHelper while the other two
> classes, CTColProxy and CTColsProxy are brand new.

*** Bug 53250 has been marked as a duplicate of this bug. ***

Created attachment 30866 [details]
Modified ColumnHelper with efficient algorithm

Modified the algorithm that adds new columns and that cleans the columns. The existing code is absolutely atrocious in terms of algorithmic design, has at least n^4 algorithmic complexity where n is the number of columns, which means that for about 1000 columns the code will execute for over an hour. Changed the algorithm to one that uses sweeping, should drop it to about n log n.

Created attachment 30867 [details]
Modified test for column grouping

Modified the tests for column grouping that relied on old faulty behavior (line 491: collapsing column 10 should not automagically collapse column 9 which is not grouped with it).

Hello,

I'm lost... I want to know exactly what I have to do to solve this problem. Should I add the two attachments (attachment 30866 [details] and attachment 30867 [details]) and rebuild a new jar? This problem is not resolved in the latest version of POI?

Please help!

Thank you in advance.

(In reply to sam fercios from comment #14)
> Should I add the two attachments (attachment 30866 [details] and attachment
> 30867 [details]) and rebuild a new jar? 

Yes, exactly. You have to replace the respective files in the POI source. 

> This problem is not resolved in the latest version of POI?

No, apparently fixing a gross inefficiency that happens during every load of an XLS file is not high on the list of priorities.

Thank you very much Wilkin. I made amendments and it works now. So I have to change the status of the bug to "resolved".

Hope I can help you one day on any subject :)

Please don't close bugs where the code hasn't yet been committed to SVN

A patch was originally proposed for this bug, but it had issues and couldn't be applied. Since then, another patch has been proposed, but without explicitly answering some of the queries raised against the original bug, and with a status which continued to be "NeedInfo"

It would be great if someone could confirm if the new patch does fix all the problems flagged against the original, and also if someone could try applying the patch(es) and confirm if all the unit tests still pass, behaviour is correct etc. Those two would greatly speed up the review/accept cycle for getting this applied!

ouups,so sorry... I was wrong.

The problem must be fixed urgently and attached to new POI versions, otherwise if I will migrate from version 3.9 to 3.10, I will have certainly some troubles that can be very dangerous for my future releases.

(In reply to Nick Burch from comment #18)
> It would be great if someone could confirm if the new patch does fix all the
> problems flagged against the original, and also if someone could try
> applying the patch(es) and confirm if all the unit tests still pass,
> behaviour is correct etc. Those two would greatly speed up the review/accept
> cycle for getting this applied!

Let me clarify: 

The original bug report dealt with the WorkbookFactory.create() call hanging on files with many columns. This was later determined to be simply a complexity issue with slow code in the ColumnHelper.

I made a fix that redoes the ColumnHelper with an efficient algorithm (based on sweeping, see http://en.wikipedia.org/wiki/Sweep_line_algorithm) while still preserving the functionality of the original. I also made sure that the new algorithm passes all unit tests. In the one case where the original tests were not passed, I debugged it on a case-by-case basis to make sure the new behavior was correct. Seeing as the relevant testing code has a comment by the lines of "I'm not really sure how this is supposed to work", I do believe that the new behavior is actually the correct one. 

I don't think I have the necessary access level needed to push this onto SVN, which is I left the patch on the NEEDINFO status. Hopefully, if some developer verifies this and pushes this to SVN, it can be changed to RESOLVED.

Sounds like the patch is ready for review, and hopefully applying. I'm tagging as such, so that all POI committers can see it's ready, and hopefully someone will have a chance to look at it shortly!

i am reviewing the patch now, checking code and that all tests passes

Thank you for the patch.
Applied with r1561435
... and because of problems with JDK 1.5, it needed another fix - r1561511