SXSSFWorkbook 导出一百万数据

1.引用bar包

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>

    /**
     * @Param [params, response, request]
     * @Return void
     * @author xiaoshi
     * @Description 分钟的数据的Excel的 导出
     * @Date 2019/9/6
     * @Time 16:50
     **/
    @RequestMapping("/exportExcelMinute")
    @ResponseBody
    public void exportExcelMinute(@RequestParam Map<String, Object> params, HttpServletResponse response,
                                  HttpServletRequest request) throws Exception {
        // 按指定站点查询
        String siteCodeList = (String) params.get("siteIds");
        String beginTime = (String) params.get("beginTime");
        String endTime = (String) params.get("endTime");
        String date = beginTime + "至" + endTime;
        User user = userService.get(getUserId());
        Date dataTime = user.getDataTime();//清除时间
        if (dataTime != null) {
            //  String beginTime = (String) params.get("beginTime");
            Date parse = sdf2.parse(beginTime);
            long time = dataTime.getTime();
            long time1 = parse.getTime();

            if (dataTime.getTime() > parse.getTime()) {
                params.put("beginTime", dataTime);
            }
            //   String endTime = (String) params.get("endTime");
            Date parse2 = sdf2.parse(endTime);
            if (dataTime.getTime() > parse2.getTime()) {
                params.put("endTime", dataTime);
            }
        }


        if (siteCodeList == null || siteCodeList.equals("undefined") || siteCodeList == "") {
            // User user=userService.get(getUserId());
            Long role = user.getRoleIds().get(0);
            if (role != 1) {
                Long deptId = user.getDeptId();
                params.put("deptId", deptId);
                List<Dept> list = airStationService.selDept(params);
                Long parentId = list.get(0).getParentId();
                params.remove("deptId");
                Long id;
                if (parentId == 0) {
                    id = list.get(0).getDeptId();
                    params.put("deptId", id);
                    params.remove("siteIds");
                } else {
                    //id=list.get(0).getParentId();
                    String siteCodes = list.get(0).getSiteCodes();
                    String[] split = siteCodes.split(",");
                    params.put("siteIds", split);
                }

            } else {
                params.remove("siteIds");
            }
        } else {
            String[] split = siteCodeList.split(",");
            params.put("siteIds", siteCodeList.split(","));
        }

        List<MinuteData> minuteDataList = minuteDataService.minuteListExcel(params);
        //50000
        if (minuteDataList.size() > 1000000) {
            throw new Exception("导出失败！");
        } else {
            this.doExportMinuteXls(response, request, minuteDataList, MinuteData.class, "烟气分钟数据", date);
        }


    }

    protected <T> void doExportMinuteXls(HttpServletResponse response, HttpServletRequest request,
                                         List<MinuteData> dataSet, Class<?> pojoClass, String title, String date) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        ServletOutputStream fOut = null;

        try {
            response.reset();
            response.setHeader("Cache-Control", "private");
            response.setHeader("Pragma", "private");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Type", "application/force-download");
            String workbookname = URLEncoder.encode(title + ".xls", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + workbookname);
//            ExportExcelStatementUtil excelStatementUtil = new ExportExcelStatementUtil();
//            HSSFWorkbook wb = excelStatementUtil.exportMinute(dataSet, date);
            SXSSFWorkbook wb = ExcelUtil.exportExcel(dataSet, date);
            fOut = response.getOutputStream();
            wb.write(fOut);
        } catch (Exception var15) {
            var15.printStackTrace();
            throw new Exception("导出失败！");
        } finally {
            try {
                fOut.flush();
                fOut.close();
            } catch (IOException var14) {
                var14.printStackTrace();
            }
        }
    }

工具类

package com.rate.web.history.utils;

import com.rate.system.rate_system.utils.DateUtils;
import com.rate.web.history.entity.MinuteData;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
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 java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * excel 工具类，可以导出 1048576 行数据
 * <p>
 * 超过报错：
 * Exception in thread "main" java.lang.IllegalArgumentException:
 * Invalid row number (1048576) outside allowable range (0..1048575)
 */
public class ExcelUtil {

//    public static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";// 默认日期格式
//    public static final int DEFAULT_COLUMN_WIDTH = 17;// 默认列宽

