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
I hope this post would be helpful feel free to comment
Download my working example
Cheers,
Baig
Hi, it is possible to export only few columns? In your use case, I would want to export only the CountryId and the CountryName.
ReplyDeleteRegards, Pedro Medeiros.
Hi Pedro,
ReplyDeletewell i m not aware of such solution i will look into it most probably this kind of solution requires some coding.
Excellent post. I'm really blown away by the power of ADF in this latest version
ReplyDeleteI agree Jeremy ADF 11g is powerful
ReplyDeletetake a look at this for a more configurable solution
ReplyDeletehow about exporting dynamic table?
ReplyDeleteHI ebox,
ReplyDeletedid 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
I am not able to get dynamic table to work .. Did you try this?
DeleteHI baig,
ReplyDeletecan the same functionality be achieved for csv file.Please share.
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.
ReplyDeleteHi Idris,
ReplyDeleteAs far as i know you can do by using custom java code but not with default component.
ZB
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
ReplyDeleteIs this issue with excel? or I can handle it in my application?
Thanks in advance.
Yes basically excel's problem the solution is to concatenate ' (quote sign) before exporting the number it will display correctly in excel.
ReplyDeletee.g. 01234 will be exported like '01234
It will not impact the data in excel during copy paste.
ZB
Hi Baig,
ReplyDeleteThis af:exportCollectionActionListener component doesnt include column footer while exporting the data to excel. Do we have any alternatives for the same?
The af:exportCollectionActionListener will only export the Table data.
DeleteIf you provide more info then i can provide better answer
DeleteZB
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..?
ReplyDeleteWe 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?
ReplyDeleteHi,
DeleteWhich 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
We are facing an issue where the excel file is not getting all the data from the treetable. The treetable has some 30K rows.
ReplyDeleteI want a header inside the excel sheet. How can i achieve that?
ReplyDeletedid u find a solution for it? i need to achieve the same
Deletehow to export the dynamic table data to excel .
ReplyDeletebecause the dynamic table doesnt supports the export collection action listner.
pls help me how to do this
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.
DeleteHi,
ReplyDeleteGreat Post. Really helpful.
Is there any way to change the width of the columns in xls on the adf side..??
Regards,
Raj
Hi,
DeleteNot in my knowledge. The option is pretty limited. You can use Apache POI if you need more control
Zeeshan
Hi Zeeshan,
ReplyDeleteI 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
Export collection only works on table's data not the other components like buttons or checkboxes
DeleteZeeshan
In My application exporting working fine but its limited to only for 32K around and remaining data is lost in excel sheet. Is there any update for updating all the data like above 50K.
ReplyDeleteHi folks,
ReplyDeleteI am trying to achieve export to excel on adf table which has some 7k records.
It starts downloading and then it is stuck and no data is exported.
Is there any other parameter we need to set.
Hi,
DeleteIMO in modern application design you probably need to generate PDF or spreadsheet using data set instead of front end UI. This option is only to export few rows of data and it is very limited. If you want to export 7K rows consider using some reporting tool or Java APIs to do it. Hope it make sense.
Regards,
Zeeshan
hi,
ReplyDeletewith the help of filedownloadactionlistener i want to download pivot table as ADF
how to proceed ?
Hi,
DeleteCheck this post http://gopsatora.blogspot.com/2013/09/exporting-adf-pivot-table-to-excel.html
Zeeshan
This feature does not work on IE 11. i.e exporting to excel in CSV Format. I am using jdev version 11.1.1.9. Are there any workarounds.
ReplyDeleteSorry this blog post is about 7 years old and I no longer work as developer and with Oracle products. You can contact Oracle support site to find out the workaround.
DeleteThanks,
Z
Hi,
ReplyDeleteI would like to export my data limit to the page. Like if i have data for up to 3 pages . each page contains 25 rows. I want to export only my first page data. when i try to export, it actually exports total records than on that particular page. Is there a way to restrict and only export the page wise.
Hi Vanathi,
DeleteI no longer work with Oracle tools so not sure where they are now...plus I am afraid to say that ADF is not designed for reporting, you can certainly do it but it requires customization of component. If you are looking for custom needs then look for some other exclusive reporting tools. You can also use custom Java APIs to render data in excel.
Zeeshan