Exporting to Excel from any ADF Table

22

No matter how fancy the application you develop, no matter how sophisticated the user interfaces: end users will always clamor for Excel. All data eventually will end up in some Excel Spreadsheet it seems. And grudgingly or not, we will of course oblige them. Depending on how difficult it actually is to achieve such Excel export functionality.

Well, frankly, using the Apache POI library and the ADF Model, it turns out to be very simple. In fact, it turns out to be very simple to create a generic Table To Excel Export facility, that we can apply to any Table (binding) in our application in a matter of mere minutes.

Note: the foundation for this solution was laid in Frank Nimphius’ Weblog article ADF Faces: Exporting table data to Excel from ADF Faces/ADF (http://www.orablogs.com/fnimphius/archives/001881.html ) which itself was based on work done by Jakub Pawlowski. We will see a refined version of their work, one that is not dependent on ADF BC as Business Service, that is generic for all Table Bindings and that allows the user control over which columns should be in the Excel Spreadsheet as well as which rows.....

We can add a generic dialog that pops up when the Export to Excel button has been pressed in the Table Header and that allows the user to:

  • Specify the name of the Excel Spreadsheet
  • Specify which rows to export (the ones displayed in the table, all rows or a selected range)
  • Indicate which columns to include in the spreadsheet, including ones not shown in the table on screen, and in which order

Adding that capability is pretty straightforward, using a commandbutton with two setactionlistener elements, a generic page that acts as popup dialog along with its generic navigation case and the generic ExcelExporter bean and its configuration in the faces-config.xml.

Create this managed bean configuration for the ExcelExportBean

  <managed-bean>
    <managed-bean-name>ExcelExportBean</managed-bean-name>
    <managed-bean-class>nl.amis.jsf.ExcelExporter</managed-bean-class>
    <managed-bean-scope>session</managed-bean-scope>
  </managed-bean>
 

The class ExcelExporter can be downloaded in the zipfile in the end of the article.

Also add this global navigation case to the faces-config.xml:

  <navigation-rule>
    <from-view-id>*</from-view-id>
    <navigation-case>
      <from-outcome>dialog:ExcelPopup</from-outcome>
      <to-view-id>/ExcelPopup.jspx</to-view-id>
    </navigation-case>
  </navigation-rule>
 

It indicates that the action outcome dialog:ExcelPopup, returned from any command button, should navigate to the generic ExcelPopupjspx page.

 

The commandButton required to add Excel Export to any af:table element looks like this:

<af:commandButton text="Export to Excel" useWindow="true"
                  action="#{ExcelExportBean.exportThisTableToExcel}">
   <af:setActionListener
           from="#{'BooksView'}"
           to="#{ExcelExportBean.tableBindingName}"/>
   <af:setActionListener
           from="#{'Title,Isbn, PublisherName,AuthorsDisplayNames'}"
           to="#{ExcelExportBean.defaultAttributeNames}"/>
</af:commandButton>

Add the useWindow attribute to the commandButton Export to Excel and set it to true – we need this to open the dialog/popup in a new window. We make use of two setActionListener elements to inject two configuration details into the ExcelExport bean. This first is the name of the table binding. This corresponds to the id attribute of the table element in the PageDefinition file. The second one – the defaultAttributeNames – specifies through a comma separated list of attribute names which columns should be included in the Excel sheet. Note: the Attribute Names in this list should correspond with the Value property of the AttrNames\Item elements in the table binding.

Running the page and pressing the Export to Excel button gives us the popup dialog that shows a shuttle with all attributes available for export:

Pressing the Export button will now invoke the doExport method on the ExcelExportBean

<f:facet name="footer">
  <af:commandButton text="Export..."
                    action="#{ExcelExportBean.doExport}"/>
</f:facet>

The bean knows which attributes to export in which order from which table binding, it has been told which rows to export – all, the ones shown in the page or a selected range – and it will export the Excel document.

 

Note: the ExcelExport class is completely independent of ADF BC as implementation technology for the business service. It only speaks ADF Model lingo: tableBinding, iterator, rowsetiterator etc. It can be used to produce Excel Exports from data coming from any Data Control.

The next steps

Moving forward I would like to be able to have the user specify Excel Template files upfront into which the data is pasted in the correct location. I will also try to implement uploading excel documents and extract data (new rows and changes) from them.

 

Resources

You can download the most recent version (which is not all that recent by the way) of POI from: http://www.apache.org/dyn/closer.cgi/jakarta/poi/.

ExcelExport class and Popup Dialog page:  excelexporter.zip
 

Download the database scripts to create the ALS database: als_database.zip

Download the JDeveloper 10.1.3.2 project (without the jar files): exportexcel.zip

 

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.

22 Comments

  1. Hi Lucas, Great Job!!!
    Only a small problem: when you click the export buttom on the popup window, the excel file is downloaded, but this buttom is left waiting, disabled, and does not respond. It is necessary to reload de popup for the button to display correctly again.
    ¿Any solution?¿Any idea?

    Thanks you,
    Eva

    Note: I am working with JDeveloper 11.1.1.5.0

  2. Enlightening! I particularly like the way you initialize your variables for the page with the command button action method and then return the action. Something I’ve not considered before. Great stuff!

  3. Hi,

    I just wanted to know whether it is possible to use it with JHeadstart as well as in ADF JDeveloper or not ?

    Thank you,
    Ahmed

  4. Hi,

    i have a smaill Problem that is whern i am exporting the data in excel file i got a performence issue that is when i want to export the data less than 1000 its taking few min when iwant to export more than 5000 then its taking 30 min can u help to slove in this issue

    thanks in Advance
    Raja

  5. Hi Lucas, Great job. we are using this in our project. While compiling ExcelExport.Java in JDeveloper 10.1.3.3.0, I am getting following errors:

    HSSFCell not found
    HSSFCellStyle not found
    HSSFDataFormat not found
    HSSFFont not found
    HSSFRow not found
    HSSFSheet not found
    HSSFWorkbook not found
    HSSFColor not found

    Not able to compile the following:
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;

    I put poi-2.5.1-final-20040804.jar in the WEB-INF/lib and also placed this poi jar file in JDev15/jdk/jre/lib directory. Am I missing something here.
    I appreciate a quick response.

    Thanks a lot.
    Ram

  6. Hi, I migrate the application to JDeveloper 10.1.3.3. When I Run the application the following errors occurres:
    Error(44,8): HSSFCell not found ;Error(45,8): HSSFCellStyle not found ;etc.
    Can you help me?

  7. Hi, used you’re code and works nice.
    I made a popup dialog where the user can choose from witch table the want the make an export. After choosing the ExcelPopup.jsp is loaded.

    Then I get a null pointer exception:
    Caused by: java.lang.NullPointerException at apro.view.backing.global.ExcelPopup.exportThisTableToExcel(ExcelPopup.java:69)
    Caused by: javax.faces.el.EvaluationException: java.lang.NullPointerException at com.sun.faces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:150) at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:92)

    This is the code of row 68&69:
    DCBindingContainer bc = getBindingContainer();
    containerName = bc.getName();

    Could you please tell me what goes wrong and how I can fix it?

  8. Lucas thanks, i have one more question. in your getAttributes method , i have added below code to store the selected values in a Cookie before return attributes statement

    String selectedAttributeNames = arrayToString(attributes,”,”);
    Cookie attrsCookie = new Cookie(tableBindingName,selectedAttributeNames);
    attrsCookie.setMaxAge(60*60*24*365);
    FacesContext ctx = FacesContext.getCurrentInstance();
    ((HttpServletResponse) ctx.getExternalContext().getResponse()).addCookie(attrsCookie);

    and reading values from cookie in getDefaultattributes , looks like it is working okay in jdeveloper , but not in Application server. in application server it shows the values from cookie when i switch between pages , but not when i close the browser and comeback into it . any ideas

    Thanks
    Rao

  9. Rao,

    The pivot has not yet been implemented. What it should do is pivot or rotate the table contents, turning Table Rows into Columns in Excel and Table Columns into Excel Rows.

    Regards,

    Lucas

  10. Hi Lucas

    This is very useful application. i was wondering if you are going to have time to work on the next steps part, if you can give any suggestions on how to implement
    “have the user specify Excel Template files upfront into which the data is pasted in the correct location” that would be great

    Thanks for good sample application

    Best Regards
    Rao

  11. The libraries required in WEB-INF\lib are: adf-faces-impl.jar, jsf-impl.jar and poi-2.5.1-final-20040804.jar – or some other POI jar. I have added the Database Scripts to the set of Resources for this article.

  12. Lucas Hi, Very good job. Could you send me by email the script to create the table and which libraries in need to include?

    Thank you in advance,
    Nota

  13. Hari Krishna on

    Can you please tell me the list of jar files to be placed in my class path to complie the code (ExcelExport.Java).I have POI jars in my classpath.Pasted below are the are the imports

    import oracle.adf.controller.v2.context.LifecycleContext;
    import oracle.adf.model.BindingContext;
    import oracle.adf.model.binding.DCBindingContainer;
    import oracle.adf.model.binding.DCIteratorBinding;

    import oracle.adf.model.binding.DCParameter;
    import oracle.adf.share.ADFContext;

    import oracle.jbo.AttributeDef;
    import oracle.jbo.AttributeHints;
    import oracle.jbo.LocaleContext;
    import oracle.jbo.NavigatableRowIterator;
    import oracle.jbo.Row;
    import oracle.jbo.RowSetIterator;
    import oracle.jbo.ViewObject;
    import oracle.jbo.common.DefLocaleContext;
    import oracle.jbo.domain.Date;
    import oracle.jbo.domain.Number;

  14. Very useful. Many thanks.

    In my test I’ve needed to change this lines in ExcelExporter.java

    //DCIteratorBinding iter = tableBinding.getIteratorBinding();
    JUIteratorBinding iter = tableBinding.getIteratorBinding();

    Juanje

  15. No unfortunately not right away. This Excel export is based on the ADF Model (Data Binding), not on ADF Faces. While ADF Faces translates to Trinidad, there is no Apache equivalent to the Data Binding. You can use ADF Model together with Trinidad, but ADF Model is not open source and requires a license.

  16. Michael Trompertz on

    Hi
    can this also be used with Apache Trinidad? If yes what are the corresponding classes in Trinidad to the oracle.adf.* and oracle.jbo.* classes?

    regards

    Michael

  17. very good Work!
    I have a question that why simplified character can’t export?

  18. Jakub Pawłowski on

    Hi Lucas,

    Great work. I’m happy that my solution with exporting data to Excel is useful for others. Could you made accessible to download (or send me by email) all application (containing script to create table with sample data) described in this entry ?

    Regards
    Jakub Pawłowski