Thu. Jun 17th, 2021

Requirement: I have few data in the XLS file and also few data in the XLSX file. Now, I want to upload the Data from these XLS file and the XLSX file into the Employees table.

The Data in the XLS file and the data in the XLSX file are shown below:

XLS File:

XLSX File:

Solution: For solution of the above requirement follow the steps as shown below:

Step 1: For this requirement, we will be using the Apache POI jar files.

Apache POI provides HSFF and XSFF to read, create and modify data from the XLS or XLSX files.

Please download POI jars by clicking on this link The APACHE Software Foundation

Step 2: Create an Oracle ADF Fusion Web Application.

Create a Business Components from Tables for the Employees table present in the HR Schema of the Oracle Database 11g XE Edition.

Add the Apache POI jars in the ViewController project of our application.

Step 3: Create a fileUploadDemo.jspx page. Drag and drop af:inputFile. Set label=”Browse File” and valueChangeListener=”#{pageFlowScope.MyBean.uploadFileVCE}”

Drag and drop EmployeesVO1 as an af:table

Thus, the complete code for the fileUploadDemo.jspx page is shown below:

<?xml version='1.0' encoding='UTF-8'?>
<jsp:root xmlns:jsp="http://java.sun.com/JSP/Page" version="2.1" xmlns:f="http://java.sun.com/jsf/core"
          xmlns:af="http://xmlns.oracle.com/adf/faces/rich">
    <jsp:directive.page contentType="text/html;charset=UTF-8"/>
    <f:view>
        <af:document title="fileUploadDemo" id="d1">
            <af:messages id="m1"/>
            <af:form id="f1" usesUpload="true">
                <af:panelFormLayout id="pfl1">
                    <f:facet name="footer">
                        <af:table value="#{bindings.EmployeesVO1.collectionModel}" var="row"
                                  rows="#{bindings.EmployeesVO1.rangeSize}"
                                  emptyText="#{bindings.EmployeesVO1.viewable ? 'No data to display.' : 'Access Denied.'}"
                                  rowBandingInterval="0"
                                  selectedRowKeys="#{bindings.EmployeesVO1.collectionModel.selectedRow}"
                                  selectionListener="#{bindings.EmployeesVO1.collectionModel.makeCurrent}"
                                  rowSelection="single" fetchSize="#{bindings.EmployeesVO1.rangeSize}"
                                  filterModel="#{bindings.EmployeesVO1Query.queryDescriptor}" filterVisible="true"
                                  queryListener="#{bindings.EmployeesVO1Query.processQuery}" varStatus="vs" id="t1"
                                  binding="#{pageFlowScope.MyBean.employeeTableBinding}">
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.EmployeeId.name}" filterable="true"
                                       sortable="true" headerText="#{bindings.EmployeesVO1.hints.EmployeeId.label}"
                                       id="c1">
                                <af:outputText value="#{row.EmployeeId}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.EmployeeId.tooltip}" id="ot1">
                                    <af:convertNumber groupingUsed="false"
                                                      pattern="#{bindings.EmployeesVO1.hints.EmployeeId.format}"/>
                                </af:outputText>
                            </af:column>
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.FirstName.name}" filterable="true"
                                       sortable="true" headerText="#{bindings.EmployeesVO1.hints.FirstName.label}"
                                       id="c2">
                                <af:outputText value="#{row.FirstName}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.FirstName.tooltip}" id="ot2"/>
                            </af:column>
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.LastName.name}" filterable="true"
                                       sortable="true" headerText="#{bindings.EmployeesVO1.hints.LastName.label}"
                                       id="c3">
                                <af:outputText value="#{row.LastName}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.LastName.tooltip}" id="ot3"/>
                            </af:column>
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.Email.name}" filterable="true"
                                       sortable="true" headerText="#{bindings.EmployeesVO1.hints.Email.label}" id="c4">
                                <af:outputText value="#{row.Email}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.Email.tooltip}" id="ot4"/>
                            </af:column>
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.PhoneNumber.name}" filterable="true"
                                       sortable="true" headerText="#{bindings.EmployeesVO1.hints.PhoneNumber.label}"
                                       id="c5">
                                <af:outputText value="#{row.PhoneNumber}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.PhoneNumber.tooltip}" id="ot5"/>
                            </af:column>
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.HireDate.name}" filterable="true"
                                       sortable="true" headerText="#{bindings.EmployeesVO1.hints.HireDate.label}"
                                       id="c6">
                                <f:facet name="filter">
                                    <af:inputDate value="#{bindings.EmployeesVO1Query.queryDescriptor.filterConjunctionCriterion.criterionMap.HireDate.value}"
                                                  id="id1">
                                        <af:convertDateTime pattern="#{bindings.EmployeesVO1.hints.HireDate.format}"/>
                                    </af:inputDate>
                                </f:facet>
                                <af:outputText value="#{row.HireDate}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.HireDate.tooltip}" id="ot6">
                                    <af:convertDateTime pattern="#{bindings.EmployeesVO1.hints.HireDate.format}"/>
                                </af:outputText>
                            </af:column>
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.JobId.name}" filterable="true"
                                       sortable="true" headerText="#{bindings.EmployeesVO1.hints.JobId.label}" id="c7">
                                <af:outputText value="#{row.JobId}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.JobId.tooltip}" id="ot7"/>
                            </af:column>
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.Salary.name}" filterable="true"
                                       sortable="true" headerText="#{bindings.EmployeesVO1.hints.Salary.label}" id="c8">
                                <af:outputText value="#{row.Salary}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.Salary.tooltip}" id="ot8">
                                    <af:convertNumber groupingUsed="false"
                                                      pattern="#{bindings.EmployeesVO1.hints.Salary.format}"/>
                                </af:outputText>
                            </af:column>
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.CommissionPct.name}"
                                       filterable="true" sortable="true"
                                       headerText="#{bindings.EmployeesVO1.hints.CommissionPct.label}" id="c9">
                                <af:outputText value="#{row.CommissionPct}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.CommissionPct.tooltip}"
                                               id="ot9">
                                    <af:convertNumber groupingUsed="false"
                                                      pattern="#{bindings.EmployeesVO1.hints.CommissionPct.format}"/>
                                </af:outputText>
                            </af:column>
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.ManagerId.name}" filterable="true"
                                       sortable="true" headerText="#{bindings.EmployeesVO1.hints.ManagerId.label}"
                                       id="c10">
                                <af:outputText value="#{row.ManagerId}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.ManagerId.tooltip}" id="ot10">
                                    <af:convertNumber groupingUsed="false"
                                                      pattern="#{bindings.EmployeesVO1.hints.ManagerId.format}"/>
                                </af:outputText>
                            </af:column>
                            <af:column sortProperty="#{bindings.EmployeesVO1.hints.DepartmentId.name}" filterable="true"
                                       sortable="true" headerText="#{bindings.EmployeesVO1.hints.DepartmentId.label}"
                                       id="c11">
                                <af:outputText value="#{row.DepartmentId}"
                                               shortDesc="#{bindings.EmployeesVO1.hints.DepartmentId.tooltip}"
                                               id="ot11">
                                    <af:convertNumber groupingUsed="false"
                                                      pattern="#{bindings.EmployeesVO1.hints.DepartmentId.format}"/>
                                </af:outputText>
                            </af:column>
                        </af:table>
                    </f:facet>
                    <af:inputFile label="Browse File" id="if1"
                                  valueChangeListener="#{pageFlowScope.MyBean.uploadFileVCE}"/>
                </af:panelFormLayout>
            </af:form>
        </af:document>
    </f:view>
