Loading Data from Excel sheet to Database table using ADF

In this blog entry, I try to explain one of the ways to load data from Excel sheet to a database table using ADF application. We stress more on what needs to be done in the manged bean.

For this to explain, I am taking a hypothetical scenario where we have a table with following columns

EMPLOYEE_NAME VARCHAR2(100) NOT NULL
SALARY        NUMBER NOT NULL
AGE           NUMBER

Our aim is to load the data to this table from an excel sheet.
Create a standalone java class to represent the above table as shown below and generate accessors as well.

public class Employee{
  //Generate getters and setters for these attributes.
  private String employeeName;
  private Number salary;
  private Number age;
}

Create an EO based on the above table and create respective VO. Create an instance of the VO in the AM and create a method(accepts a List<Employee>) in AM to insert the data into VO. This method will be called from managed bean to insert data into the table.

Create inputFile and commandButton components in a page/fragment to load the excel sheet.

<af:inputfile autosubmit="true" 
                 binding="#{pageFlowScope.loadBulkEmployee.bulkEmployeeInputFile}" 
                 id="if1" label="File" 
                 showrequired="true" 
                 valuechangelistener="#{pageFlowScope.loadBulkEmployee.onBulkEmployeeUploadFileChanged}"/>
<af:commandbutton actionlistener="#{pageFlowScope.loadBulkEmployee.onUploadEmployees}" 
                     id="ctb1" text="Load File"/>

Create below properties in the managed bean to hold the data and generate their accessors

private RichInputFile bulkEmployeeInputFile;
private InputStream uploadFileInputStream;
private List bulkEmployeeList;

Write below method to load the InputStream from file loaded in the page

public void onBulkEmployeeUploadFileChanged(ValueChangeEvent valueChangeEvent) {
  UploadedFile uploadedFile = (UploadedFile)valueChangeEvent.getNewValue();
  if (!uploadedFile.getFilename().endsWith("xlsx")){
    FacesContext.getCurrentInstance().addMessage(
    bulkEmployeeInputFile.getClientId(FacesContext.getCurrentInstance()), 
                  new FacesMessage(FacesMessage.SEVERITY_ERROR, 
                     "File Format", 
                     "Your message"));
    bulkEmployeeInputFile.resetValue();
    bulkEmployeeInputFile.setValid(false);
  }
  try{
    uploadFileInputStream = uploadedFile.getInputStream();
  }
  catch (Exception e){
    //Handle the exception
  }
}

Write below method. This is called when the user presses Upload on the screen.

public void onUploadEmployees(ActionEvent actionEvent) {
   try{
     boolean anyUploadErrors = validateSiteUploadFile(uploadFileInputStream);
     if (anyUploadErrors){
       bulkEmployeeInputFile.resetValue();
       bulkEmployeeInputFile.setValid(false);    
     }
     else{
       if (bulkEmployeeList.size() > 0){
         //Call AM metod to store the data in the respective VO
         //Call Commit method to save data
         anyUploadErrors = false;
         confirmationPopup.show(new RichPopup.PopupHints());
       }
     }
     uploadFileInputStream = null;
   }
   catch (Exception e){
     //Handle the exception
   }
 }

Write below method to validate and load the spreadsheet data We are using Apache POI utility classes to read the excel sheet. More information can be found at Apache POI.

private boolean validateSiteUploadFile(InputStream siteUploadFile)
    throws ApplicationException{
  try{
    XSSFWorkbook workBook = new XSSFWorkbook(siteUploadFile);
    XSSFSheet sheet = workBook.getSheetAt(0);
    //Don't consider first row as it's header
    int rowStart = Math.max(1, sheet.getFirstRowNum());
    //Limit the max rows to 5000
    int rowEnd = Math.min(5001, sheet.getLastRowNum());
    bulkEmployeeList = new ArrayList();
    boolean anyErrors = false;
    for(int rowNum = rowStart; rowNum<=rowEnd; rowNum++){
      XSSFRow row = (XSSFRow)sheet.getRow(rowNum);
      List<String> lineErrors = new ArrayList<String>();
      //We are considering only first 3 columns in the spreadsheet.
      int lastColumn = 3;
      Employee empRec = new Employee();
      for(int cellIndex=0; cellIndex<lastColumn; cellIndex++){
        XSSFCell cell = (XSSFCell)row.getCell(cellIndex, XSSFRow.CREATE_NULL_AS_BLANK);
        if (cell != null){
          switch (cellIndex){
          case 0://Employee Name
                 String employeeName=null;
                 if(cell.getCellType() == CELL_TYPE_BLANK){
                   lineErrors.add("Employee Name is required");
                   break;
                 }
                 else if(cell.getCellType() == CELL_TYPE_STRING){
                   employeeName = cell.getStringCellValue();
                 }
                 empRec.setEmployeeName(employeeName);
                 break;
          case 1://Salary
                 if(cell.getCellType() == CELL_TYPE_BLANK){
                   lineErrors.add("Salary should not be blank");
                   break;
                 }
                 if(cell.getCellType() != CELL_TYPE_NUMERIC){
                   lineErrors.add("Salary should be a number");
                   break;
                 }
                 Number salary = new Number(cell.getNumericCellValue());
                 empRec.setSalary(salary);
                 break;
          case 2://Age
                 if(cell.getCellType() != CELL_TYPE_BLANK){
                   if(cell.getCellType() == CELL_TYPE_STRING && 
                      !"".equals(cell.getStringCellValue())){
                     lineErrors.add("Age should be a number");
              break;
                   }
                 }
   Number age = new Number(cell.getNumericValue());
   empRec.setAge(age);
                 break;
          default: break;
          }
        }
      }
      if(lineErrors!=null&&lineErrors.size()>0){
        //Do whatever you want with the errors. 
 //This is at each row level
 anyErrors = true;
      }
      bulkEmployeeList.add(empRec);
    }
  }
  catch (Exception e){
    //Handle Exception
  }
  return anyErrors;
}

Note:Above code is developed and tested in ADF 11.1.1.5.0 under Java 1.6.0_24