This is the fourth day of my participation in the November Gwen Challenge. See details: The last Gwen Challenge 2021. Through the tool class encapsulation, realize the specified size of excel data asynchronous or synchronous reading

Pom depends on

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.14.</version>
</dependency>
Copy the code

Custom 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
 * @Date2021/11/12 9:23 PM */
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(a){
        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); }}}Copy the code

Toolclass encapsulation

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
 * @Date2021/11/12 9:20 PM */
public class ExcelUtil extends EasyExcel {
    private ExcelUtil(a) {}

    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));
    }

    / * * * *@paramClazz Excel entity mapping class *@paramData Exports data *@return* /
    public static void writeExcel(String path, Class clazz, List<? extends BaseRowModel> data, List<List<String>> titles) throws FileNotFoundException {
        // Generate EXCEL and specify the output path
        OutputStream out = new FileOutputStream(path);
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

        / / set the SHEET
        Sheet sheet = new Sheet(1.0);
        sheet.setSheetName("data");

        // Set the title
        Table table = new Table(1); table.setHead(titles); writer.write(data, sheet, table); writer.finish(); }}Copy the code

A functional test

  • ExcelTwo is an entity class that can be replaced with its own entity class
  • Sheet can specify different worksheets to read from the same file
  • ExcelType Specifies the file type: XLS/XLSX
List<ExcelTwo> dataList = ExcelUtil.read(new File("resources/321.xls"), ExcelTwo.class).excelType(ExcelTypeEnum.XLSX).sheet(0).doReadSync();
// Write to TXT text
dataList.forEach(s -> {
    try {
        write(s.toString());
    } catch(IOException e) { e.printStackTrace(); }});Copy the code

Write text method

BufferedWriter is used to append lines to write

public static void write(String s) throws IOException {
    // Convert writes to streams
    BufferedWriter bw = new BufferedWriter(new FileWriter("resources/y5.txt".true));
    // Write one line at a time
    bw.write(s);
    bw.newLine();  / / with a new line
    / / close the flow
    bw.close();
    // system.out. println(" Write successfully "); // system.out. println(" write successfully ");
}
Copy the code