导读

由于项目需要，处理百万级数据导出问题。

使用


导入依赖

  1.  <!-- 文件导出-->
  2.         <dependency>
  3.             <groupId>org.apache.poi</groupId>
  4.             <artifactId>poi</artifactId>
  5.             <version>3.17</version>
  6.         </dependency>
  7.         <dependency>
  8.             <groupId>org.apache.poi</groupId>
  9.             <artifactId>poi-ooxml</artifactId>
  10.             <version>3.17</version>
  11.         </dependency>

**

工具类

PoiUtil导出工具类

  1. package com.aa.utils;
  3. import com.aa.excel.ExcelConstant;
  4. import com.aa.excel.WriteExcelDataDelegated;
  5. import org.apache.poi.xssf.streaming.SXSSFCell;
  6. import org.apache.poi.xssf.streaming.SXSSFRow;
  7. import org.apache.poi.xssf.streaming.SXSSFSheet;
  8. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  9. import org.slf4j.Logger;
  10. import org.slf4j.LoggerFactory;
  12. import javax.servlet.http.HttpServletResponse;
  13. import java.io.FileOutputStream;
  14. import java.io.IOException;
  15. import java.io.OutputStream;
  16. import java.util.ArrayList;
  17. import java.util.Date;
  20. /**
  21.  * @Author hyanchao
  22.  * @create 2019/11/19 17:19
  23.  * @About POI导出工具类
  24.  */
  25. public class PoiUtil {
  27.     private final static Logger logger = LoggerFactory.getLogger(PoiUtil.class);
  29.     /**
  30.      * 初始化EXCEL(sheet个数和标题)
  31.      *
  32.      * @param totalRowCount 总记录数
  33.      * @param titles        标题集合
  34.      * @return XSSFWorkbook对象
  35.      */
  36.     public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {
  38.         // 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中
  39.         SXSSFWorkbook wb = new SXSSFWorkbook(100);
  41.         Integer sheetCount = ((totalRowCount % ExcelConstant.PER_SHEET_ROW_COUNT == 0) ?
  42.                 (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT) : (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT + 1));
  44.         // 根据总记录数创建sheet并分配标题
  45.         for (int i = 0; i < sheetCount; i++) {
  46.             SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
  47.             SXSSFRow headRow = sheet.createRow(0);
  49.             for (int j = 0; j < titles.length; j++) {
  50.                 SXSSFCell headRowCell = headRow.createCell(j);
  51.                 headRowCell.setCellValue(titles[j]);
  52.             }
  53.         }
  55.         return wb;
  56.     }
  59.     /**
  60.      * 下载EXCEL到本地指定的文件夹
  61.      *
  62.      * @param wb         EXCEL对象SXSSFWorkbook
  63.      * @param exportPath 导出路径
  64.      */
  65.     public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
  66.         FileOutputStream fops = null;
  67.         try {
  68.             fops = new FileOutputStream(exportPath);
  69.             wb.write(fops);
  70.         } catch (Exception e) {
  71.             e.printStackTrace();
  72.         } finally {
  73.             if (null != wb) {
  74.                 try {
  75.                     wb.dispose();
  76.                 } catch (Exception e) {
  77.                     e.printStackTrace();
  78.                 }
  79.             }
  80.             if (null != fops) {
  81.                 try {
  82.                     fops.close();
  83.                 } catch (Exception e) {
  84.                     e.printStackTrace();
  85.                 }
  86.             }
  87.         }
  88.     }
  91.     /**
  92.      * 下载EXCEL到浏览器
  93.      *
  94.      * @param wb       EXCEL对象XSSFWorkbook
  95.      * @param response
  96.      * @param fileName 文件名称
  97.      * @throws IOException
  98.      */
  99.     public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {
  101.         response.setHeader("Content-disposition", "attachment; filename="
  102.                 + new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名
  104.         OutputStream outputStream = null;
  105.         try {
  106.             outputStream = response.getOutputStream();
  107.             wb.write(outputStream);
  108.         } catch (Exception e) {
  109.             e.printStackTrace();
  110.         } finally {
  111.             if (null != wb) {
  112.                 try {
  113.                     wb.dispose();
  114.                 } catch (Exception e) {
  115.                     e.printStackTrace();
  116.                 }
  117.             }
  118.             if (null != outputStream) {
  119.                 try {
  120.                     outputStream.close();
  121.                 } catch (Exception e) {
  122.                     e.printStackTrace();
  123.                 }
  124.             }
  125.         }
  126.     }
  129.     /**
  130.      * 导出Excel到本地指定路径
  131.      *
  132.      * @param totalRowCount           总记录数
  133.      * @param titles                  标题
  134.      * @param exportPath              导出路径
  135.      * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
  136.      * @throws Exception
  137.      */
  138.     public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
  140.         logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
  142.         // 初始化EXCEL
  143.         SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);
  145.         // 调用委托类分批写数据
  146.         int sheetCount = wb.getNumberOfSheets();
  147.         for (int i = 0; i < sheetCount; i++) {
  148.             SXSSFSheet eachSheet = wb.getSheetAt(i);
  150.             for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {
  152.                 int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
  153.                 int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
  154.                 int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
  155.                 int endRowCount = startRowCount + pageSize - 1;
  157.                 writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
  159.             }
  160.         }
  163.         // 下载EXCEL
  164.         PoiUtil.downLoadExcelToLocalPath(wb, exportPath);
  166.         logger.info("导出完成：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
  167.     }
  170.     /**
  171.      * 导出Excel到浏览器
  172.      *
  173.      * @param response
  174.      * @param totalRowCount           总记录数
  175.      * @param fileName                文件名称
  176.      * @param titles                  标题
  177.      * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
  178.      * @throws Exception
  179.      */
  180.     public static final void exportExcelToWebsite(HttpServletResponse response, Integer totalRowCount, String fileName, String[] titles, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
  182.         logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
  184.         // 初始化EXCEL
  185.         SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);
  187.         int num = totalRowCount % ExcelConstant.PER_WRITE_ROW_COUNT == 0 ? totalRowCount / ExcelConstant.PER_WRITE_ROW_COUNT : (totalRowCount / ExcelConstant.PER_WRITE_ROW_COUNT) + 1;
  188.         // 调用委托类分批写数据
  189.         int sheetCount = wb.getNumberOfSheets();
  190.         //判断是需要几个sheet
  191.         //假设为13，最终结果要求
  192.         //0,10,20,30,40
  193.         //50,60,70,80,90
  194.         //100,110,120
  195.         ArrayList<Integer> list = new ArrayList<>();
  196.         if (num <= ExcelConstant.PER_WRITE_ROW_COUNT) {
  197.             list.add(num);
  198.         } else {
  199.             for (int i = 0; i < num / ExcelConstant.PER_WRITE_ROW_COUNT; i++) {
  200.                 list.add(ExcelConstant.PER_WRITE_ROW_COUNT);
  201.             }
  202.             if (num % ExcelConstant.PER_WRITE_ROW_COUNT != 0) {
  203.                 list.add(num % ExcelConstant.PER_WRITE_ROW_COUNT);
  204.             }
  205.         }
  206.         //此list.size相当于sheet的页数。
  207.         for (int i = 0; i < list.size(); i++) {
  208.             SXSSFSheet eachSheet = wb.getSheetAt(i);
  209.             for (int j = 1; j <= list.get(i); j++) {
  210.                 //获取当前页等信息
  211.                 int currentPage = ((i * ExcelConstant.PER_WRITE_ROW_COUNT) + j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT;
  212.                 int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
  213.                 int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
  214.                 int endRowCount = startRowCount + pageSize - 1;
  215.                 //调用方法从数据库查询，并且写入到Excel中
  216.                 writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
  217.             }
  218.         }
  220.         // 下载EXCEL
  221.         PoiUtil.downLoadExcelToWebsite(wb, response, fileName);
  223.         logger.info("导出完成：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
  224.     }
  225. }

