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
![Check out the Apex 4.0 preview at Carl Backstrom's blog!]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Check out the Apex 4.0 preview at Carl Backstrom's blog!")
Hi Eva,
This article was written quite a long time ago, for ADF 10g. In 11g, there is built-in support for export to excel, using the the panelCollection component and the af:exportCollectionActionListener component.
See for an example Exporting table to Excel in Oracle ADF
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
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!
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
hi,
i am looking for solution to export from adf to excel. can anyone give me or mail me step by step instruction for same.
thanks,
ashita
ashita_bhat@yahoo.com
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
Can you publish the link to the blob on uploading excel data?
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
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?
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?
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
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
Hello lucas
what is the Pivot option do on the excelpopup page
Thanks
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
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.
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
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;
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
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.
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
very good Work!
I have a question that why simplified character can’t export?
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