JAVA: Export data (from the database) for Excel and send it to the client side

Question

As the name implies, I need to put some data (which I received from the database) in an excel sheet, and then send it to the client side so that the user can save, open or cancel the action.

I saw several articles regarding this one coming up: How can I get a user to upload my file? (Java, MVC, Excel, POI) . Referring to the links provided by Stevens, I tried 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, the WorkbookFactory not defined. Secondly, I could not correctly understand how the code works.

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 is saved on the server. I want the file not to be saved on the server side, it must go directly to the client side

(If that helps) I am using: frameworks 2 framework, hibernate

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

For some reason I cannot use displayTag .

Javascript would be my last application (although I implemented it) because it requires changing some default security settings in the browser (if this can be avoided, I am also open for javascript).

Please let me know how to 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> 

Error performing 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

Good. So, finally, I went through all the roadblocks and figured out how to do it.

I realized that the problem I encountered was not the creation of an excel file, but the problem that sent it to the client side, and this also without creating a file or a temporary file on the server.

So, here's how to do it (I ripped the details from my source code so you can easily understand it).

In the action file, you first need to create an HSSFWorkbook object, put data on it, and then not save it to disk on the server, send it to the client using the input stream.

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 and contentDisposition were also set in the action itself as the result of 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> 

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

:)

Answer 2

You have two different copies of the POI on your class path, one old and one new. This is why you get the java.lang.IncompatibleClassChangeError exception: the org.apache.poi.hssf.usermodel.HSSFWorkbook class does not implement the requested org.apache.poi.ss.usermodel.Workbook interface — you collected a new copy against it, but at run time it finds some new and some old banks.

This is described in the POI FAQ . Ideally, you should just look at all the banks in your setup and score the old POI. If not, the POI FAQ entry has sample code that you can use to get the JVM to print where it loaded the POI classes from. This will show you the jar file name, and you can delete the old one.