基于阿里的EsayExcel文件导出

基于阿里的EsayExcel文件导出

用esayExcel从mysql数据库导出文件

首先是依赖

 <!--easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

我这边的需求是这样的,如果是没有条件导出,那么就默认导出全部数据,如果是有条件的,那么就按照条件进行文件导出,我这边的条件是按照指定时间进行导出文件,所以我对起始时间和结束时间封装到了map集合中
按照时间进行查询数据库,返回值为list集合

<!--根据日期查询-->
    <select id="findByDate" resultType="com.element.admin.entity.Member">
        select * from tb_member where apply between #{dateStar} and #{dateEnd}
    </select>

Service层

    /**
     * 导出excel
     * @param response
     * @param map map封装了起始时间和结束时间
     */
    public void excelExport(HttpServletResponse response, Map map) throws IOException;

ServiceImpl 实现类

/**
     * 文件下载
     *
     * @param response
     * @param map 
     * @return
     * @throws IOException
     */
    @Override
    public void excelExport(HttpServletResponse response, Map map) throws IOException {
        
        try {
            //定义一个list集合
            List<Member> members = new ArrayList<>();
            //判断map中是否有数据
            if (map == null) {
                //没有数据,那么就查询所有数据
                members = memberMapper.selectAll();
            }
       
            else if (map.get("dateStar").equals("") && map.get("dateEnd").equals("")) {
                members = memberMapper.selectAll();

            } else {
                //如果map中有数据,那么根据条件进行查询
                String dateStar = (String) map.get("dateStar");
                String dateEnd = (String) map.get("dateEnd");
                members = memberMapper.findByDate(dateStar, dateEnd);
            }
            List<MemberVo> voList = new ArrayList<>();
            for (Member member : members) {
                MemberVo vo = new MemberVo();
                BeanUtils.copyProperties(member, vo);
                voList.add(vo);
            }
            //文件名称
            String fileName = "XXXXXX";
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" +
                    new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xlsx");
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
            Sheet sheet = new Sheet(1, 0, Member.class);
            //设置自适应宽度
            sheet.setAutoWidth(Boolean.TRUE);
            //sheet的名称
            sheet.setSheetName("XXXX");
            writer.write(members, sheet);
            writer.finish();
            out.flush();
            response.getOutputStream().close();
            out.close();
           // return new Result(true, StatusCode.OK, "文件下载成功");
        } catch (Exception e) {
            e.printStackTrace();
          //  return new Result(false, StatusCode.ERROR, "文件下载失败");
        }

    }

Controller层

    /**
     *
     * @param response
     * @param map
     * @throws Exception
     */
    @ApiOperation(value = "文件导出", tags = {"ExcelController"})
    @PostMapping("/getExcel")
    public void excelExport(HttpServletResponse response,
                              @ApiParam(name = "map对象",value = "传入日期数据",required = false) @RequestBody(required = false) Map map) throws Exception {

       adminService.excelExport(response, map);
       //return new Result(true, StatusCode.OK, "成功", null);
    }

到此结束,用postman测试,完成没问题