EXCEL大数据量导出的解决方案

将web页面上显示的报表导出到excel文件里是一种很常见的需求。然而，当数据量较大的情况下，excel本身的支持最多65535行数据的问题便凸显出来。下面就给出大数据量导出到excel的解决方 案。

首先，对于数据超过了65535行的问题，很自然的就会想到将整个数据分块，利用excel的多sheet页的功能，将超出65535行后的数据写入到下一个sheet页中，即通过多sheet页的方式，突破了最高65535行数据的限定。

具体做法就是，单独做一个链接，使用JSP导出，在JSP上通过程序判断报表行数，超过65535行后分SHEET写入。这样这个问题就得以解决了。

更进一步地说，在这种大数据量的报表生成和导出中，要占用大量的内存，尤其是在使用TOMCAT的情况下，JVM最高只能支持到2G内存，则会发生 内存溢出的情况。此时的内存开销主要是两部分，一部分是该报表生成时的开销，另一部分是该报表生成后写入一个EXCEL时的开销。由于JVM的GC机制是 不能强制回收的，因此，对于此种情形，我们给出一个变通的解决方案。

首先，将该报表设置起始行和结束行参数，在API生成报表的过程中，分步计算报表（主要性能花费在查询生成报表中），比如一张20万行数据的报表，在生成过程中，可通过起始行和结束 行分4-5次进行。这样，就降低了报表生成时的内存占用，在后面报表生成的过程中，如果发现内存不够，即可自动启动JVM的GC机制，回收前面报表的缓 存。

导出EXCEL的过程，放在每段生成报表之后立即进行，改多个SHEET页为多个EXCEL，即在分步生成报表的同时分步生成EXCEL，则通过 POI包生成EXCEL的内存消耗也得以降低。通过多次生成，同样可以在后面EXCEL生成所需要的内存不足时，有效回收前面生成EXCEL时占用的内 存。

再使用文件操作，对每个客户端的导出请求在服务器端根据SESSIONID和登陆时间生成唯一的临时目录，用来放置所生成的多个EXCEL，然后调 用系统控制台，打包多个EXCEL为RAR或者JAR方式，最终反馈给用户一个RAR包或者JAR包，响应客户请求后，再次调用控制台删除该临时目录。

使用这种方法，首先是通过分段运算和生成，有效降低了报表从生成结果到生成EXCEL的内存开销。其次是通过使用压缩包，响应给用户的生成文件体积 大大缩小，降低了多用户并发访问时服务器下载文件的负担，有效减少多个用户导出下载时服务器端的流量，从而达到进一步减轻服务器负载的效果。

• 创建系统全局线程池

final int numOfCpuCores = Runtime.getRuntime().availableProcessors(); final double blockingCoefficient = 0.9;// 阻尼系数 final int maximumPoolSize = (int)(numOfCpuCores / (1 - blockingCoefficient)); ExecutorService threadPool = new ThreadPoolExecutor(numOfCpuCores, maximumPoolSize,  0L,  TimeUnit.MILLISECONDS,  new LinkedBlockingQueue <Runnable>(), Executors.privilegedThreadFactory(),  new ThreadPoolExecutor.DiscardOldestPolicy()); 复制代码

• 采用多线程分段查询生成报表，同步生成Excel，最后压缩成Zip文件

