目录
官网地址
官方文档地址https://www.yuque.com/easyexcel/doc/easyexcel
EasyExcel环境准备
导入依赖
<!--easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
映射写入实体类
@Data
public class DemoData {
//设置excel表头名称
@ExcelProperty("学生编号")
private Integer sno;
@ExcelProperty("学生姓名")
private String sname;
}
EasyExcel实现excel写操作
public static void main(String[] args) {
//实现excel写操作
// 1. 设置写入文件夹地址和excel文件名称
String filename = "E:\\write.xlsx";
// 2. 调用easyexcel里面的方法实现写操作
// 第一个参数文件路径名称 第二个参数实体类class doWrite(要写入的数据)
EasyExcel.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;
}
EasyExcel实现excel读操作
映射读出实体类
@Data
public class DemoData {
//设置excel表头名称
@ExcelProperty(value = "学生编号",index = 0)
private Integer sno;
@ExcelProperty(value = "学生姓名" ,index = 1)
private String sname;
}
创建读取操作的监听器
public class ExcelListener extends AnalysisEventListener<DemoData> {
// 一行一行读取excel内容
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
System.out.println("****" + demoData + "****");
}
// 读取表头内容
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头" + headMap);
}
// 读取完成之后
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
实现excel读操作
@Test
public void read(){
// 需要读的excel
String filename="E:\\write.xlsx";
// 第一个参数文件路径名称 第二个参数实体类class 第三个参数监听器对象
EasyExcel.read(filename,DemoData.class,new ExcelListener()).sheet().doRead();
}
使用EasyExcel进行有二级分类的课程添加实践
映射excel对象
@Data
public class SubjectData {
@ExcelProperty(index = 0)
private String onSubjectName;
@ExcelProperty(index = 1)
private String twoSubjectName;
}
excel.xlsx
一级分类 二级分类
前端开发 react
后端开发 java
数据库开发 orancle
controller层
// 添加课程分类
// 获取上传过来的文件,把文件内容读取出来
@PostMapping("addSubject")
public R eduSubject(MultipartFile file) {
// 上传过来的excel
eduSubjectService.saveSubject(file,eduSubjectService);
return R.ok();
}
service层
// 添加课程分类
@Override
public void saveSubject(MultipartFile file, EduSubjectService eduSubjectService) {
try {
//文件输入溜
InputStream in = file.getInputStream();
EasyExcel.read(in, SubjectData.class, new SubjectExcelListener(eduSubjectService)).sheet().doRead();
} catch (Exception e) {
e.printStackTrace();
}
}
easyexcel监听器
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
// 因为SubjectExcelListener不能交给spring进行管理,需要自己new,不能注入其他对象
// 因此不能实现数据库操作,因此要手动注入
public EduSubjectService subjectService;
public SubjectExcelListener() {
}
public SubjectExcelListener(EduSubjectService subjectService) {
this.subjectService = subjectService;
}
// 读取excel内容,一行一行进行读取
@Override
public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
if (subjectData == null) {
throw new MyException(20001, "文件数据为空");
}
// 一行一行读取,每次读取有二个值,第一个值是一级分类,第二个值是二级分类
// 判断一级分类是否重复
EduSubject existOneSubject = this.existOneSubject(subjectService, subjectData.getOnSubjectName());
if (existOneSubject == null) {
existOneSubject = new EduSubject();
existOneSubject.setParentId("0");
existOneSubject.setTitle(subjectData.getOnSubjectName());
subjectService.save(existOneSubject);
}
// 获取一级分类id值
String pid = existOneSubject.getId();
// 判断二级分类是否重复
EduSubject existTwoSubject = this.existTwoSubject(subjectService, subjectData.getTwoSubjectName(), pid);
if (existTwoSubject == null) {
existTwoSubject = new EduSubject();
existTwoSubject.setParentId(pid);
existTwoSubject.setTitle(subjectData.getTwoSubjectName());
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;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
使用EasyExcel进行数据导出实战
映射excel对象
@Data
public class SubjectData {
@ExcelProperty(index = 0)
private String onSubjectName;
@ExcelProperty(index = 1)
private String twoSubjectName;
}
excel.xlsx
一级分类 二级分类
前端开发 react
后端开发 java
数据库开发 orancle
controller层
// 导出数据
@PostMapping("exportData")
public void eduSubject(HttpServletResponse response) {
// 上传过来的excel
eduSubjectService.exportData(response);
}
service层
// 导出数据
@Override
public void exportDict(HttpServletResponse response) {
// 设置下载信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = null;
try {
fileName = URLEncoder.encode("导出数据", "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
// 以下载方式打开
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 查询数据库
List<Subject> subjectList = baseMapper.selectList(null);
// Subject->SubjectData
List<SubjectData> subjectDataList = subjectList.stream().map(subject -> {
SubjectData subjectData = new SubjectData();
BeanUtils.copyProperties(subject, subjectData);
return subjectData;
}).collect(Collectors.toList());
// 调用write
try {
EasyExcel.write(response.getOutputStream(), SubjectData.class).sheet("导出数据").doWrite(subjectDataList);
} catch (IOException e) {
e.printStackTrace();
}
}