/////////////////////////////////////////////////////////////////////////////
//
// Project ProjectForge Community Edition
// www.projectforge.org
//
// Copyright (C) 2001-2014 Kai Reinhard (k.reinhard@micromata.de)
//
// ProjectForge is dual-licensed.
//
// This community edition is free software; you can redistribute it and/or
// modify it under the terms of the GNU General Public License as published
// by the Free Software Foundation; version 3 of the License.
//
// This community edition is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
// Public License for more details.
//
// You should have received a copy of the GNU General Public License along
// with this program; if not, see http://www.gnu.org/licenses/.
//
/////////////////////////////////////////////////////////////////////////////
package org.projectforge.excel;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Array;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.ConversionException;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.ClassUtils;
import org.apache.commons.lang.StringUtils;
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.hssf.usermodel.HSSFWorkbook;
/**
* Convert a given Excel-Sheet into an object-Array.
* @param <T> baseclass for each row
* @author Wolfgang Jung (w.jung@micromata.de)
*
*/
public class ExcelImport<T>
{
/** The logger */
private static final org.projectforge.common.Logger log = org.projectforge.common.Logger.getLogger(ExcelImport.class);
/** the workbook containing the values. */
private final HSSFWorkbook work;
/** a optional map for mapping column-names to property-names. */
private Map<String, String> columnToPropertyMap;
/** the worksheet from which the values should be read. */
private int activeSheet = 0;
/** the zero-based index with the column-names */
private int columnNameRow = 0;
/** the zero-based index of the first row containing values */
private int startAtRow = 1;
/** the classfactory for creating the objects. */
private ClassFactory<T> clazzFactory;
/**
* Opens a given Excel-document. The stream is always closed.
* @param xlsStream the stream of the Excel-document.
* @throws IOException if the document is not readable
*/
public ExcelImport(final InputStream xlsStream) throws IOException
{
// Closed by HSSFWorkbook
work = new HSSFWorkbook(xlsStream);
}
/**
* get a reference to the workbook for special features.
* @return the previously loaded document
*/
public HSSFWorkbook getWorkbook()
{
return work;
}
/**
* Set the optional Map from column-names to property-names.
*
* @param columnToPropertyMap a Map from column-names (String) to property-names (String)
*/
public void setColumnMapping(final Map<String, String> columnToPropertyMap)
{
this.columnToPropertyMap = columnToPropertyMap;
}
/**
* set the sheet, from which the objects should be created.
*
* @param sheet the zero-based index
*/
public void setActiveSheet(final int sheet)
{
activeSheet = sheet;
}
/**
* set the sheet, from which the objects should be created.
*
* @param sheetName the name of the sheet
*/
public void setActiveSheet(final String sheetName)
{
activeSheet = work.getSheetIndex(sheetName);
}
/**
* set the row-index of the row containing the column-names.
*
* @param columnNameRow the zero-based row index
*/
public void setNameRowIndex(final int columnNameRow)
{
this.columnNameRow = columnNameRow;
}
/**
* set the row-index of the row containing the first object values.
*
* @param startAtRow the zero-based row index
*/
public void setStartingRowIndex(final int startAtRow)
{
this.startAtRow = startAtRow;
}
/**
* set the class which should be used for storing the values.
*
* @param clazz the class of the target-objects for the values of the rows.
*/
public void setRowClass(final Class<T> clazz)
{
this.clazzFactory = new SimpleClassFactory<T>(clazz);
}
/**
* set the classFactory for dynamically creation of classes dependent of the row-contents.
*
* @param clazzFactory the factory
*/
public void setRowClassFactory(final ClassFactory<T> clazzFactory)
{
this.clazzFactory = clazzFactory;
}
/**
* returns the columnnames found
* @return list of columnnames
*/
public List<String> getColumnNames()
{
final HSSFSheet sheet = work.getSheetAt(activeSheet);
final HSSFRow columnNames = sheet.getRow(columnNameRow);
final List<String> list = new ArrayList<String>();
for (int column = 0; column < columnNames.getPhysicalNumberOfCells(); column++) {
if (columnNames.getCell(column) == null) {
continue;
}
final String columnName = columnNames.getCell(column).getStringCellValue();
if (columnName != null) {
list.add(columnName.trim());
}
}
return list;
}
/**
* convert the contents of the table into an array.
*
* @param clazz the target class
* @return an array with the object values.
*/
@SuppressWarnings("unchecked")
public T[] convertToRows(final Class<T> clazz)
{
if (clazzFactory == null) {
setRowClass(clazz);
}
final HSSFSheet sheet = work.getSheetAt(activeSheet);
final int numberOfRows = sheet.getLastRowNum();
final List<T> list = new ArrayList<T>(numberOfRows);
final HSSFRow columnNames = sheet.getRow(columnNameRow);
for (int i = startAtRow; i <= numberOfRows; i++) {
try {
T line;
line = convertToBean(sheet.getRow(i), columnNames, i + 1);
if (line == null) {
continue;
}
if (clazz.isInstance(line) == false) {
throw new IllegalStateException("returned type "
+ line.getClass()
+ " is not assignable to "
+ clazz
+ " in sheet='"
+ sheet.getSheetName()
+ "', row="
+ i);
}
list.add(line);
} catch (final InstantiationException ex) {
throw new IllegalArgumentException("Can't create bean " + ex.toString() + " in sheet='" + sheet.getSheetName() + "', row=" + i);
} catch (final IllegalAccessException ex) {
throw new IllegalArgumentException("Getter is not visible " + ex.toString() + " in sheet='" + sheet.getSheetName() + "', row=" + i);
} catch (final InvocationTargetException ex) {
log.error(ex.getMessage(), ex);
throw new IllegalArgumentException("Getter threw an exception "
+ ex.toString()
+ " in sheet='"
+ sheet.getSheetName()
+ "', row="
+ i);
} catch (final NoSuchMethodException ex) {
throw new IllegalArgumentException("Getter is not existant " + ex.toString() + " in sheet='" + sheet.getSheetName() + "', row=" + i);
}
}
return list.toArray((T[]) Array.newInstance(clazz, 0));
}
/**
* convert a single row to an object.
*
* @param row the row containing the values.
* @param columnNames the row containing the column-names.
* @param rowNum the current rownum
* @return a new created object populated with the values.
* @throws InstantiationException if the object creation fails.
* @throws IllegalAccessException if the object creation fails or the invoked setter is not public.
* @throws InvocationTargetException if the object creation fails with an exception or the setter threw an exception.
* @throws NoSuchMethodException if the setter for the property name is not existant.
*/
private T convertToBean(final HSSFRow row, final HSSFRow columnNames, final int rowNum) throws InstantiationException,
IllegalAccessException, InvocationTargetException, NoSuchMethodException
{
if (row == null) {
log.debug("created no bean for row#" + rowNum);
return null;
}
final T o = clazzFactory.newInstance(row);
if (columnNames == null) {
return null;
}
for (int column = 0; column < columnNames.getPhysicalNumberOfCells(); column++) {
if (columnNames.getCell(column) == null) {
continue;
}
String columnName = columnNames.getCell(column).getStringCellValue();
if (columnName != null) {
columnName = columnName.trim();
}
String propName = columnName;
if (columnToPropertyMap != null) {
final String mapName = columnToPropertyMap.get(columnName);
if (mapName != null) {
propName = mapName.trim();
}
}
try {
final Class< ? > destClazz = PropertyUtils.getPropertyType(o, propName);
if (propName == null || destClazz == null) {
log.debug("Skipping column " + columnName);
continue;
}
final Object value = toNativeType(row.getCell(column), destClazz);
log.debug("Setting property=" + propName + " to " + value + " class=" + ClassUtils.getShortClassName(value, "null"));
PropertyUtils.setProperty(o, propName, value);
} catch (final ConversionException e) {
log.warn(e);
throw new ExcelImportException("Falscher Datentyp beim Excelimport", new Integer(row.getRowNum()), columnName);
} catch (final Exception e) {
log.warn(e);
throw new ExcelImportException("Falscher Datentyp beim Excelimport", new Integer(row.getRowNum()), columnName);
}
}
if (log.isDebugEnabled() == true) {
log.debug("created bean " + o + " for row#" + rowNum);
}
return o;
}
/**
* convert the cell-value to the type in the bean.
*
* @param cell the cell containing an arbitrary value
* @param destClazz the target class
* @return a String, Boolean, Date or BigDecimal
*/
private Object toNativeType(final HSSFCell cell, final Class< ? > destClazz)
{
if (cell == null) {
return null;
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
log.debug("using numeric");
if (Date.class.isAssignableFrom(destClazz)) {
return cell.getDateCellValue();
}
String strVal = String.valueOf(cell.getNumericCellValue());
strVal = strVal.replaceAll("\\.0*$", "");
return ConvertUtils.convert(strVal, destClazz);
case HSSFCell.CELL_TYPE_BOOLEAN:
log.debug("using boolean");
return Boolean.valueOf(cell.getBooleanCellValue());
case HSSFCell.CELL_TYPE_STRING:
log.debug("using string");
strVal = StringUtils.trimToNull(cell.getStringCellValue());
return ConvertUtils.convert(strVal, destClazz);
case HSSFCell.CELL_TYPE_BLANK:
return null;
case HSSFCell.CELL_TYPE_FORMULA:
return new Formula(cell.getCellFormula());
default:
return StringUtils.trimToNull(cell.getStringCellValue());
}
}
}