使用EasyExcel上传下载excel
需求分析:
使用EasyExcel上传下载excel
excel上传:实际为读操作,从文件的输入流读取数据,然后将数据保存到数据库中
excel下载: 实际为写操作,将数据库的excel数据写入输出流,保存到excel文件
官方资料:
web上传、下载
DEMO代码地址:官方Demo代码
环境:
IDEA2018.3、jdk1.8、maven3.3.9、Springboot2.2.2、Mybatis-plus3.1.2、mysql5.7
编码:
可根据EasyExcel 官方Demo代码 ,修改得到如下Springboot+Mybtis-plus的实现excel上传下载demo项目。
1.数据库准备:
数据库
CREATE DATABASE IF NOT EXISTS stusys DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
数据表
CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`sex` int(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`classid` varchar(20) DEFAULT NULL,
`score` float(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4;
INSERT INTO `stu` VALUES (1, '张三', 0, 18, '3班', 72.23);
INSERT INTO `stu` VALUES (2, '李四', 1, 19, '1班', 85.50);
INSERT INTO `stu` VALUES (3, '王五', 0, 20, '2班', 86.36);
2.新建Springboot项目及编码:
pom.xml
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot</artifactId>
<version>2.2.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.2.2.RELEASE</version>
</dependency>
<!-- mybaits-plus依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
application.properties
spring.application.name=springbootmybatisplus
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/stusys?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=123
#打印sql语句
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
# 当baseMapper的方法不能满足需求,需要自定义方法时,需要自己写xml来配置sql语句,即指定Mapper.xml的路径
# classpath*: 开头 (即加载多个 jar 包下的 XML 文件)
mybatis-plus.mapper-locations=classpath*:mybatis/*.xml
目录结构:
StuEntity.java
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName("stu")
public class StuEntity {
@TableId(type = IdType.AUTO)
private Long id;
private String name;
private Integer sex;
private Integer age;
private String classid;
private Double score;
}
StuDao.java
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import top.jipson.easyexcel.web.entity.StuEntity;
@Mapper
public interface StuDao extends BaseMapper<StuEntity> {
}
StuListener.java
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import top.jipson.easyexcel.web.entity.StuEntity;
import top.jipson.easyexcel.web.service.StuService;
import java.util.ArrayList;
import java.util.List;
/**
* 模板的读取类
*/
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class StuListener extends AnalysisEventListener<StuEntity> {
private StuService stuService;//不能被spring管理,所以无法自动注入
private static final Logger LOGGER =
LoggerFactory.getLogger(StuListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<StuEntity> list = new ArrayList<StuEntity>();
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
* 多例,每次都新建一个实例
*
* @param stuService
*/
public StuListener(StuService stuService) {
this.stuService = stuService;
}
/**
* 这个每一条数据解析都会来调用
*
*/
@Override
public void invoke(StuEntity stuEntity, AnalysisContext analysisContext) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(stuEntity));
list.add(stuEntity);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
stuService.saveBatch(list);//自动去除了自动增长的id,SQL语句 :INSERT INTO stu ( name, sex, age, classid, score ) VALUES ( ?, ?, ?, ?, ? )
LOGGER.info("存储数据库成功!");
}
}
StuService.java
import com.baomidou.mybatisplus.extension.service.IService;
import top.jipson.easyexcel.web.entity.StuEntity;
public interface StuService extends IService<StuEntity> {
}
StuServiceImpl.java
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import top.jipson.easyexcel.web.dao.StuDao;
import top.jipson.easyexcel.web.entity.StuEntity;
import top.jipson.easyexcel.web.service.StuService;
@Service
public class StuServiceImpl extends ServiceImpl<StuDao, StuEntity> implements StuService {
}
StuController.java
import com.alibaba.excel.EasyExcel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import top.jipson.easyexcel.web.entity.StuEntity;
import top.jipson.easyexcel.web.listener.StuListener;
import top.jipson.easyexcel.web.service.StuService;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
@RestController
@RequestMapping("/stu")
public class StuController {
@Autowired
private StuService stuService;
@GetMapping("list")
public List<StuEntity> list(){
List<StuEntity> list = stuService.list();
return list;
}
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试下载", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), StuEntity.class).sheet("模板").doWrite(list());
}
@PostMapping("upload")
public String upload(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), StuEntity.class, new StuListener(stuService)).sheet().doRead();
return "upload success";
}
}
3.测试:
测试下载:用浏览器输入以下url
http://localhost:8080/stu/download
验证: 下载下来的excel数据与数据库中stu表的数据一致。
测试上传:
这里没有写前端页面,所以用postman测试
输入http://localhost:8080/stu/upload,选择POST请求,点击Headers,
KEY输入Content-Type
VALUE输入multipart/form-data
点击Body,选中form-data,
KEY输入file, KEY右侧选为File(默认为Text)
VALUE选择要上传的excel文件(确保字段名和数据库一致)
上传文件示例:
测试上传.xlsx
点击Send,看到返回为"upload success"
验证:检查数据库已插入了上传excel的数据。
如需要下载代码,点击右侧--> 项目完整代码
完成!enjoy it!