使⽤EasyExcel添加多级分类功能
绪论
当⾯对很多数据要处理，并且需要导⼊导出Excel表格时。在使⽤POI时发现⾮常耗内存，这时GitHub上的阿⾥开源项⽬Easy Excel可以解决此类问题，它是基于java的读写Excel，⼗分省内存。本篇博⽂主要是总结其简单使⽤以及关于添加分类功能的实现。
快速⼊门
1. 创建项⽬导⼊依赖（此依赖依靠PIO模块，项⽬还需要导⼊PIO模块）
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
2. 创建测试的实体类DemoData
@Data
public class DemoData {
//设置excel表头名称
@ExcelProperty(value="学⽣编号",index =0)
private Integer sno;
@ExcelProperty(value="学⽣姓名",index =1)
private String sname;
}
其中index对应excel表格中的列，value是列的名称
3. 写操作
public static void main(String[] args){
//实现excel写的操作
//1 设置写⼊⽂件夹地址和excel⽂件名称
String filename ="D:\\write.xlsx";
//2 调⽤easyexcel⾥⾯的⽅法实现写操作
//write⽅法两个参数：第⼀个参数⽂件路径名称，第⼆个参数实体类class
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;
}
4. 读操作
public static void main(String[] args){
//实现excel读操作
String filename ="D:\\write.xlsx";
}
这⾥我们需要创建⼀个读取excel：
//创建读取excel
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 analysisContext){
System.out.println("表头："+headMap);
}
//读取完之后
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext){
}
}
以上是EsayExcel的⼀些基本使⽤，具体可以去看官⽅的⽂档。
应⽤场景（课程多分类的添加）
1. 表结构
DROP TABLE IF EXISTS `edu_subject`;
CREATE TABLE `edu_subject` (
`id` char(19) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程类别ID',
`title` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '类别名称',
`parent_id` char(19) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0' COMMENT '⽗ID',
`sort` int(0) UNSIGNED NOT NULL DEFAULT 0 COMMENT '排序字段',
`gmt_create` datetime(0) NOT NULL COMMENT '创建时间',
`gmt_modified` datetime(0) NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_parent_id`(`parent_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT ='课程科⽬' ROW_FORMAT = Compact;
2. 实体类EduSubject 和分类实体类SubjectData
@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="创建时间")
private Date gmtCreate;
@ApiModelProperty(value="更新时间")
private Date gmtModified;
}
@Data
public class SubjectData {
@ExcelProperty(index =0)
private String oneSubjectName;//⼀级分类
@ExcelProperty(index =1)
private String twoSubjectName;//⼆级分类
}
3. 监听类SubjectExcelListener
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 GuliException(20001,"⽂件数据为空");
}
//⼀⾏⼀⾏读取，每次读取有两个值，第⼀个值⼀级分类，第⼆个值⼆级分类
//判断⼀级分类是否重复
EduSubject eduOneSubject =istOneSubject(OneSubjectName());
if(eduOneSubject ==null){//没有相同的⼀级分类，进⾏添加
eduOneSubject =new EduSubject();
eduOneSubject.setParentId("0");
eduOneSubject.OneSubjectName());
subjectService.save(eduOneSubject);
}
//获取⼀级分类id值
String pid = Id();
//添加⼆级分类
//判断⼆级分类是否重复
EduSubject eduTwoSubject =istTwoSubject(subjectService, TwoSubjectName(), pid);
if(eduTwoSubject ==null){
eduTwoSubject =new EduSubject();
eduTwoSubject.setParentId(pid);
eduTwoSubject.TwoSubjectName());//⼆级分类名称
subjectService.save(eduTwoSubject);
}
}
//判断⼀级分类不能重复添加
private EduSubject existOneSubject(EduSubjectService subjectService, String name){
QueryWrapper<EduSubject> wrapper =new QueryWrapper<>();
wrapper.eq("title",name);
wrapper.eq("title",name);
wrapper.eq("parent_id","0");
EduSubject oneSubject = One(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 = One(wrapper);
return twoSubject;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext){
}
}
4. 实现类EduSubjectServiceImpl
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService { //添加课程分类
@Override
public void saveSubject(MultipartFile file,EduSubjectService subjectService){
try{
//⽂件输⼊流
InputStream in= InputStream();
}catch(IOException e){
e.printStackTrace();
}
}
}
5. Controller层EduSubjectController
@RestController
@RequestMapping("/eduservice/subject")
@CrossOrigin
public class EduSubjectController {
@Autowired
private EduSubjectService subjectService;
//添加课程分类
//获取上传过来⽂件，把⽂件内容读取出来
@PostMapping("addSubject")
public R addSubject(MultipartFile file){
//上传过来excel⽂件
subjectService.saveSubject(file,subjectService);
return R.ok();
}
}
6. 测试
（1）excel表中信息：
（2）运⾏项⽬，⽤Swagger测试
（3）查看数据库中是否添加成功
getsavefilename
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论