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

异常处理

读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\\Administrator\\Desktop\\demofile\\坐标测试.xlsx").build();){
      //创建工作簿对象sheet1
      ReadSheet sheet1 = EasyExcel.readSheet(0).head(Corrdinate.class)
            .registerReadListener(new PageReadListener<>(corrdinates -> {
               corrdinates.forEach(System.out::println);
            }
            )).build();
      //创建工作簿对象sheet2
      ReadSheet sheet2 = EasyExcel.readSheet("Sheet2").head(Corrdinate.class)
            .registerReadListener(new PageReadListener<>(corrdinates -> {
               corrdinates.forEach(System.out::println);
            }
            )).build();
      excelReader.read(sheet1,sheet2);
   }
}

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

@Data
public class man {
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("日期")
    @DateTimeFormat("yyyy年mm月dd日")
    private Date date;
    @ExcelProperty("成功率")
    private BigDecimal successrate;
}

自定义转换器-Read

public class StringConverterString implements Converter<String> {
    //支持java类型
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }
    //支持Excel单元格类型
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    //读的数据符合类型
    @Override
    public String convertToJavaData(ReadConverterContext<?> context) throws Exception {
        //转换
        return "姓名："+context.getReadCellData().getStringValue();
    }
    //写的数据符合类型
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception {
        return new WriteCellData<>(context.getValue());
    }
}

@Data
public class man {
    @ExcelProperty(converter = StringConverterString.class)
    private String name;
    @ExcelProperty("日期")
    @DateTimeFormat("yyyy年mm月dd日")
    private Date date;
    @ExcelProperty("成功率")
    private BigDecimal successrate;
}

读指定行

EasyExcel默认从第一行开始读取，第0行默认为列头

@Test
void selectRead(){
   String fileName="C:\\Users\\Administrator\\Desktop\\demofile\\坐标测试.xlsx";
   EasyExcel.read(fileName, man.class,new PageReadListener<>(mans -> {
      mans.forEach(System.out::println);
   })).sheet("Sheet3")
         .headRowNumber(2)//第几行，0-n
         .doRead();
}

Write

实体类

@Data

public class Corrdinate {

    private long id;

    @ExcelProperty("x")

    private String x;

    private String y;

    @ExcelProperty("是否匹配")

    private boolean z;

    @ExcelProperty("描述")

    private String desc;

    @ExcelIgnore//忽略字段

    private String de;

}

Write1

//生成数据方法

private List<Corrdinate> getData(int count) {



   List<Corrdinate> list = ListUtils.newArrayList();



   for (int i = 0; i < count; i++) {



      Corrdinate corrdinate = new Corrdinate();

      //生成10-100随机数

      corrdinate.setId(RandomUtil.randomInt(10, 100));

      corrdinate.setX(String.valueOf(RandomUtil.randomInt(10, 100)));

      corrdinate.setY(String.valueOf(RandomUtil.randomInt(10, 100)));

      list.add(corrdinate);

   }

   return list;

}



@Test

void write1(){

EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Corrdinate.class)

      .sheet()//指定工作簿

Write2

//生成数据方法

private List<Corrdinate> getData(int count) {



   List<Corrdinate> list = ListUtils.newArrayList();



   for (int i = 0; i < count; i++) {



      Corrdinate corrdinate = new Corrdinate();



      corrdinate.setId(Long.parseLong(String.valueOf(RandomUtil.randomInt(10, 100))));

      //生成10-100随机数

      corrdinate.setX(String.valueOf(RandomUtil.randomInt(10, 100)));

      corrdinate.setY(String.valueOf(RandomUtil.randomInt(10, 100)));

      //随机生成MD5

      corrdinate.setDesc(DigestUtils.md5Hex("hello"));

      list.add(corrdinate);

   }

   return list;

}

@Test

void write2(){

   try (ExcelWriter excelWriter = EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Corrdinate.class).build();){

      WriteSheet sheet2 = EasyExcel.writerSheet("Sheet2").build();
      WriteSheet sheet1 =EasyExcel.writerSheet("Sheet1").build();

      excelWriter.write(getData(10),sheet1);
      excelWriter.write(getData(10),sheet2);
   }

写入指定列

实体类

@Data

public class Corrdinate {

    private long id;

    @ExcelProperty(value = "x",index = 1)//指定列名和顺序

    private String x;

    private String y;

    @ExcelProperty("是否匹配")

    private boolean z;

    @ExcelProperty("描述")

    private String desc;

    @ExcelIgnore//忽略字段

    private String de;

}

生成数据方法

//生成数据方法

