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

5

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.

  &lt;html:link page="/renderCsv.do">
    &lt;bean:message key="link.renderCsv"/>
  &lt;/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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

5 Comments

  1. Hi,
    I’m currently developing a similar kinda appln. wherein I have to download certain things in a csv file.
    The requirement is that the downloaded file should open with a unique file name
    which would be system generated. But If say
    < % response.setHeader("Content-disposition","attachment; filename=" + generatedfilename); %>
    its appearing as generatedfile 1.csv.
    Can anyone tell me how to remove the trailing 1 in the filename?
    Also If I have a data that has leading zeros , in the csv sheet thats appearing without zero’s
    eg . Data 0002277018 is appearing 2277018 only.
    Please give a solutin for these problems asap. I’m urgently in need of these

  2. Hi, I found this topic very usefule and was able to generate CSV file.Thanks a lot for this topic.But My application is BC4J JSP,could you please tell me how can I acheive this in BC4J JSP.
    Thanks Again
    S

  3. Dick Dijkshoorn on

    You could also create a ‘real’ excel sheet by only replacing this in your original jsp:

    < %@ 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.xsl"); %>

    Note: thus make the file name : employees.xsl instead of employees.csv.

    And do not remove the html tags.

    If you run the jsp page you will get a ‘real’ excel file.

  4. Wouter van Reeven on

    After having tried this, I have two comments:

    1) In the “The ViewController project” section, make sure to drag and drop – from the DataControl palette – the EmpView data control that is nested in the DeptView data control. In my case this is EmpView3 and not EmpView1.

    2) In the code that needs to be replaced, make sure to refer to the same EmpView data control as the one that is dragged and dropped onto the JSP page. Again, in my case this has to be EmpView3 and NOT EmpView2 as mentioned in the example code. Are you sure you included EmpView2 and not EmpView1?

    After this, the code runs correctly.