packagecom.ihrm.common.poi.utils;importcom.ihrm.domain.poi.ExcelAttribute;import org.apache.poi.hssf.usermodel.*;importorg.apache.poi.poifs.filesystem.POIFSFileSystem;importorg.apache.poi.ss.format.CellFormat;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.DateUtil;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importjava.io.FileInputStream;importjava.io.InputStream;importjava.lang.reflect.Field;importjava.math.BigDecimal;importjava.text.ParseException;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;public class ExcelImportUtil{privateClass clazz;privateField fields[];publicExcelImportUtil(Class clazz) {this.clazz =clazz;
fields=clazz.getDeclaredFields();
}/*** 基于注解读取excel*/
public List readExcel(InputStream is, int rowIndex,intcellIndex) {
List list = new ArrayList();
T entity= null;try{
XSSFWorkbook workbook= newXSSFWorkbook(is);
Sheet sheet= workbook.getSheetAt(0);//不准确
int rowLength =sheet.getLastRowNum();
System.out.println(sheet.getLastRowNum());for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row=sheet.getRow(rowNum);
entity=(T) clazz.newInstance();
System.out.println(row.getLastCellNum());for (int j = cellIndex; j < row.getLastCellNum(); j++) {
Cell cell=row.getCell(j);for(Field field : fields) {if(field.isAnnotationPresent(ExcelAttribute.class)){
field.setAccessible(true);
ExcelAttribute ea= field.getAnnotation(ExcelAttribute.class);if(j ==ea.sort()) {
field.set(entity, covertAttrType(field, cell));
}
}
}
}
list.add(entity);
}
}catch(Exception e) {
e.printStackTrace();
}returnlist;
}/*** 类型转换 将cell 单元格格式转为 字段类型*/
private Object covertAttrType(Field field, Cell cell) throwsException {
String fieldType=field.getType().getSimpleName();if ("String".equals(fieldType)) {returngetValue(cell);
}else if ("Date".equals(fieldType)) {return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(getValue(cell)) ;
}else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {returnInteger.parseInt(getValue(cell));
}else if ("double".equals(fieldType) || "Double".equals(fieldType)) {returnDouble.parseDouble(getValue(cell));
}else{return null;
}
}/*** 格式转为String
*@paramcell
*@return
*/
publicString getValue(Cell cell) {if (cell == null) {return "";
}switch(cell.getCellType()) {caseSTRING:returncell.getRichStringCellValue().getString().trim();caseNUMERIC:if(DateUtil.isCellDateFormatted(cell)) {
Date dt=DateUtil.getJavaDate(cell.getNumericCellValue());return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(dt);
}else{//防止数值变成科学计数法
String strCell = "";
Double num=cell.getNumericCellValue();
BigDecimal bd= newBigDecimal(num.toString());if (bd != null) {
strCell=bd.toPlainString();
}//去除 浮点型 自动加的 .0
if (strCell.endsWith(".0")) {
strCell= strCell.substring(0, strCell.indexOf("."));
}returnstrCell;
}caseBOOLEAN:returnString.valueOf(cell.getBooleanCellValue());default:return "";
}
}
}