导读

由于项目需要，需要在页面点击导出按钮，然后根据条件进行查询数据，并导出。最开始的时候想着使用是前端的xlsxJs来实现前端Excel数据导出，后面想了想这种办法行不通，原因很简单，假如后台数据几百万的话，那么前端会瞬间崩掉，这个xlsxJS感觉适合数据量少的导出Excel文件，数据量大的话可能也有，但是现在没时间去尝试了，以后吧。So，这显然是行不通的，网上找了一堆后，有使用poi还是阿里的easyExcel亦或者easypoi，可能看着poi好感度更高吧，就选择它了，正好网上也有现成的，直接拿来使用了。

使用

添加依赖

在pom.xml文件添加以下依赖：

  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>

工具类

在相应的包下添加以下几个文件：

WriteExcelDataDelegated委托类

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

ExcelConstant常量

  1. /**
  2.  * @Author admin
  3.  * @create 2019/11/19 17:42
  4.  * @About EXCEL常量类
  5.  */
  7. public class ExcelConstant {
  9.     /**
  10.      * 每个sheet存储的记录数 10W
  11.      */
  12.     public static final Integer PER_SHEET_ROW_COUNT = 100000;
  14.     /**
  15.      * 每次向EXCEL写入的记录数(查询每页数据大小) 2W
  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. }

PoiUtil导出数据

  1. import com.demos.excel.ExcelConstant;
  2. import com.demos.excel.WriteExcelDataDelegated;
  3. import org.apache.poi.xssf.streaming.SXSSFCell;
  4. import org.apache.poi.xssf.streaming.SXSSFRow;
  5. import org.apache.poi.xssf.streaming.SXSSFSheet;
  6. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  7. import org.slf4j.Logger;
  8. import org.slf4j.LoggerFactory;
  9. import javax.servlet.http.HttpServletResponse;
  10. import java.io.FileOutputStream;
  11. import java.io.IOException;
  12. import java.io.OutputStream;
  13. import java.util.Date;
  16. /**
  17.  * @Author admin
  18.  * @create 2019/11/19 17:19
  19.  * @About POI导出工具类
  20.  */
  21. public class PoiUtil {
  23.     private final static Logger logger = LoggerFactory.getLogger(PoiUtil.class);
  25.     /**
  26.      * 初始化EXCEL(sheet个数和标题)
  27.      *
  28.      * @param totalRowCount 总记录数
  29.      * @param titles        标题集合
  30.      * @return XSSFWorkbook对象
  31.      */
  32.     public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {
  34.         // 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中
  35.         SXSSFWorkbook wb = new SXSSFWorkbook(100);
  37.         Integer sheetCount = ((totalRowCount % ExcelConstant.PER_SHEET_ROW_COUNT == 0) ?
  38.                 (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT) : (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT + 1));
  40.         // 根据总记录数创建sheet并分配标题
  41.         for (int i = 0; i < sheetCount; i++) {
  42.             SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
  43.             SXSSFRow headRow = sheet.createRow(0);
  45.             for (int j = 0; j < titles.length; j++) {
  46.                 SXSSFCell headRowCell = headRow.createCell(j);
  47.                 headRowCell.setCellValue(titles[j]);
  48.             }
  49.         }
  51.         return wb;
  52.     }
  55.     /**
  56.      * 下载EXCEL到本地指定的文件夹
  57.      *
  58.      * @param wb         EXCEL对象SXSSFWorkbook
  59.      * @param exportPath 导出路径
  60.      */
  61.     public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
  62.         FileOutputStream fops = null;
  63.         try {
  64.             fops = new FileOutputStream(exportPath);
  65.             wb.write(fops);
  66.         } catch (Exception e) {
  67.             e.printStackTrace();
  68.         } finally {
  69.             if (null != wb) {
  70.                 try {
  71.                     wb.dispose();
  72.                 } catch (Exception e) {
  73.                     e.printStackTrace();
  74.                 }
  75.             }
  76.             if (null != fops) {
  77.                 try {
  78.                     fops.close();
  79.                 } catch (Exception e) {
  80.                     e.printStackTrace();
  81.                 }
  82.             }
  83.         }
  84.     }
  87.     /**
  88.      * 下载EXCEL到浏览器
  89.      *
  90.      * @param wb       EXCEL对象XSSFWorkbook
  91.      * @param response
  92.      * @param fileName 文件名称
  93.      * @throws IOException
  94.      */
  95.     public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {
  97.         response.setHeader("Content-disposition", "attachment; filename="
  98.                 + new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名
  100.         OutputStream outputStream = null;
  101.         try {
  102.             outputStream = response.getOutputStream();
  103.             wb.write(outputStream);
  104.         } catch (Exception e) {
  105.             e.printStackTrace();
  106.         } finally {
  107.             if (null != wb) {
  108.                 try {
  109.                     wb.dispose();
  110.                 } catch (Exception e) {
  111.                     e.printStackTrace();
  112.                 }
  113.             }
  114.             if (null != outputStream) {
  115.                 try {
  116.                     outputStream.close();
  117.                 } catch (Exception e) {
  118.                     e.printStackTrace();
  119.                 }
  120.             }
  121.         }
  122.     }
  125.     /**
  126.      * 导出Excel到本地指定路径
  127.      *
  128.      * @param totalRowCount           总记录数
  129.      * @param titles                  标题
  130.      * @param exportPath              导出路径
  131.      * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
  132.      * @throws Exception
  133.      */
  134.     public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
  136.         logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
  138.         // 初始化EXCEL
  139.         SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);
  141.         // 调用委托类分批写数据
  142.         int sheetCount = wb.getNumberOfSheets();
  143.         for (int i = 0; i < sheetCount; i++) {
  144.             SXSSFSheet eachSheet = wb.getSheetAt(i);
  146.             for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {
  148.                 int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
  149.                 int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
  150.                 int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
  151.                 int endRowCount = startRowCount + pageSize - 1;
  153.                 writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
  155.             }
  156.         }
  159.         // 下载EXCEL
  160.         PoiUtil.downLoadExcelToLocalPath(wb, exportPath);
  162.         logger.info("导出完成：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
  163.     }
  166.     /**
  167.      * 导出Excel到浏览器
  168.      * 亲测可用
  169.      * @param response
  170.      * @param totalRowCount           总记录数
  171.      * @param fileName                文件名称
  172.      * @param titles                  标题
  173.      * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
  174.      * @throws Exception
  175.      */
  176.     public static final void exportExcelToWebsite(HttpServletResponse response, Integer totalRowCount, String fileName, String[] titles, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
  178.         logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
  180.         // 初始化EXCEL
  181.         SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);
  184.         // 调用委托类分批写数据
  185.         //该算法需要优化，后面会查询多余的数据，但是不会在页面显示
  186.         int sheetCount = wb.getNumberOfSheets();
  187.         for (int i = 0; i < sheetCount; i++) {
  188.             SXSSFSheet eachSheet = wb.getSheetAt(i);
  189.             for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {
  190.                 int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
  191.                 int currentPage = (i * ExcelConstant.PER_SHEET_WRITE_COUNT + j - 1) * pageSize;
  192.                 int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
  193.                 int endRowCount = startRowCount + pageSize - 1 > ExcelConstant.PER_SHEET_ROW_COUNT ? ExcelConstant.PER_SHEET_ROW_COUNT : startRowCount + pageSize - 1;
  195.                 writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
  197.             }
  198.         }
  201.         // 下载EXCEL
  202.         PoiUtil.downLoadExcelToWebsite(wb, response, fileName);
  204.         logger.info("导出完成：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
  205.     }
  206. }

