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><!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.1</version></dependency>
</dependencies>
还需要 poi 依赖
2、创建实体类
@Data
public class DemoData {
//设置excel表头名称@ExcelProperty("学生编号")private Integer sno;@ExcelProperty("学生姓名")private String sname;
}
3、实现写操作
public class TestEasyExcel {
public static void main(String[] args) {
//实现excel写的操作//1 设置写入文件夹地址和excel文件名称String filename = "E:\\write.xlsx";//2 调用easyExcel里面的方法实现写操作//write方法两个参数，第一个参数文件路径名称，第二个参数实体类classEasyExcel.write(filename,DemoData.class).sheet("学生列表").doWrite(getData());}//创建方法返回list集合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 {
//设置excel表头名称@ExcelProperty(value = "学生编号",index = 0)private Integer sno;@ExcelProperty(value = "学生姓名",index = 1)private String sname;
}
2、创建监听进行excel文件读取
public class ExcelListener extends AnalysisEventListener<DemoData> {
//一行一行读取excel内容@Overridepublic void invoke(DemoData data, AnalysisContext analysisContext) {
System.out.println("***"+data);}//读取表头内容@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头"+headMap);}//读取完成之后@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
3、最终测试
public static void main(String[] args) {
//实现excel读的操作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 {
@Autowiredprivate EduSubjectService subjectService;//添加课程分类//获取上传的文件，把文件内容读取出来@ApiOperation(value = "添加课程分类")@PostMapping("addSubject")public R addSubject(MultipartFile file) {
//上传过来excel文件subjectService.saveSubject(file,subjectService);return R.ok();}}
5、编写 EduSubjectServiceImpl 类
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
//添加课程分类@Overridepublic 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> {
//因为SubjectExcelListener不能交给Spring进行管理，需要自己new，不能注入其他对象//不能实现数据库操作private EduSubjectService subjectService;public SubjectExcelListener() {
}public SubjectExcelListener(EduSubjectService subjectService) {
this.subjectService = subjectService;}//一行一行去读取excle内容@Overridepublic 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);}//获取一级分类id值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;}//读取excel表头信息@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头信息："+headMap);}//读取完成后执行@Overridepublic 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-uploadref="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, // 接口API地址OSS_PATH: process.env.OSS_PATH,// 阿里云OSS地址fileUploadBtnText: '上传到服务器',//按钮文字importBtnDisabled: false, // 按钮是否禁用,loading: false}},created() {
},methods:{
//点击按钮上传文件到接口里面submitUpload() {
this.importBtnDisabled = truethis.loading = true// js: document.getElementById("upload").submit()this.$refs.upload.submit()},//上传成功fileUploadSuccess(response) {
//提示信息this.loading = falsethis.$message({
type: 'success',message: '添加课程分类成功'})//跳转课程分类列表//路由跳转this.$router.push({
path:'/subject/list'})},//上传失败fileUploadError() {
this.loading = falsethis.$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集合泛型是一级分类List<OneSubject> list = subjectService.getAllOneTwoSubject();return R.ok().data("list",list);
}
3、编写Service类
//课程分类列表（树形）
@Override
public List<OneSubject> getAllOneTwoSubject() {
//1 查询所有一级分类 parent_id = 0QueryWrapper<EduSubject> wrapperOne = new QueryWrapper<>();wrapperOne.eq("parent_id",0);List<EduSubject> oneSubjectList = baseMapper.selectList(wrapperOne);//2 查询所有二级分类 parent_id != 0QueryWrapper<EduSubject> wrapperTwo = new QueryWrapper<>();wrapperTwo.ne("parent_id",0);List<EduSubject> twoSubjectList = baseMapper.selectList(wrapperTwo);//创建list集合，用于存储最终封装数据List<OneSubject> finalSubjectList = new ArrayList<>();//3 封装一级分类//查询出来所有一级分类list集合集合，得到每一个一级分类对象，回去每一个一级分类对象值，//封装到要求的list集合里面 List<OneSubject> findSubjectListfor (int i = 0; i < oneSubjectList.size(); i++) {
//遍历oneSubjectList集合//得到oneSubjectList每个eduSubject对象EduSubject eduSubject = oneSubjectList.get(i);//把eduSubject里面值获取出来，放到OneSubject对象里面//多个OneSubject放到findSubjectList里面OneSubject oneSubject = new OneSubject();
// oneSubject.setId(eduSubject.getId());
// oneSubject.setTitle(eduSubject.getTitle());//把eduSubject里面获取出来的值，放到oneSubject对象里面BeanUtils.copyProperties(eduSubject,oneSubject);//多个OneSubject放到findSubjectList里面finalSubjectList.add(oneSubject);//在一级分类循环遍历查询所有的二级分类//创建list集合封装每一个一级分类的二级分类List<TwoSubject> twoFinalSubjectList = new ArrayList<>();//遍历二级分类list集合for (int m = 0; m < twoSubjectList.size(); m++) {
//获取每个二级分类list集合EduSubject tSubject = twoSubjectList.get(m);//判断二级分类parentid和一级分类id是否一样if(tSubject.getParentId().equals(eduSubject.getId())) {
//把tSubject值复制到TwoSubject里面，放到twoFinalSubjectList里面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-treeref="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 truereturn 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、启动项目服务测试
如果有收获！！！ 希望老铁们来个三连，点赞、收藏、转发。
创作不易，别忘点个赞，可以让更多的人看到这篇文章，顺便鼓励我写出更好的博客