1.添加依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17-beta1</version> </dependency>
2.新建Dto类 用于导入 导出
@Data @Accessors(chain = true) public class UserExport{ @Excel(name = "姓名",width = 40) private String name; @Excel(name = "年龄",width = 25) private String age; }
3.导出
//response 要传入的参数 public void getUpload(HttpServletResponse response, )throws IOException { //执行查询的SQL查询出来结果 exports 为封装的实体对象 如 User //在该对象字段上注释 @Excel(name = "导出文档的列名",width = 15) List<UserExport> exports = UserMapper.selectUser(param); ExportParams params = new ExportParams("用户数据" + createDate, "用户数据下载", ExcelType.XSSF); Workbook sheets = ExcelExportUtil.exportExcel(params, UserExport.class, exports); String fileName = "用户数据.xlsx"; //取得输出流 OutputStream out = response.getOutputStream(); //清空输出流 response.reset(); response.setCharacterEncoding("UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;fileName=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1")); //写入文件 sheets.write(out); sheets.close(); //关闭流 out.close();
4.本地文件导入
public void testImport() throws Exception{ ImportParams params = new ImportParams(); params.setTitleRows(0); params.setHeadRows(1); params.setNeedVerfiy(true); //文件本地所在位置 文件对应 第一行应对应实体的字段名 如 第一列姓名 上面应为 姓名 File file = new File("E:\root\xxx.xlsx"); FileInputStream fileInputStream = new FileInputStream(file); List<UserExport> list = ExcelImportUtil.importExcel(fileInputStream, UserExport.class, params); for (UserExport export : list) { UserExport Userexport = userExportMapper.selectOne(new QueryWrapper<UserExport>().lambda() .eq(UserExport ::getDeleteFlag,0).eq(UserExport ::getId,export.getId)); if(Userexport !=null){ continue; } UserExport user= new UserExport (); user.setId(UUIDUtils.getId()); user.setDeleteFlag(0); user.setCreatedTime(new Date()); user.setUpdatedTime(new Date()); BeanUtils.copyProperties(export,user); userExportMapper.insert(user); } }
5.上传文件导入
public Map importExpress(@RequestParam("file") MultipartFile file) throws Exception { Map<String, Object> rsp = new HashMap<>(); if (file == null || file.isEmpty()) { log.info("导入失败,缺少file文件"); rsp.put("retCode", -1); rsp.put("retMessage", "导入失败,缺少file文件"); return rsp; } ImportParams param = new ImportParams(); param.setTitleRows(0); param.setHeadRows(1); AtomicInteger failNum = new AtomicInteger(0); AtomicInteger successNum = new AtomicInteger(0); InputStream inputStream = file.getInputStream(); List<userExport> list = new ArrayList<>(); try { list = ExcelImportUtil.importExcel(inputStream, userExport.class, param); }catch (Exception e){ e.printStackTrace(); } finally { if (inputStream != null){ try { inputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } log.info("导入{}条数据", list.size()); for (userExport userexport : list) { UserExport Userexport = userExportMapper.selectOne(new QueryWrapper<UserExport>().lambda() .eq(UserExport ::getDeleteFlag,0).eq(UserExport ::getId,export.getId)); if(Userexport !=null){ failNum.incrementAndGet(); log.info("导入用户信息,用户:{} 已存在",Userexport.getId()); continue; } UserExport user= new UserExport (); user.setId(UUIDUtils.getId()); user.setDeleteFlag(0); user.setCreatedTime(new Date()); user.setUpdatedTime(new Date()); BeanUtils.copyProperties(export,user); userExportMapper.insert(user); successNum.incrementAndGet(); } rsp.put("retCode", "0"); rsp.put("successNum", successNum); rsp.put("failNum", failNum); return rsp; } }