小猫咪心好痛 2022-10-17 17:55 采纳率: 88.9%
浏览 83
已结题

excel模板导出信息不全

创建excel模板导出,但是信息不全,只能导出最后一次for循环的数据,前面几次都导不出来,如果把其他的for循环//了就能显示出来,合并单元格中填充的信息也显示不全

img

img

public AjaxResult exportResult(@RequestBody RiskStatistic riskStatistics) {
        //获取风险信息数据
        RiskStatistic risk = riskObjectService.riskStatistic(riskStatistics);
        List<RiskObject> object = risk.getRiskObjects();
        List<RiskCell> cell = risk.getRiskCells();
        List<RiskEvent> event = risk.getRiskEvents();
        List<RiskMeasure> measure = risk.getRiskMeasures();
        //创建HSSFWorkbook对象
        HSSFWorkbook hssfworkbook = new HSSFWorkbook();
        //生成HSSFSheet对象
        HSSFSheet hssfSheet = hssfworkbook.createSheet("风险管控清单表");
        hssfSheet.setDefaultRowHeightInPoints(20);
        hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 24));//起始行,结束行,起始列,终止列
        hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));
        hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 11));
        hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 18));
        hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 19, 24));
        //画图的顶级管理器,一个sheet只能获取一个(一定要注意这点)
        HSSFPatriarch patriarch = hssfSheet.createDrawingPatriarch();
        Drawing drawing = hssfSheet.createDrawingPatriarch();
        //excel表格的样式都是通过创建cellstyle实现的
        /*
         * 标题
         */
        HSSFCellStyle cellSheetStyle = hssfworkbook.createCellStyle();
        //内容居中
        cellSheetStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellSheetStyle.setAlignment(HorizontalAlignment.CENTER);
        //字体
        HSSFFont fontSheet = hssfworkbook.createFont();
        fontSheet.setFontName("宋体");
        fontSheet.setFontHeightInPoints((short) 18);//设置字体大小
        fontSheet.setBold(true);
        cellSheetStyle.setFont(fontSheet);
        //边框
        cellSheetStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellSheetStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellSheetStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellSheetStyle.setBorderRight(BorderStyle.THIN);//右边框
        //填写标题行
        //生成HSSFRow行
        HSSFRow row0 = hssfSheet.createRow(0);
        row0.setHeightInPoints(35);//设置行高
        HSSFRow row1 = hssfSheet.createRow(1);
        row1.setHeightInPoints(35);//设置行高
        HSSFRow row2 = hssfSheet.createRow(2);
        row2.setHeightInPoints(35);//设置行高
        //生成HSSFCell
        HSSFCell cell0 = row0.createCell(0);
        HSSFCell cell1 = row1.createCell(1);
        HSSFCell cell2 = row2.createCell(2);
        //填充文本值
        cell0.setCellValue("风险管控清单表");
        cell0.setCellStyle(cellSheetStyle);

        /*
         * 标头
         */
        //标头1样式
        HSSFCellStyle cellStyle1 = hssfworkbook.createCellStyle();
        //内容居中
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);
        cellStyle1.setWrapText(true);//设置自动换行
        //设置颜色和设置为前景色
        cellStyle1.setFillForegroundColor(IndexedColors.TEAL.index);
        cellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //字体
        HSSFFont font = hssfworkbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);//设置字体大小
        cellStyle1.setFont(font);
        //边框
        cellStyle1.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle1.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle1.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle1.setBorderRight(BorderStyle.THIN);//右边框
        //标头2样式
        HSSFCellStyle cellStyle2 = hssfworkbook.createCellStyle();
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);
        cellStyle2.setWrapText(true);//设置自动换行

        HSSFFont font2 = hssfworkbook.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 11);//设置字体大小
        cellStyle2.setFont(font2);
        cellStyle2.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle2.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle2.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle2.setBorderRight(BorderStyle.THIN);//右边框
        //标头3样式
        HSSFCellStyle cellStyle3 = hssfworkbook.createCellStyle();
        cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle3.setAlignment(HorizontalAlignment.CENTER);
        cellStyle3.setWrapText(true);//设置自动换行
        cellStyle3.setFillForegroundColor(IndexedColors.TEAL.index);
        cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFFont font3 = hssfworkbook.createFont();
        font3.setFontName("宋体");
        font3.setFontHeightInPoints((short) 11);//设置字体大小
        cellStyle3.setFont(font3);
        cellStyle3.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle3.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle3.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle3.setBorderRight(BorderStyle.THIN);//右边框
        //标头4样式
        HSSFCellStyle cellStyle4 = hssfworkbook.createCellStyle();
        cellStyle4.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle4.setAlignment(HorizontalAlignment.CENTER);
        cellStyle4.setWrapText(true);//设置自动换行

        HSSFFont font4 = hssfworkbook.createFont();
        font4.setFontName("宋体");
        font4.setFontHeightInPoints((short) 11);//设置字体大小
        cellStyle4.setFont(font4);
        cellStyle4.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle4.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle4.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle4.setBorderRight(BorderStyle.THIN);//右边框

        cell1 = row1.createCell(0);
        cell1.setCellValue("风险分析对象");
        cell1.setCellStyle(cellStyle2);

        cell1 = row1.createCell(1);
        cell1.setCellValue("风险分析单元");
        cell1.setCellStyle(cellStyle1);

        cell1 = row1.createCell(2);
        cell1.setCellValue("风险分析事件");
        cell1.setCellStyle(cellStyle2);

        cell1 = row1.createCell(3);
        cell1.setCellValue("风险管控");
        cell1.setCellStyle(cellStyle1);


        //插入数据
        row2 = hssfSheet.createRow(2);
        row2.setHeightInPoints(35);
        cell2 = row2.createCell(0);
        cell2.setCellValue("编号");
        cell2.setCellStyle(cellStyle1);
        // 设置默认列宽,width为字符个数   256*x+184
        hssfSheet.setColumnWidth(0, 256 * 6 + 184);

        cell2 = row2.createCell(1);
        cell2.setCellValue("风险分析对象");
        cell2.setCellStyle(cellStyle1);
        hssfSheet.setColumnWidth(1, 256 * 28 + 250);
        cell2 = row2.createCell(2);
        cell2.setCellValue("类型");
        cell2.setCellStyle(cellStyle1);
        hssfSheet.setColumnWidth(2, 256 * 28 + 184);
        cell2 = row2.createCell(3);
        cell2.setCellValue("危险源类型");
        cell2.setCellStyle(cellStyle1);
        hssfSheet.setColumnWidth(3, 256 * 17 + 184);
        cell2 = row2.createCell(4);
        cell2.setCellValue("责任部门");
        cell2.setCellStyle(cellStyle1);
        hssfSheet.setColumnWidth(4, 256 * 17 + 184);
        cell2 = row2.createCell(5);
        cell2.setCellValue("责任人");
        cell2.setCellStyle(cellStyle1);
        hssfSheet.setColumnWidth(5, 256 * 17 + 184);

        cell2 = row2.createCell(6);
        cell2.setCellValue("风险分析单元名称");
        cell2.setCellStyle(cellStyle2);
        hssfSheet.setColumnWidth(6, 256 * 17 + 184);
        cell2 = row2.createCell(7);
        cell2.setCellValue("风险点类型");
        cell2.setCellStyle(cellStyle2);
        hssfSheet.setColumnWidth(7, 256 * 15 + 184);
        cell2 = row2.createCell(8);
        cell2.setCellValue("具有中毒、爆炸、火灾等危险场所");
        cell2.setCellStyle(cellStyle2);
        hssfSheet.setColumnWidth(8, 256 * 25 + 184);
        cell2 = row2.createCell(9);
        cell2.setCellValue("设备名称");
        cell2.setCellStyle(cellStyle2);
        hssfSheet.setColumnWidth(9, 256 * 28 + 184);
        cell2 = row2.createCell(10);
        cell2.setCellValue("设备编号");
        cell2.setCellStyle(cellStyle2);
        hssfSheet.setColumnWidth(10, 256 * 15 + 184);
        cell2 = row2.createCell(11);
        cell2.setCellValue("作业活动编号");
        cell2.setCellStyle(cellStyle2);
        hssfSheet.setColumnWidth(11, 256 * 20 + 184);

        cell2 = row2.createCell(12);
        cell2.setCellValue("风险分析事务名称");
        cell2.setCellStyle(cellStyle3);
        hssfSheet.setColumnWidth(12, 256 * 20 + 184);
        cell2 = row2.createCell(13);
        cell2.setCellValue("可能性(L)");
        cell2.setCellStyle(cellStyle3);
        hssfSheet.setColumnWidth(13, 256 * 17 + 184);
        cell2 = row2.createCell(14);
        cell2.setCellValue("严重度(S)");
        cell2.setCellStyle(cellStyle3);
        hssfSheet.setColumnWidth(14, 256 * 28 + 184);
        cell2 = row2.createCell(15);
        cell2.setCellValue("风险值");
        cell2.setCellStyle(cellStyle3);
        hssfSheet.setColumnWidth(15, 256 * 15 + 184);
        cell2 = row2.createCell(16);
        cell2.setCellValue("风险等级");
        cell2.setCellStyle(cellStyle3);
        hssfSheet.setColumnWidth(16, 256 * 20 + 184);
        cell2 = row2.createCell(17);
        cell2.setCellValue("管控级别");
        cell2.setCellStyle(cellStyle3);
        hssfSheet.setColumnWidth(17, 256 * 20 + 184);
        cell2 = row2.createCell(18);
        cell2.setCellValue("建议新增(改进措施)");
        cell2.setCellStyle(cellStyle3);
        hssfSheet.setColumnWidth(18, 256 * 20 + 184);

        cell2 = row2.createCell(19);
        cell2.setCellValue("管控方式");
        cell2.setCellStyle(cellStyle4);
        hssfSheet.setColumnWidth(19, 256 * 20 + 184);
        cell2 = row2.createCell(20);
        cell2.setCellValue("管控措施分类1");
        cell2.setCellStyle(cellStyle4);
        hssfSheet.setColumnWidth(20, 256 * 20 + 184);
        cell2 = row2.createCell(21);
        cell2.setCellValue("管控措施分类2");
        cell2.setCellStyle(cellStyle4);
        hssfSheet.setColumnWidth(21, 256 * 20 + 184);
        cell2 = row2.createCell(22);
        cell2.setCellValue("管控措施分类3");
        cell2.setCellStyle(cellStyle4);
        hssfSheet.setColumnWidth(22, 256 * 20 + 184);
        cell2 = row2.createCell(23);
        cell2.setCellValue("管控措施描述");
        cell2.setCellStyle(cellStyle4);
        hssfSheet.setColumnWidth(23, 256 * 20 + 184);
        cell2 = row2.createCell(24);
        cell2.setCellValue("隐患排查内容");
        cell2.setCellStyle(cellStyle4);
        hssfSheet.setColumnWidth(24, 256 * 20 + 184);

      /*
        数据
         */
        HSSFCellStyle cellDataStyle = hssfworkbook.createCellStyle();
        //内容居中
        cellDataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellDataStyle.setAlignment(HorizontalAlignment.CENTER);
        cellDataStyle.setWrapText(true);//设置自动换行
        //字体
        HSSFFont fontData = hssfworkbook.createFont();
        fontData.setFontName("宋体");
        fontData.setFontHeightInPoints((short) 11);//设置字体大小
        cellDataStyle.setFont(fontData);
        //边框
        cellDataStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellDataStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellDataStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellDataStyle.setBorderRight(BorderStyle.THIN);//右边框
        //添加数据
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;

        for (int i = 0; i < object.size(); i++) {
            RiskObject objects = object.get(i);
            //创建行
            row2 = hssfSheet.createRow(i + 3);
            row2.setHeightInPoints(58);
            cell2 = row2.createCell(0);
            cell2.setCellValue(objects.getNumber());
            cell2.setCellStyle(cellDataStyle);
            cell2 = row2.createCell(1);
            cell2.setCellValue(objects.getName());
            cell2.setCellStyle(cellDataStyle);
            cell2 = row2.createCell(2);
            cell2.setCellValue(DictUtils.getDictLabel("prevention_risk_object_type", objects.getTypeValue()));
            cell2.setCellStyle(cellDataStyle);
            cell2 = row2.createCell(3);
            cell2.setCellValue(DictUtils.getDictLabel("risk_danger_type", objects.getHazardStatus()));
            cell2.setCellStyle(cellDataStyle);
            cell2 = row2.createCell(4);
            cell2.setCellValue(objects.getDeptName());
            cell2.setCellStyle(cellDataStyle);
            cell2 = row2.createCell(5);
            cell2.setCellValue(objects.getDutyPerson());
            cell2.setCellStyle(cellDataStyle);

            for (int j = 0; j < cell.size(); j++) {
                RiskCell cells = cell.get(j);
                //创建行
                row2 = hssfSheet.createRow(j + 3);
                row2.setHeightInPoints(58);
                cell2 = row2.createCell(6);
                cell2.setCellValue(cells.getName());
                cell2.setCellStyle(cellDataStyle);
//                cell2 = row2.createCell(7);
//                cell2.setCellValue(DictUtils.getDictLabel("risk_point_type", cells.getRiskType()));
//                cell2.setCellStyle(cellDataStyle);
//                cell2 = row2.createCell(8);
//                cell2.setCellValue(DictUtils.getDictLabel("is_dangerous_place", cells.getIsDangerousPlace()));
//                cell2.setCellStyle(cellDataStyle);
                cell2 = row2.createCell(9);
                cell2.setCellValue(cells.getEquipmentName());
                cell2.setCellStyle(cellDataStyle);
                cell2 = row2.createCell(10);
                cell2.setCellValue(cells.getEquipmentCode());
                cell2.setCellStyle(cellDataStyle);
                cell2 = row2.createCell(11);
                cell2.setCellValue(cells.getActivityCode());
                cell2.setCellStyle(cellDataStyle);

                for (int x = 0; x < event.size(); x++) {
                    RiskEvent events = event.get(x);
                    //创建行
                    row2 = hssfSheet.createRow(x + 3);
                    row2.setHeightInPoints(58);
                    cell2 = row2.createCell(12);
                    cell2.setCellValue(events.getName());
                    cell2.setCellStyle(cellDataStyle);
                    cell2 = row2.createCell(13);
                    cell2.setCellValue(events.getPossibility());
                    cell2.setCellStyle(cellDataStyle);
                    cell2 = row2.createCell(14);
                    cell2.setCellValue(events.getSeverity());
                    cell2.setCellStyle(cellDataStyle);
                    cell2 = row2.createCell(15);
                    cell2.setCellValue(events.getRiskValue());
                    cell2.setCellStyle(cellDataStyle);
                    cell2 = row2.createCell(16);
                    cell2.setCellValue(events.getRiskLevel());
                    cell2.setCellStyle(cellDataStyle);
                    cell2 = row2.createCell(17);
                    cell2.setCellValue(events.getControlLevel());
                    cell2.setCellStyle(cellDataStyle);
                    cell2 = row2.createCell(18);
                    cell2.setCellValue(events.getMeasures());
                    cell2.setCellStyle(cellDataStyle);


                    for (int z = 0; z < measure.size(); z++) {
                        RiskMeasure measures = measure.get(z);
                        //创建行
                        row2 = hssfSheet.createRow(z + 3);
                        row2.setHeightInPoints(58);
                        cell2 = row2.createCell(19);
                        cell2.setCellValue(measures.getDataSrc());
                        cell2.setCellStyle(cellDataStyle);
                        cell2 = row2.createCell(20);
                        cell2.setCellValue(measures.getClassify1());
                        cell2.setCellStyle(cellDataStyle);
                        cell2 = row2.createCell(21);
                        cell2.setCellValue(measures.getClassify2());
                        cell2.setCellStyle(cellDataStyle);
                        cell2 = row2.createCell(22);
                        cell2.setCellValue(measures.getClassify3());
                        cell2.setCellStyle(cellDataStyle);
                        cell2 = row2.createCell(23);
                        cell2.setCellValue(measures.getRiskMeasureDesc());
                        cell2.setCellStyle(cellDataStyle);
                        cell2 = row2.createCell(24);
                        cell2.setCellValue(measures.getTroubleshootContent());
                        cell2.setCellStyle(cellDataStyle);
                    }
                }
            }
        }
        //获取下载路径
        //获取当前时间
        Date date = new Date();
        DateFormat format = new SimpleDateFormat("yyyy/MM/dd");
        String downloadPath = format.format(date);
        //获取文件下载路径
        File newFile = new File("./temporaryFile" + "/" + downloadPath);
        if (!newFile.exists()) { //如果没有就创建
            newFile.mkdirs();
        }
        //把文件下载到下载路径
        String uuid = UUID.randomUUID().toString();
        File fos = new File(newFile + File.separator + "风险管控清单表_" + uuid + ".xls");
        try {
            hssfworkbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //设置返回路径
        String path = preventionDownloadWordConfig.getDownloadWordUrl() + "/" + downloadPath + "/" + "风险管控清单表_" + uuid + ".xls";
        //返回路径
        return AjaxResult.success(path);
    }



}
  • 写回答

2条回答 默认 最新

  • kj0922 2022-10-18 08:57
    关注

    createRow之前先getRow一下,获取一个对象row,判断row是否为空,为空的时候再createRow,不为空则getRow作为row对象使用
    这样做是因为之前row对象下面的cell可能会有数据,createRow的时候会把下面的cell都刷空

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

问题事件

  • 系统已结题 10月27日
  • 已采纳回答 10月19日
  • 赞助了问题酬金1元 10月18日
  • 创建了问题 10月17日

悬赏问题

  • ¥15 streamingtool
  • ¥15 MATLAB图像问题
  • ¥20 树莓派5做人脸情感识别与反馈系统
  • ¥15 selenium 控制 chrome-for-testing 在 Linux 环境下报错 SessionNotCreatedException
  • ¥15 使用pyodbc操作SQL数据库
  • ¥15 MATLAB实现下列
  • ¥30 mininet可视化打不开.mn文件
  • ¥50 C# 全屏打开Edge浏览器
  • ¥80 WEBPACK性能优化
  • ¥30 python拟合回归分析