Example of XSL-transforming in ADF applications – downloading Excel (CSV) files from ADF Web Applications

One of our recent projects based on Oracle ADF (JSP, Struts, ADF BC and ADF Binding Framework on top of an Oracle Database) had as one of its requirements the ability to download certain sets of data in Excel-file format (basically a Comma Separated Values file). In this post I discuss how the ADF Binding Framework and the ADF Business Components Model can be used to import model-data as XML into a JSP and more specifically into JSTL-XML tags. It then shows how you can import a simple XSL-T Stylesheet into this JSP to have the XML data from the Model transformed in a CSV format. A sample project can be downloaded.

Note: although this post gives a fine example I believe of how to use the ViewObject writeXML method through the ADF Binding Framework to get hold of data in XML Document in a JSP page and use JSTL XML tags to perform a transformation, it is by no means the best way to render csv-files. Producing csv-files can be done in a much easier way, I will demonstrate in a post soon to be published. So use this post to understand how an XML Document can be retrieved from ADF and transformed in the JSP, but not in order to produce csv-files! In yet another post I will show to use the method demonstrated in this article for rendering SVG objects, a more to the point application of this technique.

The method for getting hold of the Model-data in XML format is largely based on the exchange I had with Steve Muench. The article proposes an alternative to for example the use of JSTL’s SQL tags to select data that is then transformed into XML Document variables within a JSP. Those tags completely bypass the ADF Binding Framework and even the ADF BC Business Service. The approach followed in this post is completely in line with ADF. We can make use of the writeXML() method on the ViewObject. This method returns an XML Document with the data from the current VO Row as well as all of its details through the ViewLinks defined for the ViewObject, for as many levels as is required.

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.

Next, I have edited the ViewObject DeptView: I have added a transient attribute DeptXml of type org.w3c.dom.Document. I had the wizard generate the DeptViewRowImpl class. In that class, I have edited the code for the getDeptXml method:

  public Document getDeptXml() {

    HashMap h = new HashMap();
    h.put("nl.amis.empdept.model.DeptView",
          new String[]{"Deptno",
                       "Dname",
                       "Loc",
                       "EmpView"});
    h.put("nl.amis.empdept.model.EmpView",
          new String[]{"Empno",
                       "Ename",
                       "Sal",
                       "Job",
                       "Hiredate"});
    Node n = writeXML(XMLInterface.XML_OPT_ALL_ROWS,h);
    Document d = n.getOwnerDocument();
    n = ((XMLDocument)d).adoptNode(n);
    d.appendChild(n);
    return (Document)d;

  }

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 deptCsvExcel.jsp . Next, I drag and drop the DeptView’s DeptXML datacontrol to the JSP. This will create the required data binding in the page’s UI Model (aka Binding Container). Then I remove the <c:out value="${bindings.DeptXml}"/> from the JSP. Well actually, I remove everything and replace it with:

<%@ 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:set var="voxml" value="${bindings.DeptXml.attributeValue}"/>
<c:import var="xslt" url="DeptEmp.xsl" />
<x:transform xml="${voxml}" xslt="${xslt}" />

In this JSP, I get hold of the XML Document that I get offered from the DeptXml data control on the DeptView in the page variable voxml. I then import the XSLT stylesheet DeptEmp.xsl in another page-scope variable xslt and transform voxml with xslt. 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 XSL-T stylesheet I used is very straightforward:

<?xml version="1.0"?>
<xsl:stylesheet version = "1.0" 	xmlns:xsl = "http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:strip-space elements = "gazetteer"/>
<xsl:template match="/">
<xsl:for-each select="DeptViewRow/EmpView/EmpViewRow">
<xsl:text/><xsl:value-of select="normalize-space(Ename)"/>,<xsl:text/>
<xsl:value-of select="normalize-space(Job)"/>,<xsl:text/>
<xsl:value-of select="normalize-space(Hiredate)"/>,<xsl:text/>
<xsl:value-of select="normalize-space(Sal)"/>
<xsl:text disable-output-escaping = "yes" >
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

I then went back to the Struts-Config editor and created a second datapage – runDownloadCsv and runDownloadCsv.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 runDownloadCsv data page (runDownloadCsv.jsp) to the other dataPage (dataPage1 or deptCsvExcel.jsp). This created a link in the runDownloadCsv.jsp that starts the second JSP – which means it starts the download and automatic opening of Excel.

When I run the JSP runDownloadCvs.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.

Important: to have the XML JSTL tags function correctly, I have to set a Java runtime option on the Runner tabsheet in the Project Properties dialog for the View Controller project: -Dorg.xml.sax.driver=oracle.xml.parser.v2.SAXParser (in the textbox labeled Java Options).

Download the Sample Workspace for Oracle 10g JDeveloper 10.1.2: CsvExcelAdfDemo.zip

Note: The above is useful primarily as example of using writeXML() on the ViewObject to publish an XML Document, bind it to a JSP and transform it using JSTL XML tags. If all you want to do is publish csv files, there is a much easier way, using plain DataControls based on ViewObject attributes – no XML involved. The JSP would look as follows to produce a CSV file for the employees in the selected department:

<%@ 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.EmpView2.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']}"/>

Resources

See Steve Muench’s article in response to my initial questions: Questions on Exposing XML via Transient Attribute. It all started with one of Steve’s Not Yet Documented ADF Sample Applications:

Using ViewObject writeXML and JSTL XML Tag Library – Illustrates how to use the ViewObject writeXML() method to produce a multi-level XML message for an HR schema-based data model having Departments -> EmployeesInDepartment -> DirectReports->DirectReportJobHistory with a second detail collection under Departments of Deparments -> EmployeesJobHistory. This XML document is then set as a request attribute so that the JSTL XML library can render the multi-level information using XPath expressions. The sample contains both Struts and Model 1 examples (requires the HR schema).

A very useful article on creating an XSLT for transforming to a CSV file format: Conversion of XML to CSV – An XSLT example by Peter Burden