技术积累

Appearance

EasyExcel入门 ​

名称链接备注
官方文档[写Excelhttps://easyexcel.opensource.alibaba.com/docs/current/quickstart/write
github地址：https://github.com/alibaba/easyexcel
gitee地址：https://gitee.com/easyexcel/easyexcel

1. 简介 ​

1.1 入门 ​

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。

easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便

1.2 环境准备 ​

环境准备

xml
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>4.0.1</version>
    </dependency>

数据准备

easyexcel/easyexcel-test/src/test/resources at master · alibaba/easyexcel · GitHub

从阿里巴巴的网上把excel 下载下来。

1.3 常用注解 ​

ExcelProperty ​

用于匹配excel和实体类的匹配,参数如下：

名称默认值描述
value空用于匹配excel中的头，必须全匹配,如果有多行头，会匹配最后一行头
orderInteger.MAX_VALUE优先级高于value，会根据order的顺序来匹配实体和excel中数据的顺序
index-1优先级高于value和order，会根据index直接指定到excel中具体的哪一列
converter自动选择指定当前字段用什么转换器，默认会自动选择。读的情况下只要实现com.alibaba.excel.converters.Converter#convertToJavaData(com.alibaba.excel.converters.ReadConverterContext<?>) 方法即可

ExcelIgnore ​

默认所有字段都会和excel去匹配，加了这个注解会忽略该字段

ExcelIgnoreUnannotated ​

默认不加ExcelProperty 的注解的都会参与读写，加了不会参与读写

DateTimeFormat ​

日期转换，用String去接收excel日期格式的数据会调用这个注解,参数如下：

名称默认值描述
value空参照java.text.SimpleDateFormat书写即可
use1904windowing自动选择excel中时间是存储1900年起的一个双精度浮点数，但是有时候默认开始日期是1904，所以设置这个值改成默认1904年开始

NumberFormat ​

数字转换，用String去接收excel数字格式的数据会调用这个注解。

名称默认值描述
value空参照java.text.DecimalFormat书写即可
roundingModeRoundingMode.HALF_UP格式化的时候设置舍入模式

2. 快速入门 ​

2.1 读Excel ​

1)编写模型类并加入注解 2)监听器介绍 3)调用官方API完成写功能

首先创建实体类

java
package com.lkcoffee.demo;

import java.util.Date;

import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

/**
 * 基础数据类.这里的排序和excel里面的排序一致
 *
 * @author Jiaju Zhuang
 **/
@Data
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

然后我们查看demo.xlsx的内容

QQ_1721116657979

写代码来读取代码

java
package com.lkcoffee.demo;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Optional;

/**
 * @Desciption:
 * @Author: feixiang.li
 * @date: 2024-07-11 14:58
 **/
@Slf4j
public class Test3 {


    public void read(){
        System.out.println(this.getClass().getClassLoader().getResource("").getPath());
        String fileName = this.getClass().getClassLoader().getResource("").getPath() + "demo" + File.separator + "demo.xlsx";
        log.info(fileName);
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new ReadListener<DemoData>() {
            @Override
            public void invoke(DemoData data, AnalysisContext context) {
                log.info("读取到一条数据: {}",data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("所有数据解析完成！");
            }
        }).sheet().doRead();
    }

    public static void main(String[] args) {
        Test3 test3=new Test3();
        test3.read();
    }
}

下面可以看到获取的结果

QQ_1721110260478

下图是完成的项目结构

QQ_1721110322826

下面是我们完成的pom目录

java
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.ucarinc.framework</groupId>
  <artifactId>demo1</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>demo1</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>2.0.13</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>2.0.13</version>
    </dependency>

    <dependency>
      <groupId>org.junit.jupiter</groupId>
      <artifactId>junit-jupiter</artifactId>
      <version>5.10.2</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.32</version>
    </dependency>
    <dependency>
      <groupId>org.mockito</groupId>
      <artifactId>mockito-core</artifactId>
      <version>5.11.0</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.mockito</groupId>
      <artifactId>mockito-inline</artifactId>
      <version>5.2.0</version>
      <scope>test</scope>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
      <version>3.14.0</version>
    </dependency>
    <dependency>
      <groupId>org.mockito</groupId>
      <artifactId>mockito-junit-jupiter</artifactId>
      <version>5.11.0</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>4.0.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/com.alibaba.fastjson2/fastjson2 -->
    <dependency>
      <groupId>com.alibaba.fastjson2</groupId>
      <artifactId>fastjson2</artifactId>
      <version>2.0.51</version>
    </dependency>
      <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>4.13.2</version>
          <scope>compile</scope>
      </dependency>


  </dependencies>


