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$