Simple Xls Parsing Application with POJO class

Simple Xls Parsing Application with POJO class.

Here we are going to explore simple Xls(Excel Sheet) parsing application with sample Pojo Class Employee. 
This sample application parse excel sheet, takes values from it and again add them back to excel sheet.

Required Jar Files to run this application are,



Copy all jars to Lib folder then set library build path if you are using eclipse.
To run directly just set path to library files.

Our Employee POJO,

Employee.java

/**
 * @author Sharoon
 *
 */
public class Employee {
   
    private int id;
    private String name;
    private double salary;


    public Employee(int id, String name,double salary){
        this.id = id;
        this.name = name;
        this.salary = salary;
    }
   
    public Employee() {
    }

    /**
     * @return the id
     */
    public int getId() {
        return id;
    }
    /**
     * @param id the id to set
     */
    public void setId(int id) {
        this.id = id;
    }
    /**
     * @return the name
     */
    public String getName() {
        return name;
    }
    /**
     * @param name the name to set
     */
    public void setName(String name) {
        this.name = name;
    }
   
    /**
     *
     * @return salary
     */
    public double getSalary() {
        return salary;
    }
   
    /**
     *
     * @param salary
     */
    public void setSalary(double salary) {
        this.salary = salary;
    }

}



Our Main Class XlsParsingExample uses java reflection to process get and set methods of POJO Employee.
Below code part shows the reflection usuage,

                    String columnName = columnNames[i++];
                    Field f1 = employee.getClass().getDeclaredField(columnName.trim());
                    f1.setAccessible(true);
                    f1.set(newRecord, getTypeValue(f1.getType(),cell));

Will just pass column name which was already parsed from excel sheet and get the method of Employee POJO and set the value.

Also, we use below method to type check method type and assign values appropriately.

    private static Object getTypeValue(Class<?> type, Cell cell) {
        Object typedValue = null;
        if(type == int.class){
            typedValue = (int) cell.getNumericCellValue();
        } else if(type == double.class){
            typedValue = cell.getNumericCellValue();
        } else if(type == boolean.class){
            typedValue = cell.getBooleanCellValue();
        } else if(type == String.class){
            typedValue = cell.getStringCellValue();
        }
        return typedValue;
    }    

XlsParsingExample.java

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

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 XlsParsingExample {

    /**
     * @param args
     */
    public static void main(String[] args) {
       
        List<Employee> empList = new ArrayList<Employee>();
       
        try
        {
           
            File file = new File("D:\\Project\\employee.xls");
           
            FileInputStream fileStream = new FileInputStream(file);
            //Get the workbook instance for XLS file
            XSSFWorkbook workbook = new XSSFWorkbook(fileStream);
                //Get first sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);    
           
            getAndPrintEmployees(sheet);
           
            // load employee objects from excel file
            // Now empList will be holding all employee objects
            Employee employee = new Employee();
            loadEmployeesToList(sheet,empList,employee);
           
            // now print employee objects from list using Enhanced For loop
            System.out.println(" Id \t Name \t Salary \n");
            for(Employee emp:empList){
                System.out.print(emp.getId()+"\t");
                System.out.print(emp.getName()+"\t");
                System.out.println(emp.getSalary());
            }
   
            // Method to add employee objects to excel sheet
            addEmployeeToExcel(sheet,empList);
           
          // Write newly modified workbook to a file.
          FileOutputStream fileOut = new FileOutputStream("D:\\Project\\employee.xls");
          workbook.write(fileOut);
          fileOut.close();
        }
        catch(FileNotFoundException e)
        {
         e.printStackTrace();
        }
        catch(IOException e)
        {
          System.out.println(e);
        } catch (SecurityException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }       
       

    }

    private static void getAndPrintEmployees(XSSFSheet sheet) {
       
        System.out.println(" -- Employee Report -------");
        Iterator<Row> rowIterator = sheet.iterator();
        while(rowIterator.hasNext()){
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
               
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                   
                }
            }
            System.out.println();
        }
       
    }

   
    private static void loadEmployeesToList(XSSFSheet sheet,
            List<Employee> empList, Employee employee) throws InstantiationException,
            IllegalAccessException, SecurityException, NoSuchFieldException {
       
        Row column = sheet.getRow(0);
        String columnNames[] = getColumnNames(column);
       
        Iterator<Row> rowIterator = sheet.iterator();
       
        int rowOne = 0;

        while(rowIterator.hasNext()){
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
           
            Employee newRecord = employee.getClass().newInstance();
           
            // Skip First row coz that is column names
            if(rowOne >0){          
                int i=0;
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String columnName = columnNames[i++];
                    Field f1 = employee.getClass().getDeclaredField(columnName.trim());
                    f1.setAccessible(true);
                    f1.set(newRecord, getTypeValue(f1.getType(),cell));
                       
                }
                empList.add(newRecord);   
            }
            rowOne++;
        }
       
    }       

    private static String[] getColumnNames(Row column) {
        String columns[] = new String[column.getPhysicalNumberOfCells()];
        Iterator<Cell> cellIterator = column.cellIterator();
        int i = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            columns[i++] = cell.getStringCellValue();
        }       
       
        return columns;
    }   
   
   
    /**
     * Method to get typed value
     * @param type
     * @param value
     * @return
     */
    private static Object getTypeValue(Class<?> type, Cell cell) {
        Object typedValue = null;
        if(type == int.class){
            typedValue = (int) cell.getNumericCellValue();
        } else if(type == double.class){
            typedValue = cell.getNumericCellValue();
        } else if(type == boolean.class){
            typedValue = cell.getBooleanCellValue();
        } else if(type == String.class){
            typedValue = cell.getStringCellValue();
        }
        return typedValue;
    }   
   
    private static void addEmployeeToExcel(XSSFSheet sheet,
            List<Employee> empList) {
        int rowCount = sheet.getPhysicalNumberOfRows();
       
        for(Employee emp:empList){
            Row newRow = sheet.createRow(++rowCount);
           
            Cell idCell = newRow.createCell(0);
            idCell.setCellValue(emp.getId());

            Cell nameCell = newRow.createCell(1);
            nameCell.setCellValue(emp.getName());

            Cell salaryCell = newRow.createCell(2);
            salaryCell.setCellValue(emp.getSalary());
        }
    }   
}

Sample Excel Sheet,