Export to Excel enhancements in JDeveloper 11.1.1.9.0 and JDeveloper 12.1.3

In the current JDeveloper version 11.1.1.9.0 and 12.1.3 the af:exportCollectionActionListener got enhanced by options to filter the data to export.

Enhanced options of exportCollectionListener

The option this blog talks about is the one marked, the FilterMethod. The ducumentation for 12.12 Exporting Data from Table, Tree, or Tree Table does not reveal too much about how to use this FilterMethod.
The sample we build in this blog entry shows how the FilterMethod can be used to filter the data to be exported to excel.
In older version of JDev you hadto use a trick to filter the data which was downloaded from a table see Validate Data before Export via af:exportCollectionActionListener or af:fileDownloadActionListener. The new property of the af:exportCollectionActionListener allows to filter the data without using the trick.
The sample just load the employees table from the HR DB schema and shows it in a table on the screen. In the toolbar we add a button which has the af:exportCollectionActionListener attached.

Running application

Below is the page code of the toolbar holdign the export button:

                            <f:facet name="toolbar">
                                <af:toolbar id="t2">
                                    <af:button text="Export to Excel" id="b1">
                                        <af:exportCollectionActionListener type="excelHTML" exportedId="t1" filename="emp.xsl" title="Export"
                                                                           filterMethod="#{ExportToExcelBean.exportCollectionFilter}"/>
                                    </af:button>
                                </af:toolbar>

The filterMethod of the af:exportCollectionActionListener points to a bean method exportCollectionFilter in a request scoped bean ExportToExcelBean. The method gets called for each cell of the table which gets exported.

    /**
     * This method gets called for each cell which is to be exported.
     * It can be used to filter data to be exported. In this case salary values > 6000 are not exported
     * @param uIComponent component of the cess which gets to be exported
     * @param exportContext context of the exported data (holds e.g. file name, character set...)
     * @param formatHandler format to be exported
     * @return true if cell value is exported, false if not
     */
    public Boolean exportCollectionFilter(UIComponent uIComponent, ExportContext exportContext, FormatHandler formatHandler) {
        if (exportContext.isFirstInRow()) {
            count++;
            _logger.info("Start a new Row " + count);
        }
        _logger.info("Export Collection UIComponent: " + uIComponent.getId());
        if (uIComponent instanceof RichOutputText) {
            RichOutputText rot = (RichOutputText) uIComponent;
            Object val = rot.getValue();
            String headerText = "";
            UIComponent component = rot.getParent();
            if (component instanceof RichColumn) {
                RichColumn col = (RichColumn) component;
                headerText = col.getHeaderText();
            }
            StringBuilder sb = new StringBuilder();
            sb.append("Name: ");
            sb.append(headerText);
            sb.append(" Value: ");
            sb.append(val);
            _logger.info(sb.toString());
            // check if the salary is greater than 6000
            if ("Salary".equals(headerText)) {
                if (((BigDecimal) val).intValue() > 6000) {
                    // if yes return false so that the value isn't exported
                    _logger.info("Skip Vals > 6000");
                    return false;
                }
            }
        }

        return true;
    }

The method gets the uiComponent which represents the current cell to be exported, the ExportContext and the FormatHandler for the export. The ExportContext hold information about the filename, title, the used character set and status information about the row and cells currently exported. The status can be used to find out is a new row just starts to be exported ro is a cell is part of a span of cells. In the sample we use this information to print a log message for each row exported.
The FormatHandler is used to generate the document to be exported and the data in it. I did not find a way to use my own handler and there is no documentation about how to use another handler, so we leaf this as is for the moment.
In the sample method we like to filter the employee data in a way, that salaries greater than 6000 are not exported to the resulting file. As the method is called for each cell, the first thing to find out is which cell currently used. In lines 15-29 we use the current UIComponent to find out which column we are in. In lines 31-37 we check the salary column. In case the salary value is greater than 6000 we return false as this will trigger that the cell value is not exported. If the salary is below or equal to 6000 we return true and the cell value is exported.
Below we see the result we get if we export the table without the filterMethod set:

Exported table without filter

and the result with the filter method set:

