EasyExcel入门_easyexcel依赖

EasyExcel特点

生成Excel比较有名的框架Apache poi、jxl等,但是他们都存在一个严重的问题就是非常的耗内存,如果系统并发量不大可能还行,但是一旦并发上来后一定会OOM或者JVM频繁的full gc

EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称,EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析

EasyExcel采用一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)

EasyExcel对poi进行封装, 采用SAX方式解析

Dom方式(一次性将Excel中的内容全部放到内存中来,再进行读取),缺点就是数据量大的话会很影响效率

SAX方式(一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener) ,能够大大提高效率,不会产生什么内存溢出问题)

引入依赖

<dependencies>       <!--easyexcel依赖-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </dependency>
</dependencies>
 
<!--同时也需要引入下面这两个依赖,poi 3.17版本和easyexcel 2.1.1版本比较契合-->
<poi.version>3.17</poi.version>
<!--xls-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
</dependency>
 
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
</dependency>

EasyExcel进行写操作

创建一个实体

@Data
//@Accessors(chain = true)
//要去掉这个连缀书写的注解,因为Accessors和EasyExcel冲突,一行一行读取的时候会返回null
public class DemoData {
    //设置excel表头名称
    @ExcelProperty("学生编号")
    private Integer sno;
    @ExcelProperty("学生姓名")
    private String sname;
}

实现写操作方法

public class TestEasyExcel {
    public static void main(String[] args) {
        //实现Excel写的操作
        String filename = "D:\\write.xlsx";
        //调用EasyExcel里面的方法实现写操作
        //write两个参数,第一个参数文件路径名称,第二个参数实体类, .sheet给sheet页设置名称 .doWrite传需要写入的List
        EasyExcel.write(filename,DemoData.class).sheet("学生列表").doWrite(getDate());
        //第二种写法
//        ExcelWriter excelWriter = EasyExcel.write(filename, DemoData.class).build();
//        WriteSheet writeSheet = EasyExcel.writerSheet("方法二").build();
//        excelWriter.write(getDate(), writeSheet);
//        //需要手动关闭,第一种不需要
//        excelWriter.finish();
    }
 
    //创建方法返回List集合
    private static List<DemoData> getDate(){
        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;
    }
}

前端页面导出Excel

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
 
@RestController
@RequestMapping("/excel")
public class ExcelController {
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {
        response.setContentType("application/octet-stream");
//        servletResponse.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;fileName=" +
                new String("模板名称.xlsx".getBytes("gbk"), "iso8859-1"));
 
        //第一种导出方式
//        EasyExcel.write(response.getOutputStream(), DemoData.class)
//                //设置自适应列宽
//                .head(DemoData.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//                .sheet(0, "学生列表1").doWrite(getData());
 
        //第二种导出方式
        //导出多个sheet页
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), DemoData.class)
                //设置自适应列宽
                .head(DemoData.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .build();
        WriteSheet writeSheet = EasyExcel.writerSheet(0, "学生列表1").build();
        excelWriter.write(getData(), writeSheet);
        WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "学生列表2")
                .head(DemoData.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .build();
        excelWriter.write(getData(), writeSheet2);
 
        excelWriter.finish();
    }
 
    public List<DemoData> getData() {
        List<DemoData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            DemoData demoData = new DemoData();
            demoData.setSno(i);
            demoData.setSname("name" + i);
            list.add(demoData);
        }
        return list;
    }
}

EasyExcel进行读操作

实体

@Data
//@Accessors(chain = true)   
//要去掉这个连缀书写的注解,因为Accessors和EasyExcel冲突,一行一行读取的时候会返回null
public class DemoData {
    //value对应excel表头名称,index对应表中的列,0代表第一列
    @ExcelProperty(value = "学生编号",index = 0)
    private Integer sno;
    @ExcelProperty(value = "学生姓名",index = 1)
    private String sname;
}

创建读取操作的监听器

public class ExcelListener extends AnalysisEventListener<DemoData> { //放入接收excel的实体
    //一行一行读取excel内容
    @Override
    public void invoke(DemoData data, AnalysisContext analysisContext) {
        System.out.println("***" + data);
    }
    //读取excel表头内容
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头:" + headMap);
    }
    //读取完成之后
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
 
    }
}

实现excel读操作

public class TestEasyExcel {
    public static void main(String[] args) {
        //实现Excel读操作
        String filename = "D:\\write.xlsx";
        //文件路径,实体,监听器
		EasyExcel.read(filename,DemoData.class,new ExcelListener()).sheet().doRead();
    }
}

示例:

@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();
    }
}

public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
 
    public EduSubjectService eduSubjectService;
    public SubjectExcelListener() {}
    public SubjectExcelListener(EduSubjectService eduSubjectService){
        this.eduSubjectService = eduSubjectService;
    }
 
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if (subjectData ==null)
            throw new GuliException(20001,"读取文件为空");
        EduSubject oneEduSubject = getOneEduSubject(eduSubjectService, subjectData.getOneSubjectName());
        if (oneEduSubject == null){
            oneEduSubject = new EduSubject();
            oneEduSubject.setTitle(subjectData.getOneSubjectName());
            oneEduSubject.setParentId("0");
            eduSubjectService.save(oneEduSubject);
        }
        String pid = oneEduSubject.getId();
        EduSubject twoEduSubject = getTwoEduSubject(eduSubjectService, subjectData.getTwoSubjectName(), pid);
        if (twoEduSubject == null){
            twoEduSubject = new EduSubject();
            twoEduSubject.setTitle(subjectData.getTwoSubjectName());
            twoEduSubject.setParentId(pid);
            eduSubjectService.save(twoEduSubject);
        }
    }
 
    private EduSubject getOneEduSubject(EduSubjectService eduSubjectService,String name){
        QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("parent_id","0");
        queryWrapper.eq("title",name);
        EduSubject one = eduSubjectService.getOne(queryWrapper);
        return one;
    }
 
    private EduSubject getTwoEduSubject(EduSubjectService eduSubjectService,String name,String pid){
        QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("parent_id",pid);
        queryWrapper.eq("title",name);
        EduSubject one = eduSubjectService.getOne(queryWrapper);
        return one;
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
 
    }
}