Export to Excel via a servlet with POI not working under internet explorer 6

[James Blarney]

One of our customers is complaining about not being able to export to excel when using internet explorer. I did some research on the issue and it goes back to something called apache POI. POI claims that you have to change your code:

from Apache POI FAQ: http://poi.apache.org/faq.html

The problem usually manifests itself as the junk characters being shown on screen. The problem persists even though you have set the correct mime type.
The short answer is, don't depend on IE to display a binary file type properly if you stream it via a servlet. Every minor version of IE has different bugs on this issue.
The problem in most versions of IE is that it does not use the mime type on the HTTP response to determine the file type; rather it uses the file extension on the request. Thus you might want to add a .xls to your request string. For example http://yourserver.com/myServelet.xls?param1=xx. This is easily accomplished through URL mapping in any servlet container. Sometimes a request likehttp://yourserver.com/myServelet?param1=xx&dummy=file.xls is also known to work.
To guarantee opening the file properly in Excel from IE, write out your file to a temporary file under your web root from your servelet. Then send an http response to the browser to do a client side redirection to your temp file. (Note that using a server side redirect using RequestDispatcher will not be effective in this case)
Note also that when you request a document that is opened with an external handler, IE sometimes makes two requests to the webserver. So if your generating process is heavy, it makes sense to write out to a temporary file, so that multiple requests happen for a static file.
None of this is particular to Excel. The same problem arises when you try to generate any binary file dynamically to an IE client. For example, if you generate pdf files using FOP, you will come across many of the same issues.

The POI mailing list says that I have to do this: http://www.mail-archive.com/[e-mail]/msg08720.html

Hi Dragen,

We serve POI written XLS all the time through TOmcat 4.1.31 - we learned the hard way the following:
Do not serve through a JSP - write a Servlet:

(1) JSP is supposed to be TEXT only.
(2) In JSP it is easy to insert a space in your output before setting the header and that makes the output have mimetype=text/plain.

public class ExportXlsServlet extends HttpServlet {

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setHeader("Expires","Thu, 01 Dec 1994 16:00:00 GMT");
response.setDateHeader("Last-Modified", System.currentTimeMillis() );
        response.setHeader("Pragma","no-cache");
response.setHeader("Content-disposition", "attachment;filename=served.xls");
        response.setContentType("application/vnd.ms-excel");

        write the XLS to response.getOutputStream());
    }
}

/*This works well with IE 6 / IE 7 / Firefox 1.5 & 2.0 / Safari etc.*/

    <servlet>
        <servlet-name>exportxls</servlet-name>
        <servlet-class>com.mysevlets.ExportXlsServlet</servlet-class>
    </servlet>

Then your url is /exportxls?parms=whatever

When you open your Excel file from the Web in IE - DO NOT let it open it in the Browser - Save it and Open. If anyones knows an easy ubiquitous way to unset that, I'd love to know.
If you use application/octet-stream and allow IE to open in the browser then you will start to have trouble with binary files that are served that way ...
Regards,
Dave Fisher



How do I implement the advice above into this file: ExcelResult.java I already added those changes to the web.xml file.

/*
 * Created on Jan 20, 2005
 *
 * 
 */
package com.blah.common.poi;

import java.io.OutputStream;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.opensymphony.webwork.ServletActionContext;
import com.opensymphony.xwork.ActionInvocation;
import com.opensymphony.xwork.Result;

/**
 * @author Nick
 * 
 *  
 */
public class ExcelResult implements Result {

    public void execute(ActionInvocation invocation) throws Exception {

        HSSFWorkbook workbook = (HSSFWorkbook) invocation.getStack().findValue(
                "workbook");
        HttpServletResponse response = ServletActionContext.getResponse();
        response.addHeader("Content-Type", "application/excel");
        OutputStream os = response.getOutputStream();

        workbook.write(os);
        os.flush();
    }

}

This is my revised code. Keep in mind, im kind of a noob.

public class ExcelResult implements Result {

    public void execute(ActionInvocation invocation) throws Exception {

        HSSFWorkbook workbook = (HSSFWorkbook) invocation.getStack().findValue(
                "workbook");
        HttpServletResponse response = ServletActionContext.getResponse();
        /*response.addHeader("Content-Type", "application/excel");*/
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment; filename=report.xls");
        OutputStream os = response.getOutputStream();

        workbook.write(os);
        os.flush();
    }

}

Ill be happy to provide more information as requested

[Tuan Th]

anyone know how to use POI with more intelligent?

[Martijn Verburg]

Tuan,

Welcome to Javaranch! Can you provide more details, I'm not sure what you're trying to ask!