// 1.这里每个Excel放6万条数据（分6个sheet页，每个1万条），当数据量超过6万条时，数据采用分段查询 //  传递（起始行，结束行）参数，分段查询，即分步生成报表的同时分步生成EXCEL int SINGLE_EXCEPORT_EXCEL_MAX_NUM = 60000; int count = bo.getTotalRecord(); final String fileNameWithTimestamp = fileName + "_" + DateUtil.getNowDateminStr(); if (count > SINGLE_EXCEPORT_EXCEL_MAX_NUM ) { int excelCount = count / SINGLE_EXCEPORT_EXCEL_MAX_NUM + (count % SINGLE_EXCEPORT_EXCEL_MAX_NUM != 0 ? 1 : 0); final CountDownLatch latch = new CountDownLatch(excelCount); final Long userId = user.getUserId(); for(int i = 1; i <= excelCount; i++){ bo.setPageNo(i); bo.setPageSize(SINGLE_EXCEPORT_EXCEL_MAX_NUM); final ParkRecordQryBO itemBo = new ParkRecordQryBO(bo); final int index = i; // 取一线程执行本次查询 threadPool.execute(new Runnable(){ @Override public void run() { Page page = service.getParkRecord(itemBo); List<ParkRecordQryBO> records = page.getResults(); try { // 2.生成单个excel ExportExcelUtil.createOneExcel(fileNameWithTimestamp, index , expRowsList, records, userId); } catch (Exception e) { e.printStackTrace(); } latch.countDown(); } }); } // 3.压缩excel文件并导出 latch.await(); ExportExcelUtil.createZipExport(request, response, fileNameWithTimestamp, userId); 复制代码

• 生成一个Excel存放到本地路径

/**  * @Description: 生成一个Excel存放到本地路径  * @param fileNameWithTimestamp  * @param index  * @param excelHeader  * @param dataList  * @param <T>  * @param userId  */ public static <T> void createOneExcel(final String fileNameWithTimestamp,                                  int index,                              final String[] excelHeader,                               final List<T> dataList,                               Long userId ) { final String localRelativePath = "" + userId + "/"+ fileNameWithTimestamp ; Workbook wb = null; FileOutputStream fos = null; try { // 创建一个Workbook，对应一个Excel文件 wb = writeExcel(dataList, excelHeader); // 生成本地Excel初始文件 Map<String, Object> fileInfo = new HashMap<String, Object>(); FileUtil.createFile(localRelativePath, fileNameWithTimestamp + "_" + index + "_.xls", fileInfo); fos = new FileOutputStream(fileInfo.get("realPath").toString() ); wb.write(fos); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (wb != null)     wb.close(); if (fos != null)      fos.close(); } catch (IOException e) { e.printStackTrace(); } } } 复制代码

• 压缩打包所有Excel文件并导出

/**  * @param request  * @param response  * @param fileNameWithTimestamp  * @param userId  */ public static void createZipExport(HttpServletRequest request,                                  HttpServletResponse response,                                 final String fileNameWithTimestamp,                                  Long userId) throws Exception{ final String localRelativePath = "" + userId + "/"+ fileNameWithTimestamp; // 创建文件夹,先将生成的excel保存到服务器本地目录 // excel文件路径:'/app/file/[userId]/[fileNameWithTimestamp]/[fileNameWithTimestamp_i].xlS' String excelFold = FileUtil.getFileRootPath() + localRelativePath; // zip文件所在路径："/app/file/userId/fileNameWithTimestamp.zip" String zipFold = FileUtil.getFileRootPath() + userId; // 生成zip文件 final String zipFileName = fileNameWithTimestamp +".zip"; FileUtil.createZipFile(excelFold, zipFold, zipFileName); // 创建导出输入流 InputStream is = null; try{ is = new FileInputStream(new File(zipFold + File.separator + zipFileName)); } catch(IOException e){ e.printStackTrace(); } BufferedInputStream bis = new BufferedInputStream(is); // ServletOutputStream out = response.getOutputStream(); BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream()); // 解决设置名称时的乱码问题 String zipName = handleFileName(request, zipFileName); // 设置response参数，可以打开下载页面 response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + zipName); byte[] buff = new byte[2048]; int bytesRead; // Simple read/write loop. while ((bytesRead = bis.read(buff, 0, buff.length)) != -1 ) { bos.write(buff, 0, bytesRead); } bis.close(); bos.close(); // 删除用来临时保存Excel的文件夹及zip文件 FileUtil.deleteDir(new File(zipFold)); }

jvjs---原文地址：https://juejin.im/post/5b581d72e51d4533f528220a