Easypoi导入导出

前几天，用户突然急需要一个导入某个大区人员的功能，最好能在中午吃饭前实现，基于时间紧急就考虑到easypoi去实现功能。现在没事将easypoi导入和导出写上用于记录学习过程。

目标功能，导入的同时，将Excel中重复数据进行批量更新，错误数据之前导出，例子基于SpringBoot来进行测试

        <!--  在pom文件中加入 easyPoi -->
        <dependency>
		  <groupId>cn.afterturn</groupId>
		  <artifactId>easypoi-spring-boot-starter</artifactId>
		  <version>3.2.0</version>
		</dependency>

为了方便快捷，前台导入使用了Layui组件

<link href="/css/layui.css" rel="stylesheet">
<link href="/css/style.css?v=4.1.0" rel="stylesheet">
<script src="/js/jquery.min.js?v=2.1.4"></script>
<script src="/js/bootstrap-paginator.min.js"></script>
<script src="/js/content.js?v=1.0.0"></script>
<script src="/js/layui.js"></script>

js代码

//导入Excel
layui.use('upload', function () {
    var upload = layui.upload;
    var uploadInst = upload.render({
        elem: '#test1', //绑定元素属性
        url: prefix+'/excelImport', //上传接口
        size: 1000,//文件大小
        accept: 'file',//类型
        done: function (r) {
        	debugger
        	if(r.code==500){
        		layer.msg(r.msg);
        		reLoad();
        	}
        	//分开处理重复的和错误的
        	else if(r.repeatList.length!=0){
        		//对Url进行编码，或者降低服务器版本
        		/*document.location.href = prefix+'/exportFail?list='+encodeURI(JSON.stringify(r.repeatList));*/
        		document.location.href = prefix+'/exportRepat'
        		layer.msg("Excel中有重复数据已导出");
        		reLoad();
        	}
        	else if(r.failList.length!=0){
        		document.location.href = prefix+'/exportFail'
        		layer.msg("Excel中错误的数据已导出");
        		reLoad();
        	}
        	else{
        		layer.msg("导入成功");
        		reLoad();
        	}
        },
    });
});

控制层

	@ResponseBody
	@PostMapping("/excelImport")
	@RequiresPermissions("test:test:excel")
	public R excelImport(@RequestParam("file") MultipartFile file,HttpServletResponse response,HttpServletRequest req) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {
		return taskInfoService.excelImport(file,response,req);
	}
	@ResponseBody
	@GetMapping("/exportFail")
	public void exportFail(HttpServletResponse response,String list,HttpServletRequest request){
    List<KaTaskDO> vo = JSON.parseArray(list,KaTaskDO.class);
    taskInfoService.exportFail(response,vo,request);
	}

vo属性

    @Excel(name = "大区",needMerge = true,orderNum = "1")
	@NotBlank(message = "部门不能为空")
	private String deptName;//Excel单列表头
	
	@ExcelCollection(name = "2019年分月销售",orderNum = "2")
	private List<MonthPoi> month = new ArrayList<>();//Excel复合表头,一级表头
    
    @Excel(name = "1月",width = 15, isImportField = "true_st")
	/*@Pattern(regexp="^\\d+$",message="盒数必须为整数") */
	private BigDecimal month1;//二级表头
	
	@Excel(name = "2月",width = 15, isImportField = "true_st")
	/*@Pattern(regexp="^\\d+$",message="盒数必须为整数")*/
	private BigDecimal month2;//二级表头