Exported table with filter

You can download the sample application which was build using JDeveloper 12.1.3 and the HR DB schema from GitHub.

dvt:treemap showing node detail in popup

This post describes how to implement an dvt:treemap which shows a af:popup when the user clicks on a detail node in the map.
The documentation of the dvt:treemap component tell us that the dvt:treemapnode supports the af:showPopupBehaviortag and reacts on the ‘click’ and ‘mouseHover’ events.
This is part of the solution and allows us to begin implementing the use case. We add an af:showPopupBehavior to the nodes we want to show detail information for.

After creating a default Fusion Web Application which uses the HR DB schema, we begin with creating the data model for the model project. For this small sample the departments and employees tables will be sufficient.

Data model

Data control

The views are named according to their usage to make it easier to understand the model. This is all we need for the model.

Let’s start with the UI which only consist of a single page. The page has a header part and a center part. In the center area we build the treemap by dragging the Departments from the data controls onto the page and dropping it as treemap. After that, in the dialog we specify the first level of the map to be the departmentId (which shows the department name as the label) and the for the second level we choose the employeeId (which shows the last name of the employee as label) from the employees. The whole process is shown in the gallery below.

Create Treemap from Departments – Step 1

Select DepartmentId as value – Step 2

Select DepartmentName as Label – Step 3

Select EmployeeId as Value for second level of Treemap – Step 4

Select LastName as Label for second level of Treemap – Step 5

Resulting page – Step 6

The resulting treemap is very basic in it’s features, e.g. there is no legend as you see later.
In the next step we create an af:popup to show the nodes detail information. This process is outlined in the next gallery. We drag the popup component onto the page below the af:treemap component

Position of the af:popup component

Create an af:popup to show detail about node

Set properties of the popup – Step 1

Create a PopupFetchListener – Step 2

Create a bean class – Step 3

Create a popupFetchListener method – Step 4

Create a popupFetchListener method – Step 5

Save work – Step 6

One thing to take note of are the properties of the popup. First we set the content delivery to ‘lazyUncached’, which makes sure that the data is loaded every time the popup is opened. Otherwise we’ll see only the data from the first time the popup has been opened. Second change is to set the launcherVar to ‘source’. This is the variable name we later use to access the node data. Third change is to set the event context to ‘launcher’. This means that events delivered by the popup and its descendents are delivered in the context of the launch source.

The treemap for example, when an event is delivered ‘in context’ then the data for the node clicked is made ‘current’ before the event listener is called, so if getRowData() is called on the collectionModel in the event listener it will return the data of the node that triggered the event. This is exactly what we need.

Finally we add a popupFetchListener to the popup which we use to get the data from the current node to a variable in the bindings. In the sample this variable ‘nodeInfo’ is defined in the variable iterator of the page and an attribute binding ‘nodeInfo1’ is added. More info on this can be found here.

Create storage and binding for node information – Step 1

Create storage and binding for node information – Step 2

Create storage and binding for node information – Step 3

Create storage and binding for node information – Step 4

The code below shows the popupFetchListener:

package de.hahn.blog.treemappopup.view.beans;

import javax.el.ELContext;
import javax.el.ExpressionFactory;

import javax.faces.application.Application;
import javax.faces.context.FacesContext;

import oracle.adf.model.BindingContext;
import oracle.adf.share.logging.ADFLogger;
import oracle.adf.view.rich.event.PopupFetchEvent;

import oracle.binding.AttributeBinding;
import oracle.binding.BindingContainer;


/**
 * Treemap handler bean
 * @author Timo Hahn
 */
public class TreemapBean {
    private static ADFLogger logger = ADFLogger.createADFLogger(TreemapBean.class);

    public TreemapBean() {
    }

    /**
     * listen to popup fetch.
     * @param popupFetchEvent event triggerd the fetch
     */
    public void fetchListener(PopupFetchEvent popupFetchEvent) {
        // retrieve node information 
        String lastName = (String) getValueFromExpression("#{source.currentRowData.lastName}");
        Integer id = (Integer) getValueFromExpression("#{source.currentRowData.EmployeeId}");
        //build info string
        String res = lastName + " id: " + id;
        logger.info("Information: " + res);
        // get the binding container
        BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry();

        // get an ADF attributevalue from the ADF page definitions
        AttributeBinding attr = (AttributeBinding) bindings.getControlBinding("nodeInfo1");
        //set the value to it
        attr.setInputValue(res);
    }

