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

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

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:

<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>

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’s it!

Share this:
Rama Rimmalapudi

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

More posts by Rama Rimmalapudi

9 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

  • Eduardo Alves says:

    Hi, I have an issue in ADF that is i’m doing an export file for xls but when the file is exported the number always come with the caracter “,” in the end of number but the number is not decimal can you help?

  • Rama Rimmalapudi says:

    Hi Eduardo,
    I am not sure why it is adding “,”. I would check the data and output text. Please share your code, if no issue with the data.

    Thanks,
    Rama

Leave a Reply

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