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="" version="2.1" xmlns:f=""
xmlns:af="">
<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
840 total views, 2 views today