Service

    @Override
    @Transactional
	public R excelImport(MultipartFile file, HttpServletResponse response,HttpServletRequest req)
			throws NoSuchFieldException, SecurityException,
			IllegalArgumentException, IllegalAccessException {
        Map<String, Object> map = new HashMap<String, Object>();
		List<KaTaskDO> repeatList = null;// 导出错误数据专用
		ImportParams importParams = new ImportParams();
		// 需要验证
		importParams.setNeedVerfiy(true);
		importParams.setHeadRows(2);//因为有二级表头，所以读数据从索引为2的开始读取
        
		// 特殊列处理，需要反射修改对象注解信息
       	String old, newValue;
		KaTaskDO ka = new KaTaskDO();
		Field field = ka.getClass().getDeclaredField("month");
		ExcelCollection excel = field.getAnnotation(ExcelCollection.class);
		old = excel.name();
		log.info(old + "");
		InvocationHandler invocationHandler = Proxy.getInvocationHandler(excel);
		Field declaredField = invocationHandler.getClass().getDeclaredField(
				"memberValues");
		declaredField.setAccessible(true);
		Map memberValues = (Map) declaredField.get(invocationHandler);
		memberValues.put("name",
				String.valueOf(Calendar.getInstance().get(Calendar.YEAR))
						+ "年分月销售任务");
		newValue = excel.name();
		log.info(newValue + "");
try {
			ExcelImportResult<KaTaskDO> result = ExcelImportUtil
					.importExcelMore(file.getInputStream(), KaTaskDO.class,
							importParams);
			//处理数据
			List<KaTaskDO> successList = result.getList();
			List<KaTaskDO> failList = result.getFailList();
			List<KaTaskDO> list = new ArrayList<KaTaskDO>();
			List<KaTaskDO> updateList = new ArrayList<KaTaskDO>();

			
			// 自定义遍历校验(将失败的和重复的分别放入集合中)
			for (KaTaskDO kaTaskDO : successList) {
			}
	
			
		    if (CollectionUtils.isNotEmpty(failList)) {
						req.getSession().getServletContext()
								.setAttribute("failList", failList);
					}
				    if (CollectionUtils.isNotEmpty(repeatList)) {
						req.getSession().getServletContext()
								.setAttribute("repeatList", repeatList);
					}
				    
			// 暂时放入，前端判断用
			map.put("failList", failList);
			map.put("repeatList", repeatList);
			// 操作数据库
			if (list.size() != 0 && repeatList.size() == 0) {
				// 数据覆盖
				if (updateList.size() != 0) {
					taskInfoDao.updateOldData(updateList);
				}
				int count = taskInfoDao.insertKATask(list);
				if (count != 0) {
					R.ok("导入成功");
				} else {
					R.error("导入失败");
				}
			}
		} catch (IOException e) {
			log.error(e.getMessage(), e);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		
			return R.ok(map);
		
}

导出失败的数据，(重复数据和这个一样，可以合并)

	@Override
	public void exportFail(HttpServletResponse response, List<KaTaskDO> vo,HttpServletRequest request) {
		Map<String, Object> map = new HashMap<String, Object>();
		List<KaTaskDO> failList = (List<KaTaskDO>) request
				.getSession().getServletContext().getAttribute("failList");
		List<KaTaskDO> repeatList = (List<KaTaskDO>) request
				.getSession().getServletContext().getAttribute("repeatList");
		try {
			// 设置响应输出的头类型
			response.setHeader("content-Type", "application/vnd.ms-excel");
			// 下载文件的默认名称
			response.setHeader("Content-disposition", "attachment; filename="
					+ URLEncoder.encode("页签名字", "UTF-8") + ".xls");
			response.setCharacterEncoding("UTF-8");
			ExportParams exportParams = new ExportParams();
			exportParams.setSheetName("页签名字");
			// exportParams.setDataHanlder(null);//和导入一样可以设置一个handler来处理特殊数据
			Workbook workbook = null;
			if (CollectionUtils.isNotEmpty(failList)) {
				 workbook = ExcelExportUtil.exportExcel(exportParams, KaTaskDO.class, getErrorData(failList));
				}
				if (CollectionUtils.isNotEmpty(repeatList)) {
					 workbook = ExcelExportUtil.exportExcel(exportParams, KaTaskDO.class, getErrorData(repeatList));
				}
			OutputStream os = response.getOutputStream();
			workbook.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
	}