private static final String SHEET_NAME = "培养计划表"; /** * @param response * @param trainingName * @return */ @RequestMapping("/exportTrainingPlanMessageToExcel.do_") @ResponseBody public void exportTrainingPlanMessageToExcel(HttpServletResponse response, @RequestParam("trainingName") String trainingName) { //文件的默认保存名 String fileName = "exportTrainingPlanMessageToExcel.xls"; List<List<Object>> paramList = trainingPlanService.getParamList(trainingName); ExportEmployeeMessageToExcel.exportDataToExcel(fileName, response, paramList, SHEET_NAME); }
@Override public List<List<Object>> getParamList(String trainingName) { Map<String, Object> paramMap = new HashMap<>(); paramMap.put("trainingName", trainingName); List<List<Object>> resultList = new ArrayList<>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); List<TrainingPlan> dataList = trainingPlanDao.getTrainingPlanByTrainingName(paramMap); //添加excel文件表头 List<Object> headList = new ArrayList<>(); headList.add("创建时间"); headList.add("知识技能"); resultList.add(headList); //添加excel文件数据 for (TrainingPlan trainingPlan : dataList) { list.add(sdf.format(trainingPlan.getCreateTime())); list.add(trainingPlan.getKnowledgeSkills()); resultList.add(list); } return resultList; }
public class ExportEmployeeMessageToExcel { private static final Logger log = LoggerFactory.getLogger(ExportEmployeeMessageToExcel.class); /** * 批量将数据导入到excel中 * @param fileName 生成文件的名字 * @param response * @param paramList 要导入到excel中所有的数据 * @param sheetName excel sheet名字 * @return */ public static void exportDataToExcel(String fileName, HttpServletResponse response, List<List<Object>> paramList, String sheetName){ /** * 以下为生成Excel操作 */ // 1.创建一个workbook,对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); // 2.在workbook中添加一个sheet,对应Excel中的一个sheet HSSFSheet sheet = wb.createSheet(sheetName); // 3.在sheet中添加表头第0行,老版本poi对excel行数列数有限制short HSSFRow row = sheet.createRow(0); /*// 设置表头 HSSFCell cell = row.createCell(0); cell.setCellValue("表头1"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("表头2"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("表头3"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("表头4"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("表头5"); cell.setCellStyle(style);*/ // 循环将数据写入Excel,包括表头 for (int i = 0; i < paramList.size(); i++) { row = sheet.createRow((int) i); List list= paramList.get(i); // 创建单元格,设置值 for (int j = 0; j < list.size(); j++) { row.createCell(j).setCellValue(String.valueOf(list.get(j))); } } downloadExcel(wb, response, fileName); } /** * 将导好数据的excel文件下载下来,并打开下载页面 * @param fileName 生成文件的名字 * @param response * @return */ public static void downloadExcel(HSSFWorkbook wb, HttpServletResponse response, String fileName){ try { ByteArrayOutputStream os = new ByteArrayOutputStream(); wb.write(os); byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数,可以打开下载页面 response.reset(); response.setContentType("application/octet-stream; charset=UTF-8"); response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes(),"ISO8859-1")); ServletOutputStream out = response.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[1024]; int bytesRead; // Simple read/write loop. while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (Exception e) { log.error("异常情况为:" + e.getMessage()); } finally { if (wb != null) { wb.close(); } if (bis != null) { bis.close(); } if (bos != null) { bos.close(); } } } catch (IOException e) { log.error("异常情况为:" + e.getMessage()); } } }
POM:
<!-- poi,excel解析xls格式 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <!-- poi-ooxml,excel解析xlsx格式 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>net.sf.json-lib</groupId> <artifactId>json-lib</artifactId> <version>2.4</version> <classifier>jdk15</classifier> </dependency> <!--excel文件不需要转码成二进制--> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-resources-plugin</artifactId> <version>3.0.2</version> <configuration> <encoding>UTF-8</encoding> <nonFilteredFileExtensions> <nonFilteredFileExtension>xls</nonFilteredFileExtension> <nonFilteredFileExtension>xlsx</nonFilteredFileExtension> </nonFilteredFileExtensions> </configuration> </plugin>