    /**
     * 导出Excel(.xlsx)格式
     */
    public static SXSSFWorkbook exportExcel(List<MinuteData> dataList, String date) {
//        String datePattern = DEFAULT_DATE_PATTERN;
//        int minBytes = DEFAULT_COLUMN_WIDTH;
        String sheetName = "烟气分钟数据";
        /**
         * 声明一个工作薄
         */
        SXSSFWorkbook workbook = new SXSSFWorkbook();//1000可以设置 大于1000行时会把之前的行写入硬盘
        workbook.setCompressTempFiles(true);


        // 表头1样式
        CellStyle title1Style = workbook.createCellStyle();
        title1Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
        title1Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        title1Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 前景色纯色填充
        Font titleFont = workbook.createFont();// 字体
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        title1Style.setFont(titleFont);

        // 表头2样式
        CellStyle title2Style = workbook.createCellStyle();
        title2Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        title2Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        title2Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 前景色纯色填充
        title2Style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        title2Style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右
        title2Style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下
        title2Style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左
        Font title2Font = workbook.createFont();
        title2Font.setUnderline((byte) 1);
        title2Font.setColor(HSSFColor.BLUE.index);
        title2Style.setFont(title2Font);

        // 表头3样式
        CellStyle title3Style = workbook.createCellStyle();
        title3Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        title3Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        title3Style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置颜色
        title3Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 前景色纯色填充
        title3Style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        title3Style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        title3Style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        title3Style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        title3Style.setFont(headerFont);

        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);


        String[] head0 = {date + sheetName};
        String[] headnum0 = {"0,0,0,15"};

        String[] head1 = {"        企业名称        ", "        区域        ", "        设备厂家        ",
                "       时间        ", "油烟浓度", "油烟标准浓度", "颗粒物浓度", "大气压", "非甲烷总烃", "烟气温度",
                "烟气湿度", "采样流量", "净化器电流", "排风机电流", "经度", "纬度"};
        String[] headnum1 =
                {"1,2,0,0", // 第0列跨2行
                        "1,2,1,1", // 第1列跨2行
                        "1,2,2,2",
                        "1,2,3,3",
                        "1,1,4,4",
                        "1,1,5,5",
                        "1,1,6,6",
                        "1,1,7,7",
                        "1,1,8,8",
                        "1,1,9,9",
                        "1,1,10,10",
                        "1,1,11,11",
                        "1,1,12,12",
                        "1,1,13,13",
                        "1,1,14,14"};
        // 第二行表头字段，其中的空的双引号是为了补全表格边框
        String[] head2 = {"(mg/m³)", "(mg/m³)", "(mg/m³)", "(kPa)", "(mg/m³)", "(℃)", "(%)", "(L/min)", "(A)", "(A)", "(°)", "(°)"};
        // 合并单元格
        String[] headnum2 = {"2,2,4,4", "2,2,5,5", "2,2,6,6", "2,2,7,7", "2,2,8,8", "2,2,9,9", "2,2,10,10", "2,2,11,11", "2,2,12,12", "2,2,13,13", "2,2,14,14",};

        /**
         * 生成一个(带名称)表格
         */

        SXSSFSheet sheet = null;
//        sheet.createFreezePane(0, 3, 0, 3);// (单独)冻结前三行


        /**
         * 遍历数据集合，产生Excel行数据
         */
        int rowIndex = 0;

