Friday, June 4, 2010

Preventing long numbers from displaying in exponential notation when exporting to excel

The af:exportCollectionActionListener behavior in ADF Faces Rich client provides a simple way of exporting table content to excel spreadsheets. There is one issue with that though in that long numbers will display in exponential notation. This is the fault of Excel not the export. Opening the spreadsheet in "Open Office" doesn't exhibit that behavior. There was a work-around for this problem as described in a blog by Duncan Mills but that work-around no longer works in the PS2 release of Jdeveloper, however I was able to modify that solution and make it work. The work around was to put hidden text around the data in the column that put a =TEXT(...) or =TRIM(...) tag around the long data value. This tag would get exported and inform excel that the value was to be displayed as text. This didn't work in PS2 because elements with the visible='false' attribute no longer get exported. My work-around was to set the element to be visible but add a inline style to the text component of "display:none". Now it gets exported to excel but doesn't show up in the UI. Problem solved. 

example


<af:panelGroupLayout layout="horizontal" >
<af:outputText value="=TRIM(" visible="true" inlineStyle="display:none;"/>
<af:outputText value="121212121212121212121" />
<af:outputText value=")" visible="true" inlineStyle="display:none;"/>
</af:panelGroupLayout>

3 comments:

  1. Hi Dkleppinger,
    Your workaround helped me a lot.

    Thanks much.

    Regards,
    Udaya

    ReplyDelete
  2. Hi Don,

    I am using af:exportCollectionActionListener for exporting af:table rows to Excel. I tried your solution for showing '0' by using '=TEXT(...)' but we are not able to show it.

    Working on Jdev 11.1.1.6.
    Can you please guide me.


    -Debojyoti

    ReplyDelete