CODEWITHSUNDEEP
exceljspexcel-2007apache-poi
Neets
Neets

Reputation: 4212

unreadable content error with apache POI 3.8

I'm using Apache POI 3.8. I have a JSP that generates an excel (.xslx) file. I developed it locally and the file could be opened without any problems. Then, I deployed the app in the Dev environment and when I open the generated excel file there, a warning box comes up saying: "Excel found unreadable content in ausencias.xlsx. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes." If I click Yes, then the following alert appears: "This file cannot be opened by using Microsoft Excel. Do you want to search the Microsoft Office Online Web site for a converter that can open the file?" If I click No, the file is correctly opened. But I don't know why I get these errors.

It happens even if I generate a simple empty .xslx file:

<%@page import="java.io.FileOutputStream"%>
<%@page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@page import="java.io.IOException"%>
<%@page import="javax.servlet.*"%>

<%@page import="org.apache.poi.ss.usermodel.*"%>    
<%@page import="org.apache.poi.ss.util.CellRangeAddress"%>
<%@page import="org.apache.poi.xssf.usermodel.*"%>

<%
    response.setHeader ("Content-Disposition", "attachment;filename=\"ausencias.xlsx\"");
    response.setContentType("application/vnd.ms-excel");

    Workbook wb = new XSSFWorkbook();

    Sheet sheet = wb.createSheet("Ausencias");

    ServletOutputStream fout = response.getOutputStream();

    wb.write(fout);
    fout.flush();
    fout.close();
%>

In my local machine, I have Microsoft Office Excel 2007 (12.0.6661.5000) SP2 MSO (12.0.6562.5003) In the Dev environment, I have Microsoft Office Excel 2007 (12.0.6611.1000) SP3 MSO (12.0.6607.1000)

Does anyone know a solution or a workaround?

Thanks.

EDIT: I have added the whole JSP code. I am aware that some imports may not be used; I just left them there when I cropped my original code looking for the problem.

EDIT 2: I have opened in my local machine the document generated in the development environment and it throws the same warnings. So the problem is in the file, not in the Excel version. It seems like something is tampering with the file in the Dev Env. Any ideas??

Upvotes: 0

Views: 7641

Answers (3)

Migueles
Migueles

Reputation: 26

This is the way I resolved it:

String name = "Name_of_file.xlsx";

ByteArrayOutputStream bout = new ByteArrayOutputStream();
workbook.write(bout);
bout.close();

response.setHeader("Set-Cookie", "fileDownload=true; path=/");
response.setHeader("Content-Disposition", "attachment; filename=" + name);
response.setContentLength(bout.size());

ServletOutputStream out = response.getOutputStream();

out.write(bout.toByteArray());
out.flush();
out.close();

I think the difference with the other answers is that I did not use the response.setContentType instruction and I don´t know exactly the reason it worked... but it worked. No more "Excel found unreadable content..." message.

Upvotes: 0

user1402214
user1402214

Reputation: 61

the reason you are getting this error is because the MIME type is wrong. instead of

"application/vnd.ms-excel"

you need to use

"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

for XLSX documents

Upvotes: 2

Howard Schutzman
Howard Schutzman

Reputation: 2135

I have found you should always set the content length whenever sending back a binary file as an http response. Although sometimes you can get away without setting the length, oftentimes the browser cannot reliably automatically figure out how long the file is.

Typically, the way I do this is to first write my output file to a ByteArrayOutputStream. This lets me calculate how big the output file is. I then write the ByteArrayOutputStream bytes to the response object.

Here is an example, based on your original code:

    // generate the xlsx file as a byte array
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("Ausencias");
    ByteArrayOutputStream bout = new ByteArrayOutputStream();
    wb.write(bout);
    bout.close();

    // send byte array as response
    response.setHeader ("Content-Disposition", "attachment;filename=\"ausencias.xlsx\"");
    response.setContentType("application/vnd.ms-excel");
    response.setContentLength(bout.size());
    ServletOutputStream fout = response.getOutputStream();
    fout.write(bout.toByteArray());
    fout.flush();
    fout.close();

Upvotes: 2

Related Questions