ExcelConstant常量类

  2. /**
  3.  * @create 2019/11/19 17:42
  4.  * @About EXCEL常量类
  5.  */
  7. public class ExcelConstant {
  9.     /**
  10.      * 每个sheet存储的记录数 100W
  11.      */
  12.     public static final Integer PER_SHEET_ROW_COUNT = 100000;
  14.     /**
  15.      * 每次向EXCEL写入的记录数(查询每页数据大小) 20W
  16.      */
  17.     public static final Integer PER_WRITE_ROW_COUNT = 20000;
  20.     /**
  21.      * 每个sheet的写入次数 5
  22.      */
  23.     public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;
  24. }

WriteExcelDataDelegated

  1. import org.apache.poi.xssf.streaming.SXSSFSheet;
  3. /**
  4.  * @create 2019/11/19 17:45
  5.  * @About EXCEL写数据委托类
  6.  */
  8. public interface WriteExcelDataDelegated {
  10.     /**
  11.      * EXCEL写数据委托类  针对不同的情况自行实现
  12.      *
  13.      * @param eachSheet     指定SHEET
  14.      * @param startRowCount 开始行
  15.      * @param endRowCount   结束行
  16.      * @param currentPage   分批查询开始页
  17.      * @param pageSize      分批查询数据量
  18.      * @throws Exception
  19.      */
  20.     public abstract void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception;
  21. }

