1、EasyExcel 的使用
官方地址:
GitHub:GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具
官网:EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel
1.1、写操作
1.1.1、引入依赖
注意:低版本还需要 poi 依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
1.1.2、创建实体类
注意实体类的属性和 excel 数据对应
@Data
public class ExcelData {
@ExcelProperty("学生编号") // 设置excel表头名称
private Integer no;
@ExcelProperty("学生姓名")
private String name;
}
1.1.3、实现写操作
public class EasyExcelWrite {
// 实现excel写操作
public static void main(String[] args) {
// 设置写入文件夹地址和excel文件名称
String fileName = "D:\\Java\\easyexcel\\easyexcel.xlsx";
// 写入excel的数据
List<ExcelData> list = new ArrayList<>();
for(int i = 0; i < 10; i++) {
ExcelData data = new ExcelData();
data.setName("name" + i);
data.setNo(i);
list.add(data);
}
// 调用方法实现写操作
EasyExcel.write(fileName, ExcelData.class) // 第一个参数为文件路径,第二个参数为实体类class
.sheet("学生列表")
.doWrite(list);
}
}
效果如下:
1.2、读操作
1.2.1、创建实体类
@Data
public class ExcelData {
// value设置写入excel的表头名称,index设置读取excel的第几列(从0开始)
@ExcelProperty(value = "学生编号", index = 0)
private Integer no;
@ExcelProperty(value = "学生姓名",index = 1)
private String name;
}
1.2.2、创建读操作的监听器
public class ExcelListener extends AnalysisEventListener<ExcelData> {
/**
* 一行一行的读取excel内容
* @param excelData
* @param analysisContext
*/
@Override
public void invoke(ExcelData excelData, AnalysisContext analysisContext) {
System.out.println("invoke --- " + excelData.toString());
}
/**
* 读取表头内容
* @param headMap
* @param context
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
super.invokeHeadMap(headMap, context);
System.out.println("invokeHeadMap --- " + headMap.toString());
}
/**
* 读取完成后调用的方法
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
1.2.3、读操作
public class EasyExcelWrite {
// 实现excel写操作
public static void main(String[] args) {
// 设置写入文件夹地址和excel文件名称
String fileName = "D:\\Java\\easyexcel\\easyexcel.xlsx";
// 写入excel的数据
List<ExcelData> list = new ArrayList<>();
for(int i = 0; i < 10; i++) {
ExcelData data = new ExcelData();
data.setName("name" + i);
data.setNo(i);
list.add(data);
}
// 调用方法实现写操作
EasyExcel.write(fileName, ExcelData.class) // 第一个参数为文件路径,第二个参数为实体类class
.sheet("学生列表")
.doWrite(list);
}
}
2、添加课程分类
2.1、在 service_edu 添加 easyexcel 依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
2.2、使用代码生成器生成代码
修改 service_edu 中的 CodeGenerator 的表名
strategy.setInclude("edu_subject"); // 对应的表名
然后在 EduSubjectController 添加跨域注解和 Swagger 注解
@CrossOrigin
@Api(description="课程分类管理")
2.3、后端代码
2.3.1、添加 excel 数据对应的实体类
@Data
public class SubjectData {
@ExcelProperty(index = 0)
@ApiModelProperty(value = "课程一级分类")
private String oneSubjectName;
@ExcelProperty(index = 1)
@ApiModelProperty(value = "课程二级分类")
private String twoSubjectName;
}
2.3.2、添加课程实体类
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="EduSubject对象", description="课程科目")
public class EduSubject implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "课程类别ID")
@TableId(value = "id", type = IdType.ID_WORKER_STR)
private String id;
@ApiModelProperty(value = "类别名称")
private String title;
@ApiModelProperty(value = "父ID")
private String parentId;
@ApiModelProperty(value = "排序字段")
private Integer sort;
@ApiModelProperty(value = "创建时间")
@TableField(fill = FieldFill.INSERT)
private Date gmtCreate;
@ApiModelProperty(value = "更新时间")
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date gmtModified;
}
2.3.3、Service 方法
/**
* 根据上传的文件添加课程分类
* @param file 上传的excel文件
* @param eduSubjectService
*/
@Override
public void saveSubject(MultipartFile file, EduSubjectService eduSubjectService) {
try {
// 文件输入流
InputStream inputStream = file.getInputStream();
// 调用方法读取
EasyExcel.read(inputStream, SubjectData.class, new SubjectExcelListener(eduSubjectService)).sheet().doRead();
} catch (Exception e){
e.printStackTrace();
}
}
2.3.4、Controller 方法
/**
* 根据上传的文件添加课程分类
* @param file 上传的excel文件
* @return
*/
@PostMapping("/addSubject")
@ApiOperation(value = "课程分类Excel导入")
public R addSubject(MultipartFile file) {
eduSubjectService.saveSubject(file, eduSubjectService);
return R.ok();
}
2.3.5、添加读取 excel 的监听器
注意:EasyExcel 的监听器不能被 spring 管理,每次读取 excel 都要 new,然后里面用到 spring 可以构造方法传进去
// EasyExcel的监听器不能被spring管理,每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
public EduSubjectService eduSubjectService;
public SubjectExcelListener() {
}
public SubjectExcelListener(EduSubjectService eduSubjectService) {
this.eduSubjectService = eduSubjectService;
}
/**
* 判断数据库是否已经含有该一级分类
* @param eduSubjectService 一级分类名称
* @param oneSubject 要查询的一级分类名称
* @return
*/
private EduSubject existOneSubject(EduSubjectService eduSubjectService , String oneSubject){
LambdaQueryWrapper<EduSubject> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(EduSubject::getTitle, oneSubject);
queryWrapper.eq(EduSubject::getParentId, 0);
EduSubject one = eduSubjectService.getOne(queryWrapper);
return one;
}
/**
* 判断数据库是否已经含有该二级分类
* @param eduSubjectService
* @param twoSubject 二级分类的一级分类
* @param pid 二级分类名称
* @return
*/
private EduSubject existTwoSubject(EduSubjectService eduSubjectService , String twoSubject, String pid){
LambdaQueryWrapper<EduSubject> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(EduSubject::getTitle, twoSubject);
queryWrapper.eq(EduSubject::getParentId, pid);
EduSubject one = eduSubjectService.getOne(queryWrapper);
return one;
}
@Override
public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
if(subjectData == null) {
throw new GuliException(20001, "文件数据为空");
}
// 添加一级分类
EduSubject existOneSubject = this.existOneSubject(eduSubjectService, subjectData.getOneSubjectName());
if(existOneSubject == null){
existOneSubject = new EduSubject();
existOneSubject.setParentId("0");
existOneSubject.setTitle(subjectData.getOneSubjectName());
eduSubjectService.save(existOneSubject);
}
// 添加二级分类
String pid = existOneSubject.getId(); // 获取一级分类id:若原本表里没有该一级分类,在添加是也会注入进来
EduSubject existTwoSubject = this.existTwoSubject(eduSubjectService, subjectData.getTwoSubjectName(), pid);
if(existTwoSubject == null){
existTwoSubject = new EduSubject();
existTwoSubject.setParentId(pid);
existTwoSubject.setTitle(subjectData.getTwoSubjectName());
eduSubjectService.save(existTwoSubject);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
2.4、前端显示
2.4.1、添加路由
在 src\router\index.js 中添加路由
{
path: '/subject',
component: Layout,
redirect: '/subject/lsit',
name: '课程分类管理',
meta: { title: '课程分类管理', icon: 'example' },
children: [ // 子标签
{
path: 'table',
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.4.2、页面
src\views\edu\subject\save.vue
<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+'/subject.xls'">点击下载模版</a>
</el-tag>
</el-form-item>
<el-form-item label="选择Excel">
<!--
ref:组件唯一标识
auto-upload:自动上传
on-success:上传成功调用的方法
on-error:上传失败调用的方法
disabled:按钮是否禁用
limit:最大允许上传个数
action:上传的后端接口地址
name:上传的文件字段名
accept:接受上传的文件类型,其他类型参考:https://blog.csdn.net/j2spark/article/details/51260767
-->
<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>
<script>
export default {
data() {
return {
BASE_API: process.env.BASE_API, // 接口API地址
OSS_PATH: 'https://zyj-guli.oss-cn-hangzhou.aliyuncs.com', // 阿里云OSS地址
importBtnDisabled: false, // 按钮是否禁用,
loading: false,
fileUploadBtnText: '上传到服务器', // 按钮文字
}
},
created() {
},
methods: {
/**
* 点击上传按钮上传文件
*/
submitUpload() {
if(this.$refs.upload.uploadFiles.length != 0){
this.fileUploadBtnText = '正在上传';
this.importBtnDisabled = true;
this.loading = true;
this.$refs.upload.submit(); // 提交ref为upload的表单
} else {
this.$message({
type: 'error',
message: '请选择文件'
})
}
},
/**
* 上传成功
*/
fileUploadSuccess() {
// 提示信息
this.loading = false;
this.fileUploadBtnText = '上传到服务器';
this.$message({
type: 'success',
message: '上传成功,添加课程分类成功'
})
// 跳转到课程分类列表
},
/**
* 上传失败
*/
fileUploadError() {
this.loading = false;
this.fileUploadBtnText = '上传到服务器';
this.$message({
type: 'error',
message: '上传失败,添加课程分类失败'
})
}
}
}
</script>
3、课程分类树状显示
3.1、后端
3.1.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;
}
3.1.2、Service 方法
/**
* 获取所有课程分类(按照树型结构)
* @return
*/
@Override
public List<OneSubject> getTreeSubject() {
// 查询所有一级分类
LambdaQueryWrapper<EduSubject> queryWrapperOne = new LambdaQueryWrapper<>();
queryWrapperOne.eq(EduSubject::getParentId, 0);
//List<EduSubject> eduSubjects = baseMapper.selectList(queryWrapperOne);
List<EduSubject> listOne = this.list(queryWrapperOne);
// 查询所有二级分类
LambdaQueryWrapper<EduSubject> queryWrapperTwo = new LambdaQueryWrapper<>();
queryWrapperOne.ne(EduSubject::getParentId, 0);
List<EduSubject> listTwo = this.list(queryWrapperTwo);
// 封装一级分类
List<OneSubject> oneSubjects = listOne.stream().map((oneItem) -> {
OneSubject oneSubject = new OneSubject();
//oneSubject.setId(item.getId());
//oneSubject.setTitle(item.getTitle());
BeanUtils.copyProperties(oneItem, oneSubject);
List<TwoSubject> children = new ArrayList<>();
for (EduSubject twoItem : listTwo) {
if(twoItem.getParentId().equals(oneItem.getId())){
TwoSubject twoSubject = new TwoSubject();
BeanUtils.copyProperties(twoItem, twoSubject);
children.add(twoSubject);
}
}
oneSubject.setChildren(children);
return oneSubject;
}).collect(Collectors.toList());
return oneSubjects;
}
3.1.3、Controller 方法
/**
* 获取课程列表(符合前端树状格式)
* @return
*/
@GetMapping("/getAllSubject")
@ApiOperation(value = "获取课程列表(符合前端树状格式)")
public R getAllSubject(){
List<OneSubject> list = eduSubjectService.getTreeSubject();
return R.ok().data("list", list);
}
3.2、前端
3.2.1、创建 JS 文件添加获取课程分类信息的 API
src\api\edu\subject.js
import request from '@/utils/request'
export default {
/**
* 获取所有课程分类信息(树状结构的JSON格式)
* @returns
*/
getSubjectList() {
return request({
url: '/eduservice/subject/getAllSubject',
method: 'GET'
})
}
}
3.2.2、前端页面
<template>
<div class="app-container">
<!-- 检索树 -->
<el-input v-model="filterText" placeholder="搜索" style="margin-bottom:30px;" />
<!--
ref:唯一标识
:data:要展示的数据(数组)
:props:自定义配置
label 指定节点标签为节点对象的某个属性值
children 指定子树为节点对象的某个属性值
disabled 指定节点选择框是否禁用为节点对象的某个属性值
isLeaf 指定节点是否为叶子节点,仅在指定了 lazy 属性的情况下生效
:filter-node-method:检索功能的方法
-->
<el-tree ref="tree2"
:data="data"
:props="defaultProps"
:filter-node-method="filterNode"
class="filter-tree"
default-expand-all />
</div>
</template>
<script>
import subject from '@/api/edu/subject.js'
export default {
data() {
return {
filterText: '',
data: [], // 课程分类信息(树状格式)
defaultProps: {
children: 'children',
label: 'title'
}
}
},
created() {
this.getAllSubject();
},
watch: {
filterText(val) {
this.$refs.tree2.filter(val)
}
},
methods: {
/**
* 树状图的检索方法(不区分大小写),若为一级节点,可展开子节点
* @param {*} value
* @param {*} data
* @param {*} node
*/
filterNode(value, data, node) {
if (!value) return true;
let ifOne = data.title.toLowerCase().indexOf(value.toLowerCase()) !== -1;
let ifTwo = node.parent && node.parent.data && node.parent.data.title && (node.parent.data.title.toLowerCase().indexOf(value.toLowerCase()) !== -1);
let resultOne = false;
let resultTwo = false;
if (node.level === 1) {
resultOne = ifOne;
} else if (node.level === 2) {
resultTwo = ifOne || ifTwo;
}
return resultOne || resultTwo;
},
/**
* 获取所有课程分类信息(树状结构的JSON格式)
*/
getAllSubject() {
subject.getSubjectList().then(response => {
this.data = response.data.list;
})
}
}
}
</script>