pom.xml
<!--lombok用来简化实体类:需要安装lombok插件-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.4</version>
</dependency>
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
读
读比较复杂,需要创建监听器EasyListener
实体类 DemoData
package com.shengun.osss;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class DemoData {
// index表示行
@ExcelProperty(value = "学生序号",index = 0)
private Integer sno;
@ExcelProperty(value = "学生姓名", index = 1)
private String sname;
}
监听类 EasyListener
package com.shengun.osss.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.shengun.osss.DemoData;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class EasyListener extends AnalysisEventListener<DemoData> {
/**
* 存储所有读取到的数据
*/
private List<DemoData> list = new ArrayList<>();
public List<DemoData> getList() {
return list;
}
// 一行一行读取
@Override
public void invoke(DemoData data, AnalysisContext context) {
System.out.println("***" + data);
//这里就可以存储数据到mysql中
list.add(data);
}
//读取表头
@Override
public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
System.out.println("表头信息:" + headMap);
}
//读取完成之后做到事情
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
}
读数据
package com.shengun.osss;
import com.alibaba.excel.EasyExcel;
import com.shengun.osss.listener.EasyListener;
import java.util.ArrayList;
import java.util.List;
public class TestEasyExcel {
public static void main(String[] args) {
String fileName = "D:\\pdf\\write.xls";
//读
EasyListener easyListener = new EasyListener();
EasyExcel.read(fileName, DemoData.class,easyListener).sheet().doRead();
List<DemoData> list = easyListener.getList();
System.out.println("###############" + list);
}
}
写
package com.shengun.osss;
import com.alibaba.excel.EasyExcel;
import java.util.ArrayList;
import java.util.List;
public class TestEasyExcel {
public static void main(String[] args) {
String fileName = "D:\\pdf\\write.xls";
//写
EasyExcel.write(fileName, DemoData.class)
.sheet("学生列表")
.doWrite(getDataList());
}
/**
* 构造要写的数据
* @return
*/
private static List<DemoData> getDataList() {
List<DemoData> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DemoData demoData = new DemoData();
demoData.setSno(i);
demoData.setSname("lucy" + i);
list.add(demoData);
}
return list;
}
}
后端接口编写实战
上传这种合适execl数据,后台把数据保存在mysql中
实体类
package com.shengun.eduservice.entity.execl;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModel;
import lombok.Data;
/**
* excel两列参数
*/
@ApiModel(value="excel对象",description="excel对象封装")
@Data
public class SubjectData {
@ExcelProperty(index = 0)
private String oneSubjectName;
@ExcelProperty(index = 1)
private String twoSubjectName;
}
controller
在监听程序中,我们需要把数据保存在mysql中,所有我们要把eduSubjectService当成参数传递给service
eduSubjectService.saveSubject(file,eduSubjectService);
代码:
package com.shengun.eduservice.controller;
import com.shengun.commonutils.R;
import com.shengun.eduservice.entity.subject.OneSubject;
import com.shengun.eduservice.service.EduSubjectService;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.security.auth.Subject;
import java.util.List;
/**
* <p>
* 课程科目 前端控制器
* </p>
*
* @author shengun
* @since 2022-04-17
*/
@RestController
@RequestMapping("/eduservice/subject")
@CrossOrigin
@ApiModel(value="课程管理",description = "课程管理")
public class EduSubjectController {
@Autowired
private EduSubjectService eduSubjectService;
// 添加课程分类
@PostMapping("/addSubject")
@ApiOperation(value = "添加课程分类")
public R addSubject(MultipartFile file) {
eduSubjectService.saveSubject(file,eduSubjectService);
return R.ok();
}
}
service + serviceImpl
需要把eduSubjectService 当成参数传递给监听程序
EasyExcel.read(inputStream, SubjectData.class,new SubjectExcelListener(eduSubjectService)).sheet().doRead();
package com.shengun.eduservice.service;
import com.shengun.eduservice.entity.EduSubject;
import com.baomidou.mybatisplus.extension.service.IService;
import com.shengun.eduservice.entity.subject.OneSubject;
import org.springframework.web.multipart.MultipartFile;
import java.util.List;
/**
* <p>
* 课程科目 服务类
* </p>
*
* @author shengun
* @since 2022-04-17
*/
public interface EduSubjectService extends IService<EduSubject> {
//添加课程分类
void saveSubject(MultipartFile file,EduSubjectService eduSubjectService);
}
package com.shengun.eduservice.service.impl;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.shengun.eduservice.entity.EduSubject;
import com.shengun.eduservice.entity.execl.SubjectData;
import com.shengun.eduservice.entity.subject.OneSubject;
import com.shengun.eduservice.entity.subject.TwoSubject;
import com.shengun.eduservice.listener.SubjectExcelListener;
import com.shengun.eduservice.mapper.EduSubjectMapper;
import com.shengun.eduservice.service.EduSubjectService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.shengun.servicebase.exceptionHandler.ZkcExecption;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* <p>
* 课程科目 服务实现类
* </p>
*
* @author shengun
* @since 2022-04-17
*/
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
@Autowired
private EduSubjectMapper eduSubjectMapper;
@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();
// 失败,抛出异常
throw new ZkcExecption(20002, "添加课程分类失败");
}
}
}
监听类
由于一级分类和二级分类在一张表中,很多人说可以连表查询
我这里使用的是
判断一级分类可不可以添加:
name = ? and parent_id = 0
判断二级分类可不可以添加:
name = ? and parent_id = ?
package com.shengun.eduservice.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.shengun.eduservice.entity.EduSubject;
import com.shengun.eduservice.entity.execl.SubjectData;
import com.shengun.eduservice.service.EduSubjectService;
import com.shengun.servicebase.exceptionHandler.ZkcExecption;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
/**
* 方便注入,实现添加课程
*/
private EduSubjectService eduSubjectService;
public SubjectExcelListener(){}
public SubjectExcelListener(EduSubjectService eduSubjectService) {
this.eduSubjectService = eduSubjectService;
}
@Override
public void invoke(SubjectData subjectData, AnalysisContext context) {
log.info("######################################添加表中信息:" + subjectData);
if(subjectData == null){
throw new ZkcExecption(20001, "添加失败");
}
//一级分类可不可以添加
EduSubject oneEduSubject = this.existOneSubject(eduSubjectService, subjectData.getOneSubjectName());
if(oneEduSubject == null){
// 等于空,重新new个EduSubject对象
oneEduSubject = new EduSubject();
oneEduSubject.setTitle(subjectData.getOneSubjectName());
oneEduSubject.setParentId("0");
eduSubjectService.save(oneEduSubject); //添加成功之后会返回ID
}
//获取父类ID
String parentId = oneEduSubject.getId();
EduSubject twoEduSubject = this.existTwoSubject(eduSubjectService, subjectData.getTwoSubjectName(), parentId);
if (twoEduSubject == null) {
twoEduSubject = new EduSubject();
twoEduSubject.setTitle(subjectData.getTwoSubjectName());
twoEduSubject.setParentId(parentId);
eduSubjectService.save(twoEduSubject); //添加二级分类
}
}
/**
* 判断一级分类可不可以添加
* @return
*/
private EduSubject existOneSubject(EduSubjectService eduSubjectService,String name) {
QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("title", name);
queryWrapper.eq("parent_id", "0");
EduSubject eduSubject = eduSubjectService.getOne(queryWrapper);
return eduSubject;
}
/**
* 判断二级分类可不可以添加
* @return
*/
private EduSubject existTwoSubject(EduSubjectService eduSubjectService,String name,String parentId) {
QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("title", name);
queryWrapper.eq("parent_id", parentId);
EduSubject eduSubject = eduSubjectService.getOne(queryWrapper);
return eduSubject;
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("##############所有数据解析完成############");
}
}