SpringBoot+angularJs实现百万级数据导出

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

添加依赖

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

<!-- 文件导出-->
<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
  	<version>3.17</version>
</dependency>
<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.17</version>
</dependency>

工具类

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

WriteExcelDataDelegated委托类

import org.apache.poi.xssf.streaming.SXSSFSheet;

/**
 * @Author admin
 * @create 2019/11/19 17:45
 * @About EXCEL写数据委托类
 */

public interface WriteExcelDataDelegated {

    /**
     * EXCEL写数据委托类  针对不同的情况自行实现
     *
     * @param eachSheet     指定SHEET
     * @param startRowCount 开始行
     * @param endRowCount   结束行
     * @param currentPage   分批查询开始页
     * @param pageSize      分批查询数据量
     * @throws Exception
     */
    public abstract void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception;
}

ExcelConstant常量

/**
 * @Author admin
 * @create 2019/11/19 17:42
 * @About EXCEL常量类
 */

public class ExcelConstant {

    /**
     * 每个sheet存储的记录数 10W
     */
    public static final Integer PER_SHEET_ROW_COUNT = 100000;

    /**
     * 每次向EXCEL写入的记录数(查询每页数据大小) 2W
     */
    public static final Integer PER_WRITE_ROW_COUNT = 20000;


    /**
     * 每个sheet的写入次数 5
     */
    public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;
}

PoiUtil导出数据

import com.demos.excel.ExcelConstant;
import com.demos.excel.WriteExcelDataDelegated;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;


/**
 * @Author admin
 * @create 2019/11/19 17:19
 * @About POI导出工具类
 */
public class PoiUtil {

    private final static Logger logger = LoggerFactory.getLogger(PoiUtil.class);

    /**
     * 初始化EXCEL(sheet个数和标题)
     *
     * @param totalRowCount 总记录数
     * @param titles        标题集合
     * @return XSSFWorkbook对象
     */
    public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {

        // 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中
        SXSSFWorkbook wb = new SXSSFWorkbook(100);

        Integer sheetCount = ((totalRowCount % ExcelConstant.PER_SHEET_ROW_COUNT == 0) ?
                (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT) : (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT + 1));

        // 根据总记录数创建sheet并分配标题
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
            SXSSFRow headRow = sheet.createRow(0);

            for (int j = 0; j < titles.length; j++) {
                SXSSFCell headRowCell = headRow.createCell(j);
                headRowCell.setCellValue(titles[j]);
            }
        }

        return wb;
    }


    /**
     * 下载EXCEL到本地指定的文件夹
     *
     * @param wb         EXCEL对象SXSSFWorkbook
     * @param exportPath 导出路径
     */
    public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
        FileOutputStream fops = null;
        try {
            fops = new FileOutputStream(exportPath);
            wb.write(fops);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != fops) {
                try {
                    fops.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 下载EXCEL到浏览器
     *
     * @param wb       EXCEL对象XSSFWorkbook
     * @param response
     * @param fileName 文件名称
     * @throws IOException
     */
    public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {

        response.setHeader("Content-disposition", "attachment; filename="
                + new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名

        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            wb.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != outputStream) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 导出Excel到本地指定路径
     *
     * @param totalRowCount           总记录数
     * @param titles                  标题
     * @param exportPath              导出路径
     * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
     * @throws Exception
     */
    public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {

        logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));

        // 初始化EXCEL
        SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);

        // 调用委托类分批写数据
        int sheetCount = wb.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet eachSheet = wb.getSheetAt(i);

            for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {

                int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
                int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                int endRowCount = startRowCount + pageSize - 1;
                
                writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);

            }
        }


        // 下载EXCEL
        PoiUtil.downLoadExcelToLocalPath(wb, exportPath);

        logger.info("导出完成：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    }


    /**
     * 导出Excel到浏览器
     * 亲测可用
     * @param response
     * @param totalRowCount           总记录数
     * @param fileName                文件名称
     * @param titles                  标题
     * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
     * @throws Exception
     */
    public static final void exportExcelToWebsite(HttpServletResponse response, Integer totalRowCount, String fileName, String[] titles, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {

        logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));

        // 初始化EXCEL
        SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);


        // 调用委托类分批写数据
        //该算法需要优化，后面会查询多余的数据，但是不会在页面显示
        int sheetCount = wb.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet eachSheet = wb.getSheetAt(i);
            for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {
                int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                int currentPage = (i * ExcelConstant.PER_SHEET_WRITE_COUNT + j - 1) * pageSize;
                int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                int endRowCount = startRowCount + pageSize - 1 > ExcelConstant.PER_SHEET_ROW_COUNT ? ExcelConstant.PER_SHEET_ROW_COUNT : startRowCount + pageSize - 1;

                writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);

            }
        }


        // 下载EXCEL
        PoiUtil.downLoadExcelToWebsite(wb, response, fileName);

        logger.info("导出完成：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    }
}

