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