        //第一行表头
        if (rowIndex == 0) {
            //如果rowIndex超过一百万，新建一个新的 sheet
            sheet = (SXSSFSheet) workbook.createSheet(sheetName);

            if (rowIndex > 1000000) {
                sheet = (SXSSFSheet) workbook.createSheet(sheetName + rowIndex);
            }
            // 生成 表头信息
            /**
             * 表头
             *
             * 生成head相关信息+设置每列宽度
             */
            sheet.trackAllColumnsForAutoSizing();

            SXSSFRow row0 = (SXSSFRow) sheet.createRow(rowIndex);
            for (int i = 0; i < head0.length; i++) {
                SXSSFCell cell = row0.createCell(i);
                cell.setCellValue(head0[i]);
                cell.setCellStyle(title1Style);
                sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
            }
            // 动态合并单元格
            for (int i = 0; i < headnum0.length; i++) {
                // 根据字段长度自动调整列的宽度
                sheet.autoSizeColumn(i, true);
                String[] temp = headnum0[i].split(",");
                Integer startRow = Integer.parseInt(temp[0]);
                Integer endRow = Integer.parseInt(temp[1]);
                Integer startCol = Integer.parseInt(temp[2]);
                Integer endCol = Integer.parseInt(temp[3]);
                if (!(startRow.equals(endRow) && startCol.equals(endCol))) {
                    sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
                }
            }
            //行数加一
            rowIndex++;
            // //第二行表头
            SXSSFRow row1 = (SXSSFRow) sheet.createRow(rowIndex);
            for (int i = 0; i < head1.length; i++) {
                SXSSFCell cell = row1.createCell(i);
                cell.setCellValue(head1[i]);
                cell.setCellStyle(title2Style);
                sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
            }
            // 动态合并单元格
            for (int i = 0; i < headnum1.length; i++) {
                sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
                String[] temp = headnum1[i].split(",");
                Integer startRow = Integer.parseInt(temp[0]);
                Integer endRow = Integer.parseInt(temp[1]);
                Integer startCol = Integer.parseInt(temp[2]);
                Integer endCol = Integer.parseInt(temp[3]);
                if (!(startRow.equals(endRow) && startCol.equals(endCol))) {
                    sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
                }
            }
            //行数加一
            rowIndex++;
            //第3行表头
            SXSSFRow row2 = (SXSSFRow) sheet.createRow(rowIndex);
            for (int i = 0; i < head2.length; i++) {
                SXSSFCell cell = row2.createCell(i + 4);
                cell.setCellValue(head2[i]);
                cell.setCellStyle(title3Style);
                sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
            }
            // 动态合并单元格
            for (int i = 0; i < headnum2.length; i++) {
                sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
                String[] temp = headnum2[i].split(",");
                Integer startRow = Integer.parseInt(temp[0]);
                Integer endRow = Integer.parseInt(temp[1]);
                Integer startCol = Integer.parseInt(temp[2]);
                Integer endCol = Integer.parseInt(temp[3]);
                if (!(startRow.equals(endRow) && startCol.equals(endCol))) {
                    sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
                }
            }
            //行数加一
            rowIndex++;
        }
        //设置一下，表头的列宽
        for (int i = 0; i < head1.length; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        }
        //地名区域的列宽略大一些
        sheet.setColumnWidth(1, 26 * 256);
        // 生成列数据
        for (int i = 0; i < dataList.size(); i++) {
            int colIndex = 0;
            //从第三行开始填充列数据
            SXSSFRow row = (SXSSFRow) sheet.createRow(i + rowIndex);// 创建行
            MinuteData minuteData = dataList.get(i);
            //从第一列开始填充数据(然而poi的第一列是从0开始的)
            SXSSFCell cell0 = (SXSSFCell) row.createCell(colIndex++);// 创建单元格
            if (minuteData.getSiteName() != null) {
                cell0.setCellValue(minuteData.getSiteName());
                cell0.setCellStyle(cellStyle);
            }
            //第二列
            SXSSFCell cell1 = row.createCell(colIndex++);
            if (minuteData.getAreas() != null) {
                cell1.setCellValue(minuteData.getAreas());
                cell1.setCellStyle(cellStyle);
            }
            SXSSFCell cell2 = row.createCell(colIndex++);
            if (minuteData.getName() != null) {
                cell2.setCellValue(minuteData.getName());
                cell2.setCellStyle(cellStyle);
            }
            SXSSFCell cell3 = row.createCell(colIndex++);
            if (minuteData.getDataTime() != null) {
                cell3.setCellValue(DateUtils.format(minuteData.getDataTime(), DateUtils.DATE_TIME_PATTERN));
                cell3.setCellStyle(cellStyle);
            }
            SXSSFCell cell4 = row.createCell(colIndex++);
            if (minuteData.getOis() != null) {
                cell4.setCellValue(minuteData.getOis().doubleValue());
                cell4.setCellStyle(cellStyle);
            }
            SXSSFCell cell5 = row.createCell(colIndex++);
            if (minuteData.getAlarmLimit() != null) {
                cell5.setCellValue(minuteData.getAlarmLimit().doubleValue());
                cell5.setCellStyle(cellStyle);
            }
            SXSSFCell cell6 = row.createCell(colIndex++);
            if (minuteData.getPmx() != null) {
                cell6.setCellValue(minuteData.getPmx().toString());
                cell6.setCellStyle(cellStyle);
            }

            SXSSFCell cell7 = row.createCell(colIndex++);
            if (minuteData.getBa() != null) {
                cell7.setCellValue(minuteData.getBa().toString());
                cell7.setCellStyle(cellStyle);
            }
            SXSSFCell cell8 = row.createCell(colIndex++);
            if (minuteData.getVoc() != null) {
                cell8.setCellValue(minuteData.getVoc().toString());
                cell8.setCellStyle(cellStyle);
            }
            SXSSFCell cell9 = row.createCell(colIndex++);
            if (minuteData.getTs() != null) {
                cell9.setCellValue(minuteData.getTs().toString());
                cell9.setCellStyle(cellStyle);
            }
            SXSSFCell cell10 = row.createCell(colIndex++);
            if (minuteData.getXsw() != null) {
                cell10.setCellValue(minuteData.getXsw().toString());
                cell10.setCellStyle(cellStyle);
            }
            SXSSFCell cell11 = row.createCell(colIndex++);
            if (minuteData.getQr() != null) {
                cell11.setCellValue(minuteData.getQr().toString());
                cell11.setCellStyle(cellStyle);
            }
            SXSSFCell cell12 = row.createCell(colIndex++);
            if (minuteData.getJha() != null) {
                cell12.setCellValue(minuteData.getJha().toString());
                cell12.setCellStyle(cellStyle);
            }
            SXSSFCell cell13 = row.createCell(colIndex++);
            if (minuteData.getFja() != null) {
                cell13.setCellValue(minuteData.getFja().toString());
                cell13.setCellStyle(cellStyle);
            }
            SXSSFCell cell14 = row.createCell(colIndex++);
            if (minuteData.getGpsx() != null) {
                cell14.setCellValue(minuteData.getGpsx().toString());
                cell14.setCellStyle(cellStyle);
            }
            SXSSFCell cell15 = row.createCell(colIndex++);
            if (minuteData.getGpsy() != null) {
                cell15.setCellValue(minuteData.getGpsy().toString());
                cell15.setCellStyle(cellStyle);
            }
        }

