CODEWITHSUNDEEP
javaexcelapache-poi
Bharat
Bharat

Reputation: 417

Read multiple excel file and push data to respective object in java, looking for efficient way

I have multiple excel file, using apache poi library I can read each excel file and set the data to object.

for example, employee.xls:

emp.id   firstname  dob
111111   fName      19/10/2011
222222   tName      11/12/2010

and the object as below:

 class Employee {
    private String empId;
    private String firstname;
    private Date dob;
    public String getEmpId() {
        return empId;
    }
    public void setEmpId(String empId) {
        this.empId = empId;
    }
    public String getFirstname() {
        return firstname;
    }
    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }
    public Date getDob() {
        return dob;
    }
    public void setDob(Date dob) {
        this.dob = dob;
    }
}

For pushing a data I need to read from excel file and set to the Employee object. In case I have more than 20 different xls files then I need to write code for each excel file to read and then set the data to respective object. Is there any other effective way to achieve this?

Thanks in advance!

Upvotes: 0

Views: 394

Answers (1)

Boris the Spider
Boris the Spider

Reputation: 61198

Assuming that the structure is always the same, the it seems that the logic is fairly simple:

  1. skip first row
  2. for each row
    • create an instance of the object
    • populate the fields.

I would suggest a reader something like this:

public class SheetReader<T> {
    private final Supplier<T> supplier; 
    private final List<BiConsumer<Cell, T>> populators;


    public SheetReader(Supplier<T> supplier, List<BiConsumer<Cell, T>> populators) {
        this.supplier = supplier;
        this.populators = populators;
    }


    public List<T> readSheet(final Sheet sheet, final boolean hasHeader) {
        final Iterator<Row> rows = sheet.iterator();
        if(hasHeader) {
            //skip first row
            rows.next();
        }
        final List<T> ts = new LinkedList<>();
        while(rows.hasNext()) {
            final Row row = rows.next();
            final T t = supplier.get();
            for(int i =0; i<populators.size();++i) {
                populators.get(i).accept(row.getCell(i), t);
            }
            ts.add(t);
        }
        return ts;
    }

}

Where the usage would be:

//should be ArrayList due to random access. Could also be Guava ImmutableList
final List<BiConsumer<Cell, Employee>> populators = new ArrayList<>();
//add the populators in order
populators.add((c, e) -> e.setEmpId(c.getStringCellValue()));
populators.add((c, e) -> e.setFirstname(c.getStringCellValue()));
populators.add((c, e) -> e.setDob(c.getDateCellValue()));
//pass constructor as producer
final SheetReader<Employee> reader = new SheetReader<>(Employee::new, populators);
//can read many sheets of same type with same reader
final List<Employee> employees = reader.readSheet(sheet, true);

Upvotes: 1

Related Questions