Excel导入导出的应用场景
1、数据导入:减轻录入工作量
2、数据导出:统计信息归档
3、数据传输:异构系统之间数据传输
一、EasyExcel简介
1、EasyExcel特点
Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行,但是一旦并发上来后一定会OOM或者JVM频繁的full gc。
EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。
EasyExcel采用一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)。
2、创建项目,实现EasyExcel对Excel写操作
1、pom中引入xml相关依赖
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
</dependencies>
还需要 poi
依赖
2、创建实体类
@Data
public class DemoData {
@ExcelProperty("学生编号")
private Integer sno;
@ExcelProperty("学生姓名")
private String sname;
}
3、实现写操作
public class TestEasyExcel {
public static void main(String[] args) {
String filename = "E:\\write.xlsx";
EasyExcel.write(filename,DemoData.class).sheet("学生列表").doWrite(getData());
}
private static List<DemoData> getData(){
List<DemoData> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setSno(i);
data.setSname("lucy"+i);
list.add(data);
}
return list;
}
}
3、创建项目,实现EasyExcel对Excel读操作
1、创建和excel对应实体类,标记对应列关系
@Data
public class DemoData {
@ExcelProperty(value = "学生编号",index = 0)
private Integer sno;
@ExcelProperty(value = "学生姓名",index = 1)
private String sname;
}
2、创建监听进行excel文件读取
public class ExcelListener extends AnalysisEventListener<DemoData> {
@Override
public void invoke(DemoData data, AnalysisContext analysisContext) {
System.out.println("***"+data);
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头"+headMap);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) { }
}
3、最终测试
public static void main(String[] args) {
String filename = "E:\\write.xlsx";
EasyExcel.read(filename,DemoData.class,new ExcelListener()).sheet().doRead();
}
二、课程分类后端添加功能
EasyExcel读取excel内容实现
1、引入easyexcel依赖
2、使用代码生成器把课程分类代码生成
3、创建实体类和excel对应关系
@Data
public class SubjectData {
@ExcelProperty(index = 0)
private String oneSujectName;
@ExcelProperty(index = 1)
private String twoSujectName;
}
4、编写 EduSubjectController 类
@Api(description = "课程分类")
@RestController
@RequestMapping("/eduservice/subject")
@CrossOrigin
public class EduSubjectController {
@Autowired
private EduSubjectService subjectService;
@ApiOperation(value = "添加课程分类")
@PostMapping("addSubject")
public R addSubject(MultipartFile file) {
subjectService.saveSubject(file,subjectService);
return R.ok();
}
}
5、编写 EduSubjectServiceImpl 类
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
@Override
public void saveSubject(MultipartFile file,EduSubjectService subjectService) {
try {
InputStream in = file.getInputStream();
EasyExcel.read(in,SubjectData.class,new SubjectExcelListener(subjectService)).sheet().doRead();
}catch (Exception e){
e.printStackTrace();
}
}
}
6、创建读取Excel监听器
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
private EduSubjectService subjectService;
public SubjectExcelListener() {}
public SubjectExcelListener(EduSubjectService subjectService) {
this.subjectService = subjectService;
}
@Override
public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
if(subjectData == null) {
throw new GuliException(20001,"文件数据为空");
}
EduSubject existOneSubject = this.existOneSubject(subjectService,subjectData.getOneSujectName());
if(existOneSubject == null) {
existOneSubject = new EduSubject();
existOneSubject.setTitle(subjectData.getOneSujectName());
existOneSubject.setParentId("0");
subjectService.save(existOneSubject);
}
String pid = existOneSubject.getId();
EduSubject existTwoSubject = this.existTwoSubject(subjectService,subjectData.getTwoSujectName(), pid);
if(existTwoSubject == null) {
existTwoSubject = new EduSubject();
existTwoSubject.setTitle(subjectData.getTwoSujectName());
existTwoSubject.setParentId(pid);
subjectService.save(existTwoSubject);
}
}
private EduSubject existOneSubject(EduSubjectService subjectService,String name) {
QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
wrapper.eq("title",name);
wrapper.eq("parent_id","0");
EduSubject OneSubject = subjectService.getOne(wrapper);
return OneSubject;
}
private EduSubject existTwoSubject(EduSubjectService subjectService,String name,String pid) {
QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
wrapper.eq("title",name);
wrapper.eq("parent_id",pid);
EduSubject TwoSubject = subjectService.getOne(wrapper);
return TwoSubject;
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头信息:"+headMap);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {}
}
7、重启oss服务,Swagger中测试文件上传
http://localhost:8001/swagger-ui.html
三、课程分类前端添加功能
EasyExcel读取excel内容实现
1、添加课程分类路由
在/src/router/index.js中
{
path: '/subject',
component: Layout,
redirect: '/subject/list',
name: '课程分类管理',
meta: { title: '课程分类管理', icon: 'example' },
children: [
{
path: 'list',
name: '课程分类列表',
component: () => import('@/views/edu/subject/list'),
meta: { title: '课程分类列表', icon: 'table' }
},
{
path: 'save',
name: '添加课程分类',
component: () => import('@/views/edu/subject/save'),
meta: { title: '添加课程分类', icon: 'tree' }
}
]
},
2、创建课程分类页面,修改路由对应的页面路径
3、在添加课程分类页面 实现效果
添加上传组件实现
<template>
<div class="app-container">
<el-form label-width="120px">
<el-form-item label="信息描述">
<el-tag type="info">excel模版说明</el-tag>
<el-tag>
<i class="el-icon-download"/>
<a :href="OSS_PATH + '/excel/%E8%AF%BE%E7%A8%8B%E5%88%86%E7%B1%BB%E6%A8%A1%E6%9D%BF.xls'">点击下载模版</a>
</el-tag>
</el-form-item>
<el-form-item label="选择Excel">
<el-upload
ref="upload"
:auto-upload="false"
:on-success="fileUploadSuccess"
:on-error="fileUploadError"
:disabled="importBtnDisabled"
:limit="1"
:action="BASE_API+'/eduservice/subject/addSubject'"
name="file"
accept="application/vnd.ms-excel">
<el-button slot="trigger" size="small" type="primary">选取文件</el-button>
<el-button
:loading="loading"
style="margin-left: 10px;"
size="small"
type="success"
@click="submitUpload">{{ fileUploadBtnText }}</el-button>
</el-upload>
</el-form-item>
</el-form>
</div>
</template>
js上传方法
<script>
export default {
data() {
return {
BASE_API: process.env.BASE_API,
OSS_PATH: process.env.OSS_PATH,
fileUploadBtnText: '上传到服务器',
importBtnDisabled: false,
loading: false
}
},
created() {
},
methods:{
submitUpload() {
this.importBtnDisabled = true
this.loading = true
this.$refs.upload.submit()
},
fileUploadSuccess(response) {
this.loading = false
this.$message({
type: 'success',
message: '添加课程分类成功'
})
this.$router.push({path:'/subject/list'})
},
fileUploadError() {
this.loading = false
this.$message({
type: 'error',
message: '添加课程分类失败'
})
}
}
}
</script>
4、启动服务测试
四、课程分类列表后端(树形)
1、根据返回数据创建对应实体类
一级分类
@Data
public class OneSubject {
private String id;
private String title;
private List<TwoSubject> children = new ArrayList<>();
}
二级分类
@Data
public class TwoSubject {
private String id;
private String title;
}
返回数据格式为:
2、编写Controller类
@ApiOperation(value = "课程分类列表")
@GetMapping("getAllSubject")
public R getAllSubject(){
List<OneSubject> list = subjectService.getAllOneTwoSubject();
return R.ok().data("list",list);
}
3、编写Service类
@Override
public List<OneSubject> getAllOneTwoSubject() {
QueryWrapper<EduSubject> wrapperOne = new QueryWrapper<>();
wrapperOne.eq("parent_id",0);
List<EduSubject> oneSubjectList = baseMapper.selectList(wrapperOne);
QueryWrapper<EduSubject> wrapperTwo = new QueryWrapper<>();
wrapperTwo.ne("parent_id",0);
List<EduSubject> twoSubjectList = baseMapper.selectList(wrapperTwo);
List<OneSubject> finalSubjectList = new ArrayList<>();
for (int i = 0; i < oneSubjectList.size(); i++) {
EduSubject eduSubject = oneSubjectList.get(i);
OneSubject oneSubject = new OneSubject();
BeanUtils.copyProperties(eduSubject,oneSubject);
finalSubjectList.add(oneSubject);
List<TwoSubject> twoFinalSubjectList = new ArrayList<>();
for (int m = 0; m < twoSubjectList.size(); m++) {
EduSubject tSubject = twoSubjectList.get(m);
if(tSubject.getParentId().equals(eduSubject.getId())) {
TwoSubject twoSubject = new TwoSubject();
BeanUtils.copyProperties(tSubject,twoSubject);
twoFinalSubjectList.add(twoSubject);
}
}
oneSubject.setChildren(twoFinalSubjectList);
}
return finalSubjectList;
}
4、使用swagger进行测试
五、课程分类列表前端
1、参考tree模块把前端整合出来
<template>
<div class="app-container">
<el-input v-model="filterText" placeholder="Filter keyword" style="margin-bottom:30px;" />
<el-tree
ref="tree2"
:data="data2"
:props="defaultProps"
:filter-node-method="filterNode"
class="filter-tree"
default-expand-all
/>
</div>
</template>
2、对应的js文件
<script>
import subject from '@/api/edu/subject'
export default {
data() {
return {
filterText: '',
data2: [],
defaultProps: {
children: 'children',
label: 'title'
}
}
},
created() {
this.getAllSubjectList()
},
watch: {
filterText(val) {
this.$refs.tree2.filter(val)
}
},
methods: {
getAllSubjectList(){
subject.getSubjectList()
.then(response => {
this.data2 = response.data.list
})
},
filterNode(value, data) {
if (!value) return true
return data.title.toLowerCase().indexOf(value) !== -1
}
}
}
</script>
3、前端接口调用
import request from '@/utils/request'
export default{
getSubjectList(){
return request({
url: `/eduservice/subject/getAllSubject`,
method: 'get'
})
}
}
4、启动项目服务测试
如果有收获!!! 希望老铁们来个三连,点赞、收藏、转发。
创作不易,别忘点个赞,可以让更多的人看到这篇文章,顺便鼓励我写出更好的博客
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)