个人笔记,仅供参考
一、Alibaba EasyExcel
1.简介
1.1应用场景
数据导入、数据导出、(异构系统、不可信系统之间)数据传输
1.2 官网
地址:https://github.com/alibaba/easyexcel
快速开始(参考代码):https://www.yuque.com/easyexcel/doc/easyexcel
1.3最简单的写
1.3.1新建Maven项目
项目名:alibaba_easyexcel
1.3.2 依赖
- easyexcel
- slf4j-simple + xmlbeans
- easyexcel的依赖项,maven不能正确引入,需要手动添加。
- lombok
- junit
pom.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| <dependencies>
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.7</version> </dependency>
<dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <version>1.7.5</version> </dependency>
<dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>3.1.0</version> </dependency>
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.10</version> </dependency>
<dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency>
</dependencies>
|
1.3.3业务类
<1>实体类
@ExcelProperty
- value:对应Excel中的字段
- index:
- 实体类属性对应Excel列的索引,0表示第一列
- 指定写入列
@ExcelIgnore
:忽略字段,不会出现在Excel中
@DateTimeFormat
:日期格式化
@NumberFormat
:数值格式化
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| package com.atguigu.easyexcel.entity;
import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.DateTimeFormat; import com.alibaba.excel.annotation.format.NumberFormat; import lombok.Data;
import java.util.Date;
@Data public class ExcelStudentData { @ExcelProperty("姓名") private String name;
@DateTimeFormat("yyyy年MM月dd日 hh时mm分ss秒") @ExcelProperty("生日") private Date birthday;
@NumberFormat("#.##%") @ExcelProperty(value = "薪资",index = 3) private Double salary;
@ExcelIgnore private String password; }
|
<2>测试用例
- Excel 03版
- 后缀名 xls
- 写入时指定写入03版本:excelType(ExcelTypeEnum.XLS)
- 标题+数据 不能超过65536行
- Excel 07版
- 后缀名 xlsx
- 写入时不指定版本,默认07版本
- 标题+数据 可以超过65536行
核心代码(具体含义看测试类中代码注释)
1 2 3 4
| EasyExcel.write(fileName, ExcelStudentData.class) .sheet("模板") .doWrite(data());
|
ExcelWriteTest.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
| package com.atguigu.easyexcel.test;
import com.alibaba.excel.EasyExcel; import com.alibaba.excel.support.ExcelTypeEnum; import com.atguigu.easyexcel.entity.ExcelStudentData; import org.junit.Test;
import java.util.ArrayList; import java.util.Date; import java.util.List;
public class ExcelWriteTest {
@Test public void testSimpleWrite07(){ String fileName = "D:\\.temp\\simpleWrite07.xlsx";
EasyExcel.write(fileName, ExcelStudentData.class) .sheet("模板") .doWrite(data()); } @Test public void testSimpleWrite03(){ String fileName = "D:\\.temp\\simpleWrite03.xls";
EasyExcel.write(fileName, ExcelStudentData.class) .excelType(ExcelTypeEnum.XLS) .sheet("模板") .doWrite(data()); }
public List<ExcelStudentData> data(){
List list = new ArrayList<ExcelStudentData>();
for (int i = 0; i < 65535; i++) {
ExcelStudentData data = new ExcelStudentData();
data.setName("Cysheng" + i); data.setBirthday(new Date()); data.setSalary(99.9); data.setPassword("password");
list.add(data); } return list; } }
|
1.4最简单的读
ExcelStudentDataListener.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| package com.atguigu.easyexcel.listener;
import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.atguigu.easyexcel.entity.ExcelStudentData; import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList; import java.util.List;
@Slf4j public class ExcelStudentDataListener extends AnalysisEventListener<ExcelStudentData> { public static final Integer BATCH_COUNT = 100;
List<ExcelStudentData> studentDataList = new ArrayList<ExcelStudentData>();
public void invoke(ExcelStudentData excelStudentData, AnalysisContext analysisContext) { studentDataList.add(excelStudentData);
if (studentDataList.size() >= BATCH_COUNT) { log.info("存入数据库"); studentDataList.clear(); }
}
public void doAfterAllAnalysed(AnalysisContext analysisContext) { log.info("存入数据库"); studentDataList.clear(); log.info("所有数据解析完成"); } }
|
测试用例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| package com.atguigu.easyexcel.test;
import com.alibaba.excel.EasyExcel; import com.alibaba.excel.support.ExcelTypeEnum; import com.atguigu.easyexcel.entity.ExcelStudentData; import com.atguigu.easyexcel.listener.ExcelStudentDataListener; import org.junit.Test;
public class ExcelReadTest { @Test public void testSimpleRead07(){ String fileName = "D:\\.temp\\simpleWrite07.xlsx";
EasyExcel.read(fileName, ExcelStudentData.class,new ExcelStudentDataListener()) .sheet() .doRead();
} @Test public void testSimpleRead03() { String fileName = "D:\\.temp\\simpleWrite03.xls";
EasyExcel.read(fileName, ExcelStudentData.class,new ExcelStudentDataListener()) .excelType(ExcelTypeEnum.XLS) .sheet() .doRead();
} }
|
二、课程类名管理
课程分类管理需要实现两部分:
1.路由及组件
1.1 创建组件
- 新建subject目录
- 新建import.vue和list.vue
- import.vue 用于导入课程分类
- list.vue 用于展示课程分类列表
1.2配置路由
@/router/index.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| { path: '/subject', component: Layout, redirect: '/subject/list', name: 'Subject', meta: { title: '课程分类管理' }, children: [ { path: 'list', name: 'SubjectList', component: () => import('@/views/subject/list'), meta: { title: '课程分类列表' } }, { path: 'import', name: 'SubjectImport', component: () => import('@/views/subject/import'), meta: { title: '导入课程分类' } } ] },
|
2.导入课程分类
- 需要实现两个功能:
- 课程分类模板
- 课程分类模板提供上传分类模板的格式
- 将模板上传到阿里云,提供下载
- 导入课程分类
- 选取编写完成的课程分类文件
- 导入按钮将Excel传入后端服务器
- 后端服务器存储到数据库
2.1课程分类模板
2.1.1上传阿里云
- 新建excel文件夹
- 上传”课程分类列表模板.xls”
2.1.2添加配置
将(自己的)bucket地址配置到全局常量中(重启服务器生效)
在创建的Bucket概览中查看bucket地址。
config/dev.env.js
1
| OSS_PATH: '"https://cysheng0322.oss-cn-beijing.aliyuncs.com"'
|
2.1.3代码编写
import.vue
数据:定义下载路径
1 2 3 4 5 6 7 8 9 10
| <script> export default { data() { return { defaultExcelTemplate: process.env.OSS_PATH + '/excel/课程分类列表模板.xls', importBtnDisabled: false } } } </script>
|
页面:使用a标签根据定义的下载路径下载模板
- 上面的el-form-item用于下载模板
- 下面的el-form-item用于上传Excel
- el-upload
- 类似type=file的输入框
- ref 便于在脚本中调用该元素
- :auto-upload 是否自动上传
- :on-exceed 超出限制个数回调
- :on-success 上传成功回调
- :on-error 上传失败回调
- :limit=”1” 限制文件个数
- action 上传地址
- name 组件名
- accept 限制上传类型
- 选取文件el-button
- 导入el-button
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
| <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="defaultExcelTemplate">点击下载模版</a> </el-tag>
</el-form-item>
<el-form-item label="选择Excel"> <el-upload ref="upload" :auto-upload="false" :on-exceed="fileUploadExceed" :on-success="fileUploadSuccess" :on-error="fileUploadError" :limit="1" action="http://127.0.0.1:8110/admin/edu/subject/import" name="file" accept="application/vnd.ms-excel"> <el-button slot="trigger" size="small" type="primary">选取文件</el-button> <el-button :disabled="importBtnDisabled" style="margin-left: 10px;" size="small" type="success" @click="submitUpload()">导入</el-button> </el-upload> </el-form-item> </el-form> </div> </template>
|
函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| methods: { fileUploadExceed() { this.$message.warning('只能选取一个文件') },
submitUpload() { this.importBtnDisabled = true this.$refs.upload.submit() }, fileUploadSuccess(response) { if (response.success) { this.importBtnDisabled = false this.$message.success(response.message) this.$refs.upload.clearFiles() } else { this.$message.error('上传失败! (非20000)') } }, fileUploadError(response) { this.importBtnDisabled = false this.$message.error('上传失败! (http失败)') this.$refs.upload.clearFiles() } }
|
2.2导入课程分类
实体类
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| package com.atguigu.guli.service.edu.entity.vo;
import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data;
@Data public class ExcelSubjectData {
@ExcelProperty(value = "一级分类") private String levelOneTitle;
@ExcelProperty(value = "二级分类") private String levelTwoTitle; }
|
监听器
- 存入数据库时,需要去重。
- 一级标题:不可重复
- 二级标题:同一一级分类下不可重复,不同一级分类下可以重复
- 判断逻辑
- 一级标题存在:
- parent_id字段”0” 即 一级分类名称
- title字段为传入一级分类名称
- 二级标题存在:
- parent_id字段等于传入的一级分类名称id
- title字段为传入二级分类名称
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
| package com.atguigu.guli.service.edu.lintener;
import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.atguigu.guli.service.edu.entity.Subject; import com.atguigu.guli.service.edu.entity.vo.ExcelSubjectData; import com.atguigu.guli.service.edu.mapper.SubjectMapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor;
@Data @NoArgsConstructor @AllArgsConstructor public class ExcelSubjectDataListener extends AnalysisEventListener<ExcelSubjectData> {
private SubjectMapper subjectMapper;
@Override public void invoke(ExcelSubjectData data, AnalysisContext analysisContext) { String levelOneTitle = data.getLevelOneTitle(); String levelTwoTitle = data.getLevelTwoTitle();
Subject subject = this.getLevelOneTitle(levelOneTitle);
if (subject == null){ subject = new Subject(); subject.setTitle(levelOneTitle); subject.setParentId("0"); subject.setSort(0); subjectMapper.insert(subject); }
String levelOneId = subject.getId(); Subject subjectTwo = this.getLevelTwoTitle(levelTwoTitle, levelOneId); if (subjectTwo == null){ subject = new Subject(); subject.setTitle(levelTwoTitle); subject.setParentId(levelOneId); subject.setSort(0); subjectMapper.insert(subject); } }
@Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { System.out.println("解析完成"); }
private Subject getLevelOneTitle(String levelOneTitle) {
QueryWrapper<Subject> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("title",levelOneTitle); queryWrapper.eq("parent_id","0");
return subjectMapper.selectOne(queryWrapper); }
private Subject getLevelTwoTitle(String levelTwoTitle,String levelOneId){
QueryWrapper<Subject> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("parent_id",levelOneId); queryWrapper.eq("title",levelTwoTitle);
return subjectMapper.selectOne(queryWrapper); } }
|
SubjectController.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| @ApiOperation("批量导入课程分类") @PostMapping("/import") public R batchImport( @ApiParam(value = "Excel文件", required = true) @RequestParam("file") MultipartFile multipartFile){ try { InputStream is = multipartFile.getInputStream();
subjectService.batchImport(is); return R.ok().message("批量导入成功"); } catch (Exception e) { log.info(ExceptionUtils.getMessage(e)); throw new GuliException(ResultCodeEnum.EXCEL_DATA_IMPORT_ERROR); } }
|
SubjectService.java
1
| void batchImport(InputStream is);
|
SubjectServiceImpl.java
EasyExcel官网介绍:
有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| @Service public class SubjectServiceImpl extends ServiceImpl<SubjectMapper, Subject> implements SubjectService {
@Autowired private SubjectMapper subjectMapper;
@Override public void batchImport(InputStream is) { EasyExcel.read(is, ExcelSubjectData.class,new ExcelSubjectDataListener(subjectMapper)) .excelType(ExcelTypeEnum.XLS) .sheet() .doRead(); } }
|
3.展示课程分类
将二级分类嵌套一级分类中,返回给前端,前端使用element-ui提供的组件进行展示。
两种嵌套方式:
方式一
方式二
- 先查一级类别
- 遍历一级类别,通过一级类别id查询二级类别
3.1配置
问题:编译文件只有接口的class文件,没有mapper.xml文件
原因:maven默认情况下不发布src/main/java目录下资源文件到target目录。
解决:
pom.xml
1 2 3 4 5 6 7 8 9 10 11 12 13
| <build> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> <filtering>false</filtering> </resource> </resources> </build>
|
application.yml
1 2
| mapper-locations: classpath:com/atguigu/guli/service/edu/mapper/xml/*.xml
|
3.2后端
SubjectVo
1 2 3 4 5 6 7 8 9 10 11 12
| package com.atguigu.guli.service.edu.entity.vo;
@Data public class SubjectVo implements Serializable {
private static final long serialVersionUID = 1L;
private String id; private String title; private Integer sort; private List<SubjectVo> children = new ArrayList<>(); }
|
SubjectController
1 2 3 4 5 6
| @ApiOperation(value = "嵌套数据列表") @GetMapping("nested-list") public R nestedList(){ List<SubjectVo> subjectVoList = subjectService.nestedList(); return R.ok().data("items", subjectVoList); }
|
SubjectService
1
| List<SubjectVo> nestedList();
|
SubjectServiceImpl
1 2 3 4
| @Override public List<SubjectVo> nestedList() { return baseMapper.selectNestedListByParentId("0"); }
|
SubjectMapper.java
1
| List<SubjectVo> selectNestedListByParentId(String parentId);
|
SubjectMapper.xml
1 2 3 4 5 6 7 8 9 10 11 12 13
| <resultMap id="nestedSubject" type="com.atguigu.guli.service.edu.entity.vo.SubjectVo"> <id property="id" column="id"/> <result property="title" column="title"/> <result property="sort" column="sort" /> <collection property="children" ofType="com.atguigu.guli.service.edu.entity.vo.SubjectVo" select="selectNestedListByParentId" column="id"/> </resultMap>
<select id="selectNestedListByParentId" resultMap="nestedSubject"> select id, sort, title from edu_subject where parent_id = #{parentId} </select>
|
3.3前端
3.3.1 api
src/api下 新建subject.js
1 2 3 4 5 6 7 8 9 10
| import request from '@/utils/request'
export default { getNestedTreeList() { return request({ url: '/admin/edu/subject/nested-list', method: 'get' }) } }
|
3.3.2 树形结构展示
list.vue
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
| <template> <div class="app-container"> <el-input v-model="filterText" placeholder="输入查询条件" style="margin-bottom:30px;" />
<el-tree ref="subjectTree" :data="subjectList" :props="defaultProps" :filter-node-method="filterNode" style="margin-top:10px;" />
</div> </template>
<script> import subjectApi from '@/api/subject' export default {
data() { return { filterText: '', subjectList: [], defaultProps: { children: 'children', label: 'title' } } },
watch: { filterText(val) { this.$refs.subjectTree.filter(val) } },
created() { this.fetchNodeList() },
methods: { fetchNodeList() { subjectApi.getNestedTreeList().then(response => { this.subjectList = response.data.items }) },
filterNode(value, data) { if (!value) return true return data.title.toLowerCase().indexOf(value.toLowerCase()) !== -1 } } } </script>
|