大数据量excel读取后台处理

线程池用于异步处理
public class ConcurrentHelper {
   public static final int CORE_THREAD_COUNT = 4;
   public static final int MAX_THREAD_COUNT = 20;
   public static final long KEEP_ALIVE_TIME = 60L;
   public static final int MAX_QUEUE_SIZE = 100;
   
   private static final ExecutorService EXECUTOR_SERVICE = new ThreadPoolExecutor(CORE_THREAD_COUNT, MAX_THREAD_COUNT, KEEP_ALIVE_TIME,
         TimeUnit.SECONDS, new LinkedBlockingQueue(MAX_QUEUE_SIZE), new ThreadPoolExecutor.CallerRunsPolicy());

   public static void doInBackground(Runnable task) {
      EXECUTOR_SERVICE.submit(task);
   }

   public static <T> Future<T> doInBackground(Callable<T> task) {
      return EXECUTOR_SERVICE.submit(task);
   }

   public static void destory() {
      EXECUTOR_SERVICE.shutdown();
   }
}

//开始执行Excel解析

new CallRecordImportHandler(this, record, postage, userInfoMap, specialPostage).execute();

//异步处理

private final CountDownLatch latch = new CountDownLatch(2);

public void execute() {

        ConcurrentHelper.doInBackground(() -> {
            long timestamp = System.currentTimeMillis();
            File tempFile = new File(service.getTempDir(), timestamp + ".xlsx");
            try {
                FileUtils.downloadRemoteFile(tempFile, record.getUrl());
                LOGGER.debug("download remote file success, filePath = {}, size = {}", tempFile.getPath(), tempFile.length());
                CallRecordSaxParseHandler parseHandler = new CallRecordSaxParseHandler(this, tempFile);
                CallRecordDataSaveHandler saveHandler = new CallRecordDataSaveHandler(this, this.service);
                parseHandler.execute();
                saveHandler.execute();
                latch.await();
                LOGGER.debug("解析excel和导入数据任务结束，更新导入记录...");
                if (!paresSuccess) {
                    throw new ParseExcelException("解析excel文件出错！");
                }
                if (!saveSuccess) {
                    throw new SaveExcelDataException("excel文件导入数据库出错！");
                }
                
                LOGGER.debug("开始插入导入结果");
                service.insertIntoImportResult(record);

                LOGGER.info("导入文件结束，本次导入批次：{}", this.record.getId());
                
                LOGGER.debug("开始自动结算账单");
                this.service.autoBalance(record.getId(), record.getPlatform(), beginDate, endDate);
                LOGGER.debug("自动结算账单结束");
            } catch (Exception ex) {
                LOGGER.error(ex.getMessage(), ex);
                
            } finally {
                if (tempFile != null && tempFile.exists()) {
                    tempFile.delete();
                }
            }
        });
    }

//负责Excel的读取

public class CallRecordSaxParseHandler implements IReadHandler<CallRecord> {

   private static final Logger LOGGER = LoggerFactory.getLogger(CallRecordSaxParseHandler.class);
   private final File excelFile;
   private final CallRecordImportHandler importHandler;
   private final List<CallRecord> list;
   private int index = 0;
   private int count = 0;

   public CallRecordSaxParseHandler(CallRecordImportHandler importHandler, File excelFile) {
      this.excelFile = excelFile;
      this.importHandler = importHandler;
      this.list = new ArrayList<>();
   }

   public int getCount() {
      return count;
   }
   
   public int getIndex() {
      return index;
   }

   public void execute() {
      ConcurrentHelper.doInBackground(() -> {
         try (FileInputStream input = new FileInputStream(excelFile)) {
            ExcelImportUtil.importExcelBySax(input, CallRecord.class, new ImportParams(), this);
            this.importHandler.setParesSuccess(true);
         } catch (Exception ex) {
            LOGGER.error(ex.getMessage(), ex);
         } finally {
            this.importHandler.setParseFinished(true);
            this.importHandler.getLatch().countDown();
         }
      });
   }

   @Override
   public void handler(CallRecord record) {
      index++;
      String numberCode = record.getNumberCode();
      if(StringUtils.isEmpty(numberCode)) {
         LOGGER.warn("数据第：{}行格式错误，忽略本条记录！", index);
         return;
      }
      if(!importHandler.isBigNumber(record.getNumberCode())) {
         LOGGER.warn("号码：{}非大平台号码，忽略本条记录！", numberCode);
         return;
      }
      
      importHandler.processRecord(record);
      list.add(record);
      count++;
      if (count % 2000 == 0) {
         addDataToQueue();
      }
   }

   @Override
   public void doAfterAll() {
      if (!this.list.isEmpty()) {
         addDataToQueue();
      }
      LOGGER.info("解析excel结束，总共记录 {} records.", count);
   }

   private void addDataToQueue() {
      List<CallRecord> data = new ArrayList<>(list.size());
      data.addAll(list);
      try {
         importHandler.getQueue().put(data);
      } catch (InterruptedException ex) {
         LOGGER.error(ex.getMessage(), ex);
      }
      this.list.clear();
      LOGGER.debug("放数据到队列中,目前队列大小：{}", importHandler.getQueue().size());
   }

}

//负责excel的存储
public class CallRecordDataSaveHandler {
   private static final Logger LOGGER = LoggerFactory.getLogger(CallRecordDataSaveHandler.class);
   
   private final CallRecordImportHandler importHandler;
   private final ImportRecordService service;
   private final ExecutorService processPool;
   private final Semaphore semaphore = new Semaphore(8);
   private volatile boolean hasException = false;

   public CallRecordDataSaveHandler(CallRecordImportHandler importHandler, ImportRecordService service) {
      this.importHandler = importHandler;
      this.service = service;
      this.processPool = new ThreadPoolExecutor(8, 8, 60,
         TimeUnit.SECONDS, new LinkedBlockingQueue(), new ThreadPoolExecutor.AbortPolicy());
   }

   public void execute() {
      ConcurrentHelper.doInBackground(() -> {
         try {
            while(!(importHandler.isParseFinished() && importHandler.getQueue().isEmpty())) {
               LOGGER.debug("从队列中获取数据...");
               List<CallRecord> data = this.importHandler.getQueue().poll(10, TimeUnit.SECONDS);
               if(data != null) {
                  LOGGER.debug("从队列中获取到数据，目前队列大小：{}", importHandler.getQueue().size());
                  semaphore.acquire();
                  processPool.submit(()->{
                     try {
//取出数据后进行存储                        service.batchCreateCallRecord(importHandler.getRecord().getId(), data);
                     } catch(Exception ex) {
                        LOGGER.error(ex.getMessage(), ex);
                        hasException = true;
                     } finally {
                        data.clear();
                        semaphore.release();
                     }
                  });
               }
            }
            LOGGER.debug("等待处理线程执行结束...");
            processPool.shutdown();
            processPool.awaitTermination(30, TimeUnit.MINUTES);
            importHandler.setSaveSuccess(!hasException);
         } catch (Exception ex) {
            LOGGER.error(ex.getMessage(), ex);
         } finally {
            importHandler.getLatch().countDown();
         }
      });
   }
   
}