springboot+Easypoi文档的导入与导出

EXCEL:导入

说明:

导入需要列与实体类的注解名一一对应,否则不会报错,会导致无法解析该列数据.导入时可以提前准备excel模板,先下载模板然后填写数据上传导入.

代码:

pom必须引

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.1.2</version>
</dependency>

导入:

public Response<String> Import(HttpServletResponse resp,HttpServletRequest request,MultipartFile file) {

        try {
            if (file==null){
                return ResponseUtil.buildErrorResponse(ResponseCode.BUS_EXCEPTION);
            }
            List<BusReimbursementVo> reimbursementVos = FileExcelUtil.importExcel(file, 0, 1, BusReimbursementVo.class);
            if (reimbursementVos.size()>0) {

            }
            System.out.println(reimbursementVos);

        } catch (Exception e) {
            e.printStackTrace();
            return ResponseUtil.buildErrorResponse(ResponseCode.BUS_EXCEPTION);
        }
        return ResponseUtil.buildErrorResponse(ResponseCode.SUCCESS);
    }

EXCEL:导出---无模板导出下载 实体类必须有注解

public void export(HttpServletResponse response,HttpServletRequest request,@RequestParam(value = "orderBy",defaultValue = "id desc")String orderBy) throws Exception {
        //模拟数据库数据

        try {
            Map<String, Object> searchPara=getQueryCondition(request,orderBy);

            List<BusReimbursementVo> beans = busReimbursementService.list(searchPara);
            //导出
            FileExcelUtil.exportExcel(beans,BusReimbursementVo.class,"日常报销单.xlsx",response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



//实体类片段
    /**
     * 报销人员姓名
     */
    @Size(max=50,message = "报销人员姓名超过最大长度50")
    @Excel(name = "报销人员",width = 25)//,orderNum = "0"
    private String leaveUserName;
    /**
     * 填报时间
     */
    @cn.afterturn.easypoi.excel.annotation.Excel(name = "填报时间",width = 20,exportFormat = "yyyy-MM-dd",orderNum = "0")
    private Timestamp fillingTime;
    /**
     * 报销金额
     */
    @NotNull(message = "报销金额不能为空!!")
    @Excel(name = "报销金额",width = 25,orderNum = "1")
    private BigDecimal reimbursementAmount;

EXCEL:导出---根据模板导出下载 实体类不需要注解

public void exportTemplateExcel(HttpServletResponse response,HttpServletRequest request,@RequestParam(value = "orderBy",defaultValue = "id desc")String orderBy) throws Exception {
        //模拟数据库数据

        try {
            //获取模板
            TemplateExportParams params = new TemplateExportParams("template/日常报销单模板.xlsx");
            //整合数据
            Map<String, Object> searchPara=getQueryCondition(request,orderBy);
            //查询数据
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("title", "7月报销");

            List<BusReimbursementVo> beans = busReimbursementService.list(searchPara);
            map.put("maplist", beans);
            //生成工作簿
            Workbook workbook = ExcelExportUtil.exportExcel(params, map);

            //输出流下载
            OutputStream outputStream=null;
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode("日常报销单.xlsx", "UTF-8"));
            outputStream = response.getOutputStream();

            workbook.write(outputStream);

              } catch (Exception e) {
            e.printStackTrace();
        }
    }

模板的样式:

导出下载word:

pom添加以下引入:

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.0.3</version>
</dependency>
<!--注意:word中要使用循环等标签必须单独导入以下依赖-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.1</version>
</dependency>

! word导出拼凑map数据时 需要对数据校验 不可以有null或""或" " 出现为空最好赋值为无或其他表示说明 否则模板对应字段占位不会取消代替

public void exportTemplateWord(HttpServletResponse response,HttpServletRequest request,@RequestParam(value = "orderBy",defaultValue = "id desc")String orderBy) throws Exception {

        String imagePath="D:\\testImage\\1595989432.jpg";
        /*String imagePath="http://pic1.win4000.com/pic/5/bf/6de447e563.jpg";
        outImage(imagePath,"D:\\testImage","meinv");*/
        WordImageEntity entity=new WordImageEntity();
        entity.setHeight(300);
        entity.setWidth(560);
        entity.setUrl(imagePath);
//        entity.setUrl("D:\\testImage\\meinv.jpg");
        try {
            //获取数据
            BusReimbursementVo reimbursementVo = busReimbursementService.findById("484096486594842624");

            Map<String,Object> map=new HashMap<>();
            map.put("leaveUserName",reimbursementVo.getLeaveUserName());
            map.put("reimbursementDate",reimbursementVo.getReimbursementDate());
            if (reimbursementVo.getProjectName()!=null) {
                map.put("projectName",reimbursementVo.getProjectName());
            }else {
                map.put("projectName","无");
            }
            if (reimbursementVo.getReimbursementAmount()!=null) {
                map.put("reimbursementAmount",reimbursementVo.getReimbursementAmount()+"元/人民币");
            }else {
                map.put("reimbursementAmount","无");
            }
            if (reimbursementVo.getReimbursementInstructions()!=null) {
                map.put("reimbursementInstructions",reimbursementVo.getReimbursementInstructions());
            }else {
                map.put("reimbursementInstructions","无");
            }
            if (reimbursementVo.getReviewerOneName()!=null) {
                map.put("reviewerOneName",reimbursementVo.getReviewerOneName());
            }else {
                map.put("reviewerOneName","无");
            }
            if (reimbursementVo.getAuditInstructionsOne()!=null) {
                map.put("auditInstructionsOne",reimbursementVo.getAuditInstructionsOne());
            }else {
                map.put("auditInstructionsOne","无");
            }
            if (reimbursementVo.getReviewerTwoName()!=null) {
                map.put("reviewerTwoName",reimbursementVo.getReviewerTwoName());
            }else {
                map.put("reviewerTwoName","无");
            }
            if (reimbursementVo.getAuditInstructionsTwo()!=null) {
                map.put("auditInstructionsTwo",reimbursementVo.getAuditInstructionsTwo());
            }else {
                map.put("auditInstructionsTwo","无");
            }
            map.put("title","公司业务报销单");
            map.put("image",entity);

            XWPFDocument doc= WordExportUtil.exportWord07("template/报销单.docx",map);
            //输出流下载
            OutputStream outputStream=null;
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode("日常报销单.docx", "UTF-8"));
            outputStream = response.getOutputStream();

            doc.write(outputStream);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 基于模板导出word  表格遍历数据
     * @param response
     * @param request
     * @param orderBy
     * @throws Exception
     */
    @RequestMapping("exportTemplateWordList")
    @ResponseBody
    public void exportTemplateWordList(HttpServletResponse response,HttpServletRequest request,@RequestParam(value = "orderBy",defaultValue = "id desc")String orderBy) throws Exception {


        try {
            //获取数据
            Map<String,Object> par=new HashMap<>();
            List<BusReimbursementVo> reimbursementVos = busReimbursementService.list(par);
            List<Map<String,Object>> tlist=new ArrayList<>();

            Map<String,Object> map=new HashMap<>();

            if (reimbursementVos!=null && reimbursementVos.size()>0) {
                for (BusReimbursementVo reimbursementVo : reimbursementVos) {
                    Map<String,Object> mm=new HashMap<>();
                    mm.put("leaveUserName",reimbursementVo.getLeaveUserName());
                    mm.put("reimbursementDate",reimbursementVo.getReimbursementDate());
                    mm.put("reimbursementAmount",reimbursementVo.getReimbursementAmount());
                    mm.put("fillingTime",reimbursementVo.getFillingTime());
                    tlist.add(mm);
                }
            }
            map.put("title","公司业务报销单");
            map.put("list",tlist);


            XWPFDocument doc= WordExportUtil.exportWord07("template/报销单列表.docx",map);
            //输出流下载
            OutputStream outputStream=null;
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode("日常报销单.docx", "UTF-8"));
            outputStream = response.getOutputStream();

            doc.write(outputStream);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

word模板:

本文集网络多家帖子整合,亲测可用.如有问题,评论区多多指导