EasyExcel的使用

整个Excel称为workbook

每个workbook里面有很多的 sheet

在每个sheet有行 列 单元格

写出操作

引入EasyExcel依赖

<dependencies>
    <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </dependency>
</dependencies>

创建实体类,设置表头和添加的数据字段

@Data
public class User {
    @ExcelProperty(value = "用户编号")
    private int id;
    @ExcelProperty(value = "用户名称")
    private String name;
}

调用方法实现写操作

简例:

public class TestWrite {
    public static void main(String[] args) {
        //设置文件名称和路径
        String fileName = "D:\\gr.xlsx";
        //调用方法
        EasyExcel.write(fileName,User.class)
                .sheet("写操作")
                .doWrite(data());
    }
    //循环设置要添加的数据，最终封装到list集合中
    private static List<User> data() {
        List<User> list = new ArrayList<User>();
        for (int i = 0; i < 10; i++) {
            User data = new User();
            data.setId(i);
            data.setName("lucy"+i);
            list.add(data);
        }
        return list;
    }
}

具体实例:

遍历数据表,建立list集合.调用方法导出到本地 

 //课程分类导出功能
    @Override
    public void exportData(HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = null;
            fileName = URLEncoder.encode("课程分类", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");
            //查询课程分类表所有数据
            List<Subject> subjectList = baseMapper.selectList(null);
            //List<Subject> ---> List<SubjectEeVo>
            List<SubjectEeVo> subjectEeVoList = new ArrayList<>();
            for (Subject subject : subjectList) {
                SubjectEeVo subjectEeVo = new SubjectEeVo();
                //使用工具类复制对象值
                BeanUtils.copyProperties(subject,subjectEeVo);
                subjectEeVoList.add(subjectEeVo);
            }
            //EasyExcel写操作
            EasyExcel.write(response.getOutputStream(), SubjectEeVo.class)
                    .sheet("课程分类")
                    .doWrite(subjectEeVoList);
        } catch (Exception e) {
            throw  new GgktException(20001,"导出失败");
        }
    }

读入操作

引入依赖

创建excel表格对应的实体类,设置对应关系  (index = 0 下标为0 的列数据)

@Data
public class User {
    @ExcelProperty(value = "用户编号",index = 0)
    private int id;
    @ExcelProperty(value = "用户名称",index = 1)
    private String name;
}

简例: 

创建监听器一行一行解析过程:  创建类,继承一个封装了解析方法的类

public class ExcelListener extends AnalysisEventListener<User> {
    //一行一行读取excel内容,把每行内容封装到user对象
    //从excel第二行开始读取
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        System.out.println(user);
    }
    //读取表头
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头"+headMap);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

调用EasyExcel读方法实现操作

public class TestRead {
    public static void main(String[] args) {
        //设置文件名称和路径
        String fileName = "D:\\gr.xlsx";
        //调用方法进行读操作
        EasyExcel.read(fileName,User.class,new ExcelListener()).sheet().doRead();
    }
}

 实例:

创建监听器一行一行解析过程:  创建类,继承一个封装了解析方法的类

@Component
public class SubjectListener extends AnalysisEventListener<SubjectEeVo> {
    //注入Mapper
    @Autowired
    private SubjectMapper subjectMapper;
    //一行一行读取数据,从第二行开始
    @Override
    public void invoke(SubjectEeVo subjectEeVo, AnalysisContext analysisContext) {
        Subject subject = new Subject();
        //SubjectEeVo ---> Subject
        BeanUtils.copyProperties(subjectEeVo,subject);
        //添加到数据库
        subjectMapper.insert(subject);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

调用EasyExcel读方法实现操作

//注入拦截器
@Autowired
private SubjectListener subjectListener;
-----------------------------------------------------------------------------
//课程分类导入
@Override
public void importData(MultipartFile file) {
    try {
        EasyExcel.read(file.getInputStream(),SubjectEeVo.class,subjectListener).sheet().doRead();
    } catch (IOException e) {
        throw new GgktException(20001,"导入失败");
    }
}