8个线程运算excel慢怎么解决？多线程解决导出excel性能问题

8个线程运算excel慢怎么解决？多线程解决导出excel性能问题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(M

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