package my.bulkupload.excelengine;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.log4j.Logger;
import pershing.bulkupload.formatters.Formatter;
public class ExcelParser<T> {
private final static Logger logger = Logger.getLogger(ExcelParser.class);
private int skipNoOfRows = 0; /* Make this configurable */
private final Map<String, Formatter> formatterMap = new HashMap<String, Formatter>();
public ExcelParser(int skipNoOfRows) throws InstantiationException, IllegalAccessException, ClassNotFoundException {
this.skipNoOfRows = skipNoOfRows;
this.init();
}
public void registerFormatter(String type, Formatter objInstance) throws InstantiationException, IllegalAccessException {
this.formatterMap.put(type, objInstance);
}
@SuppressWarnings("unchecked")
private void init() throws InstantiationException, IllegalAccessException, ClassNotFoundException {
Class<Formatter> obj = (Class<Formatter>) Class.forName("com.excel2objects.common.excel.formatters.IntegerFormatter");
Formatter objInstance = obj.newInstance();
this.formatterMap.put("java.lang.Integer", objInstance);
obj = (Class<Formatter>) Class.forName("com.excel2objects.common.excel.formatters.FloatFormatter");
objInstance = obj.newInstance();
this.formatterMap.put("java.lang.Float", objInstance);
obj = (Class<Formatter>) Class.forName("com.excel2objects.common.excel.formatters.BooleanFormatter");
objInstance = obj.newInstance();
this.formatterMap.put("java.lang.Boolean", objInstance);
obj = (Class<Formatter>) Class.forName("com.excel2objects.common.excel.formatters.DateFormatter");
objInstance = obj.newInstance();
this.formatterMap.put("java.util.Date", objInstance);
obj = (Class<Formatter>) Class.forName("com.excel2objects.common.excel.formatters.StringFormatter");
objInstance = obj.newInstance();
this.formatterMap.put("java.lang.String", objInstance);
obj = (Class<Formatter>) Class.forName("com.excel2objects.common.excel.formatters.DoubleFormatter");
objInstance = obj.newInstance();
this.formatterMap.put("java.lang.Double", objInstance);
}
public List<T> getObjects(HSSFSheet sheet, Class<T> className, Map<Integer, String> map) throws ClassNotFoundException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, InstantiationException, UnparsbleException {
final List<T> listObjects = new ArrayList<T>();
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
logger.debug("Processing row" + i);
if (i < this.skipNoOfRows) continue;
final T obj = this.createObject(className, sheet.getRow(i), map);
listObjects.add(obj);
}
return listObjects;
}
@SuppressWarnings("unchecked")
private T createObject(Class<T> className, HSSFRow row, Map<Integer, String> map) throws ClassNotFoundException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, InstantiationException, UnparsbleException {
final Class<T> obj = (Class<T>) Class.forName(className.getName());
final T objInstance = obj.newInstance();
logger.debug("Setting properties for class" + className.getName());
if (row == null) return null;
for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
logger.debug("Processing cell" + i);
final String propertyName = map.get(new Integer(i + 1));
logger.debug("Processing cell propertyName" + propertyName);
final HSSFCell cell = row.getCell(i);
if (cell == null) continue;
if (propertyName == null || propertyName.trim().equalsIgnoreCase("")) {
continue;
}
Object value = null;
final Object cellValue = this.getCellValue(cell);
if (cellValue == null) continue;
logger.debug("Getting descriptor for property propertyName" + propertyName);
/* Below commented section should be converted in to a simple set to a map that was created from the configuration, we shud not be using any beans*/
/* final PropertyDescriptor descriptor = PropertyUtils.getPropertyDescriptor(objInstance, propertyName);
if (descriptor == null) throw new UnparsbleException("Check if property" + propertyName + "exists");
final Class<?> type = descriptor.getPropertyType(); */
final Formatter<?> formatter = this.formatterMap.get(type.getName());
if (formatter == null) throw new UnparsbleException("No Formatters for" + type.getName() + "exists");
try {
value = formatter.from(cellValue);
} catch (final Exception e) {
logger.error(e);
value = null;
}
PropertyUtils.setSimpleProperty(objInstance, propertyName, value); /* this is concrete object - shud be a map */
}
return objInstance;
}
private Object getCellValue(HSSFCell cell) {
String strValue = new String("");
Double dblValue = new Double();
if (cell == null) return null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
str = cell.toString().trim();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
objSimpleDateFormat = new SimpleDateFormat("dd/MM/yyyy");
str = objSimpleDateFormat.format(cell.getDateCellValue());
} else {
num = cell.getNumericCellValue();
return num;
}
break;
case Cell.CELL_TYPE_BOOLEAN:
str = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
str = "";
break;
case Cell.CELL_TYPE_ERROR:
str = "";
break;
}
return str;
}
}