unreadable content error with apache POI 3.8

Question

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??

If you save the file, rather than opening it in Excel, is it the right size? Does that open properly? Are there any junk characters on the front? — Gagravarr, Jun 11 '12 at 16:42
same happens if I save it. It is the right size because after all those warnings the file is correctly opened. — Neets, Jun 11 '12 at 17:38
Are you certain you have the same version of POI on both? No chance that there's an older copy of the POI jars that might be being used instead? — Gagravarr, Jun 11 '12 at 22:13

score 2 · Answer 1 · answered Jun 12 '12 at 06:08

2

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();

answered Jun 12 '12 at 06:08

Howard Schutzman

2,115
1
15
8

i appreciate your help, but the problem is still there even with your suggestion. thanks anyways! – Neets Jun 12 '12 at 09:24
I missed the fact that you said it was a jsp. The code above was in a servlet. I have another idea about why it might be failing. It's hard to explain or validate without seeing the exact jsp source. First, I suggest you try your code in a servlet and see if that works. Then if you upload your exact jsp code to my server using http://www.bridgesights.com/hondobridge/pruftp/index.php I might be able to help you further. – Howard Schutzman Jun 12 '12 at 14:19
1

You have to be very careful with the actual formatting of the jsp when you send non-html data back. For example, all of the includes have to be on one line and the <% has to be on the same line as the includes. In general, it is easier to use a servlet rather than jsp to send back non-textual data. But it can be done in a jsp if you are very careful not to send extra newline characters with careful formatting. – Howard Schutzman Jun 12 '12 at 14:39
yes, it it a JSP. Moreover, I can't try the code in a servlet because we're not allowed to touch the servlets (we don't even have access to them). So we can only do it with JSPs. Thanks for your help! – Neets Jun 12 '12 at 19:14
well if you want to send me the actual jsp, it is very possible it is simply a formatting problem. Basically, the <% tag has to be on the first line. So all your imports have to also be on the first line. If you want, I can send you a jsp that works. Just tell me where to send the code. (I haven't done any fiddles yet, so haven't yet figured out how to do one.) – Howard Schutzman Jun 12 '12 at 20:01
but if it was a formatting problem then it would show a compilation error, right? in this case the file is generated, so the JSP is working. also, the JSP code is the exactly the same as in my local machine and it works there perfectly. In any case, I have edited the original question to post the complete JSP. Thanks! – Neets Jun 13 '12 at 06:30
The problem is not caused by a compile error. It is caused because it is eassy to inadvertently create extra whitespace in your output. This doesn't matter when you are sending text, such as html, but it matters a good deal when sending binary files. See the following for a detailed explanation of how to send a binary file with a jsp, ensuring there is no inadvertent white space: http://stackoverflow.com/questions/1133920/is-it-possible-to-download-a-binary-file-from-jsp – Howard Schutzman Jun 13 '12 at 13:04
you are right. I had to remove the line breaks between the imports. What confused me is that in my local machine, it works with the line breaks. – Neets Jun 14 '12 at 11:53

score 2 · Answer 2 · answered Oct 19 '12 at 01:19

2

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

answered Oct 19 '12 at 01:19

user1402214

61
1
9

score 0 · Answer 3 · answered Sep 20 '16 at 13:06

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.

unreadable content error with apache POI 3.8

3 Answers3