EasyExcel的读写操作

letg

已于 2022-07-22 11:41:08 修改

阅读量4.6k

点赞数

CC 4.0 BY-SA版权

文章标签： java

于 2022-07-03 16:29:23 首次发布

本文链接：https://blog.youkuaiyun.com/qq_46624276/article/details/125585914

本文介绍使用EasyExcel进行Excel文件的读写操作方法。包括配置依赖、定义实体类、编写读写测试代码等关键步骤。同时展示了如何通过监听器逐行处理Excel数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

添加依赖：

	<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
    </dependency>
    
     <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.28</version>
        </dependency>
        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
        </dependency>

一、写操作

1.1 实体类

读操作添加 @ExcelProperty注解，并且value值为excel的表头名称


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

}

1.2 测试写操作

public class TestWrite {
    public static void main(String[] args) {
        String fileName = "C:\\data\\hzxy.xlsx";
        EasyExcel.write(fileName,User.class)
                .sheet("写操作")
                .doWrite(data());
                }
                }

private static List<User> data(){
        ArrayList<User> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setId(i);
            user.setName("张三"+i);
            list.add(user);
        }
        return list;
    }
    }

在这里插入图片描述

二、写操作(写操作需要继承一个监听器，进行一行一行读取)

2.1 监听器

invoke方法会进行excel一行一行读取，只会从第二行读取，第一行为表头

public class ExcelListener extends AnalysisEventListener<User> {

    //一行一行读取excel的内容，把每行内容封装
    //第一行不读，因为第一行为表头
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        System.out.println(user);
    }

    //表头是从该方法读取
    @Override
    public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
        System.out.println("表头:"+headMap);
        super.invokeHead(headMap, context);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

2.2 测试代码

public class TestWrite {
    public static void main(String[] args) {
        String fileName = "C:\\data\\hzxy.xlsx";
//        EasyExcel.write(fileName,User.class)
//                .sheet("写操作")
//                .doWrite(data());
        EasyExcel.read(fileName,User.class,new ExcelListener())
                .sheet("读操作")
                .doRead();
    }

在这里插入图片描述

三、实现

3.1 导出

public void exportData(HttpServletResponse response) {
        try {
            //设置下载信息
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");

            String fileName = URLEncoder.encode("课程分类","UTF-8");
            response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");
            //查询所有课程数据
            List<Subject> subjectList = baseMapper.selectList(null);

            List<SubjectEeVo> subjectEeVos = new ArrayList<>();

            for (Subject subject : subjectList) {
                SubjectEeVo subjectEeVo = new SubjectEeVo();
                BeanUtils.copyProperties(subject,subjectEeVo);
                subjectEeVos.add(subjectEeVo);
            }

            EasyExcel.write(response.getOutputStream(), SubjectEeVo.class)
                    .sheet("课程分类")
                    .doWrite(subjectEeVos);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

3.2 导入

 @Override
    public void importData(MultipartFile file) {
        try {
            EasyExcel.read(file.getInputStream(),SubjectEeVo.class,subjectListener)
                    .sheet()
                    .doRead();
        } catch (IOException e) {
           throw  new GgktException(20001,"导入失败");
        }
    }

@Component
public class SubjectListener extends AnalysisEventListener<SubjectEeVo> {

    @Autowired
    private SubjectMapper subjectMapper;

    @Override
    public void invoke(SubjectEeVo subjectEeVo, AnalysisContext analysisContext) {
        Subject subject = new Subject();
        BeanUtils.copyProperties(subjectEeVo,subject);
        subjectMapper.insert(subject);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}