使用EasyExcel导入、根据模板下载(附前后端代码)

使用以上导包如果报错 NoClassDefFoundError，可能需要引入cglib包

 1、excel的导入

前端代码：

:

由于前端type="file"的样式固定，可以隐藏input，通过其他按钮点击触发上传，用户上传文件后input的值发生变化触发change事件调用ajax

	$('#fileId').change(function(){
			//获取用户上传文件
			var files = $('#fileId')[0].files
			var i_type = 0;
			//定义传参格式
			var data = new FormData();
			data.append('avatar', files[0]);
			data.append('i_type',i_type);
			$.ajax({
				url: cp + 'mgt/depotwares/import.do',
				type: 'POST',
				data: data,
				cache: false,
				processData: false,
				contentType: false,
				success:function (data) {
					var result = JSON.parse(data);
					$("#fileId").val('');
					if(result.s_code == "000000"){
						var sucess = result.o_data[0]
						var fail = result.o_data[1]
					layer.confirm(("导入成功! 成功:"+sucess+",失败:"+fail),{
						btn: ['关闭','下载失败信息'] //按钮
					}, function(){//是
						layer.closeAll();
					},function(){//
						let downloadurl="http://localhost:8081/fhxhcps//mgt/depotwares/downloadfail.do";
						let label = $("<a>");
						label.prop("href",downloadurl);
						$("body").append(label)
						label[0].click();
						label.remove();
					});
					}else{
						layer.msg(result.s_msg)
					}
				}
			});

		});

 后端代码：

controller层接收

	public void WaresBatchUpdate(HttpServletRequest request,
								 HttpServletResponse response,
								 @RequestParam(required = false, value = "avatar") MultipartFile filename,
								 HttpSession session,  Model model,String i_type) throws IOException

 实现类：

	ResultMap resMap = new ResultMap();
		//更新条数结果返回
		List list = new ArrayList();
		//记录所有excel数据
		List lists = new ArrayList();
		//用于写入保存失败数据
		List<ExcelVO> failList = new ArrayList();
		List<PvdSettleVO> pvdfailList = new ArrayList<>();
		List<IWaresVO> iWaresList = new ArrayList<>();
		//记录成功条数
		int sucess = 0;
		//记录失败条数
		int fail = 0;
		//判断当前修改税务编码还是供应商
		Workbook workbook = null;

  Properties properties = PropertiesUtil.load("pdf_config/exportPdf.properties");
        String wordGenerDir = properties.getProperty("excel.taxno.savepath");
        String name = Str(userMap.get("s_name"),"");
        String sUser = Str(userMap.get("s_user"),"");
        Date now = new Date();
        SimpleDateFormat outFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String time = outFormat.format(now);
        String newFile = name+sUser+"DT"+time +file.getOriginalFilename();
        String path2 = wordGenerDir+newFile;

        ResultMap resMap = new ResultMap();
        File foler = null;
        // 读取excel文件
        FileInputStream in = null;
        // 获取工作簿
        Workbook workbook = null;
        //获取路径
        foler = new File(path2);
        String path = foler.getAbsolutePath();
        String filetype = path.substring(path.lastIndexOf("."));

        try {

            file.transferTo(foler);
            File dest = new File(path);
            in = new FileInputStream(path);
            if (!foler.getParentFile().exists()) {
                //创建
                foler.getParentFile().mkdirs();
            }
            if (filetype.equals(".xls")) {
                workbook = new HSSFWorkbook(in);
            } else if (filetype.equals(".xlsx")) {
                workbook = new XSSFWorkbook(in);
            } else {
                resMap.setS_code(Constants.RES_MSG_ERROR);
                resMap.setS_msg("文件类型必须是xls或xlsx:" + file.getOriginalFilename());
                dest.delete();
                return resMap;
            }
        }catch (Exception e){
            System.out.println("文件异常");
        }finally {
            try {
                if (in != null) {
                    in.close();
                }
                if (workbook != null) {
                    workbook.close();
                }

            } catch (IOException e) {
                resMap.setS_msg("关闭流失败");
            }
        }
        try {
			//获取sheet1
			Sheet sheet = workbook.getSheetAt(0);
			//获取表中内容
			int rowCount = sheet.getPhysicalNumberOfRows();
			for (int rowNum = 1; rowNum < rowCount; rowNum++) {
				String value1 = "";
				String value2 = "";
				String value3 = "";
				Row rowData = sheet.getRow(rowNum);
				Map map = new HashMap();
				if (rowData != null) {
					Cell cell1 = rowData.getCell(0);
					Cell cell2 = rowData.getCell(1);
					Cell cell3 = rowData.getCell(2);
					value1 = ExcelUtils.getCellValue(cell1);
					value2 = ExcelUtils.getCellValue(cell2);
					value3 = ExcelUtils.getCellValue(cell3);
					map.put("col1", value1);
					map.put("col2", value2);
					map.put("col3", value3);
					lists.add(map);
				}
			}


				//excel写入未导入成功信息
                    //repath即为存放文件路径(绝对路径+名称)
					ExcelWriterBuilder write = EasyExcel.write(repath);
					ExcelWriterSheetBuilder sheet1 = write.sheet();
					sheet1.doWrite(failList);

2.Excel的模板下载

这里用的是在本地下载文件，指定一个文件路径创建excel模板，通过流的方式直接下载

前端代码：直接通过访问接口方式建立下载链接

$("#downloadWare").click(function () {
			$.ajax({
				url: cp + 'mgt/depotwares/downloadWareTemplete.do',
				method: 'get',
				type:"application/vnd.ms-excel",
				responseType:"blob",
				success: function(data){
					let downloadurl="http://localhost:8081/depotwares/downloadWareTemplete.do";
					let label = $("<a>");
					label.prop("href",downloadurl);
					$("body").append(label)
					label[0].click();
					label.remove();
					layer.msg("下载成功")
				}
			})
		})

后端代码：

try {
				// path是指下载的文件的路径。
				File file = new File(ExcelUtils.getFolerPath());
				// 取得文件名。
				String filename = file.getName();
				// 以流的形式下载文件。
				InputStream fis = new BufferedInputStream(new FileInputStream(ExcelUtils.getFolerPath()));
				byte[] buffer = new byte[fis.available()];
				fis.read(buffer);
				fis.close();
				// 清空response
				response.reset();
				// 设置response的Header
			if(request.getHeader("user-agent").toLowerCase().indexOf("firefox")>-1){
				response.setHeader("Content-Disposition","attachment;filename=" +new String(filename.getBytes("utf-8"),"ISO-8859-1"));
			}else {
				response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
			}
				response.addHeader("Content-Length", "" + file.length());
				OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
				response.setContentType("application/octet-stream");
				toClient.write(buffer);
				toClient.flush();
				toClient.close();
		} catch (Exception e) {
			response.reset();
			response.setContentType("application/json");
			response.setCharacterEncoding("utf-8");
			resMap.setS_code(Constants.RES_CODE_ERROR);
			resMap.setS_msg(Constants.RES_MSG_ERROR);
	
		}