</project>

2.2 写Excel ​

1)编写模型类并加入注解 2)编写获取测试数据的方法 3)调用官方API完成写功能

创建实体类

java
package com.lkcoffee.demo.write;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

/**
 * @Desciption:
 * @Author: feixiang.li
 * @date: 2024-07-16 16:00
 **/
@Data
public class Employee {

    @ExcelProperty("员工编号")
    private Long id;
    @ExcelProperty("员工姓名")
    private String name;

    @ExcelProperty("员工入职日期")
    private Date date;

    @ExcelProperty("员工薪水")
    private Double salary;
    
}

开始写入

java
package com.lkcoffee.demo.write;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.lkcoffee.demo.read.DemoData;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

/**
 * @Desciption:
 * @Author: feixiang.li
 * @date: 2024-07-11 14:58
 **/
@Slf4j
public class Test4 {

    public List<Employee> getData(int size){
        List<Employee> data=new ArrayList<>();
        for (int i = 0; i < size; i++) {
            Employee employee=new Employee();
            employee.setDate(new java.util.Date());
            employee.setId((long) i);
            employee.setSalary(1000.0);
            employee.setName("张三"+i);
            data.add(employee);
        }
        return data;
    }
    public void write(int size){
        // 模拟10万条数据，然后写入excel
        List<Employee> data=getData(size);
        EasyExcel.write(new File("D:\\code\\java-test\\demo3\\test.xlsx"),Employee.class).sheet("员工信息").doWrite(data);
    }

    public static void main(String[] args) {
        log.info("开始写入");
        // 开始计时
        long startTime = System.currentTimeMillis();
        // 写10万条数据大概需要多少时间
        Test4 test4=new Test4();
        test4.write(100000);
        // 结束计时
        long endTime = System.currentTimeMillis();
        // 打印运行时间，单位为秒
        log.info("写入完成，耗时：{}s", (endTime - startTime)/1000);
    }
}

QQ_1721119890017

可以看到2秒钟。就可以写入10万条数据，

QQ_1721119931072

编写工具类

java
package com.lkcoffee.demo.utils;

import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.collections4.CollectionUtils;

public class TestFileUtil {

    public static InputStream getResourcesFileInputStream(String fileName) {
        return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName);
    }

    public static String getPath() {
        return TestFileUtil.class.getResource("/").getPath();
    }

    public static TestPathBuild pathBuild() {
        return new TestPathBuild();
    }

    public static File createNewFile(String pathName) {
        File file = new File(getPath() + pathName);
        if (file.exists()) {
            file.delete();
        } else {
            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }
        }
        return file;
    }

    public static File readFile(String pathName) {
        return new File(getPath() + pathName);
    }

    public static File readUserHomeFile(String pathName) {
        return new File(System.getProperty("user.home") + File.separator + pathName);
    }

    /**
     * build to test file path
     **/
    public static class TestPathBuild {
        private TestPathBuild() {
            subPath = new ArrayList<>();
        }

        private final List<String> subPath;

        public TestPathBuild sub(String dirOrFile) {
            subPath.add(dirOrFile);
            return this;
        }

        public String getPath() {
            if (CollectionUtils.isEmpty(subPath)) {
                return TestFileUtil.class.getResource("/").getPath();
            }
            if (subPath.size() == 1) {
                return TestFileUtil.class.getResource("/").getPath() + subPath.get(0);
            }
            StringBuilder path = new StringBuilder(TestFileUtil.class.getResource("/").getPath());
            path.append(subPath.get(0));
            for (int i = 1; i < subPath.size(); i++) {
                path.append(File.separator).append(subPath.get(i));
            }
            return path.toString();
        }

    }

}

然后该成其他写法

java
package com.lkcoffee.demo.write;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.lkcoffee.demo.utils.TestFileUtil;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

/**
 * @Desciption:
 * @Author: feixiang.li
 * @date: 2024-07-11 14:58
 **/
@Slf4j
public class Test5 {

    public List<Employee> getData(int size){
        List<Employee> data=new ArrayList<>();
        for (int i = 0; i < size; i++) {
            Employee employee=new Employee();
            employee.setDate(new java.util.Date());
            employee.setId((long) i);
            employee.setSalary(1000.0);
            employee.setName("张三"+i);
            data.add(employee);
        }
        return data;
    }
    public void write(int size){
        // 模拟10万条数据，然后写入excel
        List<Employee> data=getData(size);

        // 写法2
        String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        log.info("文件名称:{}",fileName);
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, Employee.class).sheet("模板").doWrite(data);