    // get a value as object from an expression
    private Object getValueFromExpression(String name) {
        FacesContext facesCtx = FacesContext.getCurrentInstance();
        Application app = facesCtx.getApplication();
        ExpressionFactory elFactory = app.getExpressionFactory();
        ELContext elContext = facesCtx.getELContext();
        Object obj = elFactory.createValueExpression(elContext, name, Object.class).getValue(elContext);
        return obj;
    }
}

Finally we have to design the popup to show the node info from the attribute binding ‘nodeInfo1’. The popup uses a dialog with an af:outputText like

Show the node info in the popup

and set an af:showPopupBehavior to the node showing the employees

Define an af:showPopupBehavior – Step 1

Define an af:showPopupBehavior – Step 2

Define an af:showPopupBehavior – Step 3

Define an af:showPopupBehavior – Step 4

Define an af:showPopupBehavior – Step 5

Define an af:showPopupBehavior – Step 6

Running the finished application brings up the treemap, not pretty but enough to see this use case working. If we click on an employee node we see the popup with the last name of the employee and the employee id, the primary key of the selected row in the employees iterator.

Run the application – click on a node – Step 1

Run the application – click on a node – Step 2

Run the application – click on a node – Step 3

Run the application – click on a node – Step 4

You can download the sample application which was build using JDeveloper 12.1.3 and the HR DB schema from GitHub.

Pitfalls when using libraries of newer version than shipped with JDeveloper or WebLogic Server

A question on JDeveloper & ADF OTN forum cought my attention. A user wanted to use a method of the Apache Commons-IO library named FileUtils.getTempDiretory() but got an error when he tried to use code completion or when he tried to compile the code. The problem was that the compiler (or code completion) did not pick up the right java class from the library even as it was installed in the project a library.
As the original code used belongs to one of my samples I was interested in finding a reason for this behavior as I could see no obvious reason for this behavior.

An inspection of a provided test case quickly revealed the problem and a solution was found too. This blog is about the problem and the solution to it. Lets start with building a test case:

Create ADF Fusion Web Application – Step 1

Create ADF Fusion Web Application – Step 2

Create ADF Fusion Web Application – Step 3

Create ADF Fusion Web Application – Step 4

Create ADF Fusion Web Application – Step 5

Create ADF Fusion Web Application – Step 6

Final created Application

The test case had a model project which used a couple of libraries which we add too to make this sample as close as possible to the test case.

Model Project Properties

There is no code whatsoever used in the model project just the libraries are defined!

To make use of the FileUtils.getTempDiretory() method we have to first download the Apache Commons-IO in a version higher then 2.0. The current version is 2.4 which you get from the given link. Once you unzip the zip (or tar.gz) to a directory of your choice we create a new library for JDeveloper (Tools->Manage Libraries…)

Create new Library for Commons-IO 2.4 – Step 1

Create new Library for Commons-IO 2.4 – Step 2

Create new Library for Commons-IO 2.4 – Step 3

Create new Library for Commons-IO 2.4 – Step 4

Final Library for Commons-IO 2.4

We add This new library to the view controller project

Add Library to Project Properties

Select new Commons-IO Library

Library added to Project

Next is to create a java bean where we try to use the FileUtils.getTempDiretory() method

Create new Java Bean Class In View Controller Project – Step 1

Create new Java Bean Class In View Controller Project – Step 2

Create new Java Bean Class In View Controller Project – Step 3

Create new Java Bean Class In View Controller Project – Final

Add Method from Commons-IO FileUtils Class – Step 1

Add Method from Commons-IO FileUtils Class – Step 2

Add Method from Commons-IO FileUtils Class – Step 3

Compilation Error

Here we see the problem mentioned in the OTN question. The FileUtils.getTempDiretory() does not show up at all. The JavaDoc of the Apache Commons-IO 2.4 package shows that the method is available since version 2.0

