Monday, May 16, 2016

ADF Hidden Gem - Export Collection Listener

How many times you complained about ADF export collection listener generated output? There are two options for the output - CSV and excelHTML. Both of them are not really Excel friendly (Excel complains, each time when such file is opened) and produced output lacks formatting. Luckily there is a way to specify custom formatter for ADF export collection listener and set your own output type. In this way we can produce better and customized output file for Excel, you can construct Excel document with different formatting and layout.

Demo application with custom formatter for ADF export collection listener is available on GitHub repository - rs-export-xls. This was implemented by Red Samurai Consulting colleague - Fedor Zymarev. You are free to check-out the source code - use it in your projects and add new formatting features.  It would be great, if you could contribute to the community and commit any improvements - simply request merge approval into master on GitHub repository.

Demo runs with the entry page, which contains ADF UI table with Export all/selected rows options:


This is how Excel output looks like. There is a placeholder for worksheet title, header is highlighted and format is recognized by Excel:


You only need to set custom type (RSExcelExport) for ADF export collection listener, instead selecting excelHTML available by default:


Custom type is not available in the list of options, you should type it:


Format type is defined with Java class registered in oracle.adf.view.rich.export.FormatHandler text file, located in ADF META-INF/services folder:


Formatting implementation logic take place in RSExcelFormatHandle class. We are using Apache POI, Java API for Microsoft documents, to prepare native format for Excel:

9 comments:

Sébastien said...

Perfect, we'll use it soon!

Anonymous said...

Thank you, worked like a charm!

Also, we have noticed that the regular excelHTML option in 12.2.1.1 can not even be opened at all by Excel 2010, while this one can. It also avoids a bug, because normally when using the tag to do custom headers (instead of the normal header on the ), the excel export breaks, yet with this renderer it all works out okay.

Andrej Baranovskij said...

Perfect, good news !

Anonymous said...

Hi Andrejus, Is there a way we can export DVT components to PDF.
For example the hierarchyViewer DVT component at runtime is shown as flash content (.swf) and I am looking for a way to export it to PDF.

Thanks.

Benjamin said...

Hello Andrejus, in multi-user environment does it works ?
Because handler class is instantiated once at first used, so if several users launch export at same time, according to the implementation of the handler class (global variables) there will be a problem.
Do I miss something ?
Thanks

Andrej Baranovskij said...

Hi,

The RSExcelFormatHandler contains state (writer, workbook, sheet, current row, ..) so just override the clone method and return new instance of RSExcelFormatHandler.


The ExcelFormatHandler class is the default one of ADF, does the same (overrides clone method and returns new instance of ExcelFormatHandler).

Regards,
Andrejus

Benjamin said...

Hi Andrejus, many thanks for your advice. It's perfect !
Regards
Benjamin

Leo Blom said...

Works like a charm. Perfect solution for me

José Mendes said...

Hi Andrejus,

The downloaded file name is not correct when using Internet Explorer, for that reason the "open" option won't appear. Any idea how to fix this?