import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.apache.commons.beanutils.BeanUtils;
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;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
public static final String FILE_EXT_XLSX = "xlsx";
public static final String FILE_EXT_XLS = "xls";
public static class ColumnMeta {
int colIdx;
String field;
public ColumnMeta(int colIdx, String field) {
this.colIdx = colIdx;
this.field = field;
}
}
public static class ExcelMeta<T> {
Class<T> classz;
Integer sheetIdx;
Integer startRow = 1;
List<ColumnMeta> columns = new ArrayList<ColumnMeta>();
public ExcelMeta<T> addColumn(ColumnMeta colMeta) {
this.columns.add(colMeta);
return this;
}
public ExcelMeta(Class<T> classz) {
this.classz = classz;
}
public ExcelMeta(Class<T> classz, Integer sheetIdx, int startRow) {
this.classz = classz;
this.sheetIdx = sheetIdx;
this.startRow = startRow;
}
}
/**
* 读Excel文件
* @param inputStream 文件输入流
* @param exelMeta 表格元数据
* @return 实体集合
* @throws Exception
* @throws FileNotFoundException
*/
public static <T> List<T> readFromExcel(String fileFullPath, ExcelMeta<T> exelMeta)
throws FileNotFoundException {
InputStream inputStream = null;
try {
inputStream = new FileInputStream(fileFullPath);
if(fileFullPath.toLowerCase().endsWith(FILE_EXT_XLSX)) {
return readFromXLSX2007(inputStream, exelMeta);
}
else if(fileFullPath.toLowerCase().endsWith(FILE_EXT_XLS)) {
return readFromXLS2003(inputStream, exelMeta);
}
}finally {
try {
if(inputStream != null)
inputStream.close();
} catch (IOException e) {}
}
return Collections.emptyList();
}
/**
* 读Excel2007版文件
* @param inputStream 文件输入流
* @param exelMeta 表格元数据
* @return 实体集合
*/
public static <T> List<T> readFromXLSX2007(InputStream inputStream, ExcelMeta<T> exelMeta) {
XSSFSheet sheet = null;
try {
XSSFWorkbook workbook2007 = new XSSFWorkbook(inputStream);
sheet = exelMeta.sheetIdx == null
? workbook2007.getSheetAt(workbook2007.getActiveSheetIndex())
: workbook2007.getSheetAt(exelMeta.sheetIdx);
} catch (Exception e) {
// log
}
if(sheet == null) {
return Collections.emptyList();
}
List<T> list = new ArrayList<T>();
try {
for (int i = exelMeta.startRow; i <= sheet.getLastRowNum(); i++) {
XSSFRow row = sheet.getRow(i);
if (row == null) continue;
T bean = createInstance(exelMeta.classz);
int colNullCount = 0;
for(ColumnMeta colMeta : exelMeta.columns) {
XSSFCell cell = row.getCell(colMeta.colIdx);
Object value = getValueFromCell(cell);
if(value == null) colNullCount++;
setValueToBean(bean, value, colMeta);
}
if(exelMeta.columns.size() > 0 && colNullCount < exelMeta.columns.size())
list.add(bean);
}
} catch (InstantiationException e) {
// log
} catch (InvocationTargetException e) {
// log
}
return list;
}
/**
* 读Excel2003版文件
* @param inputStream 文件输入流
* @param exelMeta 表格元数据
* @return 实体集合
*/
public static <T> List<T> readFromXLS2003(InputStream inputStream, ExcelMeta<T> exelMeta) {
HSSFSheet sheet = null;
try {
HSSFWorkbook workbook2003 = new HSSFWorkbook(inputStream);
sheet = exelMeta.sheetIdx == null
? workbook2003.getSheetAt(workbook2003.getActiveSheetIndex())
: workbook2003.getSheetAt(exelMeta.sheetIdx);
} catch (Exception e) {
// log
}
if(sheet == null) {
return Collections.emptyList();
}
List<T> list = new ArrayList<T>();
try {
for (int i = exelMeta.startRow; i <= sheet.getLastRowNum(); i++) {
HSSFRow row = sheet.getRow(i);
if (row == null) continue;
T bean = createInstance(exelMeta.classz);
int colNullCount = 0;
for(ColumnMeta colMeta : exelMeta.columns) {
HSSFCell cell = row.getCell(colMeta.colIdx);
Object value = getValueFromCell(cell);
if(value == null) colNullCount++;
setValueToBean(bean, value, colMeta);
}
if(exelMeta.columns.size() > 0 && colNullCount < exelMeta.columns.size())
list.add(bean);
}
} catch (InstantiationException e) {
// log
} catch (InvocationTargetException e) {
// log
}
return list;
}
private static void setValueToBean(Object bean, Object value, ColumnMeta colMeta) throws InvocationTargetException {
// DateConverter d = new DateConverter();
// d.setPattern(colMeta.cellType.format);
// ConvertUtils.register(d, java.util.Date.class);
try {
BeanUtils.setProperty(bean, colMeta.field, value);
} catch (Exception e) {
throw new InvocationTargetException(e);
}
}
private static <T> T createInstance(Class<T> classz) throws InstantiationException{
T bean = null;
try {
bean = classz.newInstance();
} catch (Exception e) {
throw new InstantiationException("Can't create instance");
}
return bean;
}
private static Object getValueFromCell(Object cell) {
Object obj = null;
if (cell == null) {
return obj;
}
if(cell.getClass() == XSSFCell.class){
XSSFCell xCell = (XSSFCell)cell;
int cellType = xCell.getCellType();
if(cellType == XSSFCell.CELL_TYPE_STRING){
return xCell.getStringCellValue();
}
if(cellType == XSSFCell.CELL_TYPE_NUMERIC){
Object retVal = null;
if(DateUtil.isCellDateFormatted(xCell)){
retVal = xCell.getDateCellValue();
}
else{
double doubleVal = xCell.getNumericCellValue();
long longVal = Math.round(doubleVal);
if(Double.parseDouble(longVal + ".0") == doubleVal) retVal = longVal;
else retVal = doubleVal;
}
return retVal;
}
if(cellType == XSSFCell.CELL_TYPE_BOOLEAN) {
return xCell.getBooleanCellValue();
}
}
else if(cell.getClass() == HSSFCell.class){
HSSFCell hCell = (HSSFCell)cell;
int cellType = hCell.getCellType();
if(cellType == XSSFCell.CELL_TYPE_STRING){
return hCell.getStringCellValue();
}
if(cellType == XSSFCell.CELL_TYPE_NUMERIC){
Object retVal = null;
if(DateUtil.isCellDateFormatted(hCell)){
retVal = hCell.getDateCellValue();
}
else{
double doubleVal = hCell.getNumericCellValue();
long longVal = Math.round(doubleVal);
if(Double.parseDouble(longVal + ".0") == doubleVal) retVal = longVal;
else retVal = doubleVal;
}
return retVal;
}
if(cellType == XSSFCell.CELL_TYPE_BOOLEAN) {
return hCell.getBooleanCellValue();
}
}
return null;
}
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.apache.commons.beanutils.BeanUtils;
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;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
public static final String FILE_EXT_XLSX = "xlsx";
public static final String FILE_EXT_XLS = "xls";
public static class ColumnMeta {
int colIdx;
String field;
public ColumnMeta(int colIdx, String field) {
this.colIdx = colIdx;
this.field = field;
}
}
public static class ExcelMeta<T> {
Class<T> classz;
Integer sheetIdx;
Integer startRow = 1;
List<ColumnMeta> columns = new ArrayList<ColumnMeta>();
public ExcelMeta<T> addColumn(ColumnMeta colMeta) {
this.columns.add(colMeta);
return this;
}
public ExcelMeta(Class<T> classz) {
this.classz = classz;
}
public ExcelMeta(Class<T> classz, Integer sheetIdx, int startRow) {
this.classz = classz;
this.sheetIdx = sheetIdx;
this.startRow = startRow;
}
}
/**
* 读Excel文件
* @param inputStream 文件输入流
* @param exelMeta 表格元数据
* @return 实体集合
* @throws Exception
* @throws FileNotFoundException
*/
public static <T> List<T> readFromExcel(String fileFullPath, ExcelMeta<T> exelMeta)
throws FileNotFoundException {
InputStream inputStream = null;
try {
inputStream = new FileInputStream(fileFullPath);
if(fileFullPath.toLowerCase().endsWith(FILE_EXT_XLSX)) {
return readFromXLSX2007(inputStream, exelMeta);
}
else if(fileFullPath.toLowerCase().endsWith(FILE_EXT_XLS)) {
return readFromXLS2003(inputStream, exelMeta);
}
}finally {
try {
if(inputStream != null)
inputStream.close();
} catch (IOException e) {}
}
return Collections.emptyList();
}
/**
* 读Excel2007版文件
* @param inputStream 文件输入流
* @param exelMeta 表格元数据
* @return 实体集合
*/
public static <T> List<T> readFromXLSX2007(InputStream inputStream, ExcelMeta<T> exelMeta) {
XSSFSheet sheet = null;
try {
XSSFWorkbook workbook2007 = new XSSFWorkbook(inputStream);
sheet = exelMeta.sheetIdx == null
? workbook2007.getSheetAt(workbook2007.getActiveSheetIndex())
: workbook2007.getSheetAt(exelMeta.sheetIdx);
} catch (Exception e) {
// log
}
if(sheet == null) {
return Collections.emptyList();
}
List<T> list = new ArrayList<T>();
try {
for (int i = exelMeta.startRow; i <= sheet.getLastRowNum(); i++) {
XSSFRow row = sheet.getRow(i);
if (row == null) continue;
T bean = createInstance(exelMeta.classz);
int colNullCount = 0;
for(ColumnMeta colMeta : exelMeta.columns) {
XSSFCell cell = row.getCell(colMeta.colIdx);
Object value = getValueFromCell(cell);
if(value == null) colNullCount++;
setValueToBean(bean, value, colMeta);
}
if(exelMeta.columns.size() > 0 && colNullCount < exelMeta.columns.size())
list.add(bean);
}
} catch (InstantiationException e) {
// log
} catch (InvocationTargetException e) {
// log
}
return list;
}
/**
* 读Excel2003版文件
* @param inputStream 文件输入流
* @param exelMeta 表格元数据
* @return 实体集合
*/
public static <T> List<T> readFromXLS2003(InputStream inputStream, ExcelMeta<T> exelMeta) {
HSSFSheet sheet = null;
try {
HSSFWorkbook workbook2003 = new HSSFWorkbook(inputStream);
sheet = exelMeta.sheetIdx == null
? workbook2003.getSheetAt(workbook2003.getActiveSheetIndex())
: workbook2003.getSheetAt(exelMeta.sheetIdx);
} catch (Exception e) {
// log
}
if(sheet == null) {
return Collections.emptyList();
}
List<T> list = new ArrayList<T>();
try {
for (int i = exelMeta.startRow; i <= sheet.getLastRowNum(); i++) {
HSSFRow row = sheet.getRow(i);
if (row == null) continue;
T bean = createInstance(exelMeta.classz);
int colNullCount = 0;
for(ColumnMeta colMeta : exelMeta.columns) {
HSSFCell cell = row.getCell(colMeta.colIdx);
Object value = getValueFromCell(cell);
if(value == null) colNullCount++;
setValueToBean(bean, value, colMeta);
}
if(exelMeta.columns.size() > 0 && colNullCount < exelMeta.columns.size())
list.add(bean);
}
} catch (InstantiationException e) {
// log
} catch (InvocationTargetException e) {
// log
}
return list;
}
private static void setValueToBean(Object bean, Object value, ColumnMeta colMeta) throws InvocationTargetException {
// DateConverter d = new DateConverter();
// d.setPattern(colMeta.cellType.format);
// ConvertUtils.register(d, java.util.Date.class);
try {
BeanUtils.setProperty(bean, colMeta.field, value);
} catch (Exception e) {
throw new InvocationTargetException(e);
}
}
private static <T> T createInstance(Class<T> classz) throws InstantiationException{
T bean = null;
try {
bean = classz.newInstance();
} catch (Exception e) {
throw new InstantiationException("Can't create instance");
}
return bean;
}
private static Object getValueFromCell(Object cell) {
Object obj = null;
if (cell == null) {
return obj;
}
if(cell.getClass() == XSSFCell.class){
XSSFCell xCell = (XSSFCell)cell;
int cellType = xCell.getCellType();
if(cellType == XSSFCell.CELL_TYPE_STRING){
return xCell.getStringCellValue();
}
if(cellType == XSSFCell.CELL_TYPE_NUMERIC){
Object retVal = null;
if(DateUtil.isCellDateFormatted(xCell)){
retVal = xCell.getDateCellValue();
}
else{
double doubleVal = xCell.getNumericCellValue();
long longVal = Math.round(doubleVal);
if(Double.parseDouble(longVal + ".0") == doubleVal) retVal = longVal;
else retVal = doubleVal;
}
return retVal;
}
if(cellType == XSSFCell.CELL_TYPE_BOOLEAN) {
return xCell.getBooleanCellValue();
}
}
else if(cell.getClass() == HSSFCell.class){
HSSFCell hCell = (HSSFCell)cell;
int cellType = hCell.getCellType();
if(cellType == XSSFCell.CELL_TYPE_STRING){
return hCell.getStringCellValue();
}
if(cellType == XSSFCell.CELL_TYPE_NUMERIC){
Object retVal = null;
if(DateUtil.isCellDateFormatted(hCell)){
retVal = hCell.getDateCellValue();
}
else{
double doubleVal = hCell.getNumericCellValue();
long longVal = Math.round(doubleVal);
if(Double.parseDouble(longVal + ".0") == doubleVal) retVal = longVal;
else retVal = doubleVal;
}
return retVal;
}
if(cellType == XSSFCell.CELL_TYPE_BOOLEAN) {
return hCell.getBooleanCellValue();
}
}
return null;
}
}
ExcelMeta<SapNimbleContract> exelMeta = new ExcelMeta<SapNimbleContract>(SapNimbleContract.class, null, 3);
exelMeta.addColumn(new ColumnMeta(0, "startDate"))
.addColumn(new ColumnMeta(1, "personNumber"))
.addColumn(new ColumnMeta(2, "personName"));
List<SapNimbleContract> sapNimbleContracts = ExcelUtil.readFromExcel(fullPath, exelMeta);