Not able to open excel files larger than 10MB

[quaso]

When trying to read excel file larger than 10MB, an error occurs:

"Unexpected error Tried to allocate an array of length 162,386,364, but the maximum length for this record type is 100,000,000. If the file is not corrupt or large, please open an issue on bugzilla to request increasing the maximum allowable size for this record type. As a temporary workaround, consider setting a higher override value with IOUtils.setByteArrayMaxOverride()"

[mdeinum]

Thank you for using this library and for taking the time to register an issue. Could you provide a sample project (preferably a test) so that we can reproduce this?

[quaso]

I do not have any test nor sample project ready, but it is just about using StreamingXlsxItemReader with Excel file >10MB

[mdeinum]

Could you take some time to prepare a test project? You know what you've done to do this, when I try to setup a test it might not work due to differences in file, test etc. etc. So please take some time to make this into a test. You can share the repo or the project as a zip with this ticket.

[mdeinum]

I did a quick test with a 50Mb test file and I had no issues reading it. 14 columns of data with 500000 rows. So without a sample or test to reproduce this I'm afraid I have no clue on where to look for fixing this. I tested with both the standard Apache POI used in the build version 4.1.2 and the most recent version 5.2.2, neither of them throw an exception.

[quaso]

I'll try to do it :-)

Dňa st 15. 6. 2022, 16:12 Marten Deinum @.***> napísal(a):

I did a quick test with a 50Mb test file and I had no issues reading it. 14 columns of data with 500000 rows. So without a sample or test to reproduce this I'm afraid I have no clue on where to look for fixing this.

— Reply to this email directly, view it on GitHub https://github.com/spring-projects/spring-batch-extensions/issues/97#issuecomment-1156526675, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB7U5BR2C4THTOVVRZIWVSLVPHQDFANCNFSM5YWWWHHQ . You are receiving this because you authored the thread.Message ID: @.***>

[mdeinum]

A start would be to add the full stacktrace to this thread instead of only the message, that might clear some things up. I did some quick googling and do you have an XLSX with large text blocks in them? That appears to be an known issue inside Apache POI?!

As a workaround (as mentioned in the error message as well), before executing your job you can do the following

org.apache.poi.util.IOUtils.setByteArrayMaxOverride(Integer.MAX_VALUE);

It will now use a larger range. The range I found for 100_000_000 is for OLE object (wrapper around some binary object). Looking at that I'm not sure if that is something we should fix in Spring Batch Excel, or that you as a user should set a better maximum. We could however make it easier to specify the maximum for a job/step and reset it afterwards, although you still would need to provide a new MAX value.

The max for this specific record type is there to prevent malicious XLSX files to be read and prevent OOM or worse.