文章/答案/技术大牛

发布

社区首页 >专栏 >Springboot3+EasyExcel由浅入深

Springboot3+EasyExcel由浅入深

原创

QGS

发布于 2024-01-12 12:36:35

64900

代码可运行

文章被收录于专栏：QGS星球QGS星球

运行总次数：0

代码可运行

环境介绍

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

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

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

EasyExcel

Sheet工作簿

Row，行，索引从0开始

Column，列，索引从0开始

Cell，单元格

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 (113<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<115
               && 22<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<24 ){
            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 (113<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<115
               && 22<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<24 ){
            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 (113<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<115
                  && 22<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<24 ){
               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 (113<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<115
                  && 22<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<24 ){
               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";
    }
  },