SpringBoot+Hutool工具类Excel工具-ExcelUtil实现excel文件的导入导出

Hutool会用的话极大了简化了操作Excel的过程,提高开发效率,废话少说上代码。

excel文件导出:

	public void downLoadFile(UserDto dto,HttpServletResponse response) {
		String outFileName = "测试文件名"+System.currentTimeMillis()+".xlsx";
		String ENCODING="UTF-8";
		List<Map<String, Object>> users= UserRepository.getUserByIds(dto.getIds());
		if(users.isEmpty()){
			return;
		}
		ExcelWriter writer = ExcelUtil.getWriter(true);
	    // 通过工具类创建writer并且进行别名
		assembleWriter(writer);
	    // 准备将对象写入我们的 List
	        writer.write(users, true);
	    try {
	        // 获取我们的输出流
	        final OutputStream output = response.getOutputStream();
			//response.setHeader("Content-Type", "application/octet-stream;charset=utf-8"); // 告诉浏览器输出内容为流
	        //response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(outFileName, "UTF-8"));
	        response.setContentType("application/vnd.ms-excel;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment;filename=" + outFileName + ";filename*=utf-8''"
					+ URLEncoder.encode(outFileName, ENCODING));
	        writer.flush(output, true);
	        writer.close();
	        // 这里可以自行关闭资源或者写一个关闭资源的工具类
	        IoUtil.close(output);
	    } catch (Exception e) {
			LogUtil.error("用户导出失败：", e);
	    }
	}

表头工具类:

private void assembleWriter(ExcelWriter writer) {
		writer.addHeaderAlias("username", "用户名");
		writer.addHeaderAlias("password", "密码");
		writer.addHeaderAlias("home", "家乡");
		

	}

Excel文件导入:

public void importFile(MultipartFile file) throws Exception {
			
		String fileName = file.getOriginalFilename();
	        // 上传文件为空
	        if (StringUtils.isEmpty(fileName)) {
	            throw new WorkException( "没有导入文件");
	        }
	        //上传文件大小为1000条数据
	        if (file.getSize() > 1024 * 1024 * 10) {
				LogUtil.error("文件大小超过10M", new WorkException( "文件大小超过10M"));
	            throw new WorkException( "上传失败: 文件大小不能超过10M!");
	        }
	        // 上传文件名格式不正确
	        if (fileName.lastIndexOf(".") != -1 && !".xlsx".equals(fileName.substring(fileName.lastIndexOf(".")))) {
	            throw new WorkException( "文件名格式不正确, 请使用后缀名为.xlsx的文件");
	        }
	        InputStream  inputStream = file.getInputStream();
	        ExcelReader excelReader = ExcelUtil.getReader(inputStream, "sheet1");
	        assembleReader(excelReader);
	        List<User> all = excelReader.readAll(User.class);
	        for (User user: all) {
				save(user); //数据入库
			}
	}

表头工具类:

private void assembleReader(ExcelReader reader) {
		reader.addHeaderAlias("用户名", "username");
		reader.addHeaderAlias("密码", "password");
		reader.addHeaderAlias("家乡", "home");	
	}