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