SpringBoot 使用easyexcel实现excel读取

「这是我参与11月更文挑战的第4天，活动详情查看：2021最后一次更文挑战」。 通过工具类的封装，实现指定大小的excel数据异步或同步读取

pom依赖

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

自定义AnalysisEventListener

可以不自定义，使用默认的:useDefaultListener(true)

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;

/**
 * @Author youngxw
 * @Date 2021/11/12 下午9:23
 */
public class EasyExcelConsumerListener<T> extends AnalysisEventListener<T> {
    private int pageSize;
    private List<T> list;
    private Consumer<List<T>> consumer;

    public EasyExcelConsumerListener(int pageSize, Consumer<List<T>> consumer) {
        this.pageSize = pageSize;
        if (null!=consumer){
            this.consumer = consumer;
        }
        list = new ArrayList<>(pageSize);
    }

    public List<T> getList(){
        return this.list;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        list.add(data);
        if (null!=consumer){
            if (list.size() >= pageSize) {
                consumer.accept(list);
                list.clear();
            }
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (null!=consumer){
            consumer.accept(list);
        }
    }
}

工具类封装

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;

import java.io.*;
import java.util.List;
import java.util.function.Consumer;
/**
 * @Author youngxw
 * @Date 2021/11/12 下午9:20
 */
public class ExcelUtil extends EasyExcel {
    private ExcelUtil() {}

    public static <T> ExcelReaderBuilder read(String pathName, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
        return read(pathName, head, new EasyExcelConsumerListener<>(pageSize, consumer));
    }

    public static <T> ExcelReaderBuilder read(File file, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
        return read(file, head, new EasyExcelConsumerListener<>(pageSize, consumer));
    }

    public static <T> ExcelReaderBuilder read(File file, Class<T> head) {
        return read(file, head, new EasyExcelConsumerListener<>(1000, null));
    }

    public static <T> ExcelReaderBuilder read(InputStream inputStream, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
        return read(inputStream, head, new EasyExcelConsumerListener<>(pageSize, consumer));
    }

    public static <T> ExcelReaderBuilder read(InputStream inputStream, Class<T> head) {
        return read(inputStream, head, new EasyExcelConsumerListener<>(1000, null));
    }

    /**
     *
     * @param clazz Excel实体映射类
     * @param data 导出数据
     * @return
     */
    public static void writeExcel(String path, Class clazz, List<? extends BaseRowModel> data, List<List<String>> titles) throws FileNotFoundException {
        // 生成EXCEL并指定输出路径
        OutputStream out = new FileOutputStream(path);
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

        // 设置SHEET
        Sheet sheet = new Sheet(1, 0);
        sheet.setSheetName("data");

        // 设置标题
        Table table = new Table(1);

        table.setHead(titles);

        writer.write(data, sheet, table);
        writer.finish();
    }
}

功能测试

• ExcelTwo为实体类，可替换为自己的实体类
• sheet可指定读取同一文件的不同工作表
• excelType可以指定文件类型：xls/xlsx

List<ExcelTwo> dataList = ExcelUtil.read(new File("resources/321.xls"), ExcelTwo.class).excelType(ExcelTypeEnum.XLSX).sheet(0).doReadSync();
//写入到txt文本中
dataList.forEach(s -> {
    try {
        write(s.toString());
    } catch (IOException e) {
        e.printStackTrace();
    }
});

写入文本方法

采用BufferedWriter以追加的方式进行一行一行写入

public static void write(String s) throws IOException {
    //将写入转化为流的形式
    BufferedWriter bw = new BufferedWriter(new FileWriter("resources/y5.txt",true));
    //一次写一行
    bw.write(s);
    bw.newLine();  //换行用
    //关闭流
    bw.close();
    //System.out.println("写入成功");
}