记录万级数据Excel导入优化

实现方式：Poi+数据校验+多线程+异步批量分组插入

poi依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.9</version>
        </dependency>

　　

 

参数校验：非空，或正则校验

class DownloadTemplateVO extends IExcelModel{
  //卡
  @Excel(name="号")
  @NotBlank(message = "号不能为空")
  @BeanProperty
  var no:String =_

  //登记时间
  @Excel(name="登记时间" ,format = "yyyy-MM-dd hh:mm:ss")
  @BeanProperty
  @NotNull
  var enterTime:Date =_

  //失败原因  用于设置失败原因
 // @Excel(name="失败原因")
  @BeanProperty
  var errorMsg:String =_

}

　　

 

controller

 @PostMapping(value = Array("/importExcel"), produces = Array(MediaType.MULTIPART_FORM_DATA_VALUE))
  def importExcel(entityVO: CardVO, @RequestParam("multipartFile") multipartFile: MultipartFile,
                  request: HttpServletRequest, response: HttpServletResponse) {
    //获取文件后缀
    val suffix = multipartFile.getOriginalFilename().substring(multipartFile.getOriginalFilename().lastIndexOf(".") + 1)
    //文件格式
    val EXT_FILE_NAME = "xlsx"
    if (!EXT_FILE_NAME.equals(suffix)) {
      return write(response, BaseResponse.error("文件格式不正确"))
    }
    val importParams: ImportParams = new ImportParams()

    // 需要验证
    importParams.setNeedVerify(true);
    val list = new java.util.ArrayList[SuccessCardVO]()

    val result: ExcelImportResult[DownloadTemplateVO] = ExcelImportUtil.importExcelMore(multipartFile.getInputStream,
      classOf[DownloadTemplateVO], importParams)

    //验证通过List
    val successList: util.List[DownloadTemplateVO] = result.getList
    val saveList=new CopyOnWriteArrayList[Card]()
    //创建线程池
    val executor=Executors.newFixedThreadPool(1000)
    //记录成功、失败数
    val successTotal=new java.util.concurrent.atomic.AtomicInteger()
    val failTotal=new java.util.concurrent.atomic.AtomicInteger()
    successList.forEach(
      downloadTemplateVo => {
       val thread=new Thread(){
        override def run(): Unit ={
            val hdqryCard = hdqryService.getEntityByCardNo(downloadTemplateVo.cardNo)
          //查询数据库不存在 失败数自增
            if(hdqryCard==null){
              failTotal.incrementAndGet()
            }
            val card: Card = new Card
            copyHdqryCardEntityToCardEntity(hdqryCard, card)
            card.setCardNo(downloadTemplateVo.cardNo)
            card.setOperationUserId(getOptId.toString)
            card.setEnterTime(entityVO.enterTime)
            val createTime = new Date()
            card.setCreateTime(createTime)
            card.setIsTimeout("0")
            card.setIsInform("0")
            card.setCardTreatmentState("0")

            saveList.add(card)
          //成功数自增
          successTotal.incrementAndGet()

            val successCardVO = new SuccessCardVO
            BeanUtils.copyProperties(downloadTemplateVo, successCardVO)
            successCardVO.setCreateTime(createTime)
            successCardVO.setResult("上传成功")
            list.add(successCardVO)
          }
        }
        executor.submit(thread)
      })
    //关闭线程池
    executor.shutdown()
    //异步批量插入
    service.saveList(saveList)

    //验证失败List
    val failList: util.List[DownloadTemplateVO] = result.getFailList
    failList.forEach(
      downloadTemplateVo => {
        val s = new SuccessCardVO
        BeanUtils.copyProperties(downloadTemplateVo, s)

        val failWorkbook = result.getFailWorkbook
        val sheet = failWorkbook.getSheetAt(0)
        //遍历获取失败原因
        for (a <- 1 until sheet.getPhysicalNumberOfRows) {
          val row = sheet.getRow(a)
          val cell = row.getCell(row.getLastCellNum - 1)
          if (cell != null && cell != "") {
            cell.setCellType(CellType.STRING)
            val value = cell.getStringCellValue()
            s.setResult(value)
          }
        }
        list.add(s)
      }
    )


    val re = new util.HashMap[String, Object]()
    re.put("result", list)
    re.put("total", list.size().toString)
    re.put("successTotal",successList.size().toString)
    re.put("failTotal",failList.size().toString)
    write(response, BaseResponse.success(re))

  }


}

　　

//异步批量插入   启动类添加注解 @EnableAsync

  @Async
  def saveList(list: List[Card]): BaseResponse[Any] = {
    //在这里将数组拆分成多个数组插入 代码省略
    mapper.insertList(list)
    BaseResponse.success()
  }