JavaDoc of FileUtils Class

If we try to compile the code we get an compilation error as seen in the last image.

What is the problem?
Well, it looks like there is another version of the Apache Commons-IO library already loaded in the classpath which gets loaded first. Once a library or class is loaded, another version of the same class will not overwrite the existing one.
First thing we can try is to move the new commons-io library to the top of the list of libraries.
In the test case presented here, this doesn’t work. We still get the same error. So there are libraries loaded before the view controller project libraries come to play.
Remember we added some libraries to the model project even as there is no code in the project at all?
Because the view controller project has dependency defined to the model project when we create an Fusion Web Application by default, libraries of the model project are loaded before the view controller projects.
We have can solve the problem in multiple ways:
1. remove the dependency to the model project. This is not recommended as it would mean that we have to build the model project ourselves if we have to change something in the model and want to run the application.
2. find the library which loads the FindUtils class and see if we can remove it (not all libraries are needed).
3. add the new Apache Commons-IO library to the model project and move it up front. This should load the newer version of the FindUtils class before any other.

Solution 1 isn’t really one. Solution 2 is possible and I’ll blog about it later. For this blog we use solution 3.

Solution
All we have to do is to add the Apache Commons-IO 2.4 library to the model project and move to the top of the list.

Model Project Properties with Commons-IO

If we now rebuild the workspace we see that to error is gone

No Compilation Error

The code completion still shows the method red underlined. This is a bug in JDeveloper which doesn’t pick up the right library. Anyway, the compiler will use the right library and we can compile the application.

Now we add another method to the FileBean which returns the path to the temporary directory. This we use in a page index.jsf to show it on the ui.

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE html>
<f:view xmlns:f="http://java.sun.com/jsf/core" xmlns:af="http://xmlns.oracle.com/adf/faces/rich">
  <af:document title="index.jsf" id="d1">
    <af:form id="f1">
      <af:panelGridLayout id="pgl1">
        <af:gridRow height="50px" id="gr2">
          <af:gridCell width="100%" halign="stretch" valign="stretch" id="gc1">
            <!-- Header -->
            <af:outputText value="Preferred Package Test" id="ot1" inlineStyle="font-size:x-large;"/>
          </af:gridCell>
        </af:gridRow>
        <af:gridRow height="100%" id="gr1">
          <af:gridCell width="100%" halign="stretch" valign="stretch" id="gc2">
            <!-- Content -->
            <af:outputText value="Tempdir path = #{FileBean.tempDir}" id="ot2"/>
          </af:gridCell>
        </af:gridRow>
      </af:panelGridLayout>
    </af:form>
  </af:document>
</f:view>

When we run the application we get an exception

    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:311)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:263)
Caused by: java.lang.NoSuchMethodError: org.apache.commons.io.FileUtils.getTempDirectoryPath()Ljava/lang/String;
    at de.hahn.blog.preferredpackages.view.beans.FileBean.getTempDir(FileBean.java:16)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)

Why’s that?
The application compiled without an error and we still get a NoSuchMethodError. The reason for this is that when we start the WebLogic Server the older version of the Apache Commons-IO jar is loaded first, blocking loading of the newer version we need to get to the FileUtils.getTempDirectoryPath() method.
To allow the server to load our newer version of the jar we need to change a descriptor named weblogic-application.xml which is specific for WebLogic Server. For other servers there exist other descriptors allowing the same.
In this descriptor we add a preferred package for the org.apache.commons.io package. Open the weblogic-appliaction.xml descriptor and select the ‘Class Loading…’ node.

Application Descriptors: weblogic-application.xml

Here we enter the package name org.apache.commons.io to the ‘Application Preferred Libraries’ section.

Add preferred package to weblogic-application.xml – Step 1

Add preferred package to weblogic-application.xml – Step 2

to get this result in the source view of the descriptor:

    <prefer-application-packages>
        <package-name>org.apache.commons.io</package-name>
    </prefer-application-packages>

After restarting the application the index.jsf page show up OK

Running Test Page

You can download the sample application which was build using JDeveloper 12.1.3 from GitHub.