excel多线程导入

excel多线程导入

{
    /**
     * 线程池参数
     */
    public static final int CORE_POOL_SIZE = 3;

    public static final int MAXIMUM_POOL_SIZE = 200;

    public static final long KEEP_ALIVE_TIME = 0L;

    public static final int BLOCK_QUEUE_CAPACITY = 1024;

    public static final String THREAD_FACTORY_BUILDER_NAME = "excel-network-read-pool-%d";
    /**
     * workbook 参数
     */
    public static final int ONE = 1;

    public static final int ZERO = 0;

    @Autowired
    private AssetInternetThingsManager assetInternetThingsManager;

    /**
     * 
     *
     * @param empNo
     * @param tenantId
     * @param file
     * @return
     * @throws Exception
     */
    @Override
    public ServiceData readExcelAndMatch(String empNo, Integer tenantId, MultipartFile file) throws Exception {
        //文件校验
        
        //workbook 校验
        ServiceData sheetResult = ExcelUtils.validExcelSheet(file);
        if(Objects.nonNull(sheetResult)){
            return sheetResult;
        }
        //校验通过，多线程读取数据
        try (InputStream inputStream = file.getInputStream()){

            XSSFWorkbook workBook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workBook.getSheet(ExcelUtils.TEMPLATE);
            //总行数
            int totalRow = sheet.getPhysicalNumberOfRows();
            //取第一行
            Row firstRow = sheet.getRow(ONE);
            if(Objects.isNull(firstRow)){
                return CommonUtils.createBusErrServiceData(Constants.NULL_MESSAGE_BOX);
            }
            //总列数
            int columnCount = sheet.getRow(ZERO).getLastCellNum();
            //阿里扫描对应
            ThreadFactory namedThreadFactory = new ThreadFactoryBuilder().setNameFormat(THREAD_FACTORY_BUILDER_NAME).build();
            //Common Thread Pool
            ExecutorService pool = new ThreadPoolExecutor(CORE_POOL_SIZE, MAXIMUM_POOL_SIZE, KEEP_ALIVE_TIME, TimeUnit.MILLISECONDS,
                    new LinkedBlockingQueue<Runnable>(BLOCK_QUEUE_CAPACITY),
                    namedThreadFactory,
                    new ThreadPoolExecutor.AbortPolicy());
            //开多线程提高性能，Guava版本
            ListeningExecutorService executorService = MoreExecutors.listeningDecorator(pool);
            List<AssetInternetThingsExcel> excelList = new ArrayList();
            List<AssetInternetThingsWithBLOBs> addList = new ArrayList();
            List<String> deleteList = new ArrayList<>();
            StringBuilder errorMsg= new StringBuilder();
            try {
                List<ListenableFuture<RowValidResult>> listenableFutures = new ArrayList();
                for (int i = ONE; i < totalRow; i++) {
                    Row row = sheet.getRow(i);
                    ListenableFuture<RowValidResult> future = executorService.submit(() -> validRowData(row, columnCount));
                    listenableFutures.add(future);
                }
                //等待成功封装结果
                ListenableFuture<List<RowValidResult>> results = Futures.allAsList(listenableFutures);
                for (RowValidResult rowValidResult : results.get()) {
                    if (rowValidResult.isSuccess()) {
                        excelList.add((AssetInternetThingsExcel) rowValidResult.getData());
                    } else {
                        errorMsg.append(rowValidResult.getErrorMessage());
                    }
                }
            }finally {
                executorService.shutdown();
            }
            //处理结果
            if(StringUtils.isNotEmpty(errorMsg)){
                return CommonUtils.createBusErrServiceData(errorMsg.toString());
            }
            dealExcelResult(excelList,addList,deleteList,empNo);
            //批量删除
            if(CollectionUtils.isNotEmpty(deleteList)){
                assetInternetThingsManager.deleteList(deleteList);
            }
            //批量插入
            if(CollectionUtils.isNotEmpty(addList)){
                assetInternetThingsManager.addList(addList);
            }
        }
        return new ServiceData();
    }

    /**
     * 处理结果
     */
    private void dealExcelResult(List<AssetInternetThingsExcel> excelList, List<AssetInternetThingsWithBLOBs> addList, List<String> deleteList, String empNo) {
        Date nowDate = new Date();
        //list 使用 资产编号去重
        deleteList.addAll(excelList.stream().map(AssetInternetThingsExcel::getAssetNo).distinct().collect(Collectors.toList()));
        List<AssetInternetThingsExcel> duplicateList = excelList.stream().collect(Collectors.collectingAndThen(
                Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(AssetInternetThingsExcel::getAssetNo))), ArrayList::new));
        
    }


  
    private RowValidResult validRowData(Row row, int cellCount) {
      
        List<String> errMsgBox = new ArrayList<>();
        AssetInternetThingsExcel vo = parseRow(row, cellCount, errMsgBox);
        if (!CollectionUtils.isEmpty(errMsgBox)) {
            return new RowValidResult(vo, false, StringUtils.join(errMsgBox, Constants.SEMICOLON));
        }
        return new RowValidResult(vo);
    }

   
    private AssetInternetThingsExcel parseRow(Row row, int columnCount, List<String> errMsgBox) {
        AssetInternetThingsExcel vo = new AssetInternetThingsExcel();
        for (int colIdx = 0; colIdx < columnCount; colIdx++) {
            AssetInstrumentMeterExcelFieldEnum field = AssetInstrumentMeterExcelFieldEnum.getByColumn(colIdx);
            Cell cell = row.getCell(colIdx, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            vo.setRowIndex(cell.getRowIndex() + 1);
            String value = ExcelUtils.getCellStringValue(cell);
            switch (field) {
                case ASSET_SUB_CATEGORY:
                    vo.setAssetSubCategory(value);
                    ckStrIsNull(vo.getRowIndex(), AssetInstrumentMeterExcelFieldEnum.ASSET_SUB_CATEGORY, value, errMsgBox);
                    break;
                }
                }
                }