        // 写法3
         fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        log.info("文件名称:{}",fileName);
        // 这里 需要指定写用哪个class去写
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(data, writeSheet);
        }

    }

    public static void main(String[] args) {
        log.info("开始写入");
        // 开始计时
        long startTime = System.currentTimeMillis();
        // 写10万条数据大概需要多少时间
        Test5 test4=new Test5();
        test4.write(100000);
        // 结束计时
        long endTime = System.currentTimeMillis();
        // 打印运行时间，单位为秒
        log.info("写入完成，耗时：{}s", (endTime - startTime)/1000);
    }
}

QQ_1721121209033

2.3 导出自适应行高 ​

注解@ColumnWidth ​

java
@Getter
@Setter
@EqualsAndHashCode
@ContentRowHeight(10)
@HeadRowHeight(20)
@ColumnWidth(25)
public class WidthAndHeightData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    /**
     * 宽度为50
     */
    @ColumnWidth(50)
    @ExcelProperty("数字标题")
    private Double doubleData;
}

官方提供策略 ​

不想自己编写的话。使用官方提供的LongestMatchColumnWidthStyleStrategy()

java
try (ExcelWriter excelWriter = EasyExcel.write(fileName)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(getData(100), writeSheet);
        }

自定义策略 ​

这里我给大家详细说一下。首先是我这边定义的如果columnWidth 长度大于10，就赋值为20，否则就赋值为10，你们可以自行调整。在计算长度的那个方法中，有个switch 方法，其中对字符串，布尔，还有数值型进行区分。然后去用不同的公式计算。这里会遇到一个坑，如果你的导出的文档有很多数字，比如金额什么的。就可以把case NUMBER删掉，因为可能列宽不够导致导出来的值是”######“。

java
package com.lkcoffee.demo.write;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {

    private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth > 0) {
                if (columnWidth > 10) {
                    columnWidth = 20;
                }else{
                    columnWidth = 10;
                }
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    Sheet sheet = writeSheetHolder.getSheet();
                    sheet.setColumnWidth(cell.getColumnIndex(), 256 * columnWidth + 184);
                }
            }
        }
    }

    /**
     * 计算长度
     *
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData<?> cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        // 换行符（数据需要提前解析好）
                        int index = cellData.getStringValue().indexOf("\n");
                        return index != -1 ?
                                cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }

}

这里我是10个字符就插入一个换行符，你们可以根据业务场景自行调整。下面我解释一下我为什么重写afterRowDispose方法，因为在我们导出的时候除了使用一个集合进行填充，还会对单个单个单元格进行填充，也就是可能会同时使用excelWriter.fill(var1, var2, var3); 和excelWriter.fill(var1, var2); 这两个方法。因为在填充单个单元格时是没有相对行索引的概念的，所以我进行了判空处理。这个问题我排查了一下午真的裂开。特此指出。

java
package com.lkcoffee.demo.write;


import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;

import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;


import java.util.Iterator;

public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {

    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 300;

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {

    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }
        // 默认为 1 行高度
        int maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellType() == CellType.STRING) {
                String value = cell.getStringCellValue();
                for (int i = 0; i < value.length(); i += 10) {
                    if (i + 10 < value.length()) {
                        value = value.substring(0, i) + "\n" + value.substring(i, i + 10) + value.substring(i + 10);
                    } else {
                        value = value.substring(0, i) + "\n" + value.substring(i);
                    }
                }
                if (value.contains("\n")) {
                    int length = value.split("\n").length;
                    maxHeight = Math.max(maxHeight, length);
                }
            }
        }
        row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
    }

    @Override
    public void afterRowDispose(RowWriteHandlerContext context) {
        if (context.getHead() != null) {
            if(ObjectUtils.isEmpty(context.getRelativeRowIndex())){
                return;
            }
            if (Boolean.TRUE.equals(context.getHead())) {
                this.setHeadColumnHeight(context.getRow(), context.getRelativeRowIndex());
            } else {
                this.setContentColumnHeight(context.getRow(), context.getRelativeRowIndex());
            }

        }
    }
}

使用

java
package com.lkcoffee.demo.write;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.lkcoffee.demo.domain.Employee;
import com.lkcoffee.demo.domain.IndexEmployee;
import com.lkcoffee.demo.utils.TestFileUtil;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

/**
 * @Desciption:
 * @Author: feixiang.li
 * @date: 2024-07-11 14:58
 **/
@Slf4j
public class Test7 {