DateUtil日期处理

  1. import java.text.SimpleDateFormat;
  2. import java.util.Date;
  4. /**
  5.  * @Author admin
  6.  * @create 2019/11/19 17:39
  7.  * @About 日期工具类
  8.  */
  10. public class DateUtil {
  12.     public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";
  14.     /**
  15.      * 将日期转换为字符串
  16.      *
  17.      * @param date   DATE日期
  18.      * @param format 转换格式
  19.      * @return 字符串日期
  20.      */
  21.     public static String formatDate(Date date, String format) {
  22.         SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
  23.         return simpleDateFormat.format(date);
  25.     }
  26. }

具体使用

Controller层

  • 首先查询条数
  2.     /**
  3.      * 根据参数导出系统表中的字段
  4.      * 步骤一  先查询是否有数据集,如果没有，不进行下一步请求
  5.      *
  6.      * @param archiveErrorSimpleQuery 参数(total,page,size等)
  7.      * @return
  8.      */
  9.     @GetMapping("/getDataJoinExports")
  10.     public ResponseMsg getDataJoinExports(ArchiveErrorSimpleQuery archiveErrorSimpleQuery, HttpServletResponse resp) {
  11.         ResponseMsg msg = new ResponseMsg();
  12.         //权限校验...略
  14.         //封装数据
  15.         List<ArchivrErrorSimpleExportVO> archivrErrorSimpleExportVOS = null;
  16.         try {
  17.             //查询总数量
  18.             archiveErrorSimpleQuery.setPage((archiveErrorSimpleQuery.getPage() - 1) * archiveErrorSimpleQuery.getSize());
  19.             int totalCount = anomalyAnalysisService.countDataJoinByQuery(archiveErrorSimpleQuery);
  20.             msg.setTotal(totalCount);
  21.             if (totalCount > 0) {
  22.                 msg.setSuccess(true);
  23.                 msg.setData("导出系统异常明细数据");
  24.             }
  25.         } catch (Exception e) {
  26.             msg.setMsg(e.getMessage());
  27.         }
  28.         return msg;
  29.     }

  • 如果总条数大于0，再进行数据导出

    1. /**
    2.    * 根据参数导出系统表中的字段
    3.    * 步骤二  步骤一查询出数据集后，直接查询数据集
    4.    *
    5.    * @param archiveErrorSimpleQuery 参数(total,page,size等)
    6.    * @return
    7.    */
    8.   @GetMapping("/getDataJoinExportList")
    9.   public ResponseMsg getDataJoinExportList(ArchiveErrorSimpleQuery archiveErrorSimpleQuery, HttpServletResponse resp) {
    10.       ResponseMsg msg = new ResponseMsg();
    11.       //权限校验...略
    13.       //封装数据
    14.       List<ArchivrErrorSimpleExportVO> archivrErrorSimpleExportVOS = null;
    15.       try {
    16.           //根据条件查询数据集合
    17.           archivrErrorSimpleExportVOS = anomalyAnalysisService.getDataJoinExportList(archiveErrorSimpleQuery,resp);
    18.       } catch (Exception e) {
    19.           msg.setMsg(e.getMessage());
    20.       }
    21.       msg.setSuccess(archivrErrorSimpleExportVOS != null);
    22.       msg.setData("导出系统异常明细数据");
    23.       return msg;
    24.   }

