使用VUE+SpringBoot+EasyExcel 整合导入导出数据
使用VUE+SpringBoot+EasyExcel 整合导入导出数据
创建一个普通的maven项目即可
项目目录结构
1 前端
存放在resources/static 下
index.html
Document
姓名 生日 薪资
暂无数据 共 1000 条- 1
- 2
- 3
- 4
- 5
- 6
- 200
前往页
2 数据库
CREATE TABLE `student` (
`name` varchar(255) DEFAULT NULL COMMENT '姓名',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`salary` decimal(10,4) DEFAULT NULL COMMENT '薪资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3 后端
3.1 contrller
StudentController
@Slf4j
@RestController
@CrossOrigin
@RequestMapping("/api/excel/")
public class StudentController {
@Resource
private StudentMapper studentMapper;
@GetMapping("list")
public HashMap list(@RequestParam int pageNum,@RequestParam int pageSize){
// 分页查询
Page page = new Page<>(pageNum, pageSize);
studentMapper.selectPage(page,null);
// 封装数据
HashMap map = new HashMap<>();
ArrayList excelDictDTOList = new ArrayList<>();
// 转换数据
page.getRecords().forEach(student -> {
ExcelStudentDTO studentDTO = new ExcelStudentDTO();
BeanUtils.copyProperties(student,studentDTO);
excelDictDTOList.add(studentDTO);
});
map.put("list",excelDictDTOList);
map.put("size",page.getTotal());
return map;
}
/**
* 导入
* @param file 文件对象
*/
@RequestMapping("import")
@Transactional(rollbackFor = {Exception.class})
public String importData( @RequestParam("file") MultipartFile file){
try {
// 读取文件流
EasyExcel.read
(file.getInputStream(),// 前端上传的文件
ExcelStudentDTO.class,// 跟excel对应的实体类
new ExcelDictDTOListener(studentMapper))// 监听器
.excelType(ExcelTypeEnum.XLSX)// excel的类型
.sheet("模板").doRead();
log.info("importData finished");
} catch (IOException e) {
log.info("失败");
e.printStackTrace();
}
return "上传成功";
}
/**
* 导入
*/
@GetMapping("export")
public String exportData(HttpServletResponse response){
try {
// 设置响应体内容
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("myStu", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream()
,ExcelStudentDTO.class).sheet().doWrite(studentMapper.selectList(null));
} catch (Exception e) {
e.printStackTrace();
}
return "上传成功";
}
}
3.2 mapper
StudentMapper
@Mapper
public interface StudentMapper extends BaseMapper {
void insertBatch(List list);
}
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
insert into student(name, birthday, salary)
values
(
#{item.name} ,
#{item.birthday} ,
#{item.salary}
)
3.3 bean
ExcelStudentDTO
导入数据时 要保证excel中列名和ExcelStudentDTO一致奥
/**
* excel对应的实体类
* @author jiejie
*/
@Data
public class ExcelStudentDTO {
// excel中的列名
@ExcelProperty("姓名")
private String name;
@ExcelProperty("生日")
private Date birthday;
@ExcelProperty("薪资")
private BigDecimal salary;
}
Student
/**
* 数据库对应的实体类
* @author jiejie
*/
@Data
@TableName(value = "student")
public class Student {
/**
* 姓名
*/
@TableField(value = "name")
private String name;
/**
* 生日
*/
@TableField(value = "birthday")
private Date birthday;
/**
* 薪资
*/
@TableField(value = "salary")
private BigDecimal salary;
public static final String COL_NAME = "name";
public static final String COL_BIRTHDAY = "birthday";
public static final String COL_SALARY = "salary";
}
3.3 listener
官方文档
EasyExcel读取文件需要用到
ExcelDictDTOListener
/**
* 监听
* 再读取数据的同时 对数据进行插入操作
* @author : look-word
* @date : 2022-05-10 21:35
**/
@Slf4j
//@AllArgsConstructor //全参
@NoArgsConstructor //无参
public class ExcelDictDTOListener extends AnalysisEventListener {
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List list = new ArrayList();
private StudentMapper studentMapper;
//传入mapper对象
public ExcelDictDTOListener(StudentMapper studentMapper) {
this.studentMapper = studentMapper;
}
/**
*遍历每一行的记录
* @param data
* @param context
*/
@Override
public void invoke(ExcelStudentDTO data, AnalysisContext context) {
log.info("解析到一条记录: {}", data);
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", list.size());
studentMapper.insertBatch(list); //批量插入
log.info("存储数据库成功!");
}
}
3.5 config
mybatisPlus分页插件
MybatisPlusConfig
@Configuration
public class MybatisPlusConfig {
/**
* 新的分页插件,一缓和二缓遵循mybatis的规则,
* 需要设置 MybatisConfiguration#useDeprecatedExecutor = false
* 避免缓存出现问题(该属性会在旧插件移除后一同移除)
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
paginationInnerInterceptor.setDbType(DbType.MYSQL);
paginationInnerInterceptor.setOverflow(true);
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
@Bean
public ConfigurationCustomizer configurationCustomizer() {
return configuration -> configuration.setUseDeprecatedExecutor(false);
}
}
3.6 配置文件
application.yaml
server:
port: 8811
spring:
datasource: # mysql数据库连接
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/2022_source_springboot?serverTimezone=GMT%2B8&characterEncoding=utf-8
username: root
password: 317311
mybatis-plus:
configuration:# sql日志
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations:
- classpath:mapper/*.xml
4 启动测试
启动springboot哦
页面效果图
导出效果
注意
导入数据时 要保证excel中列名和ExcelStudentDTO一致奥