记录 SpringBoot Jar包部署上传文件，预览文件（访问服务器文件）和 POI 解析 Excel

最新推荐文章于 2024-06-01 09:15:00 发布

code monkey

最新推荐文章于 2024-06-01 09:15:00 发布

阅读量315

点赞数

文章标签： poi springboot 文件上传

本文链接：https://blog.youkuaiyun.com/wa123579/article/details/112838964

版权

上传文件

FileUtil 工具类

public class FileUtil {

    public static String getUploadFilePath(String path) throws FileNotFoundException {
        File pathRoot = new File(ResourceUtils.getURL("classpath:").getPath());
        if(!pathRoot.exists()) {
            pathRoot = new File("");
        }
        String realPath = pathRoot.getAbsolutePath().replace("%20"," ")
                .replace('/', '\\')+"\\upload\\"+path+"\\";
        File f = new File(realPath);
        if(!f.exists()) {
            f.mkdirs();
        }
        return realPath;
    }
}

上传接口

@PostMapping("/import")
    public ApiResponse enterpriseImport(@NotNull(message = "文件不能为空") MultipartFile file, HttpServletRequest request) throws IOException {
        if (file.isEmpty()) {
            throw new FileException(CommonCode.FILE_EMPTY);
        }
        String path = FileUtil.getUploadFilePath("excel");
        String fileName = file.getOriginalFilename();
        File targetFile = new File(path + fileName);
        file.transferTo(targetFile);
        return enterpriseService.importEnterprise(targetFile);
    }

ExcelUtil 工具类

public class ExcelUtil {

  private static final String EXCEL_XLS = "xls";
  private static final String EXCEL_XLSX = "xlsx";


  public static Workbook getWorkbook(File file) throws IOException {
      FileInputStream fileStream = new FileInputStream(file);
      if(file.getName().endsWith(EXCEL_XLS)) {
          return new HSSFWorkbook(fileStream);
      } else if(file.getName().endsWith(EXCEL_XLSX)){
          return new XSSFWorkbook(fileStream);
      }
      return null;
  }

  /**
   * 读取单元格的值
   * @param cell 单元格
   * @return string
   */
  public static String getCellValue(Cell cell) {
      Object result = "";
      if (cell != null) {
          switch (cell.getCellType()) {
              case Cell.CELL_TYPE_STRING:
                  result = cell.getStringCellValue();
                  break;
              case Cell.CELL_TYPE_NUMERIC:
                  result = cell.getNumericCellValue();
                  break;
              case Cell.CELL_TYPE_BOOLEAN:
                  result = cell.getBooleanCellValue();
                  break;
              case Cell.CELL_TYPE_FORMULA:
                  result = cell.getCellFormula();
                  break;
              case Cell.CELL_TYPE_ERROR:
                  result = cell.getErrorCellValue();
                  break;
              case Cell.CELL_TYPE_BLANK:
                  break;
              default:
                  break;
          }
      }
      return result.toString();
  }
}

POI 解析 Excel

// 错误信息
      StringBuilder errorMsg = new StringBuilder();
      List<Enterprise> list = new ArrayList<>();
      Workbook workbook = null;
      try {
          workbook = ExcelUtil.getWorkbook(file);
      } catch (IOException e) {
          throw new FileException(CommonCode.FILE_ERROR);
      }
      if (workbook == null) {
          throw new FileException(CommonCode.FILE_ERROR);
      }
      // 只获取第一个
      Sheet sheet = workbook.getSheetAt(0);
      // 从第二行开始，第一行为表头
      for (int i = 1; i <= sheet.getLastRowNum(); i++) {
          try {
              // 业务逻辑
              list.add(enterprise);

          } catch (Exception e) {
              e.printStackTrace();
              errorMsg.append("【第").append(i).append("行 数据解析失败】");
          }
      }
      // 检查文件解析是否有误
      if (StringUtils.isNotBlank(errorMsg.toString()) || list.size() == 0) {
          // TODO 抛出异常
          return new ApiFailResponse(errorMsg.toString());
      }
      mapper.insertBatch(list);
      return new ApiMessageResponse(CommonCode.SUCCESS);
  }

MyBatis 批量插入

 <insert id="insertBatch">
        insert into
            gs_enterprise(xxx,xxx,xxx)
        values
        <foreach collection ="list" item="xx" separator =",">
            (#{xx.xxx},#{xx.xxx},#{xx.xxx})
        </foreach >
    </insert>

预览文件（访问服务器本地文件）

SpringMVC 配置

@Configuration
public class WebMvcConfig implements WebMvcConfigurer {

    private String path = FileUtil.getUploadFilePath("pdf");

    public WebMvcConfig() throws FileNotFoundException {
    }

    @Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) {
        registry.addResourceHandler("/file/preview/*")
                .addResourceLocations("file:"+path);
    }
}