课程分类存储结构.png
EasyExcel写操作.png
EasyExcel读操作.png
EasyExcel读操作分类.png
一、引入pom依赖
<dependencies>
<!--阿里开源操作excel表格的工具类,引入的前提是已经引入poi因为他是对poi的封装-->
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
</dependencies>
二、创建实体类
设置表头和添加的数据字段
package com.caiweiwei.demo.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class DemoData {
//设置表头名称
@ExcelProperty(value = "学生编号",index = 0)
private Integer sno;
@ExcelProperty(value = "学生姓名",index = 1)
private String name;
}
三、实现写操作
//实现对excel写的操作
//1.设置写入文件夹的地址和文件的名称
String filename = "F:\\write.xlsx";
//调用easyExcel里面的方法实现写的操作
//write方法的两个参数:第一个参数文件路径名称,第二个参数实体类class
EasyExcel.write(filename, DemoData.class).sheet("学生列表").doWrite(getData());
创建方法循环设置要添加到Excel的数据
//循环设置要添加的数据，最终封装到list集合中
private static List<DemoData> data() {
List<DemoData> list = new ArrayList<DemoData>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setSno(i);
data.setSname("张三"+i);
list.add(data);
}
return list;
}
四、实现读操作
创建对应实体类
package com.caiweiwei.demo.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class DemoData {
//设置表头名称
@ExcelProperty(value = "学生编号",index = 0)
private Integer sno;
@ExcelProperty(value = "学生姓名",index = 1)
private String name;
}
创建读取操作的监听器
package com.caiweiwei.demo.excel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.Map;
public class ExcelListener extends AnalysisEventListener<DemoData> {
//一行一行读取excel内容
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
System.out.println("*****"+demoData);
}
//读取表头内容
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头"+headMap);
}
//读取完成之后做什么事情
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
调用实现最终的读取
public static void main(String[] args) throws Exception {
// 写法1：
String fileName = "F:\\01.xlsx";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, ReadData.class, new ExcelListener()).sheet().doRead();
}
五、案例代码
功能描述 表格写有一级分类和对应二级分类,读取表格内容将分类存储到数据库中,并把重复的去掉
读取表格实体类
package com.caiweiwei.eduservice.entity.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class SubjectData {
@ExcelProperty(index = 0)
private String oneSubjectName;
@ExcelProperty(index = 1)
private String twoSubjectName;
}
controller层
package com.caiweiwei.eduservice.controller;
import com.caiweiwei.commonutils.R;
import com.caiweiwei.eduservice.service.EduSubjectService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
/**
* <p>
* 课程科目 前端控制器
* </p>
*
* @author testjava
* @since 2020-09-15
*/
@RestController
@RequestMapping("/eduservice/subject")
@CrossOrigin
public class EduSubjectController {
@Autowired
private EduSubjectService eduSubjectService;
@PostMapping
public R saveSubject(MultipartFile file) {
eduSubjectService.saveSubject(file, eduSubjectService);
return R.ok();
}
}
service实现类
package com.caiweiwei.eduservice.service.impl;
import com.alibaba.excel.EasyExcel;
import com.caiweiwei.eduservice.entity.EduSubject;
import com.caiweiwei.eduservice.entity.excel.SubjectData;
import com.caiweiwei.eduservice.listener.SubjectExcelListener;
import com.caiweiwei.eduservice.mapper.EduSubjectMapper;
import com.caiweiwei.eduservice.service.EduSubjectService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
/**
* <p>
* 课程科目 服务实现类
* </p>
*
* @author testjava
* @since 2020-09-15
*/
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
/**
* 功能描述:添加课程分类
*
* @Author: 蔡威威
* @Date: 2020/9/15 21:36
*/
@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();
}
}
}
编写的监听器
package com.caiweiwei.eduservice.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.caiweiwei.eduservice.entity.EduSubject;
import com.caiweiwei.eduservice.entity.excel.SubjectData;
import com.caiweiwei.eduservice.service.EduSubjectService;
import com.caiweiwei.servicebase.exceptionhandler.GuliException;
import java.util.Map;
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
//因为SubjectExcelListener不能交给spring管理,需要自己new,不能注入其他对象
//不能实现数据库操作
public EduSubjectService eduSubjectService;
public SubjectExcelListener() {
}
public SubjectExcelListener(EduSubjectService eduSubjectService) {
this.eduSubjectService = eduSubjectService;
}
//读取excel内容,一行一行进行读取
@Override
public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
if (subjectData == null) {
throw new GuliException(20001, "文件数据为空");
}
//一行一行读取,每次读取有两个值,第一个值为一级分类,第二个值为对应的二级分类
EduSubject eduSubject = existOneSubject(eduSubjectService, subjectData.getOneSubjectName());
//添加一级分类
if (eduSubject == null) {
eduSubject = new EduSubject();
eduSubject.setParentId("0");
eduSubject.setTitle(subjectData.getOneSubjectName());
eduSubjectService.save(eduSubject);
}
//添加二级分类
//获取一级分类的id值
String pid = eduSubject.getId();
EduSubject existTwoSubject = this.existTwoSubject(eduSubjectService, subjectData.getTwoSubjectName(), pid);
if (existTwoSubject == null) {
existTwoSubject = new EduSubject();
existTwoSubject.setTitle(subjectData.getTwoSubjectName());
existTwoSubject.setParentId(pid);
eduSubjectService.save(existTwoSubject);
}
}
//判断一级分类是否重复
private EduSubject existOneSubject(EduSubjectService eduSubjectService, String name) {
QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("title", name);
queryWrapper.eq("parent_id", "0");
return eduSubjectService.getOne(queryWrapper);
}
//判断二级分类是否重复
private EduSubject existTwoSubject(EduSubjectService eduSubjectService, String name, String pid) {
QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("title", name);
queryWrapper.eq("parent_id", pid);
return eduSubjectService.getOne(queryWrapper);
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
上传表格示意图
课程分类
网友评论