Generate xlsx file with POI in a zip

Question

I export data in an xlsx files with the POI Api and added they're in a Zip file. When I open the zip I do not have any xlsx files but three directories (docProps, xl and _rels) and 1 file [Content_Types] xml. I think it's the xlsx file's description, but I don't understand why.

Code :

public InputStream exportXlsx(List<MyObject> listeOfObject) throws IOException {

        ByteArrayOutputStream excelOutputStreamZip = new ByteArrayOutputStream();
        ZipOutputStream zip = new ZipOutputStream(excelOutputStreamZip);

        for (MyObject myObject : listeOfObject) {

            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet wsheet = wb.createSheet("mySheet");
            XSSFRow row = wsheet.createRow(0);
            XSSFCell cell = row.createCell(1);
            cell.setCellValue(myObject.getValue1());

            // Create all sheet and cell....

            // Write WB conntent in outputStream
            wb.write(excelOutputStreamZip);

            addEntry(zip, myObject.getFileName(), excelOutputStreamZip);
        }

        InputStream inputStreamZipByte = new ByteArrayInputStream(
                ((ByteArrayOutputStream) excelOutputStreamZip).toByteArray());
        zip.close();

        return inputStreamZipByte;

    }

    public void addEntry(OutputStream zip, String filename, ByteArrayOutputStream os) {

        byte[] bytes = os.toByteArray();

        ZipEntry entry = new ZipEntry(filename);
        Date d = new Date();
        entry.setTime(d.getTime());
        try {
            ((ZipOutputStream) zip).putNextEntry(entry);
            ((ZipOutputStream) zip).write(bytes);
            ((ZipOutputStream) zip).closeEntry();
        } catch (IOException e) {
            log.error("Can't read the file !", e);
        } catch (ClassCastException cce) {
            log.error("Bad format !", cce);
        }

    }

This code is write in a service which is inject in a Struts2 action.

struts.xml :

<action name="*MyAction" class="com.omb.view.action.myAction" method="{1}">
    <result name="export" type="stream">
        <param name="contentType">application/zip</param>
        <param name="inputName">inputStreamZipByte</param>
        <param name="contentDisposition">attachment;filename="myZip.zip"</param> 
        <param name="bufferSize">1024</param>
    </result>
</action>

score 2 · Accepted Answer · answered Aug 14 '14 at 12:59

2

I found a part of my solution, but now I have another problem :

the problem for the inital post was in the handling of streams. Because I used the same outputStream for the Zip and the Workbook. Solution was created a new ByteArrayOutpuStream for each workbook.

// Write WB conntent in outputStream    
ByteArrayOutputStream wbOutputStream = new ByteArrayOutputStream();  
wb.write(wbOutputStream);    
addEntry(zip, myObject.getFileName(), wbOutputStream);    
wbOutputStream.close();

But...now the Zip file generated is corrupted...

answered Aug 14 '14 at 12:59

Ousmane MBINTE

664
4
15
37

If you want to dynamically add multiple files (xls, xlsx, and so on) inside a ZIP from Struts2, [take a look at this](http://stackoverflow.com/a/16654313/1654265), it may help. – Andrea Ligios Aug 26 '14 at 11:38
1

thank you Andrea, I solved my problem with the post. – Ousmane MBINTE Sep 01 '14 at 15:45
am also getting the same error when using JSF and Spring I too dont understand why – rParvathi Apr 27 '17 at 11:05

score 0 · Answer 2 · edited May 23 '17 at 10:30

0

The xlsx file is a zip file. You should check the MIME type appropriate for the contentType parameter of the result. See What is a correct mime type for docx, pptx etc?

edited May 23 '17 at 10:30

Community

1
1

answered Aug 13 '14 at 20:08

Roman C

49,761
33
66
176

1

Ok thank's for your help. I tried to generete a set of xlsx files and put them in a zip file that will be downloaded by the user. So should i change the contentType in the struts2 config file ? Even if would like have a zip file ? – Ousmane MBINTE Aug 14 '14 at 07:56

raj karthi · Answer 3 · 2016-04-18T20:39:53.133

Just write the workbook to BytearrayInputStream then convert into byte array Use that byte array in ZipOutPutStream

This one works for me ..........................

response.setContentType("application/zip");

response.setHeader("Content-Disposition","attachment;filename=\""+fileName+".zip\"");

workBook = getWorkbook(displayList, cfgType);

ByteArrayOutputStream baos = new ByteArrayOutputStream();

ZipOutputStream zos = new ZipOutputStream(baos);

ByteArrayOutputStream fileBaos = new >ByteArrayOutputStream();

zos.putNextEntry(new ZipEntry("Test.xlsx"));

workBook.write(fileBaos);

zos.write(fileBaos.toByteArray());

zos.putNextEntry(new ZipEntry("Test1.xlsx"));

zos.write(fileBaos.toByteArray());

fileBaos.close(); zos.close();

response.getOutputStream().write(baos.toByteArray()); response.flushBuffer();

Generate xlsx file with POI in a zip

3 Answers3