Java 亿万级数据导出到Excel

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

直接开始撸代码~

• 导入依赖

 <!-- 文件导出-->
        <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>

• PoiUtil导出工具类

package com.aa.utils;

import com.aa.excel.ExcelConstant;
import com.aa.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.ArrayList;
import java.util.Date;


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 num = totalRowCount % ExcelConstant.PER_WRITE_ROW_COUNT == 0 ? totalRowCount / ExcelConstant.PER_WRITE_ROW_COUNT : (totalRowCount / ExcelConstant.PER_WRITE_ROW_COUNT) + 1;
        // 调用委托类分批写数据
        int sheetCount = wb.getNumberOfSheets();
        //判断是需要几个sheet
        //假设为13，最终结果要求
        //0,10,20,30,40
        //50,60,70,80,90
        //100,110,120
        ArrayList<Integer> list = new ArrayList<>();
        if (num <= ExcelConstant.PER_WRITE_ROW_COUNT) {
            list.add(num);
        } else {
            for (int i = 0; i < num / ExcelConstant.PER_WRITE_ROW_COUNT; i++) {
                list.add(ExcelConstant.PER_WRITE_ROW_COUNT);
            }
            if (num % ExcelConstant.PER_WRITE_ROW_COUNT != 0) {
                list.add(num % ExcelConstant.PER_WRITE_ROW_COUNT);
            }
        }
        //此list.size相当于sheet的页数。
        for (int i = 0; i < list.size(); i++) {
            SXSSFSheet eachSheet = wb.getSheetAt(i);
            for (int j = 1; j <= list.get(i); j++) {
                //获取当前页等信息
                int currentPage = ((i * ExcelConstant.PER_WRITE_ROW_COUNT) + j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT;
                int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                int endRowCount = startRowCount + pageSize - 1;
                //调用方法从数据库查询，并且写入到Excel中
                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));
    }
}

• ExcelConstant常量类

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

public class ExcelConstant {

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

    /**
     * 每次向EXCEL写入的记录数(查询每页数据大小) 20W
     */
    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;
}

• WriteExcelDataDelegated

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

/**
 * @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;
}

• 实现层使用

    //userInfoQuery传入的参数
	@Override
    public List<userInfoVO> getJcFullCodeListExports(userInfoQuery userInfoQuery, HttpServletResponse resp) {
        // 总记录数
        Integer totalRowCount = userInfoQuery.getTotal();
        // 导出EXCEL文件名称
        String filaName = "用户表导出";
        String[] titles = {"名称", "编号", "编码", "班级"};
        // 开始导入
        try {
            PoiUtil.exportExcelToWebsite(resp, totalRowCount, filaName, titles, new WriteExcelDataDelegated() {
                @Override
                public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {
                    userInfoQuery.setPage(currentPage);//设置当前页
                    userInfoQuery.setSize(pageSize);//设置页面查询条数
                    //根据条件从数据库查询数据
                    List<userInfoVO> exportVOList = statisticalAnalysisMapper.getJcFullCodeInfoList(userInfoQuery);
                   	//绑定数据
                    if (!CollectionUtils.isEmpty(exportVOList)) {
                        // --------------   这一块变量照着抄就行  强迫症 后期也封装起来     ----------------------
                        for (int i = startRowCount; i <= endRowCount; i++) {
                            SXSSFRow eachDataRow = eachSheet.createRow(i);
                            if ((i - startRowCount) < exportVOList.size()) {
                                userInfoVO eachUserVO = exportVOList.get(i - startRowCount);
                                // ---------   这一块变量照着抄就行  强迫症 后期也封装起来     -----------------------
                                int column = 0;
                                eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getQame()));
                                eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getACode()));
                                eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getQCode()));
                                eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getOName()));
                            }
                        }
                    }

                }
            });
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

• 前端页面JS

 //导出指定数据
    $scope.exportByParams = function () {
        $('.load-bg').fadeIn(200);
        var req_data = $.extend({}, $scope.form, $scope.page);
        if (req_data['total'] != 0) {
            var params = Object.keys(req_data).map(function (key) {
                return encodeURIComponent(key) + "=" + encodeURIComponent(req_data[key]);
            }).join("&");
            var URLINFO = '';
            if ($scope.form.type === '1') {
                URLINFO = "/statistical/getUserListExports";
            } else if ($scope.form.type === '2') {
                URLINFO = "/statistical/getClassInfoListExports";
            }
            window.location.href = URLINFO + "?" + params;
            alert("点击确定后请耐心等待片刻，约2分钟，待下载完成后可继续操作！");
            $('.load-bg').fadeOut(200);
        } else {
            $('.load-bg').fadeOut(200);
            alert("无数据！")
            $('.details_export').fadeOut(200);
        }
    };