0

I am trying to open existing xlsx file (Ms-excel 2010) to append with more data using Apache-POI (v 3.15).

The existing xlsx file (size 700Kb) contains number of tabs with pivot tables, charts etc.

File file = new File(FILE_PATH);
OPCPackage opcPackage = OPCPackage.open(file);
XSSFWorkbook wbk = new XSSFWorkbook(opcPackage);

But the exception thrown is as below,

Caused by: java.io.IOException: Zip bomb detected! The file would exceed the max. ratio of compressed file size to the size of the expanded data. This may indicate that the file is used to inflate memory usage and thus could pose a security risk. You can adjust this limit via ZipSecureFile.setMinInflateRatio() if you need to work with files which exceed this limit. Counter: 819241, cis.counter: 8192, ratio: 0.009999499536766349Limits: MIN_INFLATE_RATIO: 0.01 at org.apache.poi.openxml4j.util.ZipSecureFile$ThresholdInputStream.advance(ZipSecureFile.java:257)

I have tried following changes:
1. tried to change "ZipSecureFile.setMinInflateRatio()" to fix tihs, but JVM crashes with heap space error (even though I allocated more than 4GB)
2. tried to use inputsteam, Workbookfactory.create to create workbook, tried to open as SXSSF.

But none of this has worked for me. Any one has any idea ?

ManishChristian
  • 3,759
  • 3
  • 22
  • 50
sachinsd
  • 51
  • 3
  • 10
  • with such big excel file i can't test it sadly but it really seems to be a problem of the file size because 75kb excel file work smootly – XtremeBaumer Dec 12 '16 at 15:46
  • 700KB doesn't seem to be bigger of the file as I can see it should be able to manage files even with size of MB. Having said that, I have tried with only one tab with existing pivot table (size around 50Kb) and still the same issue. So it seems to me that it is about pivot tables and references than size. – sachinsd Dec 12 '16 at 15:50
  • how "big" are your pivot tables? are they expanded? – XtremeBaumer Dec 12 '16 at 15:51
  • Not necessary, some of them are very small and I have tried to use only one tab with that small pivot table but still the same zip bomb exception! (I am not sure how I can provide those pivot table examples here) – sachinsd Dec 12 '16 at 16:11
  • i think you would have to upload them somewhere – XtremeBaumer Dec 13 '16 at 07:05
  • The file is 700KB compressed, it seems to be much larger when uncompressed due to the data that you have in there. The .xlsx is a .zip file, can you try to unzip it and see how big the resulting files are? – centic Dec 13 '16 at 08:27

2 Answers2

0

So I found solution to the problem but as with many Microsoft related issues, I couldn't locate root cause particularly.

As this template was provided by analysts working on it before (by just deleting data bits), I felt excel file might be an issue.

What I did was, I started new spreadsheet with same pivot tables created one by one from scratch in each tab (a bit manual work but didn't take long). The final template was 60Kb so there must have been some hidden data/"something invisible" which was there before and causing this to fail.

I managed to use this template to create new excel sheet and successfully added data around 600k rows in seconds. Awesome!

sachinsd
  • 51
  • 3
  • 10
0

Bit late to answer this. hope it will help people facing same kind of problem.

For me what worked is

ZipSecureFile.setMinInflateRatio(0.0);

you can have look at the description here

https://community.pega.com/node/715956

techasutos
  • 121
  • 6