Springboot3+EasyExcel由浅入深-阿里云开发者社区

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";
    }
  },

效果

编辑

Springboot3+EasyExcel由浅入深

Read

Read1

Read2

异常处理

读sheet

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

自定义转换器-Read

读指定行

Write

Write1

Write2

写入指定列

多级列名

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

自定义转换器-Write

指定列宽行高

批量写入excel方法

自定义模板写入excel

自定义监听器

Web上传下载

热门文章

最新文章

相关课程

相关电子书

相关实验场景

Springboot3+EasyExcel由浅入深

Read

Read1

Read2

异常处理

读sheet

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

自定义转换器-Read

读指定行

Write

Write1

Write2

写入指定列

多级列名

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

自定义转换器-Write

指定列宽行高

批量写入excel方法

自定义模板写入excel

自定义监听器

Web上传下载

热门文章

最新文章

相关课程

相关电子书

相关实验场景

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

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