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

Question

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!

Is the format always the same? With a header row and the one object per line? — Boris the Spider
– Boris the Spider, Commented Mar 13, 2015 at 17:13
Yes! but there are diffrent xls, for example department, companies etc.. — Bharat
– Bharat, Commented Mar 13, 2015 at 17:18

Boris the Spider · Accepted Answer · 2015-03-14 08:25:26Z

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

skip first row
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);

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related