Reputation: 417
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
Reputation: 61198
Assuming that the structure is always the same, the it seems that the logic is fairly simple:
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