//所需依赖
<dependency> <groupId>net.sf.jxls</groupId> <artifactId>jxls-core</artifactId> <version>1.0.6</version> </dependency> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls</artifactId> <version>2.4.7</version> </dependency> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-jexcel</artifactId> <version>1.0.7</version> </dependency> <dependency> <groupId>org.testng</groupId> <artifactId>testng</artifactId> <version>RELEASE</version> </dependency>
@RequestMapping("/exportExcelqymx") public void exportExcelqymx(HttpServletRequest request, HttpServletResponse response,String monitorDate,String RegionCode,String MaintainEntName,String entid) { try { Date date = new Date(); Calendar instance = Calendar.getInstance(); instance.setTime(date); //实现日期减一操作,昨天 instance.add(Calendar.DATE, -1); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); //定义查询日期 monitorDate = dateFormat.format(instance.getTime()); List list = new ArrayList(); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("MM月dd日"); String format = simpleDateFormat.format(instance.getTime()); List<ExcelImpl> findT_data_entpointlxmx = tdataentpointlxdwmxService.findT_data_entpointlxmx(monitorDate, RegionCode, MaintainEntName, entid, 1, 99999999); Map resultMap = new HashMap(); resultMap.put("month",format); int index = 0; for(int i=0;i<findT_data_entpointlxmx.size();i++) { ExcelImpl excelImpl = findT_data_entpointlxmx.get(i); String maintainEntName = excelImpl.getMaintainEntName(); Map resMaps = new HashMap(); resMaps.put("index", (index+1)+""); resMaps.put("qxmc", excelImpl.getRegionName()); resMaps.put("ywmc", excelImpl.getMaintainEntName()); resMaps.put("qymc", excelImpl.getEntName()); resMaps.put("jcdid", excelImpl.getPointId()); resMaps.put("jcdmc", excelImpl.getPointName()); resMaps.put("mn", excelImpl.getMn()); index++; list.add(resMaps); } //数据列表 resultMap.put("BodyList", list); //其他数据 resultMap.put("DateTime", DateUtil.getYearStr() + "年" + DateUtil.getMonthStr() + "月" + DateUtil.getDayOfMonthStr() + "日"); //模板文件位置 String filepath = templatePath + "excel_lxqyqd.xlsx"; //导出文件名字 String newFileName = "表4_" + DateUtil.getMonth() + "月" + DateUtil.getDayOfMonthStr() + "日离线企业清单.xlsx"; kbToCreateExcels(request, response, resultMap, filepath, newFileName); }catch(Exception e) { e.printStackTrace(); } } private void kbToCreateExcel(HttpServletRequest request, HttpServletResponse response, Map resultMap, String filepath, String newFileName) throws InvalidFormatException { XLSTransformer transformer = new XLSTransformer(); InputStream is = null; try { is = new BufferedInputStream(new FileInputStream(filepath)); } catch (FileNotFoundException e1) { e1.printStackTrace(); } // InputStream, List, List, String, Map, int HSSFWorkbook workBook = (HSSFWorkbook) transformer.transformXLS(is, resultMap); //操作Excel2003以前(包括2003)的版本,扩展名是.xls
// XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(is, resultMap); //是操作Excel2007的版本,扩展名是.xlsx saveWorkbook(workBook, response, newFileName.trim()); try { is.close(); } catch (IOException e) { e.printStackTrace(); } } private void saveWorkbook(HSSFWorkbook resultWorkbook, HttpServletResponse response, String fileName) { try { response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8")); response.setContentType("application/msexcel"); OutputStream os = response.getOutputStream(); resultWorkbook.write(os); os.flush(); os.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
excel模板:
参考博客地址:https://www.freesion.com/article/2517161403/