private List<Corrdinate> getData(int count) {



   List<Corrdinate> list = ListUtils.newArrayList();



   for (int i = 0; i < count; i++) {



      Corrdinate corrdinate = new Corrdinate();



      corrdinate.setId(Long.parseLong(String.valueOf(RandomUtil.randomInt(10, 100))));

      //生成10-100随机数

      corrdinate.setX(String.valueOf(RandomUtil.randomInt(10, 100)));

      corrdinate.setY(String.valueOf(RandomUtil.randomInt(10, 100)));

      //随机生成MD5

      corrdinate.setDesc(DigestUtils.md5Hex("hello"));

      corrdinate.setDe(DigestUtils.md5Hex("de"));

      list.add(corrdinate);

   }

   return list;

}

写测试

@Test

void writeColumn(){

   Set<String> set = new TreeSet<>();

   set.add("de");



   EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Corrdinate.class)

         .excludeColumnFieldNames(set)

         .sheet("Sheet2")//指定工作簿

         .doWrite(getData(10));//写入10个数据

}

多级列名

实体类

@Data

public class Corrdinate {

    private long id;

    @ExcelProperty(value = "x",index = 1)//指定列名和顺序

    private String x;

    private String y;

    @ExcelProperty({"一级列名","是否匹配"})

    private boolean z;

    @ExcelProperty({"一级列名","描述"})

    private String desc;

    //@ExcelIgnore//忽略字段

    //设置一级列名，二级列名

    @ExcelProperty({"一级列名","二级列名"})

    private String de;

}

写测试

@Test

void writeDemo(){

   EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Corrdinate.class)

         .sheet("Sheet3")//指定工作簿

         .doWrite(getData(10));//写入10个数据

}

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

实体类

@Data

public class man {

    @ExcelProperty(converter = StringConverterString.class)

    private String name;

    @ExcelProperty("日期")

    @DateTimeFormat("yyyy年mm月dd日")

    private Date date;

   @NumberFormat("#.##%")

    private BigDecimal successrate;

}

生成数据并测试写入

//生成数据方法

private List<Man> getData2(int count) {
   List<Man> list = ListUtils.newArrayList();

   for (int i = 0; i < count; i++) {
      Man m = new Man();
      m.setName("张三"+i);
      m.setSuccessrate(BigDecimal.valueOf(RandomUtil.randomDouble(0.0, 1)));
      m.setDate(new Date());
      list.add(m);
   }
   return list;
}


@Test

void writeDemo(){

   EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Man.class)

         .sheet("Sheet3")//指定工作簿

         .doWrite(getData2(10));//写入10个数据

}

}

自定义转换器-Write

自定义转换器

public class StringConverterString implements Converter<String> {

    //支持java类型

    @Override

    public Class<?> supportJavaTypeKey() {

        return String.class;

    }

    //支持Excel单元格类型

    @Override

    public CellDataTypeEnum supportExcelTypeKey() {

        return CellDataTypeEnum.STRING;

    }



    //读的数据符合类型

    @Override

    public String convertToJavaData(ReadConverterContext<?> context) throws Exception {

        //转换

        return "姓名："+context.getReadCellData().getStringValue();



    }

    //写的数据符合类型

    @Override

    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception {

        return new WriteCellData<>("写入数据"+context.getValue());

    }

}

生成数据并测试写入

private List<Man> getData2(int count) {



   List<Man> list = ListUtils.newArrayList();



   for (int i = 0; i < count; i++) {



      Man m = new Man();

      m.setName("张三"+i);

      m.setSuccessrate(BigDecimal.valueOf(RandomUtil.randomDouble(0.0, 1)));

      m.setDate(new Date());



      list.add(m);

   }

   return list;

}



@Test

void writeDemo(){

   EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Man.class)

         .sheet("Sheet3")//指定工作簿

         .doWrite(getData2(10));//写入10个数据

}

指定列宽行高

@Data

@HeadRowHeight(30)// 指定列头行高度

@ContentRowHeight(15)// 指定内容行高度

@ColumnWidth(12)//指定列宽

public class Man {

    @ExcelProperty(converter = StringConverterString.class)

    private String name;

    @ExcelProperty("日期")

    @DateTimeFormat("yyyy年mm月dd日")

    private Date date;

   @NumberFormat("#.##%")

    private BigDecimal successrate;

}

批量写入excel方法

编写实体类

@TableName(value ="product")

@Data

@NoArgsConstructor

@AllArgsConstructor

