// 第一步,创建一个webbook,对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet("平均分"); //设置列宽 sheet.setColumnWidth(0,12*500); sheet.setColumnWidth(1,25*500); sheet.setColumnWidth(2,12*500); sheet.setColumnWidth(3,5*500); sheet.setColumnWidth(4,20*500); sheet.setColumnWidth(5,5*500); //字体 HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 14);//设置字体大小 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体 HSSFFont font1 = wb.createFont(); font.setFontHeightInPoints((short) 12);//设置字体大小 // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short HSSFRow row = sheet.createRow((int) 0); row.setHeightInPoints(25); // 第四步,创建单元格 HSSFCellStyle styletitle = wb.createCellStyle();//头样式 styletitle.setWrapText(true);//设置自动换行 styletitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中 styletitle.setAlignment(HorizontalAlignment.CENTER);//水平居中 styletitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 styletitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 styletitle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 styletitle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 styletitle.setFont(font); HSSFCellStyle style = wb.createCellStyle();//内容样式 style.setFont(font1); style.setWrapText(true);//设置自动换行 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中 style.setAlignment(HorizontalAlignment.CENTER);//水平居中 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 HSSFCell cell = row.createCell((short) 0); cell.setCellValue("列1"); cell.setCellStyle(styletitle); cell = row.createCell((short) 1); cell.setCellValue("列2"); cell.setCellStyle(styletitle); cell = row.createCell((short) 2); cell.setCellValue("列13"); cell.setCellStyle(styletitle); cell = row.createCell((short) 3); cell.setCellValue("列4"); cell.setCellStyle(styletitle); cell = row.createCell((short) 4); cell.setCellValue("列5"); cell.setCellStyle(styletitle); cell = row.createCell((short) 5); cell.setCellValue("列6"); cell.setCellStyle(styletitle); // 第五步,写入实体数据 实际应用中这些数据从数据库得到, List<DepartmentScoreVo> departmentScoreVos = this.questionnaireService.selectScore(id); if(departmentScoreVos != null && departmentScoreVos.size() > 0){ int row1 = 1; int row2 = 1; int row3 = 0; if (departmentScoreVos != null && departmentScoreVos.size() > 0){ for (int i = 0; i < departmentScoreVos.size(); i++) { row1 = row3+1; // 第四步,创建单元格,并设置值 List<QuestionScoreVo> questionScoreVos = departmentScoreVos.get(i).getQuestionScoreVos(); if (questionScoreVos != null && questionScoreVos.size() > 0){ for (int j = 0; j < questionScoreVos.size(); j++){ row2 = row3+1; List<DepartmentMarkVo> departmentMarkVos = questionScoreVos.get(j).getDepartmentMarkVos(); if (departmentMarkVos != null && departmentMarkVos.size() > 0){ for (int k = 0; k < departmentMarkVos.size(); k++){ row = sheet.createRow(row3+1); row.setHeightInPoints(20); row3++; row.createCell((short) 0).setCellValue(departmentScoreVos.get(i).getDepartment().getName()); row.createCell((short) 1).setCellValue(questionScoreVos.get(j).getQuestion().getTitle()); row.createCell((short) 2).setCellValue(departmentMarkVos.get(k).getName()); if(departmentMarkVos != null && departmentMarkVos.get(k) != null && departmentMarkVos.get(k).getScore() != null){ row.createCell((short) 3).setCellValue(departmentMarkVos.get(k).getScore()); }else { row.createCell((short) 3).setCellValue("弃权"); } row.createCell((short) 4).setCellValue(departmentMarkVos.get(k).getCommentDesc()); if(questionScoreVos.get(j) != null && questionScoreVos.get(j).getAverageScore() != null && questionScoreVos.get(j).getAverageScore() != null){ row.createCell((short) 5).setCellValue(questionScoreVos.get(j).getAverageScore().doubleValue()); }else { row.createCell((short) 5).setCellValue(""); } row.getCell(0).setCellStyle(style); row.getCell(1).setCellStyle(style); row.getCell(2).setCellStyle(style); row.getCell(3).setCellStyle(style); row.getCell(4).setCellStyle(style); row.getCell(5).setCellStyle(style); } } if((row3 - row2) > 0){ sheet.addMergedRegion(new CellRangeAddress(row2,row3,1,1)); sheet.addMergedRegion(new CellRangeAddress(row2,row3,5,5)); } } } if ((row3 - row1) > 0){ sheet.addMergedRegion(new CellRangeAddress(row1,row3,0,0)); } } } } // 第六步,将文件存到指定位置 OutputStream out = null; try { out = response.getOutputStream(); String fileName = "表1.xls";// 文件名 response.setContentType("application/x-msdownload"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8")); wb.write(out); } catch (Exception e) { } finally { try { out.close(); } catch (IOException e) { } }
导出Excel表格
最新推荐文章于 2024-01-04 10:18:37 发布