poi分页导出，只能解决十几万数据的导出，内存问题没解决，有方案的可以留言

最新推荐文章于 2022-10-27 22:15:00 发布
麻花2013
最新推荐文章于 2022-10-27 22:15:00 发布
阅读量881
点赞数
分类专栏： java技术文章标签： poi分页导出
本文链接：https://blog.csdn.net/u012269637/article/details/78904900
版权
java技术专栏收录该内容
59 篇文章 0 订阅
订阅专栏
public void exportBulldingRoomList(TaskCustomSearchQo taskCustomSearchQo, HttpServletResponse response) throws Exception {
    String fileName = "数据采集统计表";
    response.setContentType("application/application/vnd.ms-excel");
    response.setHeader("Content-disposition",
            "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
    String status = String.valueOf(taskCustomSearchQo.getStatus());
    taskCustomSearchQo.setOffset(0);
    int limit=taskCustomSearchQo.getLimit();
    if (limit==0){
        limit=50;
    }
    taskCustomSearchQo.setLimit(200000);
    Integer userId = getUserId();
    if (userId == null) {
        return;
    }

        taskCustomSearchQo.setUserId(userId);
        List<TaskCustomVo> taskCustomVoParentList = bulldingRoomService.exportTaskCustomList(taskCustomSearchQo);
        String count=taskCustomSearchQo.getIsCount();
        if (StringUtils.isNotBlank(count)){
            if ("1".equals(count)){
                taskCustomVoParentList=  getOne(taskCustomVoParentList);
            }
        }

        List<TaskCustomVo> taskCustomVoList = new ArrayList<TaskCustomVo>();
        if (status!=null&&"4".equals(status)){
            List<Integer> taskids=new ArrayList<Integer>() ;
            for (TaskCustomVo taskCustomVo : taskCustomVoParentList) {
                taskids.add(taskCustomVo.getTaskTemplateId());
            }
            taskCustomSearchQo.setTaskids(taskids);
            List<TaskCustomVo> taskCustomVoChildList = bulldingRoomService.queryAllChildFinishTaskCustomList(taskCustomSearchQo);
            if (StringUtils.isNotBlank(count)){
                if ("1".equals(count)){
                    taskCustomVoChildList=getOne(taskCustomVoChildList);
                }
            }
            for (TaskCustomVo taskCustomVo2 : taskCustomVoParentList) {
                taskCustomVoList.add(taskCustomVo2);
                for (TaskCustomVo taskCustomVo3 : taskCustomVoChildList) {
                    if (taskCustomVo2.getTaskTemplateId()==taskCustomVo3.getParentId()){
                        taskCustomVoList.add(taskCustomVo3);
                    }
                }
            }
        }else{
            taskCustomVoList=taskCustomVoParentList;
        }

    //根据动态获取列表头名称加载相对的动态列数据
    taskCustomVoList = getFromData(status, taskCustomVoList, taskCustomSearchQo);
    OutputStream outputStream = response.getOutputStream();// 打开流
    // 声明一个工作薄
    SXSSFWorkbook wb = new SXSSFWorkbook(100);
        //临时文件进行压缩，建议不要true，否则会影响导出时间
    wb.setCompressTempFiles(false);
        int tatol = taskCustomVoList.size() - 1;
        int pagerow = 50000;
        int pages = tatol / pagerow;
        if (tatol < 50000) {
            //声明一个单子并命名
            SXSSFSheet sheet = wb.createSheet("数据采集统计表");
            sheet.setColumnWidth(1, 40 * 256);
            sheet.setColumnWidth(2, 30 * 256);
            //给单子名称一个长度
            sheet.setDefaultColumnWidth((short) 15);
            // 生成一个样式
            CellStyle style = wb.createCellStyle();
            //创建第一行（也可以称为表头）
            SXSSFRow row = sheet.createRow(0);
            //样式字体居中
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            //给表头第一行一次创建单元格
            SXSSFCell cell = row.createCell((short) 0);
            cell.setCellValue("任务编码");
            cell.setCellStyle(style);
            cell = row.createCell((short) 1);
            cell.setCellValue("任务名称");
            cell.setCellStyle(style);
            cell = row.createCell((short) 2);
            cell.setCellValue("任务所属区域");
            cell.setCellStyle(style);
            cell = row.createCell((short) 3);
            cell.setCellValue("状态");
            cell.setCellStyle(style);
            cell = row.createCell((short) 4);

            if (status != null && "4".equals(status)) {
                cell.setCellValue("完成时间");
                cell.setCellStyle(style);
                cell = row.createCell((short) 5);
                cell.setCellValue("完成人");
                cell.setCellStyle(style);
                cell = row.createCell((short) 6);

                cell.setCellValue("建筑种类");
                cell.setCellStyle(style);
                cell = row.createCell((short) 7);
                cell.setCellValue("是否有门牌");
                cell.setCellStyle(style);
                cell = row.createCell((short) 8);
                cell.setCellValue("建筑物参考定位");
                cell.setCellStyle(style);
                cell = row.createCell((short) 9);
                cell.setCellValue("街路巷门牌号");
                cell.setCellStyle(style);
                cell = row.createCell((short) 10);
                cell.setCellValue("备注");
                cell.setCellStyle(style);
            }
            //向单元格里填充数据
            for (int i = 0; i < taskCustomVoList.size(); i++) {
                row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue(taskCustomVoList.get(i).getTaskTemplateId());

                row.createCell(1).setCellValue(taskCustomVoList.get(i).getTaskName());
                row.createCell(2).setCellValue(taskCustomVoList.get(i).getNamePath());
                row.createCell(3).setCellValue(taskCustomVoList.get(i).getStatus());
                if (status != null && "4".equals(status)) {
                    row.createCell(4).setCellValue(taskCustomVoList.get(i).getFinishData());
                    row.createCell(5).setCellValue(taskCustomVoList.get(i).getTaskUserName());
                    row.createCell(6).setCellValue(taskCustomVoList.get(i).getBuildType());
                    row.createCell(7).setCellValue(taskCustomVoList.get(i).getHavaRoomNumber());
                    row.createCell(8).setCellValue(taskCustomVoList.get(i).getBuildingLocation());
                    row.createCell(9).setCellValue(taskCustomVoList.get(i).getRoomNumber());
                    row.createCell(10).setCellValue(taskCustomVoList.get(i).getRemarks());

                }
            }

        } else {
            for (int l = 0; l < pages + 1; l++) {
                int rows=0;
                int o = l * pagerow;
                //声明一个单子并命名
                SXSSFSheet sheet = wb.createSheet("数据采集统计表" + l);
                sheet.setColumnWidth(1, 40 * 256);
                sheet.setColumnWidth(2, 30 * 256);
                //给单子名称一个长度
                sheet.setDefaultColumnWidth((short) 15);
                // 生成一个样式
                CellStyle style = wb.createCellStyle();
                //创建第一行（也可以称为表头）
                SXSSFRow row = sheet.createRow(rows);
                //样式字体居中
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                style.setWrapText(true);
                //给表头第一行一次创建单元格
                SXSSFCell cell = row.createCell((short) 0);
                cell.setCellValue("任务编码");
                cell.setCellStyle(style);
                cell = row.createCell((short) 1);
                cell.setCellValue("任务名称");
                cell.setCellStyle(style);
                cell = row.createCell((short) 2);
                cell.setCellValue("任务所属区域");
                cell.setCellStyle(style);
                cell = row.createCell((short) 3);
                cell.setCellValue("状态");
                cell.setCellStyle(style);
                cell = row.createCell((short) 4);

                if (status != null && "4".equals(status)) {
                    cell.setCellValue("完成时间");
                    cell.setCellStyle(style);
                    cell = row.createCell((short) 5);
                    cell.setCellValue("完成人");
                    cell.setCellStyle(style);
                    cell = row.createCell((short) 6);

                    cell.setCellValue("建筑种类");
                    cell.setCellStyle(style);
                    cell = row.createCell((short) 7);
                    cell.setCellValue("是否有门牌");
                    cell.setCellStyle(style);
                    cell = row.createCell((short) 8);
                    cell.setCellValue("建筑物参考定位");
                    cell.setCellStyle(style);
                    cell = row.createCell((short) 9);
                    cell.setCellValue("街路巷门牌号");
                    cell.setCellStyle(style);
                    cell = row.createCell((short) 10);
                    cell.setCellValue("备注");
                    cell.setCellStyle(style);
                }
                for (int p = o; p < pagerow * (l + 1); p++) {
                    rows++;
                    if (p < tatol) {
                        //向单元格里填充数据
                        row = sheet.createRow(rows);
                        if (p>100000){
                            System.out.println(taskCustomVoList.get(p).getTaskTemplateId());
                        }
                        row.createCell(0).setCellValue(taskCustomVoList.get(p).getTaskTemplateId());
                        row.createCell(1).setCellValue(taskCustomVoList.get(p).getTaskName());
                        row.createCell(2).setCellValue(taskCustomVoList.get(p).getNamePath());
                        row.createCell(3).setCellValue(taskCustomVoList.get(p).getStatus());
                        if (status != null && "4".equals(status)) {
                            row.createCell(4).setCellValue(taskCustomVoList.get(p).getFinishData());
                            row.createCell(5).setCellValue(taskCustomVoList.get(p).getTaskUserName());
                            row.createCell(6).setCellValue(taskCustomVoList.get(p).getBuildType());
                            row.createCell(7).setCellValue(taskCustomVoList.get(p).getHavaRoomNumber());
                            row.createCell(8).setCellValue(taskCustomVoList.get(p).getBuildingLocation());
                            row.createCell(9).setCellValue(taskCustomVoList.get(p).getRoomNumber());
                            row.createCell(10).setCellValue(taskCustomVoList.get(p).getRemarks());

                        }
                    }
                    if (rows % 100 == 0) {
                        ((SXSSFSheet) sheet).flushRows();
                    }
                }
            }
        }

        wb.write(outputStream);// HSSFWorkbook写入流

        outputStream.flush();// 刷新流

        outputStream.close();// 关闭流
        wb.close();

}