Much better way of producing CSV (Excel) Files from ADF WebApplications

A few days ago, I wrote a post on using XML derived from the ADF BC based Model in an XSLT transformation to render comma separated values (csv) files from ADF Web Applications:Example of XSL-transforming in ADF applications – downloading Excel (CSV) files from ADF Web Applications . While everything I wrote in that article is correct, it illustrates most certainly not the optimal way of producing these files. You could view that article as an interesting demonstration of the cooperation between ADF BC, ADF Binding Framework, JSTL XML and XSLT. This post will show a much better method for downloading csv-files that can immediately be opened in MS Excel. This method is easier to implement and will use less resources when executed, which especially for larger data collections will have a noticeable effect.

The trigger for this post and its predecessor is a rather common requirement that we encountered in one of our recent projects based on Oracle ADF (JSP, Struts, ADF BC and ADF Binding Framework on top of an Oracle Database): the ability to download certain sets of data in Excel-file format (basically a Comma Separated Values file). In this post I discuss how we can easily and rapdily JSPs on top of the ADF Binding Framework that produce csv-files that be made downloadable from the application.

The demo project: EMP and DEPT

As usual I have chosen EMP and DEPT (the standard SCOTT schema) as my example. I will construct a simple Web Application (JSP, Struts) using JDeveloper 10.1.2. My Model project will contain two ViewObjects, DeptView and EmpView, with a ViewLink between them (Dept is the Source, Emp the Destination). I have created these ADF Business Components in the Model project from a SCOTT_LOCAL connection that links to the SCOTT schema in my local database. From the New Gallery I have picked Business Tier, Business Components, Business Components from Tables and selected the EMP and DEPT table from this connection.

The Application Module’s Data Model contains two ViewObject usages: DeptView1 (of ViewObject DeptView) and its nested child EmpView1 (of ViewObject EmpView, via the ViewLink between Dept and Emp).

The ViewController project

In the struts-config.xml visual editor, I drag and drop a new DataPage to the canvas. I double click and go to the JSP editor; I call the JSP renderCSV.jsp . Next, I drag and drop – from the DataControl palette – the EmpView1 data control as read only table. This will create the required data binding in the page’s UI Model (aka Binding Container). Then I replace the generated JSP code with this:

<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c"%><%@ taglib uri="http://java.sun.com/jstl/xml" prefix="x"%><%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html"%>
<%@ page contentType="application/vnd.ms-excel" %><% response.setHeader("Content-disposition","attachment; filename=" + "employees.csv"); %>
<c:forEach var="Row" items="${bindings.EmpView1.rangeSet}">
<c:out value="${Row['Ename']}"/>,<c:out value="${Row['Job']}"/>,<c:out value="${Row['Hiredate']}"/>,<c:out value="${Row['Sal']}"/>
</c:forEach>

remove all linefeeds, except the one after <c:out value="${Row['Sal']}"/>

Note that in the beginning of the JSP I have set the mime-type of the JSP to application/vnd.ms-excel and the file-type that is displayed in the Save As dialog in the Browser to employees.csv. Note: in my application I had to remove all linefeeds from the JSP in order to prevent unwanted empty rows from appearing in the CSV-file!

The Range Size property for the EmpView1 iterator is set to 10 by default. I will change this to -1, since I want all Employees in the current department to be included in the csv-file. Do this by clicking on the EmpView1Iterator in the UI Model for the renderCSV.jsp and opening the property inspector.

I then went back to the Struts-Config editor and created a second datapage – formDepts and formDepts.jsp. I drag and dropped DeptView onto this page as ReadOnly Form. I dragged it again, as Navigation Buttons. This gives me a simple webpage that shows the current department and allows me to browse through the set of departments with Next/Previous and First/Last buttons.

I returned to the struts-config editor and added a PageLink from the formDepts data page (formDepts.jsp) to the other dataPage (renderCSV or renderCSV.jsp). This created a link in the formDepts.jsp that starts the second JSP – which means it starts the download and automatic opening of Excel.

  <html:link page="/renderCsv.do">
    <bean:message key="link.renderCsv"/>
  </html:link>

When I run the JSP formDepts.jsp, I get the following situation in the browser:

I have navigated to Department 20 and now I click on the Download CSV link:

Click on Open. Excel is started and displays the document. The Excel document contains the Employee data for the Employees in the current department.