SSM结合easyexcel数据导出 SSM结合easyexcel数据导出

1、项目结构
文章图片

2、添加依赖

org.springframework.boot spring-boot-starter-data-jdbc org.springframework.boot spring-boot-starter-web org.mybatis.spring.boot mybatis-spring-boot-starter 2.2.0 mysql mysql-connector-java runtime org.projectlombok lombok true com.alibaba easyexcel 2.1.1

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 = "https://www.it610.com/article/学号",index = 0) private Integer id; @ExcelProperty(value = "https://www.it610.com/article/姓名",index = 1) private String name; }

5、编写mapper层

@Mapper public interface UserMapper {/** *查询数据 * @return */ List dowloadExcel(); }

6、编写UserMapper.xml

select * from user01;

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 data() { List list = userMapper.dowloadExcel(); return list; } }

9、controller层编写

@RestController public class UserController { @Autowired private UserService userService; @GetMapping(value = "https://www.it610.com/downloadExcel") public void downloadExcel(HttpServletResponse response) throws Exception{ userService.downloadExcel(response); }}

10、测试
【SSM结合easyexcel数据导出】localhost:1313/downloadExcel