Saturday, April 3, 2010

Exporting table to Excel in Oracle ADF

Hi,
I will continue to enhance my Working with Popups post and will add a export to excel functionality to af:table component. You will know the power of ADF with just simple clicks you are able to add this functionality to table.

What you will learn

 - Functionality of af:panelCollection
 - Working with menus
 - Adding Exporting to excel behavior to the af:table

Download this workspace change connection settings and follow steps

Adding Panel Collection
- Open the countries.jspx page and select af:table component in structure window  give this table a proper name i.e "myTab" by setting id property of the table in property Inspector. 

- Right click the af:table component and choose "Surround with..." and Choose Panel Collection
Note: A panel component that aggregates collection components like table, treeTable and tree to display standard/application menus, toolbars and statusbar items


Adding a Menu in Panel Collection
- Drop af:menu component in the Menu facet of the Panel collection set the Text property as "File"
- Drop af:menuitem on af:menu set the Text property as "Export to Excel"

Adding Export behavior to Menu
- Drop an af:exportCollectionActionListener to the Menu item. A dialog box will appear choose type as the only option available "excelHTML" Choose exportedId from the menu choose edit navigate through the nodes and find af:table "myTab" click OK and OK again.


- Complete Structure window would looks like this


- Set the properties of the af:exportCollectionActionListener as shown in the slide for more meaningful file name and title of worksheet


Run the Form
- Right click on the countries.jspx page and choose Run

- Page similar to this will appear Choose File > Export to Excel from the Menu

- Excel file save dialog will appear



I hope this post would be helpful feel free to comment

Download my working example 

Cheers,
Baig

28 comments:

  1. Hi, it is possible to export only few columns? In your use case, I would want to export only the CountryId and the CountryName.

    Regards, Pedro Medeiros.

    ReplyDelete
  2. Hi Pedro,

    well i m not aware of such solution i will look into it most probably this kind of solution requires some coding.

    ReplyDelete
  3. Excellent post. I'm really blown away by the power of ADF in this latest version

    ReplyDelete
  4. I agree Jeremy ADF 11g is powerful

    ReplyDelete
  5. take a look at this for a more configurable solution

    ReplyDelete
  6. how about exporting dynamic table?

    ReplyDelete
  7. HI ebox,

    did you followed the same steps? I haven't tried with dynamic table. the same steps should work as well as we can see exporting done at runtime.

    Other option is to write servlet to create excel file. i will try to post servlet example in future.

    ZB

    ReplyDelete
    Replies
    1. I am not able to get dynamic table to work .. Did you try this?

      Delete
  8. HI baig,
    can the same functionality be achieved for csv file.Please share.

    ReplyDelete
  9. hi baig,,,can we make multiple sheet a=in an excel file in java and the export this table and more table to that sheets.

    ReplyDelete
  10. Hi Idris,

    As far as i know you can do by using custom java code but not with default component.

    ZB

    ReplyDelete
  11. I already have export to excel button in my applications table. The issue is for a string attribute, which is a number actually... eg: '300100002555003' it gives following thing in excel. 3.001E+14

    Is this issue with excel? or I can handle it in my application?
    Thanks in advance.

    ReplyDelete
  12. Yes basically excel's problem the solution is to concatenate ' (quote sign) before exporting the number it will display correctly in excel.

    e.g. 01234 will be exported like '01234

    It will not impact the data in excel during copy paste.

    ZB

    ReplyDelete
  13. Hi Baig,

    This af:exportCollectionActionListener component doesnt include column footer while exporting the data to excel. Do we have any alternatives for the same?

    ReplyDelete
    Replies
    1. The af:exportCollectionActionListener will only export the Table data.

      Delete
    2. If you provide more info then i can provide better answer

      ZB

      Delete
  14. Yes i have a webpage i have to export the table as well as the table header string as well as footer (esp header) can that be done..?

    ReplyDelete
  15. We have a problem using export to excel feature in IE8. The downloaded file is being saved without any file extension and are unable to view the content. Any pointers plz?

    ReplyDelete
    Replies
    1. Hi,

      Which JDev version you are working with? Make sure you selected excelHTML as type of export listener.

      Also these kind of issues are usually bugs in the release unfortunately i cannot help. Contact Oracle support for that.

      If you tell me the Jdev version then i can test locally if the experience is same or not.

      Regards,
      Z

      Delete
  16. We are facing an issue where the excel file is not getting all the data from the treetable. The treetable has some 30K rows.

    ReplyDelete
  17. I want a header inside the excel sheet. How can i achieve that?

    ReplyDelete
    Replies
    1. did u find a solution for it? i need to achieve the same

      Delete
  18. how to export the dynamic table data to excel .
    because the dynamic table doesnt supports the export collection action listner.
    pls help me how to do this

    ReplyDelete
    Replies
    1. Did you find any solution for exporting the dynamic table data to excel. Even I have that requirement. Trying to a find a solution for it.

      Delete
  19. Hi,

    Great Post. Really helpful.
    Is there any way to change the width of the columns in xls on the adf side..??

    Regards,
    Raj

    ReplyDelete
    Replies
    1. Hi,

      Not in my knowledge. The option is pretty limited. You can use Apache POI if you need more control

      Zeeshan

      Delete
  20. Hi Zeeshan,

    I am using JDeveloper Version 11.1.2.3.0,

    In my application, I have added to button on ADF Table for Edit and Delete purpose. so when I am exporting data by above process i am also getting Edit and Delete button also. Now my question is When I am exporting table data these 2 button did not come on excel sheet.

    Thanks,
    Kashif

    ReplyDelete
    Replies
    1. Export collection only works on table's data not the other components like buttons or checkboxes

      Zeeshan

      Delete