我们在做项目的时候,经常用到导出功能,比如根据条件检索出有用的信息展示到页面,但是光展示还不行,还必须要以excel文件形式导出来,今天我们来用SpringBoot来实现这个功能,我们今天就以人员信息为列,导出所有检索到的人员信息。
环境搭起来:
先创建人员信息表:
CREATE TABLE `user` (
`id` varchar(225) NOT NULL,
`name` varchar(225) DEFAULT NULL,
`age` int(22) DEFAULT NULL,
`dept` varchar(225) DEFAULT NULL,
`education` varchar(225) DEFAULT NULL,
`competent` varchar(223) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入一些人员信息
然后上代码:框架搭起来:
/**
* zigao
*/
public class ResponseMessage implements Serializable {
int status;
Object message;
String errmsg;
public int getStatus() {
return this.status;
}
public void setStatus(int status) {
this.status = status;
}
public Object getMessage() {
return this.message;
}
public void setMessage(Object message) {
this.message = message;
}
public String getErrmsg() {
return this.errmsg;
}
public void setErrmsg(String errmsg) {
this.errmsg = errmsg;
}
public ResponseMessage() {
}
public ResponseMessage(int status) {
this.status = 0;
}
public ResponseMessage(Object data) {
this.status = 0;
this.message = data;
}
public ResponseMessage(int status, Object data) {
this.status = status;
this.message = data;
}
public ResponseMessage(int status, String err) {
this.status = status;
this.errmsg = err;
}
}
/**
*
*
* @author zigao
* @date 2020年08月01日 11:58
*/
@RequestMapping("/export")
@RestController
public class UserController {
//打印日志
public Logger logger = LoggerFactory.getLogger(this.getClass());
@Autowired
private UserService userService;
@GetMapping("/exportUser")
public void listUser(HttpServletRequest request,HttpServletResponse response)throws IOException {
ResponseMessage responseMessage = new ResponseMessage(0);
try{
String agent = request.getHeader("USER-AGENT").toLowerCase();
System.out.println(agent);
List<User> listUser = userService.getUser();
userService.exportUserData(listUser,response);
responseMessage.setMessage(listUser);
}catch (Exception ex){
logger.error("导出信息异常", ex);
throw new RuntimeException(ex.getMessage());
}
}
}
@Data
public class User {
/**
* 用户编码
*/
@ExcelProperty("用户编码")
private String id;
/**
* 名字
*/
@ExcelProperty("名字")
private String name;
/**
* 年龄
*/
@ExcelProperty("年龄")
private Integer age;
/**
*部门
*/
@ExcelProperty("部门")
private String dept;
/**
* 学历
*/
@ExcelProperty("学历")
private String education;
/**
* 职称
*/
@ExcelProperty("职称")
private String competent;
}
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
/**
* 查询用户信息
* @return
*/
public List<User> getUser(){
return userMapper.getUser();
}
/**
* 文件导出实现类
* @param listUser
* @param response
* @throws IOException
*/
public void exportUserData(List<User> listUser, HttpServletResponse response)throws IOException{
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = "统计用户信息" + System.currentTimeMillis() + ".xlsx";
String file = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + file);
EasyExcel.write(response.getOutputStream(), User.class).sheet("sheet1").doWrite(listUser);
}
}
@Repository
@Mapper
public interface UserMapper {
/**
* 获取用户信息
* @param
* @return
*/
@Select("select * from user")
List<User> getUser();
}
@SpringBootApplication
@MapperScan("com.zigao.com")
public class zigaoApplication {
public static void main(String[] args) {
SpringApplication.run(zigaoApplication.class, args);
System.out.println("===============================已启动================================");
}
}
server:
port: 8888
servlet:
# 上下文路径
context-path: /api
tomcat:
uri-encoding: UTF-8
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
pageSizeZero: false #pageSize=0
spring:
application:
# 项目名称
name: zigao
profiles:
# 环境
active: dev
jackson:
# json 序列化配置
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT
http:
# 编码
encoding:
charset: UTF-8
enabled: true
force: true
spring:
datasource:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test3?useUnicode=true&characterEncoding=utf-8
username: root
password: 1qaz2WSX
#设置文件大小
servlet:
multipart:
max-file-size: 50MB
max-request-size: 50MB
mybatis:
mapper-locations: classpath:mapper/*Mapper.xml
type-aliases-package: com.zigao
configuration:
call-setters-on-nulls: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
<groupId>zigao</groupId>
<artifactId>zigao.cnn</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.7.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<name>zigao</name>
<description>zigao.cnn</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.13 </version>
</dependency>
<!--分页包-->
<!--阿里json包-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.41</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<!--web起步依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--alibaba easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.0.5</version>
</dependency>
<!--mybatis起步依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
<!--springboot起步依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--mysql连接依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>5.1.46</version>
</dependency>
<!--spring-boot测试工具起步依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.assertj</groupId>
<artifactId>assertj-core</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
代码完事了,我们来测试下: