Springboot3+EasyExcel由浅入深

环境介绍

技术栈	springboot3+easyexcel
软件	版本
IDEA	IntelliJ IDEA 2022.2.1
JDK	17
Spring Boot	3

EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。

他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。

官网https://easyexcel.opensource.alibaba.com/

Sheet工作簿

Row，行，索引从0开始

Column，列，索引从0开始

Cell，单元格

目录

Read

Read1

Read2

异常处理

读sheet

自定义格式转换 日期，数字-Read

自定义转换器-Read

读指定行

Write

Write1

Write2

写入指定列

多级列名

自定义格式转换 日期，数字-Write

自定义转换器-Write

指定列宽行高

批量写入excel方法

自定义模板写入excel

自定义监听器

Web上传下载

---

Read

Read1

List<Corrdinate> newlist =new ArrayList<>();

String fileName="C:\\Users\\Administrator\\Desktop\\demofile\\坐标测试.xlsx";

EasyExcel.read(fileName, Corrdinate.class, new ReadListener<Corrdinate>() {

   @Override

   public void invoke(Corrdinate corrdinate, AnalysisContext analysisContext) {

      try {

         if (11<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<120

               && 2<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<244 ){

            corrdinate.setZ(true);

         }else {

            corrdinate.setZ(false);

         }

         newlist.add(corrdinate);

      }catch (Exception e){

         System.out.println(e);

         corrdinate.setDesc("类型转换失败");

         newlist.add(corrdinate);

      }





   }

   @Override

   public void doAfterAllAnalysed(AnalysisContext analysisContext) {

      System.out.println("读取完成");

   }

}).sheet().doRead();

Read2

String fileName="C:\\Users\\Administrator\\Desktop\\demofile\\坐标测试.xlsx";

EasyExcel.read(fileName, Corrdinate.class,new PageReadListener<Corrdinate>(corrdinates -> {

   for (Corrdinate corrdinate : corrdinates) {

      try {

         if (1<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<110

               && 2<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<240 ){

            corrdinate.setZ(true);

         }else {

            corrdinate.setZ(false);

         }

         newlist.add(corrdinate);

      }catch (Exception e){

         System.out.println(e);

         corrdinate.setDesc("类型转换失败");

         newlist.add(corrdinate);

      }

   }

})).sheet().doRead();

异常处理

重写onException方法

@Test
void readException() {
   List<Corrdinate> oldlist =new ArrayList<>();
   List<Corrdinate> newlist =new ArrayList<>();
   String fileName="C:\\Users\\Administrator\\Desktop\\坐标测试.xlsx";
   EasyExcel.read(fileName, Corrdinate.class, new ReadListener<Corrdinate>() {
      @Override
      public void invoke(Corrdinate corrdinate, AnalysisContext analysisContext) {
            if (1<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<110
                  && 2<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<240 ){
               corrdinate.setZ(true);
            }else {
               corrdinate.setZ(false);
            }
            newlist.add(corrdinate);
      }
      @Override
      public void doAfterAllAnalysed(AnalysisContext analysisContext) {
         System.out.println("读取完成");
      }
      @Override
      public void onException(Exception exception, AnalysisContext context) throws Exception {
         System.out.println(exception);
      }
   }).sheet().doRead();
   for (Corrdinate corrdinate : newlist) {
      System.out.println(corrdinate.toString());
   }
}

try- catch

@Test
void read1() {
   List<Corrdinate> oldlist =new ArrayList<>();
   List<Corrdinate> newlist =new ArrayList<>();
   String fileName="C:\\Users\\Administrator\\Desktop\\测试.xlsx";
   EasyExcel.read(fileName, Corrdinate.class, new ReadListener<Corrdinate>() {
      @Override
      public void invoke(Corrdinate corrdinate, AnalysisContext analysisContext) {
         try {
            if (1<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<200
                  && 20<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<300 ){
               corrdinate.setZ(true);
            }else {
               corrdinate.setZ(false);
            }
            newlist.add(corrdinate);
         }catch (Exception e){
            System.out.println(e);
            corrdinate.setDesc("类型转换失败");
            newlist.add(corrdinate);
         }
      }
      @Override
      public void doAfterAllAnalysed(AnalysisContext analysisContext) {
         System.out.println("读取完成");
      }

   }).sheet().doRead();

   for (Corrdinate corrdinate : newlist) {
      System.out.println(corrdinate.toString());
   }
}

读sheet

读所有工作簿

@Test
void readManyShoot() {
   String fileName="C:\\Users\\Administrator\\Desktop\\demofile\\坐标测试.xlsx";
   EasyExcel.read(fileName, Corrdinate.class,new PageReadListener<>(corrdinates -> {
      corrdinates.forEach(System.out::println);
   })).doReadAll();
}

读任意工作簿

@Test
void readAppointSheet() {
   try (ExcelReader excelReader = EasyExcel.read("C:\\Users\\Administr