excel文件 模板导出

spring mvc + poi

/**     * excel文件导出     * @param resource     * @param mobanName     * @return     */    public HSSFWorkbook downExcel1(ArrayList<Map<String, Object>> resource,String mobanName){        HSSFWorkbook wb=null;        //读取模板        try {            String fileDir = Class.class.getClass().getResource("/").getPath() + "doc";            File demoFile = new File(fileDir + "/"+mobanName);            FileInputStream in = new FileInputStream(demoFile);            wb = new HSSFWorkbook(in);            HSSFSheet sheet = wb.getSheetAt(0);            //拿到模板占位符位置            int lastRowNum = sheet.getLastRowNum();            ArrayList<Map> maps = new ArrayList<>();            HashMap<String, String> map = new HashMap<>();            for (int i = 0; i <= lastRowNum; i++) {                HSSFRow row = sheet.getRow(i);//行                int lastCellNum = row.getLastCellNum();                for (int j = 0; j < lastCellNum; j++) {                    String value = row.getCell(j).getStringCellValue();                    String regEx = "\\$.*?\\$";                    boolean result = Pattern.compile(regEx).matcher(value).find();                    if (result) {                        int x = j;//横                        int y = i;//纵                        String key = value.substring(1, value.length() - 1);                        HashMap<String, Object> tempMap = new HashMap<>();                        tempMap.put("x", x);                        tempMap.put("y", y);                        tempMap.put("key", key);                        maps.add(tempMap);                    }                }            }            //填充数据            for (int i = 0; i < resource.size(); i++) {                for (Map.Entry<String, Object> entry : resource.get(i).entrySet()) {                    maps.forEach(temp -> {                        if (entry.getKey().equals(temp.get("key"))) {                            Integer y = (Integer) temp.get("y");                            Integer x = (Integer) temp.get("x");                            HSSFCell cell = null;                            try {                                cell = sheet.getRow(y).getCell(x);//替换                                cell.setCellValue((String) entry.getValue());                            } catch (Exception e) {                                cell = sheet.createRow(y).createCell(x);//新增                                cell.setCellValue((String) entry.getValue());                            }                            temp.put("y", y + 1);                        }                    });                }            }        } catch (Exception e) {            e.printStackTrace();        }        return wb;    }    @ApiOperation("导出excel")    @GetMapping(value = "/excel")    @ResponseBody    public void downExcel(HttpServletResponse response, HttpServletRequest request) throws Exception {        ArrayList<Map<String, Object>> resource = new ArrayList<>();        List<JwOpinion> jwOpinions = new JwOpinion().selectAll();        jwOpinions.forEach(temp -> {            Map<String, Object> jsonObject = ObjectUtil.ObjectToJsonString(temp);            resource.add(jsonObject);        });        HSSFWorkbook wb = this.downExcel1(resource, "mo.xls");        //下载文件        String filename = "负面人员信息";        response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes(), "iso-8859-1") + ".xls");        response.setContentType("application/vnd.ms-excel");        request.setCharacterEncoding("UTF-8");        response.setCharacterEncoding("UTF-8");        OutputStream out = response.getOutputStream();        wb.write(out);        out.flush();        out.close();    }

模板 mo.xls

申请日期    出国开始时间  出国结束时间$applyDate$   $abroadBeginDate$   $abroadEndDate$