JAVA : Exporting Data (from database) to excel and sending it to client side

Question

As the title suggest, I need to put some data (which I have got from database) into an excel sheet, and then send it to client side so that user can save , open or cancel the action.

I have seen some articles regarding this, the closest one being : How can I get the user to download my file? (Java, MVC, Excel, POI). Referring to links provided by Stevens I tried out the following code :

public String execute(){
    setContentDisposition("attachment; filename=\"" + ename + "\"");
    try{
        ServletContext servletContext = ServletActionContext.getServletContext();
        String filePath = servletContext.getRealPath("/WEB-INF/template/excel/mytemplate.xls");
        File file = new File(filePath);
        Workbook wb = WorkbookFactory.create(new FileInputStream(file));
        Sheet sheet = wb.getSheetAt(0);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        InputStream excelStream;
        excelStream = new ByteArrayInputStream(baos.toByteArray());
    }catch(Exception e){
        System.out.println(e.getMessage());
    }
    return SUCCESS;
}

Here firstly WorkbookFactory is not defined. Secondly, I could not understand properly how the code is working.

I also found this link : http://www.roseindia.net/answers/viewqa/Java-Beginners/14930-How-to-export-data-from-database-to-excel-sheet-by-using-java--in-standalone-project.html. But here the excel file gets saved on the server. I want that the file should not be saved on the server side, it should directly go to client side

(If it helps) I am using : struts 2 framework, hibernate

I am open to using other things like POI API, jQuery or any other good stuff.

I can not use displayTag for some reason.

Javascript would be my last resort (although I have implemented with it) because it requires changing some default security settings of the browser (If this can be avoided I am open to javascript as well).

Please advise how should I go about this now.

Thanks!!

EDIT :

    <result-types>
        <result-type name="jsp" class="org.apache.struts2.views.jsp"/>
        <result-type name="tiles" class="org.apache.struts2.views.tiles.TilesResult"/>
        <result-type name="stream" class="org.apache.struts2.dispatcher.StreamResult"/>
    </result-types>
    <action name="myActionName" class="package.myActionClass">
         <result type="stream">
            <param name="contentType">"application/vnd.ms-excel"</param>
            <param name="inputName">excelStream</param>
            <param name="contentDisposition">contentDisposition</param>
            <param name="bufferSize">1024</param>
         </result>
    </action>

The error while executing action:

java.lang.reflect.InvocationTargetException

java.lang.IncompatibleClassChangeError: Class org.apache.poi.hssf.usermodel.HSSFWorkbook does not implement the requested interface org.apache.poi.ss.usermodel.Workbook

Answer 1

Okay. So finally I am through with all the roadblocks and have figured out a way to do this.

I realized that the problem I was facing was not in creating the excel file, the problem was sending it to client side, and that too without creating a file or temporary file on the server.

So here is how to go about it (I have ripped off details from my original code so that you can easily understand it).

In the action file you first have to create a HSSFWorkbook object, put data on it and then without saving it to disk on server, send it to client using inputstream.

Action File code :

public String execute(){

    setContentDisposition("attachment; filename=\"" + ename + ".xls\"");

    try{
        HSSFWorkbook hwb=new HSSFWorkbook();
        HSSFSheet sheet =  hwb.createSheet("new sheet");

        //////You can repeat this part using for or while to create multiple rows//////
            HSSFRow row = sheet.createRow(rowNum);
            row.createCell(0).setValue("col0");
            row.createCell(1).setValue("col1");
            row.createCell(2).setValue("col2");
            row.createCell(3).setValue("col3");
            .
            .
            .
        ///////////////////////////////////////////////////////////////////////////////

        ///////////////////////////////////////////////////////////////////////////////
        //////Now you are ready with the HSSFworkbook object to be sent to client//////
        ///////////////////////////////////////////////////////////////////////////////

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        hwb.write(baos);
        excelStream = new ByteArrayInputStream(baos.toByteArray());

        ///////////////////////////////////////////////////////////////////////////////
        ////Here HSSFWorkbook object is sent directly to client w/o saving on server///
        ///////////////////////////////////////////////////////////////////////////////
    }catch(Exception e){
        System.out.println(e.getMessage());
    }
    return SUCCESS;
}

Now in the struts-config file just write (note that excelStream & contentDisposition has been set in the action itself also the result-type here is org.apache.struts2.dispatcher.StreamResult):

    <action name="actionName" class="actionClass">
        <result type="stream">
            <param name="contentType">"application/vnd.ms-excel"</param>
            <param name="inputName">excelStream</param>
            <param name="contentDisposition">contentDisposition</param>
            <param name="bufferSize">1024</param>
        </result>
    </action>

Thats it. Now when the action is executed, the user will be prompted to save or open the file.

:)

Answer 2

You have two different copies of POI on your classpath, one old and one new. That's why you're getting the exception java.lang.IncompatibleClassChangeError: Class org.apache.poi.hssf.usermodel.HSSFWorkbook does not implement the requested interface org.apache.poi.ss.usermodel.Workbook - you've compiled against the new copy, but at runtime it's finding some new and some old jars.

This is covered in the POI FAQ. Ideally you should just be able to look at all the jars in your setup, and zap the old POI one. If not, the POI FAQ entry has some sample code you can use to get the JVM to print out where it has loaded POI classes from. That will show you the jar file name, and you can remove the old one.