    public List<IndexEmployee> getData(int size){
        List<IndexEmployee> data=new ArrayList<>();
        for (int i = 0; i < size; i++) {
            IndexEmployee employee=new IndexEmployee();
            employee.setDate(new java.util.Date());
            employee.setId((long) i);
            employee.setSalary(1000.0);
            employee.setName("张三3223432sdffffffffffffffffffffffffffffffffffffffffff" +
                    "ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff"+i);
            data.add(employee);
        }
        return data;
    }


    @Test
    public void write(){
        String fileName = TestFileUtil.getPath() + "myWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭


        try (ExcelWriter excelWriter = EasyExcel.write(fileName)
                .registerWriteHandler(new CustomCellWriteHeightConfig())
                .registerWriteHandler(new CustomCellWriteWidthConfig())
                .build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(getData(100), writeSheet);
        }

    }

}

另一种方案

java
public class ColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

  private static final int MAX_COLUMN_WIDTH = 255;

  private Map<String, List<TableHeaderVO>> sheetHeaderMap;

  public ColumnWidthStyleStrategy(Map<String, List<TableHeaderVO>> sheetHeaderMap) {
    this.sheetHeaderMap = sheetHeaderMap;
  }

  @Override
  protected void setColumnWidth(
      WriteSheetHolder writeSheetHolder,
      List<WriteCellData<?>> cellDataList,
      Cell cell,
      Head head,
      Integer relativeRowIndex,
      Boolean isHead) {
    final int rowIndex = cell.getRowIndex();
// 只在第一行设置列宽即可   
 if (rowIndex != 0) {
      return;
    }
    final List<TableHeaderVO> list = sheetHeaderMap.get(writeSheetHolder.getSheetName());

    Integer columnWidth = dataLength(list, cell);
    if (columnWidth < 0) {
      return;
    }
    if (columnWidth > MAX_COLUMN_WIDTH) {
      columnWidth = MAX_COLUMN_WIDTH;
    }
    Integer maxColumnWidth = 1;
    if (columnWidth > maxColumnWidth) {
      writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
    }
  }

// 根据 cell 中写入的内容，查找该列配置的表头列宽
  private Integer dataLength(List<TableHeaderVO> list, Cell cell) {

    final String cellValue = cell.getStringCellValue();

    final Optional<TableHeaderVO> headerOptional =
        list.stream().filter(k -> cellValue.equals(k.getLabel())).findFirst();
    return headerOptional.map(tableHeaderVO -> tableHeaderVO.getWidth().intValue()).orElse(1);
  }

https://gitcode.csdn.net/65e93a3a1a836825ed78e15c.html

2.4 制定写入的列 ​

java
package com.lkcoffee.demo.domain;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

/**
 * @Desciption:
 * @Author: feixiang.li
 * @date: 2024-07-16 16:00
 **/
@Data
public class IndexEmployee {

    @ExcelProperty(value = "员工编号",index = 0)
    private Long id;
    @ExcelProperty(value = "员工姓名",index = 1)
    private String name;

    @ExcelProperty(value = "员工入职日期",index = 3)
    private Date date;

    @ExcelProperty(value = "员工薪水",index =4)
    private Double salary;

}

编写测试代码

java
package com.lkcoffee.demo.write;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.lkcoffee.demo.domain.Employee;
import com.lkcoffee.demo.domain.IndexEmployee;
import com.lkcoffee.demo.utils.TestFileUtil;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

/**
 * @Desciption:
 * @Author: feixiang.li
 * @date: 2024-07-11 14:58
 **/
@Slf4j
public class Test6 {

    public List<IndexEmployee> getData(int size){
        List<IndexEmployee> data=new ArrayList<>();
        for (int i = 0; i < size; i++) {
            IndexEmployee employee=new IndexEmployee();
            employee.setDate(new java.util.Date());
            employee.setId((long) i);
            employee.setSalary(1000.0);
            employee.setName("张三"+i);
            data.add(employee);
        }
        return data;
    }
    public List<IndexEmployee> data(){
        List<IndexEmployee> data=new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            IndexEmployee employee=new IndexEmployee();
            employee.setDate(new java.util.Date());
            employee.setId((long) i);
            employee.setSalary(1000.0);
            employee.setName("张三"+i);
            data.add(employee);
        }
        return data;
    }

    @Test
    public void write(){
        String fileName = TestFileUtil.getPath() + "indexWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, IndexEmployee.class).sheet("模板").doWrite(data());
    }


}

看一下结果

QQ_1721122530092

QQ_1721122513679

2.5 批量读取 ​

1)编写模型类并加入注解 2)自定义监听器 3)调用官方API完成写功能