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



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
Next step is to create a Fusion Web Application, prepared model using Employees table of HR Schema

Create a page and drop an af:inputFile component and employees viewObject as table on page, create value change listener (to read and upload data to table from excel file) for inputFile in managed bean

<af:inputFile label="Select File" id="if1"
valueChangeListener="#{viewScope.UploadExcelBean.uploadFileVCE}"
autoSubmit="true" 
labelStyle="font-weight:bold;color:navy;"/>

Create component binding of Employees table in managed bean and use these java methods

    //Component Binding of af:table
    private RichTable empTable;

    public void setEmpTable(RichTable empTable) {
        this.empTable = empTable;
    }

    public RichTable getEmpTable() {
        return empTable;
    }

ValueChangeListener for af:inputFile-

    /**Method to upload XLS/XLSX file and read data from table
     * @param valueChangeEvent
     */
    public void uploadFileVCE(ValueChangeEvent valueChangeEvent) {
        UploadedFile file = (UploadedFile) valueChangeEvent.getNewValue();

        try {
            //Check if file is XLSX
            if (file.getContentType().equalsIgnoreCase("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") ||
                file.getContentType().equalsIgnoreCase("application/xlsx")) {

                readNProcessExcelx(file.getInputStream()); //for xlsx

            }
            //Check if file is XLS
            else if (file.getContentType().equalsIgnoreCase("application/vnd.ms-excel")) {

                if (file.getFilename().toUpperCase().endsWith(".XLS")) {
                    readNProcessExcel(file.getInputStream()); //for xls
                }

            } else {
                FacesMessage msg = new FacesMessage("File format not supported.-- Upload XLS or XLSX file");
                msg.setSeverity(FacesMessage.SEVERITY_WARN);
                FacesContext.getCurrentInstance().addMessage(null, msg);
            }
            AdfFacesContext.getCurrentInstance().addPartialTarget(empTable);

        } catch (IOException e) {
            // TODO
        }
    }

Helper Method to execute OperationBinding-

    /**Method to get Binding Container of current view port
     * @return
     */
    public BindingContainer getBindingsCont() {
        return BindingContext.getCurrent().getCurrentBindingsEntry();
    }

    /**
     * Generic Method to execute operation
     * */
    public OperationBinding executeOperation(String operation) {
        OperationBinding createParam = getBindingsCont().getOperationBinding(operation);
        return createParam;

    }

Method to read and upload Excel(.XLS) file-

    /**Method to read xls file and upload to table.
     * @param xls
     * @throws IOException
     */
    public void readNProcessExcel(InputStream xls) throws IOException {

        CollectionModel cModel = (CollectionModel) empTable.getValue();
        JUCtrlHierBinding tableBinding = (JUCtrlHierBinding) cModel.getWrappedData();
        DCIteratorBinding iter = tableBinding.getDCIteratorBinding();

        //Use HSSFWorkbook for XLS file
        HSSFWorkbook WorkBook = null;

        int sheetIndex = 0;

        try {
            WorkBook = new HSSFWorkbook(xls);
        } catch (IOException e) {
            System.out.println("Exception : " + e);
        }

        HSSFSheet sheet = WorkBook.getSheetAt(sheetIndex);

        Integer skipRw = 1;
        Integer skipcnt = 1;
        Integer sno = 1;

        //Iterate over excel rows
        for (Row tempRow : sheet) {
            System.out.println(skipcnt + "--" + skipRw);
            if (skipcnt > skipRw) { //skip first n row for labels.
                //Create new row in table
                executeOperation("CreateInsert").execute();
                //Get current row from iterator
                oracle.jbo.Row row = iter.getNavigatableRowIterator().getCurrentRow();

                int Index = 0;
                //Iterate over row's columns
                for (int column = 0; column < tempRow.getLastCellNum(); column++) {
                    Cell MytempCell = tempRow.getCell(column);
                    if (MytempCell != null) {
                        Index = MytempCell.getColumnIndex();
                    
                    if (Index == 0) {
                        row.setAttribute("EmployeeId", MytempCell.getNumericCellValue());

                    } else if (Index == 1) {
                        row.setAttribute("FirstName", MytempCell.getStringCellValue());

                    } else if (Index == 2) {
                        row.setAttribute("LastName", MytempCell.getStringCellValue());

                    } else if (Index == 3) {
                        row.setAttribute("Email", MytempCell.getStringCellValue());

                    } else if (Index == 4) {
                        row.setAttribute("PhoneNumber", MytempCell.getNumericCellValue());

                    } else if (Index == 5) {
                        java.util.Date date = MytempCell.getDateCellValue();
                        DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
                        String date1 = dateFormat.format(date);

                        try {
                            date = dateFormat.parse(date1);
                        } catch (ParseException e) {
                        }
                        java.sql.Date sqlDate = new java.sql.Date(date.getTime());
                        oracle.jbo.domain.Date jboDate = new oracle.jbo.domain.Date(sqlDate);
                        row.setAttribute("HireDate", jboDate);

                    } else if (Index == 6) {
                        row.setAttribute("JobId", MytempCell.getStringCellValue());

                    } else if (Index == 7) {
                        row.setAttribute("Salary", MytempCell.getNumericCellValue());

                    } else if (Index == 8) {
                        row.setAttribute("CommissionPct", MytempCell.getNumericCellValue());

                    } else if (Index == 9) {
                        row.setAttribute("ManagerId", MytempCell.getNumericCellValue());

                    } else if (Index == 10) {
                        row.setAttribute("DepartmentId", MytempCell.getNumericCellValue());

                    }

                      } else {
                        Index++;
                     }
}
                sno++;
            }
            skipcnt++;
        }
        //Execute table viewObject
        executeOperation("Execute").execute();
    }