Service层

  1.     /**
  2.      * 满足条件的total总数量
  3.      *
  4.      * @param archiveErrorSimpleQuery
  5.      * @return
  6.      */
  7.     int countDataJoinByQuery(ArchiveErrorSimpleQuery archiveErrorSimpleQuery);
  9.     /**
  10.      * 满足条件的列表
  11.      * @param archiveErrorSimpleQuery
  12.      * @param resp
  13.      * @return
  14.      */
  15.     List<ArchivrErrorSimpleExportVO> getDataJoinExportList(ArchiveErrorSimpleQuery archiveErrorSimpleQuery, HttpServletResponse resp) throws Exception;

Service实现层

  1.      @Override
  2.     public int countDataJoinByQuery(ArchiveErrorSimpleQuery archiveErrorSimpleQuery) {
  3.         return archiveErrorSimpleMapper.countDataJoinByQuery(archiveErrorSimpleQuery);
  4.     }
  6.     @Override
  7.     public List<ArchivrErrorSimpleExportVO> getDataJoinExportList(ArchiveErrorSimpleQuery archiveErrorSimpleQuery, HttpServletResponse resp) throws Exception {
  8.         // 总记录数
  9.         Integer totalRowCount = archiveErrorSimpleQuery.getTotal();
  10.         // 导出EXCEL文件名称
  11.         String filaName = "标题透";
  12.         String[] titles = {"列1", "列2", "列3", "列4",...};
  13.         // 开始导入
  14.         PoiUtil.exportExcelToWebsite(resp, totalRowCount, filaName, titles, new WriteExcelDataDelegated() {
  15.             @Override
  16.             public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {
  17.                 //绑定数据
  18.                 archiveErrorSimpleQuery.setPage(currentPage); //设置limit的page
  19.                 archiveErrorSimpleQuery.setSize(pageSize); //设置limit的size
  20.                 //查询列表
  21.                 List<ArchivrErrorSimpleExportVO> exportVOList = archiveErrorSimpleMapper.getDataJoinExportList(archiveErrorSimpleQuery);
  22.                 if (!CollectionUtils.isEmpty(exportVOList)) {
  23.                     for (int i = startRowCount; i <= endRowCount; i++) {
  24.                         SXSSFRow eachDataRow = eachSheet.createRow(i);
  25.                         if ((i - startRowCount) < exportVOList.size()) {
  26.                             ArchivrErrorSimpleExportVO eachUserVO = exportVOList.get(i - startRowCount);
  27.                             eachDataRow.createCell(0).setCellValue(eachUserVO.getType() == null ? "" : eachUserVO.getType());
  28.                             eachDataRow.createCell(1).setCellValue(eachUserVO.getArchiveId() == null ? "" : eachUserVO.getArchiveId());
  29.                             eachDataRow.createCell(2).setCellValue(eachUserVO.getBelongSystem() == null ? "" : eachUserVO.getBelongSystem());
  30.                             eachDataRow.createCell(3).setCellValue(eachUserVO.getErrorCode() == null ? "" : eachUserVO.getErrorCode());
  31.                         }
  32.                     }
  33.                 }
  35.             }
  36.         });
  37.         return null;
  38.     }

