springboot实现导出数据库数据
先看数据库数据(主键自增,时间自动填充当前时间)
一、导入依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<--导入hutool-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.18</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
二、实体类
import java.util.Date;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
private Integer id;
private String username;
private String password;
private String nickname;
private String email;
private String phone;
private String address;
private Date createTime;
private String avatarUrl;
}
三、UserMapper 和UserMapper.xml
import java.util.List;
@Mapper
@Repository
public interface UserMapper {
List<User> list();
}
<?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.example.filedemo.mapper.UserMapper">
<select id="list" resultType="user">
select * from sys_user
</select>
</mapper>
四、controller
@Autowired
private UserMapper userMapper;
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception{
List<User> list = userMapper.list();
ExcelWriter writer= ExcelUtil.getWriter(true);
writer.addHeaderAlias("username","用户名");
writer.addHeaderAlias("password","密码");
writer.addHeaderAlias("nickname","昵称");
writer.addHeaderAlias("email","邮箱");
writer.addHeaderAlias("phone","电话");
writer.addHeaderAlias("address","地址");
writer.addHeaderAlias("createTime","创建时间");
writer.addHeaderAlias("avatarUrl","头像");
writer.write(list,true);
response.setContentType("application/vnd.openxmlfromates-officedocument.spreadsheetml.sheet;charset=utf-8");
String fileName = URLEncoder.encode("用户信息", "UTF-8");
response.setHeader("Content-Disposition","attachment;filename="+fileName+ ".xlsx");
ServletOutputStream out = response.getOutputStream();
writer.flush(out,true);
out.close();
writer.close();
}