Method to read and upload data from Excel (.XLSX) file-

    /**
     * Method to read xlsx file and upload to table.
     * @param myxls
     * @throws IOException
     */
    public void readNProcessExcelx(InputStream xlsx) throws IOException {

        CollectionModel cModel = (CollectionModel) empTable.getValue();

        JUCtrlHierBinding tableBinding = (JUCtrlHierBinding) cModel.getWrappedData();
        //Acess the ADF iterator binding that is used with ADF table binding
        DCIteratorBinding iter = tableBinding.getDCIteratorBinding();

        //Use XSSFWorkbook for XLS file
        XSSFWorkbook WorkBook = null;
        int sheetIndex = 0;

        try {
            WorkBook = new XSSFWorkbook(xlsx);
        } catch (IOException e) {

        }
        XSSFSheet sheet = WorkBook.getSheetAt(sheetIndex);

        Integer skipRw = 1;
        Integer skipcnt = 1;

        //Iterate over excel rows
        for (Row tempRow : sheet) {

            if (skipcnt > skipRw) { //skip first n row for labels.
                //Create new row in table
                executeOperation("CreateInsert").execute();
                //Get current row from iterator
                oracle.jbo.Row row = iter.getNavigatableRowIterator().getCurrentRow();
                int Index = 0;
                //Iterate over row's columns
                for (int column = 0; column < tempRow.getLastCellNum(); column++) {

                    Cell MytempCell = tempRow.getCell(column);
                    if (MytempCell != null) {
                        Index = MytempCell.getColumnIndex();
                    
                    if (Index == 0) {
                        row.setAttribute("EmployeeId", MytempCell.getNumericCellValue());

                    } else if (Index == 1) {
                        row.setAttribute("FirstName", MytempCell.getStringCellValue());

                    } else if (Index == 2) {
                        row.setAttribute("LastName", MytempCell.getStringCellValue());

                    } else if (Index == 3) {
                        row.setAttribute("Email", MytempCell.getStringCellValue());

                    } else if (Index == 4) {
                        row.setAttribute("PhoneNumber", MytempCell.getNumericCellValue());

                    } else if (Index == 5) {
                        java.util.Date date = MytempCell.getDateCellValue();
                        DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
                        String date1 = dateFormat.format(date);

                        try {
                            date = dateFormat.parse(date1);
                        } catch (ParseException e) {
                        }
                        java.sql.Date sqlDate = new java.sql.Date(date.getTime());
                        oracle.jbo.domain.Date jboDate = new oracle.jbo.domain.Date(sqlDate);
                        row.setAttribute("HireDate", jboDate);

                    } else if (Index == 6) {
                        row.setAttribute("JobId", MytempCell.getStringCellValue());

                    } else if (Index == 7) {
                        row.setAttribute("Salary", MytempCell.getNumericCellValue());

                    } else if (Index == 8) {
                        row.setAttribute("CommissionPct", MytempCell.getNumericCellValue());

                    } else if (Index == 9) {
                        row.setAttribute("ManagerId", MytempCell.getNumericCellValue());

                    } else if (Index == 10) {
                        row.setAttribute("DepartmentId", MytempCell.getNumericCellValue());

                    }

                    } else {
                        Index++;
                    }
}
            }
            skipcnt++;
        }
    }

Created two Excel files with Sample data

XLS File-

 XLSX File

Now run and check application

All Done :)
Sample ADF Application (Jdeveloper 12.1.3)- Download
Cheers :) Happy Learning