Mapper层

  1. @Mapper
  2. @Component
  3. public interface demoMapper {
  5.     int countDataJoinByQuery(@Param("simpleQuery") ArchiveErrorSimpleQuery simpleQuery);
  7.     List<ArchivrErrorSimpleExportVO> getDataJoinExportList(@Param("simpleQuery") ArchiveErrorSimpleQuery simpleQuery);
  8. }

Mapper.xml层

查询列表：

  1.   <select id="getDataJoinExportList"
  2.             resultType="ExportVO"> //返回的VO类
  3.         select
  4.         <include refid="aesExportColumn"></include>
  5.         from user
  6.         <trim prefix="WHERE" prefixOverrides="AND |OR">
  7.             <if test="simpleQuery.name!=null and simpleQuery.name!=''">
  8.                 and name = #{simpleQuery.name}
  9.             </if>
  10.                     <!-- 类似上面的参数条件-->
  12.         </trim>
  13.         order by id
  14.         limit
  15.         <if test="simpleQuery.page != null and simpleQuery.page != '' and simpleQuery.page!=0">
  16.             #{simpleQuery.page},
  17.         </if>
  18.         #{simpleQuery.size};
  19.     </select>
  21.   <select id="countDataJoinByQuery"
  22.             resultType="java.lang.Integer">
  23.         select
  24.                 count(id)
  25.         from user
  26.         <trim prefix="WHERE" prefixOverrides="AND |OR">
  27.             <if test="simpleQuery.name!=null and simpleQuery.name!=''">
  28.                 and name = #{simpleQuery.name}
  29.             </if>
  30.                     <!-- 类似上面的参数条件-->
  31.         </trim>
  32.     </select>

前端JS

前端一个按钮，点击执行export函数，执行以下JS代码

  1.     //导出
  2.     $scope.export = function () {
  3.         let total = 0;
  4.         var req_data = $.extend({}, $scope.form, $scope.page);
  5.       //先发送数量查询，如果不为空，再查询列表，并导出
  6.         let url = "/anomaly/getDataJoinExportList";//列表查询
  7.         $http({
  8.             method: "GET",
  9.             url: "/anomaly/getDataJoinExports", //数量查询
  10.             params: req_data, //$scope.form下的一些参数，前端页面传入
  11.         }).then(function successCallback(response) {
  12.             if (response.data.success) {
  13.               //我是将第一次查询的total总数量，作为参数第二个方法接受，就不需要再次查询总数量了，这指定不安全的，如果需要考虑更安全的方式话，需要再次查询一遍。
  14.                 total = response.data.total;
  15.                 window.location.href = (url + "?startTime=" + $scope.form.startTime + "&endTime=" + $scope.form.endTime
  16.                     + "&type=" + $scope.form.type + "&belongSystem=" + $scope.form.belongSystem + "&total=" + total)
  17.             } else {
  18.                 alert("无数据！")
  19.             }
  20.         }, function errorCallback() {
  21.         })
  22.     };

注意 ：下载的时候不能使用ajax或者$http的形式发送请求，不然页面会下载不了，出现以下错误：
image.png
解决办法 ：就是使用window.location.harf=url的形式发送请求。

效果展示

120w数据，大约需要两分钟，已经很长了，总觉的那个算法循环的地方可以优化，回头看看，再研究下。
image.png

参考