实现层使用

  1.     //userInfoQuery传入的参数
  2.     @Override
  3.     public List<userInfoVO> getJcFullCodeListExports(userInfoQuery userInfoQuery, HttpServletResponse resp) {
  4.         // 总记录数
  5.         Integer totalRowCount = userInfoQuery.getTotal();
  6.         // 导出EXCEL文件名称
  7.         String filaName = "用户表导出";
  8.         String[] titles = {"名称", "编号", "编码", "班级"};
  9.         // 开始导入
  10.         try {
  11.             PoiUtil.exportExcelToWebsite(resp, totalRowCount, filaName, titles, new WriteExcelDataDelegated() {
  12.                 @Override
  13.                 public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {
  14.                     userInfoQuery.setPage(currentPage);//设置当前页
  15.                     userInfoQuery.setSize(pageSize);//设置页面查询条数
  16.                     //根据条件从数据库查询数据
  17.                     List<userInfoVO> exportVOList = statisticalAnalysisMapper.getJcFullCodeInfoList(userInfoQuery);
  18.                        //绑定数据
  19.                     if (!CollectionUtils.isEmpty(exportVOList)) {
  20.                         // --------------   这一块变量照着抄就行  强迫症 后期也封装起来     ----------------------
  21.                         for (int i = startRowCount; i <= endRowCount; i++) {
  22.                             SXSSFRow eachDataRow = eachSheet.createRow(i);
  23.                             if ((i - startRowCount) < exportVOList.size()) {
  24.                                 userInfoVO eachUserVO = exportVOList.get(i - startRowCount);
  25.                                 // ---------   这一块变量照着抄就行  强迫症 后期也封装起来     -----------------------
  26.                                 int column = 0;
  27.                                 eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getQame()));
  28.                                 eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getACode()));
  29.                                 eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getQCode()));
  30.                                 eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getOName()));
  31.                             }
  32.                         }
  33.                     }
  35.                 }
  36.             });
  37.         } catch (Exception e) {
  38.             e.printStackTrace();
  39.         }
  40.         return null;
  41.     }

前端页面JS

  1.  //导出指定数据
  2.     $scope.exportByParams = function () {
  3.         $('.load-bg').fadeIn(200);
  4.         var req_data = $.extend({}, $scope.form, $scope.page);
  5.         if (req_data['total'] != 0) {
  6.             var params = Object.keys(req_data).map(function (key) {
  7.                 return encodeURIComponent(key) + "=" + encodeURIComponent(req_data[key]);
  8.             }).join("&");
  9.             var URLINFO = '';
  10.             if ($scope.form.type === '1') {
  11.                 URLINFO = "/statistical/getUserListExports";
  12.             } else if ($scope.form.type === '2') {
  13.                 URLINFO = "/statistical/getClassInfoListExports";
  14.             }
  15.             window.location.href = URLINFO + "?" + params;
  16.             alert("点击确定后请耐心等待片刻，约2分钟，待下载完成后可继续操作！");
  17.             $('.load-bg').fadeOut(200);
  18.         } else {
  19.             $('.load-bg').fadeOut(200);
  20.             alert("无数据！")
  21.             $('.details_export').fadeOut(200);
  22.         }
  23.     };
  • 页面按钮

image.png

  • 导出文件

image.png

END

搞定~