EasyExcel写入和读取多个sheet

        最近在工作中，作者频频接触到Excel处理，因此也对EasyExcel进行了一定的研究和学习，也曾困扰过如何处理多个sheet，因此此处分享给大家，希望能有所帮助

目录

1.依赖

2. Excel类

3.处理Excel读取和写入多个sheet

4. 执行结果

1.依赖

        首先需要导入EasyExcel依赖（lombok只是为了写实体类方便）

    <dependencies>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.34</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>

    </dependencies>

2. Excel类

        编写输出的Excel类，这里以Class类和Student类为例

@ToString
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Class {
    @ExcelProperty("班级id")
    private Integer id;

    @ExcelProperty("班级名称")
    private String name;
}

@ToString
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {

    @ExcelProperty("班级id")
    private Integer classId;

    @ExcelProperty("学生id")
    private Integer id;

    @ExcelProperty("学生姓名")
    private String name;
}

3.处理Excel读取和写入多个sheet

        通过注册监听器的方式处理数据的加载以及异常捕获等事件

@Slf4j
public class ExcelProcess {

    public void read(File file) {
        List<Class> classes = new ArrayList<>();
        List<Student> students = new ArrayList<>();

        try {
            EasyExcel.read(file).sheet(0) //指定处理第一页sheet
                    .head(Class.class) // 指定Class类型
                    .registerReadListener(new ExcelReadListener<Class>(classes, file.getName())) //注册监听器，在监听器中进行进一步操作
                    .doRead();
            EasyExcel.read(file).sheet(1)//指定处理第二页sheet
                    .head(Student.class)// 指定Student类型
                    .registerReadListener(new ExcelReadListener<Student>(students, file.getName()))//注册监听器，在监听器中进行进一步操作
                    .doRead();
        } catch (Exception e) {
            e.printStackTrace();
        }

        classes.forEach(System.out::println);
        students.forEach(System.out::println);

    }

    public void write(File file) {
        List<Class> classes = new ArrayList<Class>();
        classes.add(new Class(1, "1班"));
        classes.add(new Class(2, "2班"));

        List<Student> students = new ArrayList<>();
        students.add(new Student(1, 1, "小明"));
        students.add(new Student(1, 2, "小红"));
        students.add(new Student(2, 3, "小黄"));
        students.add(new Student(2, 3, "小蓝"));

        try {
            writeExcel(file, "excel名称", classes, students);
        } catch (Exception e) {
            log.error("写入excel失败", e);
            throw e;
        }
    }

    /**
     * 正常web开发网络响应传入的是流,因此需要再给一个excel名称，这里用不到
     *
     * @param file
     * @param excelName
     * @param lists     需要写入的数据
     */
    private void writeExcel(File file, String excelName, List... lists) {
        ExcelWriter excelWriter = EasyExcel.write(file).build();
        for (int i = 0; i < lists.length; i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet" + (i + 1))
                    .head(lists[i].get(0).getClass())
                    .build();
            excelWriter.write(lists[i], writeSheet);
        }
        excelWriter.finish();
    }

    public static void main(String[] args) {
        File file = new File("test.xlsx");
        ExcelProcess excelProcess = new ExcelProcess();

        excelProcess.write(file);
        excelProcess.read(file);
        
    }
}

@Slf4j
public class ExcelReadListener<T> extends AnalysisEventListener<T> {

    private final List<T> dataList;
    private String fileName;

    public ExcelReadListener(List<T> dataList, String fileName) {
        this.dataList = dataList;
        this.fileName = fileName;
    }

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        //将当前读取到的数据加入集合
        dataList.add(t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("Excel文件:" + fileName + " sheet:" + analysisContext.getCurrentSheet().getSheetName() + " 读取完成");
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException e = (ExcelDataConvertException) exception;
            throw new RuntimeException("文件读取异常,sheet:" + context.getCurrentSheet().getSheetName() +
                    " 第" + (e.getRowIndex() + 1) + "行第" + (char) ('A' + e.getColumnIndex()) + "列出错，请修改后重新上传\n"
                    , e);
        } else {
            log.error("excel导入时出错");
            throw new RuntimeException("系统异常，请重新上传", exception);
        }
    }
}

4. 执行结果

        如果大家有更好的处理方式，欢迎在评论区讨论