Create object from Excel using Apache Poi

Question

I am trying to create Object from Excel. The excel file looks like this

I am trying to follow this idea from this post How to convert my xlsx sheet to java object using Apache POI. The very 1st answer looks good. However I am little lost on the part on how to create the object.

public class Tr {

    String empName;
    String empID;
    String empDept;

    //Constructor
    public Tr(String empName, String empID, String empDept) {
        this.empName = empName;
        this.empID = empID;
        this.empDept = empDept;
    }

    //The following part will  read Excel and return cell data
    public static ArrayList<String> name = new ArrayList<String>();
    public static ArrayList<String> deptId = new ArrayList<String>();
    public static ArrayList<String> dName = new ArrayList<String>();

    public ArrayList<String> getCellData(int cellNo) throws IOException {

        FileInputStream file = new FileInputStream("C:\\MyTemp\\People.xlsx");
        HSSFWorkbook book = new HSSFWorkbook(file);
        HSSFSheet sheet = book.getSheet("Sheet1");
        Iterator<Row> it = sheet.iterator();

        ArrayList<String> cellData = new ArrayList<String>();
        while (it.hasNext()) {
            cellData.add(it.next().getCell(cellNo).getStringCellValue());
        }
        return cellData;
    }

    //Assigning cell data to variables and converting to string 
    public void assignEmployee() throws IOException {
        empName = getCellData(0).toString();
        empID = getCellData(1).toString();
        empDept = getCellData(2).toString();
    }

    public static void main(String[] args) {

    }
}

Your help or ideas will be appreciated. Thanks.

score 1 · Answer 1 · answered Jul 15 '19 at 06:50

The answer you linked to doesn't quite do it this way. That answer suggests you should load the excel file once, and then iterate each Row in order to assign it to an Employee instance.

So basic usage would be like:

    FileInputStream file = new FileInputStream("C:\\MyTemp\\People.xlsx");
    HSSFWorkbook book = new HSSFWorkbook(file);
    HSSFSheet sheet = book.getSheet("Sheet1");

    Iterator<Row> it = sheet.iterator();
    Employee emp = new Employee();
    while(itr.hasNext()){
        Row row = itr.next();
        emp.assignEmployee(row);
        //  use emp instance here
    }

In other words, the class Employee would look like this:

public class Employee{
    private String empNo;
    private String empName; 

    public void assignEmployee(Row row) {
        empNo = row.getCell(0).toString();
        empName = row.getCell(1).toString();
    }
}

From here, you use the instance of Employee for whatever scope you require. I would personally go one step further and do the assigning in the constructor of Employee rather than have a method called assignEmployee (which like this may create problems with threads).

    Iterator<Row> it = sheet.iterator();

    while(itr.hasNext()){
        Row row = itr.next();
        Employee emp = new Employee(row);

        //  use emp instance here
    }

Create object from Excel using Apache Poi

1 Answers1