</jsp:root>

And the complete code for MyBean.java class is shown below:

package susantotech.com;

import java.io.IOException;
import java.io.InputStream;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import javax.faces.event.ValueChangeEvent;

import oracle.adf.model.BindingContext;
import oracle.adf.model.binding.DCIteratorBinding;
import oracle.adf.view.rich.component.rich.data.RichTable;
import oracle.adf.view.rich.context.AdfFacesContext;

import oracle.binding.BindingContainer;
import oracle.binding.OperationBinding;

import oracle.jbo.uicli.binding.JUCtrlHierBinding;

import org.apache.myfaces.trinidad.model.CollectionModel;
import org.apache.myfaces.trinidad.model.UploadedFile;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class MyBean {
    private RichTable employeeTableBinding;

    public MyBean() {
    }

    public void setEmployeeTableBinding(RichTable employeeTableBinding) {
        this.employeeTableBinding = employeeTableBinding;
    }

    public RichTable getEmployeeTableBinding() {
        return employeeTableBinding;
    }

    public BindingContainer getBindingsCont() {
        return BindingContext.getCurrent().getCurrentBindingsEntry();
    }

    public OperationBinding executeOperation(String operation) {
        OperationBinding createParam = getBindingsCont().getOperationBinding(operation);
        return createParam;

    }

    public void uploadFileVCE(ValueChangeEvent valueChangeEvent) {
        System.out.println("You are inside uploadFileVCE");
        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")) {
                readAndProcessXLSX(file.getInputStream());
            }
            //Check if file is XLS
            else if (file.getContentType().equalsIgnoreCase("application/vnd.ms-excel")) {
                if (file.getFilename()
                        .toUpperCase()
                        .endsWith(".XLS")) {
                    readAndProcessXLS(file.getInputStream());
                }
            } else {
                FacesMessage msg = new FacesMessage("File format not supported.Please try uploading XLS or XLSX file");
                msg.setSeverity(FacesMessage.SEVERITY_WARN);
                FacesContext.getCurrentInstance().addMessage(null, msg);
            }
            System.out.println("We will refresh Employees table now");
            AdfFacesContext.getCurrentInstance().addPartialTarget(employeeTableBinding);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void readAndProcessXLSX(InputStream xlsx) {
        System.out.println("You are inside readAndProcessXLSX");
        CollectionModel cModel = (CollectionModel) employeeTableBinding.getValue();
        JUCtrlHierBinding tableBinding = (JUCtrlHierBinding) cModel.getWrappedData();
        DCIteratorBinding iter = tableBinding.getDCIteratorBinding();
        XSSFWorkbook WorkBook = null;
        int sheetIndex = 0;
        try {
            WorkBook = new XSSFWorkbook(xlsx);
        } catch (IOException e) {
            e.printStackTrace();
        }
        XSSFSheet sheet = WorkBook.getSheetAt(sheetIndex);
        Integer skipRw = 1;
        Integer skipcnt = 1;
        //Iterate XLS 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++;
        }
    }

    private void readAndProcessXLS(InputStream xls) {
        System.out.println("You are inside readAndProcessXLS");
        CollectionModel cModel = (CollectionModel) employeeTableBinding.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) {
                                e.printStackTrace();
                            }
                            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++;
        }
        executeOperation("Execute").execute();
    }
}

Save all and run the application. Thus, the ran application is shown below:

Now, simply browse and upload the files.

Hence, the solution to the error.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 371 total views,  2 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

Leave a Reply

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