public class Product implements Serializable {

/**

* 序号

@TableId(type = IdType.AUTO)

@ExcelProperty("序号")

private Integer number;

/**

* 创建时间

@ExcelProperty("创建时间")

private Date createtime;

/**

* 产品名称

@ExcelProperty("产品名称")

private String productname;

/**

* 产品编号

@ExcelProperty("产品编号")

private String productnumber;

/**

* 产品型号

@ExcelProperty("产品型号")

private String manufacturer;

/**

* 产品位置

@ExcelProperty("产品位置")

private String producepath;

/**

* 图片位置

@ExcelProperty("图片位置")

private String imagepath;

/**

* 使用单位

@ExcelProperty("使用单位")

private String unit;

/**

* 金额

@ExcelProperty("金额")

private Integer money;

/**

* 入库时间

@ExcelProperty("入库时间")

private Date intime;

/**

* 出库时间

@ExcelProperty("出库时间")

private Date puttime;

/**

* 操作人

@ExcelProperty("操作人")

private String operator;

/**

* 创建人

@ExcelProperty("创建人")

private String createduser;

/**

* 备注

@ExcelProperty("备注")

private String notes;

/**

* 产品数量

@ExcelProperty("产品数量")

private Integer producedigit;

/**

* 产品单位

@ExcelProperty("产品单位")

private String productunit;

@TableField(exist = false)

private static final long serialVersionUID = 1L;

}

//重复多次写入(写到单个或者多个Sheet)

@Test

public void manyWrite() {

   // 方法1: 如果写到同一个sheet

   String fileName = "C:\\Users\\13631\\Desktop\\simpleWriteTest1702391756908.xlsx";

   // 这里 需要指定写用哪个class去写

   try (ExcelWriter excelWriter = EasyExcel.write(fileName, Product.class).build()) {

      // 这里注意 如果同一个sheet只要创建一次

      WriteSheet writeSheet = EasyExcel.writerSheet("测试").build();

      long star = System.currentTimeMillis();

      // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来

      for (int i = 0; i < 5; i++) {

         // 分页去数据库查询数据 这里可以去数据库查询每一页的数据

         List<Product> data = getData(1000);

         excelWriter.write(data, writeSheet);

      }

      long end = System.currentTimeMillis();

      System.out.println("耗时:" + (end - star)/1000 + "秒");

   }

自定义模板写入excel

填充单行

填充集合

//根据模板填充数据
@Test
public void fillWrite() {
    // 方案2 分多次 填充 会使用文件缓存（省内存）
    String fileName = "C:\\Users\\13631\\Desktop\\模板写数据.xlsx";
    String templateFileName = "C:\\Users\\13631\\Desktop\\模板.xlsx";
    try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        excelWriter.fill(getData2(100), writeSheet);
    }
}

填充效果

自定义监听器

1、实体类(如上述实体类)

2、自定义监听器

Invoke和doAfterAllAnalysed是必选的

public class MyListener implements ReadListener<Product> {

    private TestMapper testMapper;
    private ArrayList<Product> list = new ArrayList<>();
    int sum=0;

    public MyListener(TestMapper testMapper) {
        this.testMapper = testMapper;
    }

    //每读一行，则调用该方法
    @Override
    public void invoke(Product product, AnalysisContext analysisContext) {
        sum++;
        list.add(product);
    }
    //每读完整个excel，则调用该方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("读取了"+sum+"行数据");
    }
}

@Test
void contextLoads() {
    String fileName = "C:\\Users\\13631\\Desktop\\simpleWriteTest1702391756908.xlsx";
    // 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行
    // 具体需要返回多少行可以在`PageReadListener`的构造函数设置
    ExcelReader reader = EasyExcel.read(fileName, Product.class, new MyListener(new TestMapper())).build();
    ReadSheet sheet = EasyExcel.readSheet().build();
    reader.read(sheet);
}

Web上传下载

web中的读(上传)

后端

//上传

@PostMapping("/upload")

@ResponseBody

public String upload(MultipartFile file) throws IOException {

long start = System.currentTimeMillis();

EasyExcel.read(file.getInputStream(), Product.class, new MyListener(productService)).sheet().doRead();

long end = System.currentTimeMillis();

System.out.println("耗时:"+(end-start)/1000+"秒");

return "success";

}

前端(vue2+Element)

<el-upload

class="upload-demo"

action="http://192.168.1.8:8007/excel/upload"

:on-preview="handlePreview"

:on-remove="handleRemove"

:before-remove="beforeRemove"

multiple

:limit="3"

:on-exceed="handleExceed"

:file-list="fileList">

<el-button size="small" type="primary">点击上传</el-button>

</el-upload>

效果

web中的写(下载)

后端

@GetMapping("download")

public void download(HttpServletResponse response) throws IOException {

    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    response.setCharacterEncoding("utf-8");

    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系

    String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");

    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

    EasyExcel.write(response.getOutputStream(), Product.class).sheet("模板").doWrite(productService.list());

}

前端

<button @click="download">导出Excel</button>


methods:{

    download(){

      document.location.href="http://192.168.1.8:8007/excel/download";

    }

  },

效果