网站备案扫描今日热点新闻2022

        最近在工作中，作者频频接触到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;}@Overridepublic void invoke(T t, AnalysisContext analysisContext) {//将当前读取到的数据加入集合dataList.add(t);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {log.info("Excel文件:" + fileName + " sheet:" + analysisContext.getCurrentSheet().getSheetName() + " 读取完成");}@Overridepublic 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. 执行结果

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