Apache POI - working with XSSFWorkbok + servlet response

Question

My java application has problems downloading XLSX files.

following the example shown in this link: Create an Excel file to load using the Apache POI , I tried two configurations for loading / saving the table.

First the .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(); 

It works.

Then I tried with the 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 to do this, I get a message: " Excel found unreadable content in" testxls.xlsx. "Do you want to restore the contents of this book? .... "

Despite this message, the spreadsheet opens normally, but I really want to delete this message.

Any ideas?

Answer 1

Use this JSP code and successfully generate the excel file. I entered the excel file through the database, you can also enter 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(); %>