Export table to Excel in Oracle ADF

If you have users that like reports, I have good news to you! Export table to Excel is very simple when you use Oracle ADF. You just have to use the exportCollectionActionListener tag. This way, you allow an action source (<commandButton>, <commandLink>, etc.) to export data from a collection component (<table>, <tree>, <treeTable>) into an external format in declarative way.

Download the sample application – ExportTableApp.zip – that export rows (all or selected) from table into Excel report. This application shows the table as printable page too.

Let’s start!
Create a Fusion Web Application (ADF) like image below.

export-table-to-excel-in-oracle-adf1

Open the ViewEmployees.jspx file.
Navigate to Data Control pane, drag the EmployeesView1 and drop inside the page as ADF Read-only Table. Mark Multiple Rows, Enable Sorting and Enable Filtering options from Edit Table Columns Dialog, and click on OK to create the table.

export-table-to-excel-in-oracle-adf2

Select the table and set the ID property as “tEmployees”.

export-table-to-excel-in-oracle-adf3

Now we need to surround table with Panel Collection, a component that contains certain controls / features to work with collection components like <table>, <treeTable> and <tree>. Go to Structure pane, right-click on af:table “tEmployees” and select Surround With. Select Panel Collection and click on OK.

export-table-to-excel-in-oracle-adf4

export-table-to-excel-in-oracle-adf5

To create menu with export options, go to Structure pane, right-click on menus facet from af:panelCollection, and select Insert inside menus | Menu. Set the Text property as “File”.

export-table-to-excel-in-oracle-adf6

Right-click on af:menu “File” and select Insert inside af:menu – File | Menu Item. Repeat the previous steps to add three Menu Items. Set the Text porpeties as “Export to Excel (All Rows)”, “Export to Excel (Selected Rows)” and “Show as Printable Page”.

export-table-to-excel-in-oracle-adf7

Go to Component Palette | Operations, drag the Export Collection Action Listener and drop inside the af:commandMenuItem “Export to Excel (All Rows)” from Structure Pane.

export-table-to-excel-in-oracle-adf8

In Insert Export Collection Action Listener Dialog, click on Edit from the ExportedId property, navigate through the nodes to find af:table “tEmployees” and click on OK. Set the Type property as excelHTML. Click on Ok.

export-table-to-excel-in-oracle-adf9

export-table-to-excel-in-oracle-adf10

export-table-to-excel-in-oracle-adf11

Set the properties of the af:exportCollectionActionListener.

export-table-to-excel-in-oracle-adf12

Repeat the previous steps for the af:commandMenuItem “Export to Excel (Selected Rows)”, and set the properties of the af:exportCollectionActionListener.

export-table-to-excel-in-oracle-adf13

Drag the Show Printable Page Behavior and drop inside the af:commandMenuItem “Show as Printable Page” from Structure Pane.

export-table-to-excel-in-oracle-adf14

The Structure pane should look like this image.

export-table-to-excel-in-oracle-adf15

Now, it is time to test the application!
Right-click on ViewEmployees.jspx page and select Run.

To export all rows, go to menu and select Export to Excel (All Rows) option.

export-table-to-excel-in-oracle-adf16

export-table-to-excel-in-oracle-adf17

To export selected rows, select some rows, go to menu and select Export to Excel (Selected Rows) option.

export-table-to-excel-in-oracle-adf18

export-table-to-excel-in-oracle-adf19

To show table as printable page, go to menu and select Show as Printable Page option. This way, you can save the table as image or PDF file.

export-table-to-excel-in-oracle-adf20

export-table-to-excel-in-oracle-adf21

Waslley Souza

Author: Waslley Souza

Consultor Oracle com foco em tecnologias Oracle Fusion Middleware e SOA. Certificado Oracle WebCenter Portal, Oracle ADF e Java.

8 thoughts on “Export table to Excel in Oracle ADF”

  1. Hello Waslley Souza ,

    Its a good example. I have a question here . How do I save the actual column names from the database to excel sheet?

    1. Naresh,

      In the View Object file, go to Attributes navigation tab and double-click an attribute.
      Go to Control Hints menu, and set the Label Text with the column name from database.
      Hope it helps you!

  2. couple of questions

    1. how to limit the number rows to download to excel. Because of more data excel download taking time
    2. Some data in excel columns shows as 3.33133E+31 instead of full value

  3. I have one column as SSN in the adf page which is masked. When I export the data to excel the ssn is not masked..Is there any way I can get the ssn into the exported excel as masked

Leave a Reply

Your email address will not be published. Required fields are marked *