利用注解实现excel导入数据

使用注解类与spring实现导入excel并对数据进行处理
excel注解类：

@Target({ElementType.FIELD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Excel {
	//excel表头名
	String name() default "";
	//数据类型，暂时不使用
	String dataType() default "string";
	//时间格式，暂时不使用
	String dateFormat() default "yyyy-MM-dd hh:mm:ss";
	//优先级 默认最低级
	int priority() default 0;
	//数据转换ss
	boolean convert() default false;
	//导入导出实现类
	String serciveName() default "";
}

excel属性工具类：

/**
 * Excel属性类，导入导出使用
 * @author hhye
 */
public class ExcelAttribute {
	//表头名
	private String name;
	//实体对应字段名
	private String fieldName;
	//数据类型，暂时只有对date日期格式处理
	private String dataType;
	//时间格式，将制定时间格式转化为date
	private String dateFormat;
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getFieldName() {
		return fieldName;
	}
	public void setFieldName(String fieldName) {
		this.fieldName = fieldName;
	}
	public String getDataType() {
		return dataType;
	}
	public void setDataType(String dataType) {
		this.dataType = dataType;
	}
	public String getDateFormat() {
		return dateFormat;
	}
	public void setDateFormat(String dateFormat) {
		this.dateFormat = dateFormat;
	}
}

公共导入导出接口

/**
 * Excel导入导出实现接口
 * @author hhye 
 *
 */
public interface ExcelService<T extends BaseEntity> extends TransactionalService{

	default void importHandler(List<T> list) {};
	
	default File exportHandler(List<SysBoAttr> title,List<T> data) { return null;};
	
}

实体类：（注意类注解和字段注解的区别）

/**
 *实体
 * <p>Description: 信息表</p>  
 * @author hhye
 */
@Excel(serciveName = "com.stonewomb.test.service.HrUserTransferInfoExcelService")
public class HrUserTransferInfo  extends BaseEntity{
	@Excel(name="创建来源")
	private String createBy;
	@Excel(name="姓名")
	private String createUser;
	public String getCreateBy() {
		return createBy;
	}
	public void setCreateBy(String createBy) {
		this.createBy = createBy;
	}
	public String getCreateUser() {
		return createUser;
	}
	public void setCreateUser(String createUser) {
		this.createUser = createUser;
	}
}

实体对应的excel接口实现类

@Service
public class HrUserTransferInfoExcelService implements ExcelService<HrUserTransferInfo>{
	@Override
	public void importHandler(List<HrUserTransferInfo> list) {
		//导入实体处理
		System.out.println("----------------------测试是否调用--------------------");
		//自定义处理
	}
}

Controller层实现上传excel文件后处理（前台使用WebUploader上传）

/**
	 * 导入单个文件
	 *
	 * @param request
	 * @param response
     * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@RequestMapping("importExcel")
	@ResponseBody
	public JsonResult<Object> importExcel(MultipartHttpServletRequest request, HttpServletResponse response) throws Exception{
		JsonResult<Object> jsonResult = new JsonResult<>();
		if (offAction(StonewombAction.SAVE)) {
            throw new StonewombException("当前请求不允许");
        }
		Excel typeExcel = this.entityClass.getDeclaredAnnotation(Excel.class);
		ExcelService<T> excelService;
		//判断是否有实现类，如果没有，直接报错
		if(typeExcel!=null&&StringUtil.isNotEmpty(typeExcel.serciveName())) {
			try {
			//使用getbean获取实体对应的实现类的bean
				excelService = (ExcelService<T>)WebAppUtil.getBean(Class.forName(typeExcel.serciveName()));
			} catch (Exception e) {
				throw new StonewombException("Excel操作接口实现类初始化错误！");
			}
		}else {
			throw new StonewombException("Excel操作接口未存在实现类！");
		}
		Map<String, MultipartFile> files = request.getFileMap();
		Iterator<MultipartFile> it = files.values().iterator();
		List<Map<String,Object>> listMap= new ArrayList<>();
		while (it.hasNext()) {
			MultipartFile f = it.next();
			String oriFileName = f.getOriginalFilename();
			String extName = FileUtil.getFileExt(oriFileName);
			//转化为大写进行判断
			extName = extName.toUpperCase();
			InputStream fis = f.getInputStream();
			try {
				//判断文件名是否符合要求
				if("XLS".equals(extName)) {
					listMap.addAll(getImportField(fis,true));
				}else if("XLSX".equals(extName)) {
					listMap.addAll(getImportField(fis,false));
				}else {
					throw new StonewombException("文件格式错误！");
				}
			} finally {
				if(fis!=null) {
					fis.close();
				}
			}
		}
		//将获取的Map数据封装回实体
		List<T> list = changeMapToData(listMap);
		beforeImport(list, request, response);
		if(excelService==null) {
			throw new StonewombException("Excel操作接口实现类初始化失败！");
		}else {
			excelService.importHandler(list);
		}
		afterImport(list, request, response);
		jsonResult.setSuccess(true);
		jsonResult.setData(list);
		jsonResult.setMessage("成功上传!");

		return jsonResult;
	}

/**
	 * 根据传入的map生成对象
	 * @param listMap
	 * @return
	 * @throws Exception
	 */
	protected List<T> changeMapToData(List<Map<String, Object>> listMap) throws Exception {
		List<T> list = new ArrayList<>();
		if(listMap!=null&&!listMap.isEmpty()) {
			for(Map<String, Object> map:listMap) {
				T entity = JSON.parseObject(JSON.toJSONString(map), this.entityClass);
				list.add(entity);
			}
		}
		return list;
	}

	/**
     * 导入前置处理
     * @param list
     * @param request
     * @param response
     * @throws StonewombException
     */
    protected void beforeImport(List<T> list, HttpServletRequest request, HttpServletResponse response) throws StonewombException {
    }
    
    /**
     * 导入后置处理
     * @param list
     * @param request
     * @param response
     * @throws StonewombException
     */
    protected void afterImport(List<T> list, HttpServletRequest request, HttpServletResponse response) throws StonewombException {
    }
    
    /**
     * 获取所有实体excel处理属性
     * @param fis
     * @param isXls
     * @return
     * @throws StonewombException
     */
    protected List<Map<String,Object>> getImportField(InputStream fis,Boolean isXls) throws StonewombException {
    	Field[] declaredFields = this.entityClass.getDeclaredFields();
    	List<ExcelAttribute> headers = new ArrayList<>();
		for (Field field : declaredFields) {
			Excel excel = field.getDeclaredAnnotation(Excel.class);
			if (excel != null&&StringUtil.isNotEmpty(excel.name())) {
				ExcelAttribute exAttr = new ExcelAttribute();
				exAttr.setFieldName(field.getName());
				exAttr.setName(excel.name());
				exAttr.setDataType(excel.dataType());
				exAttr.setDateFormat(excel.dateFormat());
				headers.add(exAttr);
			}
		}
		return ExcelUtil.createExcel(headers, fis, isXls);
    	
    }

excel操作工具类ExcelUtil实现方法：

	public static List<Map<String, Object>> createExcel(List<ExcelAttribute> headers, InputStream is, boolean isXls) {
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		try {
			Workbook wb = null;
			if (isXls) {// 当excel是2003时,创建excel2003
				wb = new HSSFWorkbook(is);
			} else {// 当excel是2007时,创建excel2007
				wb = new XSSFWorkbook(is);
			}
			dataList = readExcelValue(wb, headers, is, isXls);// 读取Excel里面客户的信息
		} catch (IOException e) {
			e.printStackTrace();
		}
		return dataList;
	}

	public static List<Map<String, Object>> readExcelValue(Workbook wb, List<ExcelAttribute> headers, InputStream is,
			boolean isXls) {
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		Map<Integer, ExcelAttribute> titleMap = new HashMap<Integer, ExcelAttribute>();
		// 得到第一个shell
		Sheet sheet = wb.getSheetAt(0);
		// 得到Excel的行数
		int totalRows = sheet.getPhysicalNumberOfRows();
		// 得到Excel的列数(前提是有行数)
		int totalCells = 0;
		if (totalRows > 1 && sheet.getRow(0) != null) {
			totalCells = sheet.getRow(1).getPhysicalNumberOfCells();
		}

		for (int r = 1; r < totalRows; r++) {
			Row row = sheet.getRow(r);
			if (row == null) {
				continue;
			}
			Map<String, Object> map = new HashMap<String, Object>();
			// 循环Excel的列
			for (int c = 0; c < totalCells; c++) {
				Cell cell = row.getCell(c);
				if (null != cell) {
					if (r == 1) {
						String name = cell.getStringCellValue();
						for (ExcelAttribute header : headers) {
							if (header.getName().equals(name)) {
								titleMap.put(c, header);
							}
						}
					} else {
						// 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
						ExcelAttribute title = titleMap.get(c);
						if (title != null) {
							if ("string".equals(title.getDataType())) {
								cell.setCellType(CellType.STRING);
							}
							String value = getCellFormatValue(cell);
							String titleName = title.getFieldName();
							if ("date".equals(title.getDataType())) {
									String formStr = getCnDateStr(value);
									Date d = DateUtil.parseDate(value, formStr);
									if(d==null) {
										d = DateUtil.parseDate(value, title.getDateFormat());
									}
									map.put(titleName, d);
							} else {
								map.put(titleName, value);
							}
						}
					}
				}
			}
			// 添加到list
			if (r != 1) {
				dataList.add(map);
			}
		}
		return dataList;
	}
	public static String getCellFormatValue(Cell cell) {
		String cellValue = "未知类型";
		if (cell == null) {
            return cellValue;
        }

		if(CellType.NUMERIC==cell.getCellTypeEnum()) {
			// 数字
			cellValue = String.valueOf(cell.getNumericCellValue()); 
		}else if(CellType.STRING==cell.getCellTypeEnum()) {
			// 字符串
			cellValue = String.valueOf(cell.getStringCellValue()); 
		}else if(CellType.BOOLEAN==cell.getCellTypeEnum()) {
			// Boolean
			cellValue = String.valueOf(cell.getBooleanCellValue()); 
		}else if(CellType.FORMULA==cell.getCellTypeEnum()) {
			// 公式
			if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				cellValue = DateUtil.formatDate(date);
			} else {
				cellValue = String.valueOf(cell.getNumericCellValue());
			}
		}else if(CellType.BLANK==cell.getCellTypeEnum()) {
			// 空值
			cellValue = ""; 
		}else if(CellType.ERROR==cell.getCellTypeEnum()) {
			// 故障
			cellValue = "非法字符"; 
		}
		return cellValue;
	}