We can export data to excel very easily using af:exportCollectionActionListener tag from ADF table as below.

[feature_box style=”1″ only_advanced=”There%20are%20no%20title%20options%20for%20the%20choosen%20style” alignment=”center”]

<af:commandButton text=”Export to Excel” id=”cbExport”>
<af:exportCollectionActionListener exportedId=”tOrders” type=”excelHTML” filename=”orders.xls”/>
</af:commandButton>

[/feature_box]

 

But the issue is with big numbers. When you open the excel file, you will see big numbers in scientific format as below.

excel-s

 

By tweaking/changing output for exported excel alone, we can instruct excel not to display big numbers in scientific format. We don’t want to display the extra formats on the adf screen, we can achieve this by adding display:none for extra formats as below:

[feature_box style=”1″ only_advanced=”There%20are%20no%20title%20options%20for%20the%20choosen%20style” alignment=”center”]

<af:column headerText=”#{bindings.OrdersVO.hints.OrderNumber.label}” id=”c18″>
<af:outputText value=’=” ‘ inlineStyle=”display:none” id=”ot13″/>
<af:outputText value=”#{row.OrderNumber}” id=”ot38″/>
<af:outputText value='”‘ inlineStyle=”display:none” id=”ot22″/>
</af:column>

[/feature_box]

 

Now the order number will display complete number (will not format as scientific) when you open the excel. On the Adf table, you will not see extra format as we  set display to none.

excel

That it!

Rama Rimmalapudi

I have been working as a Integration Architect for the past 10 years, with over 17 years of experience in software Architecture, Design, Development, Performance Tuning and production support of Java/J2EE based applications. I have 8+ years of experience designing enterprise applications using Oracle Fusion Middleware, building custom applications using ADF and System Integrations using SOA Suite & MuleSoft. I have extensive knowledge with DevOps, Release Automation, Application Integrations, WebLogic and Coherence in high volume and highly available deployment configurations.

More posts by Rama Rimmalapudi

7 Comments

  • Priyanka says:

    After using this feature I am not able to sum the values in column.

    • Yes, by default sum will not work as it is text value. But you should be able to use functions like vlaue() in excel to convert the value from text to number. For example =VALUE(B1)+VALUE(B2)+VALUE(B3) on your sum cell. In fact, if you have value of for first one and you can keep adding from there onward like =VALUE(B1)+B2+B3. I am sure there will be other functions also.

      Hope this helps.

      Thanks,
      Rama

  • srinivasa says:

    Hi i have <af:exportCollectionActionListener component in 12c but i am not able to get export data in excel it is showing error "Export is incomplete due to error" here i removed facet header="name" then it is working fine.but here i have to use acet header="name" inside select check box ihave .how to solve this problem any idea?

  • Brian H. says:

    Hi I was wondering if you can help as it is a little spin off of your topic.

    Currently I export to excel with the following: JDev 11.1.2.4 and using the export type excelHTML with a file name ending in .xsl

    The new issue is how to export to the new excel 2016 format of .xlsx? Do I simply change the file name? or is there a different Type declaration needed and additional libraries?

    Any help will be prized.

    • Rama Rimmalapudi says:

      Hi Brian,

      I think ADF does not export in that format. You will need to write special code for that. ADF exports very basic XHTML format. You may need to use something like Apache POI.

      Thanks,
      Rama

  • hari says:

    Hi,
    I my table I have shortened the names in some columns. (eg: nameeee….). SO when I export the table the full name is not coming. How can I export the full name during exporting?

    • Rama says:

      Hi Hari,

      It may be exporting full name, but not showing in Excel. Can your try expanding excel cell and see if you see full name?

      Thanks,
      Rama

Leave a Reply

Your email address will not be published. Required fields are marked *