Zip-bomb exception while writing a large formatted Excel (.xlsx)

Question

I've written a simple web app with JSF which is used to download Excel reports. I'm able to write to at least 50,000 rows with six columns onto five different sheets in under ten seconds.

The problem comes when I try to format the Excel data. The formatting takes quite sometime and important thing is when I try to format Excel with data more than 3,000 rows and download through the JSF, it throws the Zip Bomb IO exception.

Due to this, I'm unable to compute the size of the formatted workbook also (which requires writing to a byteoutputstream).

Can anybody provide any insight on this?

Is it possible to download from the server a fully formatted Excel which has 50K rows in five sheets?

Below is the code I use for formatting the Excel rows.

For Header row:

public CellStyle formatHeaderRow(SXSSFWorkbook sxWorkBook){
        CellStyle cellStyleHeader = sxWorkBook.createCellStyle();
        Font font = sxWorkBook.createFont();
        font.setFontHeightInPoints((short)10);
        font.setFontName("Arial");
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cellStyleHeader.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cellStyleHeader.setBorderTop((short) 1);
        cellStyleHeader.setBorderLeft((short) 1);
        cellStyleHeader.setBorderRight((short) 1);
        cellStyleHeader.setBorderBottom((short) 1);
        cellStyleHeader.setAlignment((short) CellStyle.ALIGN_CENTER);
        cellStyleHeader.setFont(font);
        return cellStyleHeader;
    }

For data rows:

public CellStyle formatBodyRows(SXSSFWorkbook sxWorkBook){
        CellStyle cellStyleBody =  sxWorkBook.createCellStyle();
        Font bodyFont = sxWorkBook.createFont();
        bodyFont.setFontHeightInPoints((short)10);
        bodyFont.setFontName("Arial");
        cellStyleBody.setBorderTop((short) 1);
        cellStyleBody.setBorderLeft((short) 1);
        cellStyleBody.setBorderRight((short) 1);
        cellStyleBody.setBorderBottom((short) 1);
        cellStyleBody.setAlignment((short) CellStyle.ALIGN_LEFT);
        cellStyleBody.setFont(bodyFont);
        return cellStyleBody;
    }

For column/cell spacing:

for(int cellCount=0;cellCount<row.getLastCellNum();cellCount++){
            sheet.setColumnWidth(cellCount, width);
            row.getCell(cellCount).setCellStyle(cellStyleHeader);
        }

Try things stand-alone first, with fixed test data, and comparison to Excel produced xlsx. As xlsx is a zip format one may inspect file sizes and XML contents. Maybe you have found styles being copied instead of referenced or whatever. The code looks fine. You could also use an empty template xlsx to fill. xlsx uses shared strings, so maybe you need a similar thing. — Joop Eggen, Nov 25 '15 at 14:16
Can you post the stacktrace of the Zip-Bomb exception that you get? — centic, Nov 26 '15 at 09:09
Use this worked for me [Code Snippet](https://stackoverflow.com/a/50641893/12782217) — Bexzod Xayrullayev, Sep 21 '22 at 07:02
Use this worked for me [Code Snippet](https://stackoverflow.com/a/50641893/12782217) — Bexzod Xayrullayev, Sep 21 '22 at 07:03

score 2 · Answer 1 · answered Nov 26 '15 at 09:13

The Zip-Bomb detection tries to protect you from opening malicious files which try to bring your application out of memory.

It seems it kicks in in your case because the SXSSFWorkbook internally streams out the data to a temp-file and then reads it in again to produce the final .xlsx file.

If you are sure the document is built in a safe way here, then you can increase or even disable these checks with the instructions contained in the error message.

score 0 · Answer 2 · answered Nov 25 '15 at 14:27

do things one at a time, first make sure the 3000 rows get formatted.

then work on getting the size

then work on the jsf transfer, which is my guess you would have to do through a servlet after having saved the file in the filesystem or in a lob in the database

Zip-bomb exception while writing a large formatted Excel (.xlsx)

2 Answers2

Linked