Apache POI - Working with XSSFWorkbok + servlet response

Question

I'm having problems in my java application to enable downloading XLSX files.

following the example displayed in this link: Create an excel file for users to download using Apache POI, I tried two configurations to download/save a spreadsheet.

First with a .XLS file:

response.setContentType("application/ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=testxls.xls");

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("Some text");

ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
wb.write(outByteStream);

byte[] outArray = outByteStream.toByteArray();
OutputStream outStream = response.getOutputStream();
outStream.write(outArray);
outStream.flush();

This works.

Then i tried with a XLSX file:

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=testxls.xlsx");

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue("Some text");

ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
wb.write(outByteStream);

byte[] outArray = outByteStream.toByteArray();
OutputStream outStream = response.getOutputStream();
outStream.write(outArray);
outStream.flush();

When i try this, i receive the message: "Excel found unreadable content in 'testxls.xlsx'. Do you want to recover the contents of this workbook? ...."

Despite this message, the spreadsheet opens normally, but i really want to remove this message.

Any ideas?

Why do you need a `ByteArrayOutputStream`? Can't you simply do `wb.write(response.getOutputStream())` instead? — Buhake Sindi, Mar 13 '13 at 14:17
I tried with `org.apache.commons.io.output.ByteArrayOutputStream` and `java.io.ByteArrayOutputStream` — Lucas_Mux, Mar 13 '13 at 14:26
Tried with `wb.write(response.getOutputStream())`, but the message persists. — Lucas_Mux, Mar 13 '13 at 14:33
Try this http://superuser.com/questions/401714/how-do-i-resolve-the-error-excel-found-unreadable-content-in-filename — Hardik Mishra, Mar 14 '13 at 11:42
If you write the XSSFWorkbook to a file rather than the servlet, can Excel open that without errors? That'll let you work out if you have a servlet output problem, or a XSSF problem — Gagravarr, Jun 01 '13 at 12:54

score 1 · Answer 1 · answered Jul 13 '20 at 14:55

I know this is a very old (6 years) but I made my way here after updating some old code to move from HSSF to XSSF, my issue was a missing jar xmlbeans-3.1.0.jar. The error was written to the TomCat localhost log "java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject"

Joop Eggen · Answer 2 · 2022-04-11T03:35:34.447

XSSFWprkbook is AutoCloseable and its close() does some additional work. Best use try-with-resources which ensures that close is always called event with exception or return inside the block.

ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
try (XSSFWorkbook wb = new XSSFWorkbook()) {
    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow(0);
    XSSFCell cell = row.createCell(0);
    cell.setCellValue("Some text");
           
    wb.write(outByteStream);
}
byte[] outArray = outByteStream.toByteArray();

OutputStream outStream = response.getOutputStream();
outStream.write(outArray);
outStream.flush();

score -1 · Answer 3 · answered Jun 01 '13 at 07:10

Use this JSP code and generate the excel file succesfully.I have given input to excel file through the database you can also give manual inputs.

<%HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
       try {
        java.sql.Connection con;
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/custinfo","root","abc");
            Statement st= con.createStatement(); 
            out.println("hello world");
        ResultSet rs=st.executeQuery("select name ,state ,balance,description from customerdata where customerid='"+Id+"'"); 

        HSSFRow row = sheet.createRow((short)0);
        row.createCell((short)0).setCellValue("NAME");
        row.createCell((short)1).setCellValue("STATE");
        row.createCell((short)2).setCellValue("BALANCE");
        row.createCell((short)3).setCellValue("DESCRIPTION");
        while(rs.next())
        {
             out.println("hello world data");       
            HSSFRow row1 = sheet.createRow((short)i);
            row1.createCell((short)0).setCellValue(rs.getString("name"));
            row1.createCell((short)1).setCellValue(rs.getString("state"));
         row1.createCell((short)2).setCellValue(rs.getString(3));
         row1.createCell((short)3).setCellValue(rs.getString(4));
         i=i+1;
        sheet.autoSizeColumn((short)1); 

        }

       }
      catch(SQLException e) {
        out.println("SQLException caught: " +e.getMessage());
      }%>
    // create a small spreadsheet
    <%

    %>
    <% 

    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
    wb.write(outByteStream);
    byte [] outArray = outByteStream.toByteArray();
    response.setContentType("application/ms-excel");
    response.setContentLength(outArray.length);
    response.setHeader("Expires:", "0"); // eliminates browser caching
    response.setHeader("Content-Disposition", "attachment; filename=testxls.xls");
    OutputStream outStream = response.getOutputStream();
    outStream.write(outArray);
    outStream.flush();

    %>

But this example is still HSSF, i.e. .xls, rather than XSSF for .xlsx as the OP wants. Can you highlight what's different here, and what you think makes the difference? — Rup, Apr 28 '14 at 11:52
Based on [this other answer](http://stackoverflow.com/a/23340734/243245) it looks like the important point is to set the content length. — Rup, Apr 28 '14 at 13:21

Apache POI - Working with XSSFWorkbok + servlet response

3 Answers3

Linked