多线程解决导出excel性能问题

多线程解决导出excel性能问题

第一步:Controller  发起导出数据请求

 @RequestMapping(value = "/subpolicy/hdevpayback/exportOtherExcelAll.json")
  public void exportOtherExcelAll(final HttpServletRequest request, final HttpServletResponse response,
      String statDate, String uuId) {
    if (!LockUtils.getLock("exportHardDevExcelAll", 180)) {
      try {
        response.setContentType("application/json;charset=UTF-8");
        response.getWriter().write("其他用户正在导出,请稍后再试...");
        response.getWriter().flush();
      } catch (Exception e) {
        ExceptionUtils.throwBusinessException(getClass(), "返回消息异常", e);
      }
      return;
    }
    try {
      RedisUtil.setKey(uuId, "1", 120);
      String file =
          hardDevPayBackService.exportOtherHardExcelAll(statDate, Constants.EXPORT_MODE_LOCAL, request.getSession()
              .getServletContext().getRealPath("/"));
      FileUtils.exportFile(response, new File(file), "其他设备返款全量导出", "csv");
      RecordLogManager.getInstance().commitOriginalLog(MyJedisCon.getRedisUser(request).getUserName(),
          "HDEVPAYBACK_MENU", "exportAll", "设备返款全部导出");
      RedisUtil.deleteKey(uuId);
    } finally {
      LockUtils.unlock("exportHardDevExcelAll");
    }
  }


第二步:计算总记录数,根据总记录数分配线程数和每个线程处理的记录数。

 @Override
  public String exportOtherHardExcelAll(String statDate, int mode, String filePath) {
    String csvFilePath = filePath + "exportTmp/hardDevAll/";
    if (StringUtils.isBlank(statDate)) {
      statDate = DateUtil.getYesterday();
    }
    String[] fields = {"payState", "payDate", "payNum", "payMoney", "payMark", "isPayBack", "devNumber", "devType",
        "equipNum", "devModel", "agentNumber", "agentName", "schoolAccount", "schoolName", "price", "payAgentName",
        "payAgentNumber", "paySchoolName", "paySchoolAccount", "paySchoolNumber", "formalTime", "bindTime", "category"};
    String[] head = {"返款状态", "返款时间", "返款数量", "返款金额", "说明", "可否返款", "设备编号", "设备类型", "数量", "设备型号", "代理商编号", "代理商名称",
        "幼儿园账号", "幼儿园名称", "价格", "返款代理商", "返款代理商编号", "返款幼儿园", "返款幼儿园账号", "返款幼儿园id", "幼儿园转正时间", "绑定时间", "幼儿园类别"};
    int count = countOtherExcelAll(statDate);//计算总记录数
    int[] indexs = ThreadUtils.getIndex(count, 10000, 5);//根据总记录数分配线程数和每个线程处理的记录数
    CountDownLatch latch = new CountDownLatch(indexs.length - 1);
    for (int j = 1; j < indexs.length; j++) {
      taskExecutor.execute(new HardDevExportThread(latch, Constants.THREAD_TYPE_OTHER_HARD_DEV, j, csvFilePath,
          statDate, indexs[j - 1], indexs[j] - indexs[j - 1], fields));
    }
    String exportFilePath = getOneCsv(latch, csvFilePath, head, indexs.length);
    return exportFilePath;
  }

附:ThreadUtils.getIndex方法,最终结果 [0,10000,20000,30000,40000,50000]

public class ThreadUtils {
    /**
     * 返回每个线程的数据下标始末,限制最大线程数
     * @param size 总数
     * @param minSize 单个线程最小执行数量
     * @param maxTask 最大线程数
     * @return
     */
    public static int[] getIndex(int size, int minSize, int maxTask) {
        int listIndexCount;
        double sizeDb = (double) size, minSizeDb = (double) minSize, maxTaskDb = (double) maxTask;
        if (sizeDb / minSizeDb < maxTaskDb) {
            listIndexCount = Double.valueOf(Math.ceil(sizeDb / minSizeDb)).intValue();
        } else {
            listIndexCount = maxTask;
        }
        int each = Double.valueOf(Math.floor(sizeDb / listIndexCount)).intValue();
        int[] indexs = new int[listIndexCount + 1];
        indexs[0] = 0;
        int totalCount = 0;
        for (int i = 1; i < listIndexCount; i++) {
            indexs[i] = indexs[i - 1] + each;
            totalCount += each;
        }
        // 最后一个线程可能多分担一点
        indexs[listIndexCount] = size - totalCount + indexs[listIndexCount - 1];
        return indexs;
    }
}

第三步:每个任务处理的事情  取数和导出到excel文件(每个线程导出一个文件)

 @Override
  public void run() {
    try {
      int each = 10000;
      int times = Double.valueOf(Math.floor(size / each)).intValue();
      int totalCount = 0;
      for (int i = 0; i < times; i++) {
        int beforeCount = totalCount;
        totalCount += each;
        List list;
        if (DEV_EXPORT_THREAD.equals(threadType)) {//硬件返款和其他硬件返款
          list = hardDevPayBackService.getHardExcelAllByIndex(statDate, beforeCount + startIndex, each);//取数
        }
        else {
          list = hardDevPayBackService.getOtherHardExcelAllByIndex(statDate, beforeCount + startIndex, each);
        }
        ExcelExport.exportCsvLocal(filePath, threadNum.toString(), list, null, fields, i == 0);
      }
      if (totalCount < size) {// 额外冗余each条的limit,以防count有漏掉
        List list;
        if (DEV_EXPORT_THREAD.equals(threadType)) {
          list = hardDevPayBackService.getHardExcelAllByIndex(statDate, totalCount + startIndex, size - totalCount + each);
        }
        else {
          list = hardDevPayBackService.getOtherHardExcelAllByIndex(statDate, totalCount + startIndex, size - totalCount + each);
        }
        ExcelExport.exportCsvLocal(filePath, threadNum.toString(), list, null, fields, totalCount == 0);//写入excel文件
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      countDownLatch.countDown();
    }
  }


第四步:把所有excel文件合并到一个文件

  private String getOneCsv(CountDownLatch latch, String filePath, String[] head, int fileCount) {
    BufferedReader reader = null;
    BufferedWriter writer = null;
    try {
      latch.await();//等待所有线程都完成才执行。
      File file = new File(filePath + "all.csv");
      if (file.exists() && !file.isDirectory()) {
        file.delete();
      }
      file.createNewFile();
      writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(filePath + "all.csv", true), "GB2312"));
      for (int i = 0; i < head.length; i++) {
        writer.write("\"" + head[i] + "\"");
        if (i < head.length - 1) {
          writer.write(",");
        }
      }
      writer.write("\r\n");
      for (int i = 1; i < fileCount; i++) {
        reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath + i + ".csv"), "GB2312"));
        char[] buffer = new char[1024];
        int len = 0;
        while ((len = reader.read(buffer)) > 0) {
          writer.write(buffer, 0, len);
        }
        reader.close();
        writer.flush();
      }
    } catch (Exception e) {
      e.printStackTrace();
      return null;
    } finally {
      try {
        if (reader != null) {
          reader.close();
        }
        writer.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return filePath + "all.csv";
  }







已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页