Formating Columns in Excel created by af:exportCollectionActionListener

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:


Update:  7th-June-2010
As ever people are resourceful when it comes to this kind of issue.  Don Kleppinger of SquareTwo Financial alerted me to the new approach he was taking.  In his case he still uses the wrapping approach of using the Excel formatting in af:outputText components around the main content, but rather than using visible="false"  to hide it at runtime in the browser, he has reverted to CSS to do the job for him.
   <af:outputtext value="=TEXT(" inlineStyle="display:none">
Nice one Don! Great tip.
Comments:

Post a Comment:
Comments are closed for this entry.
About

Hawaii, Yes! Duncan has been around Oracle technology way too long but occasionally has interesting things to say. He works in the Development Tools Division at Oracle, but you guessed that right? In his spare time he contributes to the Hudson CI Server Project at Eclipse
Follow DuncanMills on Twitter

Note that comments on this blog are moderated so (1) There may be a delay before it gets published (2) I reserve the right to ignore silly questions and comment spam is not tolerated - it gets deleted so don't even bother, we all have better things to do with our lives.
However, don't be put off, I want to hear what you have to say!

Search

Archives
« April 2014
MonTueWedThuFriSatSun
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
    
       
Today