Export Data from your OAF Pages

OAF gives you an Export Button feature that you can implement to exports data displayed in one or more regions of an OA Framework page to a file. Export saves the data to a .csv (comma separated values) file that can be viewed in Microsoft Excel. When you select the Export button, Microsoft Windows opens a dialog box that lets you view the file either by opening it directly in Microsoft Excel, or by saving it to a designated directory to open later.

Exporting Data From All Regions On A Page:

You can declaratively enable data export for all regions in a page by creating a page-level Export button. Here are the steps:

1] Select your pageLayout region and create a region with the style pageButtonBar beneath it.

2] Add an item of item style exportButton to the pageButtonBar region to automatically expose an Export button on the page.

When a user selects the Export button, OA Framework exports the data from all the rendered regions on that page to a .csv file.

Exporting Data From a Specific Region On A Page:

1] Select the region and create a new region item of item style exportButton.

2] Set the View Instance name for the exportButton region item to the same view object associated with the region for which you are enabling export. This ties the Export button to the correct data source.

Exporting Data From Master/Detail Regions:

If you have a page that contains both a Master region and a corresponding Detail region and you want to export all the data from these two regions regardless of which Master row is selected, you must do so programmatically. There is no declarative support for this.

Example:

1 2 3 4 5

OAExportBean expBean = (OAExportBean)webBean.findChildRecursive("Export"); // set the exportDetails for this Master/Detail region. expBean.setExportDetails(oaPageContext,"masterRegionName", "detailRegionName", "masterVuName", " detailVuName");

Note: This type of Master/Detail export is supported only when you implement a region-level export and not a page-level export (where all the regions on a page are exported).

Few Notes:

• Set the Export All Rows property on the exportButton item to True to export all rows from a table’s view object regardless of the view object’s setMaxFetchSize value. If the Export All Rows property is set to False, the total number of rows that can be exported from a table is defined by its view object’s setMaxFetchsize.

• Generally, when you export data from a page, OA Framework exports the data from the view object attribute associated with each item in each region. In some cases, when you do not want to export the data from the view attribute associated with an item, you can specify a different view attribute to export from, by setting the Export View Attribute property for that item. If you don’t set or set the Export View Attribute property to null, OA Framework exports data from the view attribute name associated with the item.

• ExportButton has few limitations to export data from some regions. Please refer the OAF Developer Guide for more details.

An alternate way:

The CSV file generated by the ExportButton is very raw and basic and often user may not like the format. If you want to design your own format for a better look and feel, you can go for this approach:

Step 1: Create a Data Template XML file.

Step 2: Create a Data Definition in Oracle XML Publisher Administrator Responsibility and attach the Data Template.

Step 3: Create the RTF Template. To design the RTF Template, you first need the data in xml file. You can quickly create a concurrent program to get the XML Data. Once you got the data, design your template.

Step 4: Create a Template in Oracle XML Publisher Administrator Responsibility and attach the RTF Template.

Step 5: Create a method in your AMIMPL to get the XML Data. Here P_PARAMETER1 and P_PARAMETER2 are the parameters to the data template.

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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

/*===========================================================================+   To Create the XML data for the data template. +===========================================================================*/ public  BlobDomain getXMLData(String p_parameter1,String p_parameter2, String dataDefCode, StringdataDefApp) {   if(isLoggingEnabled(OAFwkConstants.STATEMENT))     {         writeDiagnostics("xxtest.oracle.apps.qot.core.server","CLTierPlanAMImpl :: getXMLData - START.",OAFwkConstants.STATEMENT);     }    BlobDomain blobDomain = new BlobDomain();    OADBTransaction oadbtransaction = getOADBTransaction();    try     {      DataTemplate datatemplate = newDataTemplate(((OADBTransactionImpl)getOADBTransaction()).getAppsContext(), dataDefApp,dataDefCode );      Hashtable param = new Hashtable();      param.put("P_PARAMETER1", p_parameter1);      param.put("P_PARAMETER2", p_parameter2);      datatemplate.setParameters(param);      datatemplate.setOutput(blobDomain.getBinaryOutputStream());      datatemplate.processData();     }    catch(SQLException e)     {     if(isLoggingEnabled(OAFwkConstants.STATEMENT))         {             writeDiagnostics("xxtest.oracle.apps.qot.core.server","CLTierPlanAMImpl:: getXMLData : SQL Error: "+e.getMessage(),OAFwkConstants.STATEMENT);         }      throw new OAException("SQL Error=" + e.getMessage(),OAException.ERROR);     }    catch(Exception e)     {     if(isLoggingEnabled(OAFwkConstants.STATEMENT))         {             writeDiagnostics("xxtest.oracle.apps.qot.core.server","CLTierPlanAMImpl:: getXMLData : Error: "+e.getMessage(),OAFwkConstants.STATEMENT);         }       StackTraceElement[] stck = e.getStackTrace();       StringBuffer buff = new StringBuffer();       for(int i = 0; i < stck.length; i++)         {           buff.append(stck[i].toString());         }       throw new OAException("Exception" + buff.toString(),OAException.ERROR);      }         if(isLoggingEnabled(OAFwkConstants.STATEMENT))          {           writeDiagnostics("xxtest.oracle.apps.qot.core.server","CLTierPlanAMImpl :: getXMLData - END.",OAFwkConstants.STATEMENT);          } return blobDomain; }

Step 6:  Create a SubmitButton on the page (anywhere you want)

Step 7: In the controller, write code as below:

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 31 32 33 34 35 36 37 38 39

/*===========================================================================+   ExportButton +===========================================================================*/     if (pageContext.getParameter("ExportBtn") != null)     {     String dataDefAppShortName = "QOT";     String dataDefCode = "QUOTE_IND_TIER_PRICING" ;     String TemplateApplicationShortName = "QOT";     String TemplateCode = "QUOTE_INDIVIDUAL_TIER_PRICING_TEMPLATE" ;     Serializable[] param = { p_parameter1, p_parameter2,dataDefCode,dataDefAppShortName};     BlobDomain result = (BlobDomain) am.invokeMethod("getXMLData",param);       if  (result.getLength()==0)               {         OAException message = new OAException("No Data to view",OAException.ERROR);         pageContext.putDialogMessage(message);                     }             else        try {         Properties emptyProps = new Properties();         System.out.println("in CO result length=>"+result.getLength());                 DocumentHelper.exportDocument( pageContext,                                        TemplateApplicationShortName,                                        TemplateCode,                                        "en" , //Language                                        "US" , //Territory                                        result.getInputStream(),                                        "Excel", //OutputType,                                        emptyProps);          }     catch (Exception e)          {           if(pageContext.isLoggingEnabled(OAFwkConstants.STATEMENT))           {           pageContext.writeDiagnostics("xxtest.oracle.apps.qot.core.webui","CLTierPriceHdrLinesCO.processFormRequest:In ExportBtn for Individual Tiers: Error"+e.getMessage(),OAFwkConstants.STATEMENT);           }            throw new OAException("Exception" + e.getMessage(),OAException.ERROR);          }       }

The data will be exported as below. By using XML Publisher, you can export the data to any format like Excel, PDF, and Word etc.