SSM:通过接口导出数据库内容到Excel表格-顺毛师之家

经常遇到一些网站，尤其是管理员后台，有一个导出数据的功能。

在做暑期作业的时候，正需要这个功能，想起了之前用过的POI，查了一些资料发现POI吃内存比较严重，正好国内阿里改进了POI，形成了EasyExcel，并且已经开源。

官方说明文档
https://www.yuque.com/easyexcel/doc/easyexcel

功能预览

前端通过按钮，向后端接口发送post请求，实现文件下载。

前端

接口实现

导入Maven依赖

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.2.6</version>
    </dependency>
    <dependency>
      <groupId>org.projectlombok
      </groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.12</version>
    </dependency>

Mapper.xml

<select id="selectAll"  resultMap="BaseResultMap">
   select  *
  from uusers
</select>

Mapper接口声明

public interface UusersMapper {
    List<Uusers> selectAll();
}

Uusers表实体类

继承自com.alibaba.excel.metadata.BaseRowModel类，每个属性前添加@ExcelProperty注解，声明导出Excel的表头。

public class Uusers extends BaseRowModel {

    @ExcelProperty(value = "id",index = 0 )
    private Integer id;

    @ExcelProperty(value = "用户名",index = 1 )
    private String username;

    @ExcelProperty(value = "密码",index = 2 )
    private String password;

    @ExcelProperty(value = "身份",index = 3 )
    private String identity;

    @ExcelProperty(value = "手机号",index = 4 )
    private String phone;

    @ExcelProperty(value = "性别",index = 5 )
    private String gender;

    @ExcelProperty(value = "姓名",index = 6 )
    private String name;

    @ExcelProperty(value = "年龄",index = 7 )
    private Integer age;

    @ExcelProperty(value = "邮箱",index = 8 )
    private String email;
}

Service层

    @Autowired
    private UusersMapper uusersMapper;
    @Override
    public List<Uusers> getAll() {
        return uusersMapper.selectAll();
    }

Controller层

@RequestMapping("/downloadUsers")
@ResponseBody
public void downloadUsers(HttpServletResponse response) throws Exception {
        List<Uusers> list = uusersService.getAll();
        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
        String fileName = "用户信息表";
        Sheet sheet = new Sheet(1, 0,Uusers.class);
        //设置自适应宽度
        sheet.setAutoWidth(Boolean.TRUE);
        // 第一个 sheet 名称
        sheet.setSheetName("用户信息");
        writer.write(list, sheet);
        //通知浏览器以附件的形式下载处理，设置返回头要注意文件名有中文
        response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xlsx");
        writer.finish();
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        out.flush();
        out.close();
}

结语

以上基本就能简单实现数据导出功能，至于跨域请求等另行解决吧！

EasyExcel SSM 数据库