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

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

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

Exported table without filter


and the result with the filter method set:
Exported table with filter

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.

Validate Data before Export via af:exportCollectionActionListener or af:fileDownloadActionListener

ADF rich faces offer a nice and easy feature to stream data to the client (e.g. Excel) using the af:exportCollectionActionListener or af:fileDownloadActionListener component. Both of the components get the output stream from the response, so the application an add the data.
One problem is that the two components fire before the application has a chance to validate other data on the page or do some other needed work.
To overcome this shortcoming you can implement the trick I’m outlining in this blog. The idea is to use a normal af:commandbutton or af:commandToolbarButton on the page which calls an actionListener in a bean. In there you can validate other page data or do some other stuff in the model, and then queue an event inside the bean method to call another invisible button on the page which has the af:exportCollectionActionListener attached.

As it turned out (thanks Jiandong Xin for pointing it out), there is a problem if the button we queue the action event to has a client behavior tag attached. This is the case for af:exportCollectionActionListener or af:fileDownloadActionListener. These tags don’t work properly if we only queue an event to the parent button. As Jobinesh pointed out in his blog we need to insert a JavaScript function into the page which handles the event queuing from JavaScript and call it from the bean. This way we are able to set the needed parameters (e.g. not to wait for a response).

Now lets implement this: Lets start with a toolbar button which resides on a panel collection holding the table we want to export. The button has a binding to the bean (binding=”#{exportBean.exportCollectionButton}”) to make it easier to call it from the bean. If you don’t like to bind the button to the bean, you can search for the component by name inside the action listener we implement below. The page contains the custom handler (customHandler(event)) which we call from the bean method. This handler then queues the event to activate the button which starts the file download. The connection between the customHander and hte commandToolbarButton is the ID of the commandToolbarButton (“ctb1”). You have to change this if your button has a different ID.

<af:resource type="javascript">
  function customHandler(event){
    var exportCmd = AdfPage.PAGE.findComponentByAbsoluteId("ctb1");
    var actionEvent = new AdfActionEvent(exportCmd);
    actionEvent.forceFullSubmit();
    actionEvent.noResponseExpected();
    actionEvent.queue();
  }
</af:resource>
<af:panelCollection id="pc1">
  <f:facet name="menus"/>
    <f:facet name="toolbar">
      <af:toolbar id="t2" binding="#{exportBean.toolbar}">
        <af:commandToolbarButton text="Export..." id="ctb1"
            binding="#{exportBean.exportCollectionButton}"
            visible="false">
          <af:exportCollectionActionListener type="excelHTML" exportedId="t1"
              title="Export"/>
        </af:commandToolbarButton>
...

The vital part is that the visible property is set to “false”, so that the button will not show up on the page, but is fully functional. Somewhere else on the page (or even on the same toolbar) we have an with an action listener pointing to a bean method (exportBean in the sample). This button is visible and used to initiate the export.

...
<af:commandButton text="export via bean" id="cb8" 
    actionListener="#{exportBean.exportprgActionListener}"/>
...

The exportBean actionListener looks like this

    // toolbar button with export listner attatch bound from page
    private RichCommandToolbarButton exportCollectionButton;
...
    public void exportprgActionListener(ActionEvent actionEvent) {
        // Add event code (validation or other) here...

        // queue the event via a JavaScript inserted into the page
        FacesContext context = FacesContext.getCurrentInstance();
        ExtendedRenderKitService erks =
        Service.getService(context.getRenderKit(), ExtendedRenderKitService.class);
        erks.addScript(context, "customHandler();");
    }

In the action listener above you can validate other data on the page or call service methods in your application module before starting the export, or cancel the export if you like. The export runs as if you click the af:exportCollectionActionListener directly.
You can download a sample workspace BlogFileDownLoadTest.zip
After download remove the ‘.doc’ suffix!