创建excel模板导出，但是信息不全，只能导出最后一次for循环的数据，前面几次都导不出来，如果把其他的for循环//了就能显示出来，合并单元格中填充的信息也显示不全
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);
}
}
