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

谁借莪１个温暖的怀抱￠ 2022-07-11 05:48 218阅读 0赞

多线程解决导出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";
      }