码农code之路使用VUE+SpringBoot+EasyExcel 整合导入导出数据
创建一个普通的maven项目即可
项目目录结构
1 前端
存放在resources/static 下
index.html
html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Document</title>
<script src="https://cdn.jsdelivr.net/npm/vue@2/dist/vue.js"></script>
<link
rel="stylesheet"
href="https://unpkg.com/element-ui/lib/theme-chalk/index.css"
/>
<script src="https://unpkg.com/element-ui/lib/index.js"></script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
</head>
<body>
<div id="app">
<div class="app-container">
<div style="margin-bottom: 10px">
<el-button
@click="dialogVisible = true"
type="primary"
size="mini"
icon="el-icon-download"
>
导入Excel
</el-button>
<el-dialog
title="数据字典导入"
:visible.sync="dialogVisible"
width="30%"
>
<el-form>
<el-form-item label="请选择Excel文件">
<el-upload
:auto-upload="true"
:multiple="false"
:limit="1"
:on-exceed="fileUploadExceed"
:on-success="fileUploadSuccess"
:on-error="fileUploadError"
:action="importUrl"
name="file"
accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
>
<el-button size="small" type="primary">点击上传</el-button>
</el-upload>
</el-form-item>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button @click="dialogVisible = false">取消</el-button>
</div>
</el-dialog>
<el-button
@click="exportData"
type="primary"
size="mini"
icon="el-icon-upload2"
>
导出Excel
</el-button>
<el-table :data="list" stripe style="width: 100%">
<el-table-column prop="name" label="姓名" width="180">
</el-table-column>
<el-table-column prop="birthday" label="生日" width="180">
</el-table-column>
<el-table-column prop="salary" label="薪资"> </el-table-column>
</el-table>
<div>
<el-pagination
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="pageNum"
:page-sizes="[2, 5, 10, 20]"
:page-size="pageSize"
background
layout="total, sizes, prev, pager, next, jumper"
:total="total"
>
</el-pagination>
</div>
</div>
</div>
</div>
</body>
<script>
new Vue({
el: '#app',
data() {
return {
dialogVisible: false,
list: [],
importUrl: 'http://localhost:8811/api/excel/import',
pageNum: 1,
pageSize: 5,
total: 1000,
}
},
created() {
this.showList()
},
methods: {
showList() {
const request = axios.create({
baseURL: 'http://localhost:8811',
timeout: 1000,
})
request
.get('/api/excel/list', {
params: {
pageNum: this.pageNum,
pageSize: this.pageSize,
},
})
.then((res) => {
this.total = res.data.size
this.list = res.data.list
console.log(res)
})
},
fileUploadExceed() {
this.$message.warning('只能选取一个文件')
},
exportData() {
window.location.href = 'http://localhost:8811/api/excel/export'
},
fileUploadSuccess(response) {
if (response.code === 0) {
this.$message.success('数据导入成功')
this.dialogVisible = false
} else {
this.$message.error(response.message)
}
},
fileUploadError(error) {
this.$message.error('数据导入失败')
},
handleSizeChange(val) {
console.log(`每页 ${val} 条`)
this.pageSize = val
this.showList()
},
handleCurrentChange(val) {
console.log(`当前页: ${val}`)
this.pageNum = val
this.showList()
},
},
})
</script>
</html>
2 数据库
sql
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
java
@Slf4j
@RestController
@CrossOrigin
@RequestMapping("/api/excel/")
public class StudentController {
@Resource
private StudentMapper studentMapper;
@GetMapping("list")
public HashMap<String, Object> list(@RequestParam int pageNum,@RequestParam int pageSize){
Page<Student> page = new Page<>(pageNum, pageSize);
studentMapper.selectPage(page,null);
HashMap<String, Object> map = new HashMap<>();
ArrayList<ExcelStudentDTO> 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;
}
@RequestMapping("import")
@Transactional(rollbackFor = {Exception.class})
public String importData( @RequestParam("file") MultipartFile file){
try {
EasyExcel.read
(file.getInputStream(),
ExcelStudentDTO.class,
new ExcelDictDTOListener(studentMapper))
.excelType(ExcelTypeEnum.XLSX)
.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");
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
java
@Mapper
public interface StudentMapper extends BaseMapper<Student> {
void insertBatch(List<ExcelStudentDTO> list);
}
StudentMapper.xml
xml
<?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="look.word.mapper.StudentMapper">
<insert id="insertBatch" >
insert into student(name, birthday, salary)
values
<foreach collection="list" item="item" separator=",">
(
#{item.name} ,
#{item.birthday} ,
#{item.salary}
)
</foreach>
</insert>
</mapper>
3.3 bean
ExcelStudentDTO
导入数据时 要保证excel中列名和ExcelStudentDTO一致奥
Java
@Data
public class ExcelStudentDTO {
@ExcelProperty("姓名")
private String name;
@ExcelProperty("生日")
private Date birthday;
@ExcelProperty("薪资")
private BigDecimal salary;
}
Student
java
@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
java
@Slf4j
@NoArgsConstructor
public class ExcelDictDTOListener extends AnalysisEventListener<ExcelStudentDTO> {
private static final int BATCH_COUNT = 5;
List<ExcelStudentDTO> list = new ArrayList<ExcelStudentDTO>();
private StudentMapper studentMapper;
public ExcelDictDTOListener(StudentMapper studentMapper) {
this.studentMapper = studentMapper;
}
@Override
public void invoke(ExcelStudentDTO data, AnalysisContext context) {
log.info("解析到一条记录: {}", data);
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
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
java
@Configuration
public class MybatisPlusConfig {
@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
yaml
server:
port: 8811
spring:
datasource:
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一致奥
__EOF__
