Export ViewObject data to Excel File Using Apache POI in Oracle ADF

Hello All

Previously I have posted about importing data in ADF Table from Excel file

This post is about exporting viewObject data in Excel file using Apache POI API, Apache POI provides HSFF and XSFF to read , create and modify spreadsheets.
You can download POI jars from The APACHE Software Foundation or from here
Other than this you need to use xmlbeans and common-collections Jar

Import data from XLS and XLSX (Excel) to ADF Table Using Apache POI

ADF provides feature to export data to excel using export collection listener with just one click but there is no built in feature to upload data from excel file to ADF table

For this requirement we have to use some other API and Apache POI is best for excel communication, In this post I am going to discuss about uploading excel (XLS and XLSX) file data to ADF table

Creating and Exporting hierarchical data to excel using dvt:pivotTable & dvt:exportPivotTableData in Oracle ADF

Hello All,
This post is about exporting tree structured(hierarchical ) data to an excel file.
suppose we have export department wise detail of employees , we can achieve this in ADF using pivot table (<dvt:pivotTable>)

What is pivot tabel (oracle docs)- 
 
UIComponent class: oracle.adf.view.faces.bi.component.pivotTable.UIPivotTable
Component type: oracle.dss.adf.pivotTable.PivotTable
The Pivot Table supports the display of multiple nested attributes on a row and column header. In addition, the Pivot Table supports the ability to dynamically change the layout of the attributes displayed in the row or column headers via drag and drop pivoting.
now see the implementation part -

• Create a Fusion Web Application using HRSchema , for this sample app i have created a view (Department Wise Employees) to export data



CREATE OR REPLACE FORCE EDITIONABLE VIEW "APP_USR"."DEPT_EMP" ("DEPARTMENT_NAME", "EMPLOYEE_NAME", "SALARY") AS 
  SELECT A.DEPARTMENT_NAME, B.FIRST_NAME,B.SALARY FROM DEPARTMENTS A, EMPLOYEES B WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID (+);



• Prepare model using this view





• Create a page and drop this view as pivot table (ADF Faces component) from data control





• Now configure pivot table (set row & column attributes, insert appropriate drilling) - see the snaps











• Now run your page and see working pivot table - here pivot table is created successfully 





• Drop a button  and a <dvt:exportPivotTableData> under button to export pivot table



<af:commandButton text="Export" id="b1">
                    <dvt:exportPivotTableData type="excelHTML" exportedId="pt1" filename="Dep_Empl.xls"
                                              title="PivotTable export"/>
                </af:commandButton>



• Run your page and click on export button and see your tree structured data in exported excel file

Cheers :) Happy Learning Sample ADF Application