SpringBoot向Excel模板中写入数据并下载 （无需获取file对象及模板绝对路径！！！）

最近工作有一个需求是要进行excel模板下载的，因为excel表头有一些复杂，所以没有用代码的方式来直接生成excel模板，而是将excel表格处理好以后，将查询出的数据写入excel中，而且我觉得最牛逼的是不需要获取file对象以及文件的绝对路径，兄弟们，绝bi好用，吐血推荐！！废话不多说，直接上excel和代码。(因为代码是自己写的，所以不涉及太多泄密内容，因此可以展示出来)

• excel模板如下，模板随意写，简单或更复杂的表头都可以。

代码如下：亲测好用，绝bi好用，兄弟们！！！

//我完全是在controller中写的 因此代码全部奉上
/*** 指标监测报表导出* * @param request* @return* @throws Exception*/@RequestMapping("/excelTable")public void excelTable(HttpServletResponse response, HttpServletRequest request) throws Exception{Map map = new HashMap<>();// 获取导出的时间参数String date = request.getParameter("Date");map.put("Date", date);String fileName = "zbjc.xlsx";// 使用类加载器获取excel文件流,基于模板填充数据ClassPathResource classPathResource = new ClassPathResource(fileName);InputStream is = null;XSSFWorkbook workbook = null;try {is = classPathResource.getInputStream();workbook = new XSSFWorkbook(is);XSSFSheet sheet = null;// 获取第一个sheet页// getSheetAt和getsheet参数不一样， getSheetAt 的参数是索引，// getSheet的参数是sheet的名称， 获取具体名称的sheet。sheet = workbook.getSheetAt(0);//去数据库中查询出的数据，这块就不给大家看了List resutList = sgwpdmSystemParameterService.findAll("02", map);for (int i = 0; i < resutList.size(); i++) {int j = 4 + i;writeExcel(sheet, resutList, j, i);}} catch (IOException e) {e.printStackTrace();}//文件下載response.reset();response.setContentType("text/html;charset=UTF-8");response.setContentType("application/x-msdownload");String newName = "";try {newName = URLEncoder.encode("指标监测导出" + System.currentTimeMillis() + ".xlsx", "UTF-8");response.addHeader("Content-Disposition", "attachment;filename=\"" + newName + "\"");OutputStream toClient = new BufferedOutputStream(response.getOutputStream());workbook.write(toClient);toClient.flush();} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}//上述代码中有一个封装的方法，在下面展示
/*** 指标监测导出封装方法* * @param sheet* @param resutList*            数据集合* @param rownum*            excel行数* @param index*            集合索引 从0开始*/public void writeExcel(XSSFSheet sheet, List resutList, Integer rownum, Integer index) {if (resutList.get(index) != null && !"".equals(resutList.get(index))) {Row row = sheet.getRow(rownum);String proName = resutList.get(index).getProName();Cell cell = row.getCell(0);cell.setCellValue(proName);String regDayNum = resutList.get(index).getRegDayNum();// 注册人数当日cell = row.getCell(2);cell.setCellValue(regDayNum);String regNum = resutList.get(index).getRegNum();// 注册人数累计cell = row.getCell(3);cell.setCellValue(regNum);String loginDayNum = resutList.get(index).getLoginDayNum();// 登录人数当日cell = row.getCell(4);cell.setCellValue(loginDayNum);String loginNum = resutList.get(index).getLoginNum();// 登陆人数累计cell = row.getCell(5);cell.setCellValue(loginNum);String flowDayNum = resutList.get(index).getFlowDayNum();// 流程当日cell = row.getCell(6);cell.setCellValue(flowDayNum);String flowNum = resutList.get(index).getFlowNum();// 流程累计cell = row.getCell(7);cell.setCellValue(flowNum);String menuDayNum = resutList.get(index).getMenuDayNum();// 功能当日cell = row.getCell(8);cell.setCellValue(menuDayNum);String menuNum = resutList.get(index).getMenuNum();// 功能累计cell = row.getCell(9);cell.setCellValue(menuNum);}}//数据格式如下
{"successful": true,"resultValue": {"itemCount": 0,"items": [{"id": "0","regNum": "9","regDayNum": "0","loginNum": "8","loginDayNum": "0","flowNum": "1671","flowDayNum": "0","menuNum": "949","menuDayNum": "0","proName": "北京丰台"}, {"id": "1","regNum": "9","regDayNum": "0","loginNum": "6","loginDayNum": "0","flowNum": "1035","flowDayNum": "0","menuNum": "231","menuDayNum": "0","proName": "北京通州"}, {"id": "2","regNum": "5","regDayNum": "0","loginNum": "5","loginDayNum": "0","flowNum": "148","flowDayNum": "0","menuNum": "113","menuDayNum": "0","proName": "北京延庆"}, {"id": "3","regNum": "8","regDayNum": "0","loginNum": "2","loginDayNum": "0","flowNum": "500","flowDayNum": "0","menuNum": "10","menuDayNum": "0","proName": "北京城区"}, {"id": "4","regNum": "16","regDayNum": "0","loginNum": "7","loginDayNum": "0","flowNum": "1041","flowDayNum": "0","menuNum": "322","menuDayNum": "0","proName": "北京昌平"}, {"id": "5","regNum": "9","regDayNum": "0","loginNum": "6","loginDayNum": "0","flowNum": "377","flowDayNum": "0","menuNum": "186","menuDayNum": "0","proName": "北京门头沟"}, {"id": "6","regNum": "10","regDayNum": "0","loginNum": "6","loginDayNum": "0","flowNum": "396","flowDayNum": "0","menuNum": "528","menuDayNum": "0","proName": "北京大兴"}, {"id": "7","regNum": "7","regDayNum": "0","loginNum": "0","loginDayNum": "0","flowNum": "59","flowDayNum": "0","menuNum": "0","menuDayNum": "0","proName": "北京平谷"}, {"id": "8","regNum": "17","regDayNum": "0","loginNum": "5","loginDayNum": "0","flowNum": "963","flowDayNum": "0","menuNum": "250","menuDayNum": "0","proName": "北京朝阳"}, {"id": "9","regNum": "10","regDayNum": "0","loginNum": "6","loginDayNum": "0","flowNum": "1234","flowDayNum": "0","menuNum": "168","menuDayNum": "0","proName": "北京海淀"}, {"id": "10","regNum": "7","regDayNum": "0","loginNum": "5","loginDayNum": "0","flowNum": "519","flowDayNum": "0","menuNum": "5","menuDayNum": "0","proName": "北京石景山"}, {"id": "11","regNum": "11","regDayNum": "0","loginNum": "6","loginDayNum": "0","flowNum": "258","flowDayNum": "0","menuNum": "567","menuDayNum": "0","proName": "北京亦庄"}, {"id": "12","regNum": "11","regDayNum": "0","loginNum": "7","loginDayNum": "0","flowNum": "771","flowDayNum": "0","menuNum": "255","menuDayNum": "0","proName": "北京顺义"}, {"id": "13","regNum": "10","regDayNum": "0","loginNum": "8","loginDayNum": "0","flowNum": "789","flowDayNum": "0","menuNum": "333","menuDayNum": "0","proName": "北京房山"}, {"id": "14","regNum": "46","regDayNum": "0","loginNum": "28","loginDayNum": "0","flowNum": "1715","flowDayNum": "0","menuNum": "3356","menuDayNum": "0","proName": "建设咨询公司"}, {"id": "15","regNum": "9","regDayNum": "0","loginNum": "8","loginDayNum": "0","flowNum": "214","flowDayNum": "0","menuNum": "185","menuDayNum": "0","proName": "北京怀柔"}, {"id": "16","regNum": "9","regDayNum": "0","loginNum": "5","loginDayNum": "0","flowNum": "445","flowDayNum": "0","menuNum": "272","menuDayNum": "0","proName": "北京密云"}, {"id": "17","regNum": "22","regDayNum": "0","loginNum": "217","loginDayNum": "0","flowNum": "1149","flowDayNum": "0","menuNum": "6271","menuDayNum": "0","proName": "其它"}, {"id": "18","regNum": "225","regDayNum": "0","loginNum": "335","loginDayNum": "0","flowNum": "13284","flowDayNum": "0","menuNum": "14001","menuDayNum": "0","proName": "合计"}],"dicts": []},"resultHint": "","errorPage": "","type": ""
}

需要强调一下 springboot的excel文件都是要放在src/main/resources下的 不能放在代码包中，

放在代码包中本地运行没有问题，但是打成jar包上生产机时excel文件会丢失，而且excel模板文件的命名最好用英文，便于代码处理，不要怕名字的问题，导出时可以将excel文件进行改名，一定要特别注意，否则会报空指针异常。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！