Formating Columns in Excel created by af:exportCollectionActionListener
- by Duncan Mills
The af:exportCollectionActionListener behavior in ADF Faces Rich client provides a very simple way of quickly dumping out the contents or selected rows in a table or treeTable to Excel. However, that simplicity comes at a price as it pretty much left up to Excel how to format the data. A common use case where you have a problem is that of ID columns which are often long numerics. You probably want to represent this data as a string, Excel however will probably have other ideas and render it as an exponent - not what you intended. In earlier releases of the framework you could sort of work around this by taking advantage of a bug which would allow you to surround the outputText in question with invisible outputText components which provided formatting hints to Excel. Something like this:
<af:column headertext="Some wide label"> <af:panelgrouplayout layout="horizontal">
<af:outputtext value="=TEXT(" visible="false">
<af:outputtext value="#{row.bigNumberValue}" rendered="true"/>
<af:outputtext value=",0)" visible="false">
</af:panelgrouplayout>
</af:column>
However, this bug was fixed and so it can no longer be used as a trick, the export now ignores invisible columns.
So, if you really need control over the formatting there are several alternatives: First the more powerful ADF Desktop Integration (ADFdi) package which allows you to build fully transactional spreadsheets that "pull" the data and can update it. This gives you all the control that might need on formatting but it does need specific Excel Add-ins on the client to work. For more information about ADFdi have a look at this tutorial on OTN. Or you can of course look at BI Publisher or Apache POI if you're happy with output only spreadsheets
