当前位置：首页 > 文章列表 > 数据库 > MySQL > SSM结合easyexcel数据导出

SSM结合easyexcel数据导出

来源：SegmentFault 2023-02-23 18:55:17 0浏览收藏

哈喽！今天心血来潮给大家带来了《SSM结合easyexcel数据导出》，想必大家应该对数据库都不陌生吧，那么阅读本文就都不会很困难，以下内容主要涉及到MySQL、Java、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();

}</user>

6、编写UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?><mapper namespace="com.zj.mapper.UserMapper">&lt;select id=&quot;dowloadExcel&quot; resulttype=&quot;com.zj.pojo.User&quot;&gt;
        select * from user01;
    &lt;/select&gt;</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;
    }
}</user></user>

9、controller层编写

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

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

}