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

Sharing is Caring

ADF provides a 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 that how can we import data from 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-

Import data from XLS

XLSX File

Import data from XLSX

Now run and check application

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

Related Posts

I’m an Oracle ACE, Blogger, Reviewer,
Technical Lead working on Oracle ADF

25 thoughts on “Import data from XLS and XLSX (Excel) to ADF Table Using Apache POI”

  1. its failing here Integer skipRw = 1;
    Integer skipcnt = 1;

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

    if (skipcnt > skipRw)

    as both skipcnt and skipRw are equal to 1

    if i change this to equal to also then it is failing in the next step as there is no createinsert bindings.

  2. Have you really checked sample application ?
    if (skipcnt > skipRw) this condition is used to skip first row (if there is a header) and you can see in code that skipcnt is increasing inside loop so for second row it'll execute the code

    And there is createInsert binding in pagedef , Check again

  3. my bad.. once it was skipping the first row i was having some number to date conversion error in the code … then it was not going to the skipcnt increase.. so repeatedly it was not executing the code due to skipcnt being 1.. and i have added the createinsert and execute bindings … Now this is working fine.

    Thanks,
    Vinay.

  4. When I run the page it is giving target unreachable UploadExcelBean.java … If i remove this from viewscope and give none.. the code is working fine.

    Do i need to do anything else in any file for this bean to be in viewscope.. somehow its not working for me if it is in viewscope.

  5. here in the code it is like this

    if (MytempCell != null) {
    Index = MytempCell.getColumnIndex();
    } else {
    Index++;
    }

    If the excel sheet has null values like for example take 5 rows of 4 columns each in this 20 cells if i have around 3 to 4 null values here and there… then the data that is loaded in table does not match for each row

  6. Hi,

    The example which you have shown with 2 rows of upload .. try again with 5 rows with here and there some null values in the different columns. Then the data which is uploaded does not match with that of excel sheet. some rows are missing some where null is coming in place of a value.

    Thanks,

  7. Hi,

    if (MytempCell != null) {
    Index = MytempCell.getColumnIndex();
    } else {
    Index++;
    }

    you are increasing the index in the else statement if Mytempcell is null but what about if the next cell is also null then when you are setting the attribute it is erroring out with null pointer error.. this i am checking before setting attribute like this..

    } else if (Index == 2 && MytempCell != null) {

    row.setAttribute("Salary", MytempCell.getNumericCellValue());

    }

    So the above thing is fine…

    Now there is one more problem which is while taking the new row .. if there are 4 columns and one of them is null then the code column < tempRow.getPhysicalNumberOfCells(); returns only 3 mappings it is ignoring the null column value… because of which the last column setting of value is ignored and set to null.

    please check with null values in the excel sheet..

    As for me i know that there will be only 4 columns in my excel sheet as of now i have hardcoded to column < 4 .. its working fine.. but this i also need to replace

  8. Hi Ashish,

    Thanks for that.

    Now after I did the changes I mentioned it was working fine.
    Next day I try to load an other excel it fails .. same old problem of values not matching.

    This occured for me because if you take an excel with complete values in all cells it works fine, if you create an excel with here and there some null values it works fine.. but if you create an excel with values and save it then remove values for date column here and there, then in the code (MytempCell != null) fails it does not recognise the null value and while setting date attribute it is giving null pointer error. this happens only for date column not for string or numeric.

    I dont know why this happened in my case… but we solved it by taking it as string value instead of date value like this

    else if (Index == 2 && !String.valueOf(MytempCell).isEmpty()){
    java.util.Date date = MytempCell.getDateCellValue();

    This case might rarely happen if user modifies the date column of excel before loading.

    Thanks,
    Vinay.

  9. Nice tutorial just one thing, we don't really 2 method for XLS and XLS just use the below mentioned code and one method will be enough.

    Workbook workbook = null;
    int sheetIndex = 0;
    try {
    if("XLS".equalsIgnoreCase(type)){
    workbook = new HSSFWorkbook(xls);
    }else{
    workbook = new XSSFWorkbook(xls);
    }

    } catch (IOException e) {
    e.printStackTrace();
    }
    Sheet sheet = workbook.getSheetAt(sheetIndex);

  10. Hi Ashish,
    Thanks for the example. However if I want to handle db constraint errors? For example I have a unique attribute in my table and there is repeated value for this attribute in the excel. What do you suggest to handle this ?

  11. i have issue while uploading data i have two excel files . My first file load in just few seconds but when i upload second file it take too much time almost 5 to 10 minutes.both files contain almost 2000 rows. how i can change the performance of file loading.

  12. Is it okay to call HSSFWorkbook class in Model layer ?
    I have dyanamic queries with huge result sets. The data from these i want to write to an xls.

    Can i invoke these in Model layer and write there itself in HSSFWorkbook

    Or do we need to keep HSSFWorkbook in View controller layer only,

    THanks!

  13. I got this null point exeption. How can i solve this issue kindly reply As soon as possible

    Caused By: java.lang.NullPointerException
    at view.detail.readNProcessExcel(detail.java:117)
    at view.detail.UFVCE(detail.java:55)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at com.sun.el.parser.AstValue.invoke(AstValue.java:254)
    at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:302)
    at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
    at javax.faces.event.MethodExpressionValueChangeListener.processValueChange(MethodExpressionValueChangeListener.java:144)
    at javax.faces.event.ValueChangeEvent.processListener(ValueChangeEvent.java:134)
    at org.apache.myfaces.trinidad.component.UIXComponentBase.broadcast(UIXComponentBase.java:986)
    at org.apache.myfaces.trinidad.component.UIXEditableValue.broadcast(UIXEditableValue.java:243)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:759)
    at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._executePhase(LifecycleImpl.java:444)
    at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:225)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:593)
    at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:280)
    at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:254)
    at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:136)
    at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:341)
    at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:25)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:79)
    at oracle.adf.model.servlet.ADFBindingFilter.doFilter(ADFBindingFilter.java:192)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:79)

Leave a Reply

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