DateUtil日期处理

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @Author admin
 * @create 2019/11/19 17:39
 * @About 日期工具类
 */

public class DateUtil {

    public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";

    /**
     * 将日期转换为字符串
     *
     * @param date   DATE日期
     * @param format 转换格式
     * @return 字符串日期
     */
    public static String formatDate(Date date, String format) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
        return simpleDateFormat.format(date);

    }
}

Controller层

首先查询条数

    /**
     * 根据参数导出系统表中的字段
     * 步骤一  先查询是否有数据集,如果没有，不进行下一步请求
     *
     * @param archiveErrorSimpleQuery 参数(total,page,size等)
     * @return
     */
    @GetMapping("/getDataJoinExports")
    public ResponseMsg getDataJoinExports(ArchiveErrorSimpleQuery archiveErrorSimpleQuery, HttpServletResponse resp) {
        ResponseMsg msg = new ResponseMsg();
        //权限校验...略
       
        //封装数据
        List<ArchivrErrorSimpleExportVO> archivrErrorSimpleExportVOS = null;
        try {
            //查询总数量
            archiveErrorSimpleQuery.setPage((archiveErrorSimpleQuery.getPage() - 1) * archiveErrorSimpleQuery.getSize());
            int totalCount = anomalyAnalysisService.countDataJoinByQuery(archiveErrorSimpleQuery);
            msg.setTotal(totalCount);
            if (totalCount > 0) {
                msg.setSuccess(true);
                msg.setData("导出系统异常明细数据");
            }
        } catch (Exception e) {
            msg.setMsg(e.getMessage());
        }
        return msg;
    }

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

/**
     * 根据参数导出系统表中的字段
     * 步骤二  步骤一查询出数据集后，直接查询数据集
     *
     * @param archiveErrorSimpleQuery 参数(total,page,size等)
     * @return
     */
    @GetMapping("/getDataJoinExportList")
    public ResponseMsg getDataJoinExportList(ArchiveErrorSimpleQuery archiveErrorSimpleQuery, HttpServletResponse resp) {
        ResponseMsg msg = new ResponseMsg();
        //权限校验...略
        
        //封装数据
        List<ArchivrErrorSimpleExportVO> archivrErrorSimpleExportVOS = null;
        try {
            //根据条件查询数据集合
            archivrErrorSimpleExportVOS = anomalyAnalysisService.getDataJoinExportList(archiveErrorSimpleQuery,resp);
        } catch (Exception e) {
            msg.setMsg(e.getMessage());
        }
        msg.setSuccess(archivrErrorSimpleExportVOS != null);
        msg.setData("导出系统异常明细数据");
        return msg;
    }

Service层

    /**
     * 满足条件的total总数量
     *
     * @param archiveErrorSimpleQuery
     * @return
     */
    int countDataJoinByQuery(ArchiveErrorSimpleQuery archiveErrorSimpleQuery);

    /**
     * 满足条件的列表
     * @param archiveErrorSimpleQuery
     * @param resp
     * @return
     */
    List<ArchivrErrorSimpleExportVO> getDataJoinExportList(ArchiveErrorSimpleQuery archiveErrorSimpleQuery, HttpServletResponse resp) throws Exception;

