SSM结合easyexcel数据导出

1、项目结构

2、添加依赖

 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>

3、application.properties配置

# 端口号
server.port=1313
# 数据库配置
spring.datasource.url=jdbc:mysql:///数据库名?serverTimezone=GMT%2B8&characterEncoding=utf8
# 数据库账号密码
spring.datasource.username=账号
spring.datasource.password=密码
# mybatis配置
mybatis.mapper-locations=classpath:/mapper/*.xml
logging.level.com.zj=debug

4、编写pojo层

@Data
public class User implements Serializable {

    private static final long serialVersionUID = 7957422435616014154L;

    /**
     *  @ExcelProperty(value="",index="",format="yyyy-MM-dd")
     *  value   表头名称
     *  index   输出的顺序
     *  format  时间样式
     *  @ColumnWidth为Excel的宽度
     *  @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")这里用string 去接日期才能格式化。我想接收年月日格式
     */

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

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

}

5、编写mapper层

@Mapper
public interface UserMapper {

    /**
     *  查询数据
     * @return
     */
    List<User> dowloadExcel();

}

6、编写UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.zj.mapper.UserMapper">
    <select id="dowloadExcel" resultType="com.zj.pojo.User">
        select * from user01;
    </select>
</mapper>

7、编写UserService

public interface UserService {

    /**
     * 这里使用pom.xml的servlet-api依赖，负责像客户端（浏览器）发送响应
     */
    void downloadExcel(HttpServletResponse response) throws Exception;

}

8、编写实现类

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public void downloadExcel(HttpServletResponse response) throws Exception {
//      设置样式
        String filename = URLEncoder.encode("myExcel", "utf-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition","attachment;filename=" + filename + ".xlsx");
        /**
         * EasyExcel的写文件操作,data()方法用来查询数据库数据返回list
         * sheet为excel的工作表表名
         */
        EasyExcel.write(response.getOutputStream(), User.class).sheet().doWrite(data());
    }
//  调用所查询的数据写入
    private List<User> data() {
        List<User> list = userMapper.dowloadExcel();
        return list;
    }
}

9、controller层编写

@RestController
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping(value = "/downloadExcel")
    public void downloadExcel(HttpServletResponse response) throws Exception{
        userService.downloadExcel(response);
    }

}

10、测试

localhost:1313/downloadExcel