Eazy Excel 无复杂表头导出excel任意数据量，都不会溢出

先引入依赖
<!-- poi start -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>
<!-- poi end -->

封装PageUtil 工具类


public class PageUtil {

    //总记录数
    //每页显示的记录数
    //页数
    public static int count(int rows,int eachPageSize){ //计算总页数
        int pageCount =(rows-1)/eachPageSize+1;
        return pageCount;
    }

    //计算分页,第几页，每页多少条
    public static Map<String,Object> page(Integer whichPage,Integer pageCount){
        if(whichPage == null || pageCount == null){
            return null;
        }
        Map<String,Object> result = new HashMap<>();
        int rowsStart = (whichPage - 1) * pageCount;
        result.put("rowsStart",rowsStart); //limit 起始
        result.put("pageCount",pageCount); //limit 限制数量
        return result;
    }

}

controller

@Resource
Testmapper testmapper;
 /**
    * @Author 
    * @Description 
    * @param  
     * filename 导出文件名
     * sheetName sheet页名
     * title 表头列名
     * titleColumn 每列表头对应实体属性
     * HttpServletRequest request
     * HttpServletResponse response
    * @return   
    */
    @RequestMapping("/daochu")
    public void daochu(@RequestParam(value = "filename",defaultValue = "测试导出") String filename,
                       @RequestParam(value = "sheetName",defaultValue = "sheet") String sheetName,
                       @RequestParam(value = "title",defaultValue = "主键id,随机UUID,名字,性别,地址,身份证号," +
                               "昵称,昵称2,昵称3,昵称4,昵称5,昵称6,昵称7,昵称8,昵称9,昵称10") String title,
                       @RequestParam(value = "titleColumn",defaultValue ="id,uid,name,sex,address,idcard," +
                               "nickname,nickname2,nickname3,nickname4,nickname5,nickname6,nickname7,nickname8," +
                               "nickname9,nickname10" ) String titleColumn,
                       HttpServletRequest request,
                       HttpServletResponse response) throws IOException {

        exportExcel(filename,sheetName,title,titleColumn,request,response);

    }
    public void exportExcel(String filename, String sheetName,
                            String title, String titleColumn,
                            HttpServletRequest request, HttpServletResponse response) {
        //表头
        String titleGroup[] = title.split(",");
        //表头对应字段
        String titleColumnGroup[] = titleColumn.split(",");
        if(StringUtils.isEmpty(filename)){
            System.out.println("文件名为空");
            return;
        }
        //每个sheet 页数据（ 最高1040000 多，建议100w ）
        int everySheetCount = 100000 * 10;
        //每次sql查询几条
        int eachPageSize = 10000;
        filename = filename + ".xlsx";

        //数据总条数，涉及DB 层 ！！！！！！！
        int sum = testmapper.sums();
        //一次导出10000条数据，总页数，查几次库
        int count = new PageUtil().count(sum,eachPageSize);
        SXSSFWorkbook wb = null;
            try {
                long startTime = System.currentTimeMillis();
                wb = new SXSSFWorkbook();//默认100行，超100行将写入临时文件
                wb.setCompressTempFiles(false); //是否压缩临时文件，否则写入速度更快，但更占磁盘，但程序最后是会将临时文件删掉的
                Sheet sheet = null;
                int sheetNumber = 1; //第几sheet 页
                //sheet = wb.createSheet("Sheet "+sheetNumber);
                //定义Row和Cell变量, Rows从0开始.
                Row row;
                Cell cell;
                int whichRow = 0; //第几行 默认 0 ,第0行要插入标题
                List<Map<String,Object>> Pagelist = new ArrayList<>();
                for (int rowNumber = 1; rowNumber <= count; rowNumber++) { //循环每一页！！！
                    //计算 sql limit 分页
                    Map<String, Object> page = PageUtil.page(rowNumber,eachPageSize);
                    //分页查数据，，涉及DB 层 ！！！！！！
                    Pagelist = testmapper.pagelist(page);
                    for(int i = 0;i<Pagelist.size();i++){ //循环每一行
                        //处理表头
                        if((whichRow)%everySheetCount == 0){ //取模正好等于0 ，每个sheet 页正好100w 条数据，
                            //创建新的 sheet 页
                            sheet = wb.createSheet(sheetName+sheetNumber);
                            whichRow = 0; //新的sheet 页重置行号；
                            sheetNumber++; //sheet 页加 1

                            row = sheet.createRow(whichRow);
                            CellStyle style = this.rowHeadStyle(wb);
                            //遍历列
                            for (int cellNumber = 0; cellNumber < titleGroup.length; cellNumber++) {
                                cell = row.createCell(cellNumber);
                                cell.setCellStyle(style);
                                cell.setCellValue(titleGroup[cellNumber]); //表头
                            }
                            whichRow++; //行号+1
                        }
                        //处理数据
                        row = sheet.createRow(whichRow);
                        System.err.println("创建第 "+whichRow+" 行");
                        for (int cellNumber = 0; cellNumber < titleColumnGroup.length; cellNumber++) {
                            cell = row.createCell(cellNumber);
                            cell.setCellValue(Pagelist.get(i).get(titleColumnGroup[cellNumber]).toString());
                        }
                        whichRow ++;

                    }
                    Pagelist.clear(); //清空list
                }
                //下载文件
                download1(filename,wb,request,response);
                long endTime = System.currentTimeMillis();
                System.out.println("process " + sum + " spent time:" + (endTime - startTime) + " ms.");
            } catch (Exception ex) {
                ex.printStackTrace();
            } finally {
                if (wb != null) {
                    wb.dispose();// 删除临时文件，很重要，否则磁盘可能会被写满
                }
                try {
                    if(wb != null) wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
    }
    //表头样式
    public CellStyle rowHeadStyle (SXSSFWorkbook wb){
        //创建表格样式
        CellStyle style = wb.createCellStyle();
        //单元格水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //单元格垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //填充方案
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //表格背景色
        style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
        //创建字体样式
        Font font = wb.createFont();
        //字体加粗
        font.setBold(true);
        //字体高度 11
        font.setFontHeightInPoints((short)11);
        //字体颜色
        font.setColor(Font.COLOR_NORMAL);
        //字体
        font.setFontName("宋体");
        style.setFont(font);
        return  style;
    }
	//下载
    public void download1(String filename,SXSSFWorkbook wb,HttpServletRequest request,HttpServletResponse response) throws IOException {
        ServletOutputStream out1 =response.getOutputStream();
        try{
            response.reset();
            response.setContentType("application/msexcel;charset=utf-8");
            String userAgent = request.getHeader("user-agent");
            if (userAgent !=null && userAgent.indexOf("Edge") >= 0){
                filename = URLEncoder.encode(filename,"UTF8");
            }else if(userAgent.indexOf("Firefox")>=0 || userAgent.indexOf("Chrome")>=0
                    || userAgent.indexOf("Safari")>=0){
                filename = new String ((filename).getBytes(StandardCharsets.UTF_8),"ISO8859-1");
            }else {
                filename = new String(filename.getBytes(),"ISO8859-1");
            }
            response.setHeader("content-disposition","attachment;filename="+filename);
            wb.write(out1);
            out1.flush();
        }catch (Exception e){

        }finally {
            out1.close();
        }
    }

mapper 接口

@Mapper
public interface Testmapper {

    int sums();
    List<Map<String,Object>> pagelist(@Param("page") Map<String, Object> page);

}

mapper.xml

<select id="sums" resultType="Integer">
  select count(1) from testuser_copy ;
</select>
<select id="pagelist" resultType="java.util.HashMap">
        select
        id,
        uid,
        name,
        sex,
        address,
        idcard,
        nickname,
        nickname2,
        nickname3,
        nickname4,
        nickname5,
        nickname6,
        nickname7,
        nickname8,
        nickname9,
        nickname10
        from testuser_copy
      limit ${page.rowsStart},${page.pageCount}
</select>