SpringBoot导入导出

  1. <dependency>
  2.   <groupId>cn.hutool</groupId>
  3.   <artifactId>hutool-all</artifactId>
  4.   <version>5.3.2</version>
  5. </dependency>
  6. <dependency>
  7.   <groupId>org.apache.poi</groupId>
  8.   <artifactId>poi-ooxml</artifactId>
  9.   <version>4.1.2</version>
  10. </dependency>

image.pngimage.png

导出Excel模版

  1.     @GetMapping("/getMaterialTypeImportTemplate")
  2.     public void getMaterialTypeImportStencil(HttpServletResponse response) {
  3.         materialTypeService.getMaterialTypeImportTemplate(response);
  4.     }
  1.     @Override
  2.     public void getMaterialTypeImportTemplate(HttpServletResponse response) {
  3.         String name = "物料类型清单-模板.xlsx";
  4.         InputStream inputStream = ResourceUtil.getStream("classpath:template/" + name);
  5.         try {
  6.             response.setContentType("application/octet-stream");
  7.             String gbkName = new String(name.getBytes(CharsetUtil.GBK), StandardCharsets.ISO_8859_1);
  8.             response.setHeader("Content-disposition", "attachment;filename=" + gbkName);
  9.             response.flushBuffer();
  11.             write(response, inputStream);
  12.         } catch (IOException e) {
  13.             throw new RuntimeException(e);
  14.         }
  15.     }
  18.     /**
  19.      * 将输入流写入response的输出流
  20.      *
  21.      * @param response    {@link HttpServletResponse}
  22.      * @param inputStream 文件输入流
  23.      */
  24.     private void write(HttpServletResponse response, InputStream inputStream) {
  25.         // 写入response
  26.         OutputStream out = null;
  27.         try {
  28.             out = response.getOutputStream();
  29.             int len;
  30.             byte[] b = new byte[1024];
  31.             while ((len = inputStream.read(b)) != -1) {
  32.                 out.write(b, 0, len);
  33.             }
  34.             out.flush();
  35.         } catch (IOException e) {
  36.             e.printStackTrace();
  37.         } finally {
  38.             try {
  39.                 if (out != null) {
  40.                     out.close();
  41.                 }
  42.             } catch (Exception e) {
  43.                 e.printStackTrace();
  44.             }
  45.         }
  46.     }

导入Excel

  1.     @PostMapping("/importMaterialType")
  2.     public ResultModel<?> importMaterialType(
  3.             @RequestParam("orgId") Long orgId,
  4.             @RequestPart("excel") MultipartFile multipartFile) {
  5.         return ResultUtil.success("成功", materialTypeService.importMaterialType(multipartFile, orgId));
  6.     }
  1.     @Override
  2.     public boolean importMaterialType(MultipartFile multipartFile, Long orgId) {
  3.         Long userId = ThreadLocalHolder.getSessionId();
  5.         String originalFilename = multipartFile.getOriginalFilename();
  6.         String suffix = Objects.requireNonNull(originalFilename).substring(originalFilename.lastIndexOf(".") + 1).toLowerCase(Locale.ENGLISH);
  7.         if (!"xlsx".equals(suffix) && !"xls".equals(suffix)) {
  8.             throw new CustomException("文件类型不合法, 需为'xlsx'或'xls'");
  9.         }
  11.         InputStream inputStream;
  12.         try {
  13.             inputStream = multipartFile.getInputStream();
  14.         } catch (IOException e) {
  15.             throw new RuntimeException(e);
  16.         }
  18.         ExcelReader reader = ExcelUtil.getReader(inputStream);
  19.         reader.addHeaderAlias("一级分类", "firstTypeName");
  20.         reader.addHeaderAlias("二级分类", "secondTypeName");
  21.         reader.addHeaderAlias("计量单位", "materialTypeUnit");
  22.         reader.addHeaderAlias("数量", "amount");
  23.         List<BimMaterialTypeImportVo> list = reader.readAll(BimMaterialTypeImportVo.class);
  24.         // ……
  25.     }