        return workbook;
    }

    //测试导出工具
    public static void main(String[] args) {
        List<MinuteData> objects = new ArrayList<>();
        for (int i = 0; i < 1200000; i++) {
            MinuteData minuteData = new MinuteData("21", "546", 546, new BigDecimal(5654655L),
                    new BigDecimal(5654655L), new BigDecimal(5654655L), new BigDecimal(5654655L), new BigDecimal(5654655L),
                    new BigDecimal(5654655L), new BigDecimal(5654655L), new BigDecimal(5654655L),
                    new BigDecimal(5654655L), new BigDecimal(5654655L), new BigDecimal(5654655L), new Date(),
                    new BigDecimal(5654655L), "5654655L", "5654655L");
            objects.add(minuteData);
            System.out.println("第：" + i + 1 + "条数据" + "----list size=" + objects.size());
        }

        long create = System.currentTimeMillis();
        System.out.println("导出完成...共" + objects.size() + "条数据");
        System.out.println("从建立workbook 对象开始.开始写入硬盘之前,用时:" + (System.currentTimeMillis() - create) + "毫秒");

        SXSSFWorkbook sheets = exportExcel(objects, "");

        //写时间
        long xie = System.currentTimeMillis();

        //文件 写入硬盘
        File file = new File("C://Users/mayn/Desktop");
        if (!file.exists()) file.mkdirs();// 如果不存在就创建该文件夹目录
        try {
            System.out.println("正在导出xlsx...");
            FileOutputStream os = new FileOutputStream(file.getAbsolutePath() + File.separator + "sheetName" + ".xlsx");
            sheets.write(os);
            os.flush();// 刷新此输出流并强制将所有缓冲的输出字节写出
            os.close();// 关闭流
            sheets.dispose();// 释放workbook所占用的所有 系统 资源

            System.out.println("释放资源，文件写入硬盘时间：" + (System.currentTimeMillis() - xie) + "毫秒");
        } catch (Exception e) {
            e.printStackTrace();
        }

        ExcelUtil.exportExcel(objects, "----date---");
    }

}