将 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));
- }
复制代码
来源: https://juejin.im/post/5b581d72e51d4533f528220a