Service实现层

 	@Override
    public int countDataJoinByQuery(ArchiveErrorSimpleQuery archiveErrorSimpleQuery) {
        return archiveErrorSimpleMapper.countDataJoinByQuery(archiveErrorSimpleQuery);
    }

    @Override
    public List<ArchivrErrorSimpleExportVO> getDataJoinExportList(ArchiveErrorSimpleQuery archiveErrorSimpleQuery, HttpServletResponse resp) throws Exception {
        // 总记录数
        Integer totalRowCount = archiveErrorSimpleQuery.getTotal();
        // 导出EXCEL文件名称
        String filaName = "标题透";
        String[] titles = {"列1", "列2", "列3", "列4",...};
        // 开始导入
        PoiUtil.exportExcelToWebsite(resp, totalRowCount, filaName, titles, new WriteExcelDataDelegated() {
            @Override
            public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {
                //绑定数据
                archiveErrorSimpleQuery.setPage(currentPage); //设置limit的page
                archiveErrorSimpleQuery.setSize(pageSize); //设置limit的size
                //查询列表
                List<ArchivrErrorSimpleExportVO> exportVOList = archiveErrorSimpleMapper.getDataJoinExportList(archiveErrorSimpleQuery);
                if (!CollectionUtils.isEmpty(exportVOList)) {
                    for (int i = startRowCount; i <= endRowCount; i++) {
                        SXSSFRow eachDataRow = eachSheet.createRow(i);
                        if ((i - startRowCount) < exportVOList.size()) {
                            ArchivrErrorSimpleExportVO eachUserVO = exportVOList.get(i - startRowCount);
                            eachDataRow.createCell(0).setCellValue(eachUserVO.getType() == null ? "" : eachUserVO.getType());
                            eachDataRow.createCell(1).setCellValue(eachUserVO.getArchiveId() == null ? "" : eachUserVO.getArchiveId());
                            eachDataRow.createCell(2).setCellValue(eachUserVO.getBelongSystem() == null ? "" : eachUserVO.getBelongSystem());
                            eachDataRow.createCell(3).setCellValue(eachUserVO.getErrorCode() == null ? "" : eachUserVO.getErrorCode());
                        }
                    }
                }

            }
        });
        return null;
    }

Mapper层

@Mapper
@Component
public interface demoMapper {
    
    int countDataJoinByQuery(@Param("simpleQuery") ArchiveErrorSimpleQuery simpleQuery);

    List<ArchivrErrorSimpleExportVO> getDataJoinExportList(@Param("simpleQuery") ArchiveErrorSimpleQuery simpleQuery);
}

Mapper.xml层

  <select id="getDataJoinExportList"
            resultType="ExportVO"> //返回的VO类
        select
        <include refid="aesExportColumn"></include>
        from user
        <trim prefix="WHERE" prefixOverrides="AND |OR">
            <if test="simpleQuery.name!=null and simpleQuery.name!=''">
                and name = #{simpleQuery.name}
            </if>
                    <!-- 类似上面的参数条件-->

        </trim>
        order by id
        limit
        <if test="simpleQuery.page != null and simpleQuery.page != '' and simpleQuery.page!=0">
            #{simpleQuery.page},
        </if>
        #{simpleQuery.size};
    </select>

  <select id="countDataJoinByQuery"
            resultType="java.lang.Integer">
        select
    			count(id)
        from user
        <trim prefix="WHERE" prefixOverrides="AND |OR">
            <if test="simpleQuery.name!=null and simpleQuery.name!=''">
                and name = #{simpleQuery.name}
            </if>
                    <!-- 类似上面的参数条件-->
        </trim>
    </select>

前端JS

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

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

下载的时候不能使用ajax或者$http的形式发送请求，不然页面会下载不了。

效果展示

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