MaRui

MMMM

EasyExcel 导入导出

约 3698 字大约 12 分钟

2024-06-25

Easyexcel 注解

读

  • ExcelProperty：指定当前字段对应 excel 中的那一列。可以根据名字或者 Index 去匹配。当然也可以不写，默认第一个字段就是 index = 0，以此类推。千万注意，要么全部不写，要么全部用index，要么全部用名字去匹配。千万别三个混着用，除非你非常了解源代码中三个混着用怎么去排序的

    • @ExcelProperty(value = "编号",index = 0)

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

  • DateTimeFormat：日期转换，用 String 去接收 excel 日期格式的数据会调用这个注解。里面的 value 参照 java.text.SimpleDateFormat

  • NumberFormat：数字转换，用 String 去接收 excel 数字格式的数据会调用这个注解。里面的 value 参照 java.text.DecimalFormat

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

写

  • ExcelProperty：index 指定写到第几列，默认根据成员变量排序。value 指定写入的名称，默认成员变量的名字，多个 value 可以参照快速开始中的复杂头

    • @ExcelProperty(value = "编号",index = 0)

  • ExcelIgnore：默认所有字段都会写入 excel，这个注解会忽略这个字段

  • DateTimeFormat：日期转换，将 Date 写到 excel 会调用这个注解。里面的 value 参照 java.text.SimpleDateFormat

  • NumberFormat：数字转换，用 Number 写 excel 会调用这个注解。里面的 value 参照 java.text.DecimalFormat

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

EasyExcel 依赖

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

EasyExcel 工具类

EasyExcel 单 sheet 操作参数类

EasyExcel 单 sheet 操作参数类
/**
 * @ClassName ExcelParams
 * @Desciption EasyExcel 单 sheet 操作参数类
 * @Author MaRui
 * @Date 2023/11/8 18:03
 * @Version 1.0
 */
@Data
public class ExcelParams<T> {
 
	/**
	 * Excel文件名
	 */
	private String fileName;
 
	/**
	 * Excel工作表名
	 */
	private String sheetName;
	
	/**
	 * 表头Class
	 */
	private Class<T> dataClass;
	
	/**
	 * 数据
	 */
	private List<T> dataList;
	
	/**
	 * 文件输出源
	 */
	private OutputStream fileDest;
	
	/**
	 * 文件读取源
	 */
	private InputStream fileSrc;
	
	/**
	 * Excel读取监听器
	 */
	private AnalysisEventListener<T> listener;
	
	public ExcelParams() {
		
	}
	
	public ExcelParams(String fileName, OutputStream fileDest, String sheetName, Class<T> dataClass, List<T> dataList) {
		this.fileName = fileName;
		this.fileDest = fileDest;
		this.sheetName = sheetName;
		this.dataClass = dataClass;
		this.dataList = dataList;
	}

	public ExcelParams(String fileName, String sheetName, Class<T> dataClass, List<T> dataList) {
		this.fileName = fileName;
		this.sheetName = sheetName;
		this.dataClass = dataClass;
		this.dataList = dataList;
	}
	
	public ExcelParams(Class<T> dataClass,List<T> dataList) {
		this.dataClass = dataClass;
		this.dataList = dataList;
	}
	
	public ExcelParams(InputStream fileSrc, Class<T> dataClass) {
		this.fileSrc = fileSrc;
		this.dataClass = dataClass;
	}
	
	public ExcelParams(String fileName, InputStream fileSrc, String sheetName, Class<T> dataClass, List<T> dataList) {
		this.fileName = fileName;
		this.fileSrc = fileSrc;
		this.sheetName = sheetName;
		this.dataClass = dataClass;
		this.dataList = dataList;
	}
}

EasyExcel 多 sheet 操作参数类

EasyExcel 多 sheet 操作参数类
/**
 * @ClassName ExcelParams
 * @Desciption EasyExcel 多 sheet 操作参数类
 * @Author MaRui
 * @Date 2023/11/8 18:03
 * @Version 1.0
 */
@Data
public class ExcelParamsMulti {
 
	/**
	 * Excel文件名
	 */
	private String fileName;
 
	/**
	 * Excel工作表名
	 */
	private String[] sheetName;
	
	/**
	 * 表头Class
	 */
	private Class[] dataClassArray;
	
	/**
	 * 数据
	 */
	private List[] dataListArray;
	
	/**
	 * 文件输出源
	 */
	private OutputStream fileDest;
	
	/**
	 * 文件读取源
	 */
	private InputStream fileSrc;
	
	/**
	 * Excel读取监听器
	 */
	private AnalysisEventListener[] listenerArray;
	
	public ExcelParamsMulti() {
		
	}
	
	public ExcelParamsMulti(String fileName,OutputStream fileDest, String[] sheetName, Class[] dataClassArray,List[] dataListArray) {
		this.fileName = fileName;
		this.fileDest = fileDest;
		this.sheetName = sheetName;
		this.dataClassArray = dataClassArray;
		this.dataListArray = dataListArray;
	}
 
	public ExcelParamsMulti(InputStream fileSrc, Class[] dataClassArray) {
		this.fileSrc = fileSrc;
		this.dataClassArray = dataClassArray;
	}
	
	public ExcelParamsMulti(String fileName,InputStream fileSrc, String[] sheetName, Class[] dataClassArray,List[] dataListArray) {
		this.fileName = fileName;
		this.fileSrc = fileSrc;
		this.sheetName = sheetName;
		this.dataClassArray = dataClassArray;
		this.dataListArray = dataListArray;
	}
}

EasyExcel 导入导出工具类

EasyExcel 导入导出工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.event.SyncReadListener;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.marui.file.domain.ExcelParams;
import com.marui.file.domain.ExcelParamsMulti;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName ExcelUtils
 * @Desciption EasyExcel 导入导出工具类
 * @Author MaRui
 * @Date 2023/11/8 18:03
 * @Version 1.0
 */
@SuppressWarnings("rawtypes")
public class ExcelUtils {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    public static boolean isExcel(File file) {
        String fileName = file.getName();

        if (file.isFile()) {
            int idx = fileName.lastIndexOf(".");
            if (idx > 0) {
                String ext = fileName.substring(fileName.lastIndexOf("."), fileName.length());
                if (".xls".equals(ext) || ".xlsx".equals(ext)) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * 导出 excel 文件到本地
     * @param params
     */
    public static <T> void writeExcel(ExcelParams<T> params) {
        ExcelWriter excelWriter = null;
        try {
            if (params.getFileDest() == null) {
                logger.error("Excel写出失败，fileDest 为空！");
                return;
            }

            excelWriter = EasyExcel.write(params.getFileDest(), params.getDataClass()).build();

            WriteSheet writeSheet = EasyExcel.writerSheet(params.getSheetName()).build();

            excelWriter.write(params.getDataList(), writeSheet)	;
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * Excel 写出到 response
     * @param params
     */
    public static <T> void writeExcel(ExcelParams<T> params, HttpServletResponse response) {

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 如果fileName为空，则使用当前时间戳为fileName
        String fileName = StringUtils.defaultIfBlank(params.getFileName(), String.valueOf(System.currentTimeMillis()));
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        ExcelWriterSheetBuilder writerSheetBuilder = null;
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(response.getOutputStream(), params.getDataClass()).build();

            writerSheetBuilder = EasyExcel.writerSheet(params.getSheetName());

            writerSheetBuilder.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());

            WriteSheet writeSheet = writerSheetBuilder.build();
            excelWriter.write(params.getDataList(), writeSheet);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * Execl 异步读取
     * @param params
     * @return
     */
    public static <T> void readExcel(ExcelParams<T> params) {
        ExcelReader excelReader = null;
        ReadSheet readSheet1 = null;
        try {
            if (params.getFileSrc() == null) {
                logger.error("Excel读取失败，fileSrc为空！");
                return;
            }

            excelReader = EasyExcel.read(params.getFileSrc()).autoTrim(true).build();

            // 注册监听器
            if (params.getListener() != null) {
                readSheet1 = EasyExcel.readSheet(0).head(params.getDataClass()).registerReadListener(params.getListener()).build();
            } else {
                readSheet1 = EasyExcel.readSheet(0).head(params.getDataClass()).build();
            }

            excelReader.read(readSheet1);

        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
    }

    /**
     * Execl 同步读取
     * @param params
     * @return
     */
    public static <T> List<T> readExcelSync(ExcelParams<T> params) {
        ExcelReader excelReader = null;
        ReadSheet readSheet = null;
        try {
            if (params.getFileSrc() == null) {
                logger.error("Excel读取失败，fileSrc为空！");
                return new ArrayList<T>();
            }

            excelReader = EasyExcel.read(params.getFileSrc()).autoTrim(true).build();

            // 注册监听器
            SyncReadListener syncReadListener = new SyncReadListener();
            if (params.getListener() != null) {
                readSheet = EasyExcel.readSheet(0).head(params.getDataClass())
                        .registerReadListener(syncReadListener).registerReadListener(params.getListener()).build();
            } else {
                readSheet = EasyExcel.readSheet(0).head(params.getDataClass()).registerReadListener(syncReadListener).build();
            }

            excelReader.read(readSheet);

            return (List<T>)syncReadListener.getList();

        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
    }


    /**
     * Excel 多 sheet 导出
     * @param
     */
    public static void writeExcelMulti(ExcelParamsMulti paramsMulti) {

        ExcelWriter excelWriter = null;
        try {
            if (paramsMulti.getFileDest() == null) {
                logger.error("Excel写出失败，fileDest为空！");
                return;
            }

            Class[] dataClass = paramsMulti.getDataClassArray();
            String[] sheetNames = paramsMulti.getSheetName();
            List[] dataListArray = paramsMulti.getDataListArray();

            excelWriter = EasyExcel.write(paramsMulti.getFileDest()).build();

            for (int i = 0; i < sheetNames.length; i ++) {

                WriteSheet writeSheet = EasyExcel.writerSheet(sheetNames[i]).head(dataClass[i]).build();
                excelWriter.write(dataListArray[i], writeSheet);
            }

        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * Execl 多 sheet 异步读取
     * @param
     * @return
     */
    public static void readExcelMulti(ExcelParamsMulti paramsMulti) {

        List<ReadSheet> sheetList = new ArrayList<>();
        ExcelReader excelReader = null;
        try {
            if (paramsMulti.getFileSrc() == null) {
                logger.error("Excel读取失败，fileSrc为空！");
                return;
            }

            Class[] dataClass = paramsMulti.getDataClassArray();
            AnalysisEventListener[] listener = paramsMulti.getListenerArray();

            excelReader = EasyExcel.read(paramsMulti.getFileSrc()).autoTrim(true).build();

            for (int i = 0; i < dataClass.length; i ++) {
                ExcelReaderSheetBuilder readerSheetBuilder = EasyExcel.readSheet(i).head(dataClass[i]);

                // 注册监听器
                if (listener != null && listener[i] != null) {
                    readerSheetBuilder.registerReadListener(listener[i]);
                }

                ReadSheet readSheet = readerSheetBuilder.build();
                sheetList.add(readSheet);
            }

            excelReader.read(sheetList);

        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
    }

    /**
     * Execl 多 sheet 同步读取
     * @param
     * @param
     * @return
     */
    public static List[] readExcelMultiSync(ExcelParamsMulti paramsMulti) {
        List[] dataListArray = null;
        List<ReadSheet> sheetList = new ArrayList<>();
        ExcelReader excelReader = null;
        try {
            if (paramsMulti.getFileSrc() == null) {
                logger.error("Excel读取失败，fileSrc为空！");
                return new ArrayList[0];
            }

            Class[] dataClass = paramsMulti.getDataClassArray();
            AnalysisEventListener[] listener = paramsMulti.getListenerArray();

            excelReader = EasyExcel.read(paramsMulti.getFileSrc()).autoTrim(true).build();

            dataListArray = new ArrayList[dataClass.length];
            SyncReadListener[] listenerArray = new SyncReadListener[dataClass.length];

            for (int i = 0; i < dataClass.length; i ++) {
                ExcelReaderSheetBuilder readerSheetBuilder = EasyExcel.readSheet(i).head(dataClass[i]);

                // 注册监听器
                if (listener != null && listener[i] != null) {
                    readerSheetBuilder.registerReadListener(listener[i]);
                }
                SyncReadListener syncReadListener = new SyncReadListener();
                readerSheetBuilder.registerReadListener(syncReadListener);

                ReadSheet readSheet = readerSheetBuilder.build();
                sheetList.add(readSheet);
                listenerArray[i] = syncReadListener;
            }

            excelReader.read(sheetList);

            for (int i = 0; i < listenerArray.length; i ++) {
                dataListArray[i] = listenerArray[i].getList();
            }

            return dataListArray;

        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
    }

    /**
     * csv 同步读取
     * @param params
     * @return
     */
    public static <T> List<T> readCsvSync(ExcelParams<T> params) {
        ExcelReader excelReader = null;
        ReadSheet readSheet = null;
        try {
            if (params.getFileSrc() == null) {
                logger.error("CSV 读取失败，fileSrc为空！");
                return new ArrayList<T>();
            }

            excelReader = EasyExcel.read(params.getFileSrc()).excelType(ExcelTypeEnum.CSV).autoTrim(true).build();

            // 注册监听器
            SyncReadListener syncReadListener = new SyncReadListener();
            if (params.getListener() != null) {
                readSheet = EasyExcel.readSheet(0).head(params.getDataClass())
                        .registerReadListener(syncReadListener).registerReadListener(params.getListener()).build();
            } else {
                readSheet = EasyExcel.readSheet(0).head(params.getDataClass()).registerReadListener(syncReadListener).build();
            }

            excelReader.read(readSheet);

            return (List<T>)syncReadListener.getList();

        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
                excelReader.finish();
            }
        }
    }

    /**
     * 导出 csv 文件到本地
     * @param params
     */
    public static <T> void writeCsv(ExcelParams<T> params) {
        ExcelWriter excelWriter = null;
        try {
            if (params.getFileDest() == null) {
                logger.error("csv 写出失败，fileDest 为空！");
                return;
            }

            excelWriter = EasyExcel.write(params.getFileDest(), params.getDataClass()).excelType(ExcelTypeEnum.CSV).build();

            WriteSheet writeSheet = EasyExcel.writerSheet(params.getSheetName()).build();

            excelWriter.write(params.getDataList(), writeSheet)	;
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }
}

写入/写出数据准备

excel 数据实体类

/**
 * @ClassName DemoData
 * @Desciption excel数据实体类
 * @Author MaRui
 * @Date 2023/11/7 17:46
 * @Version 1.0
 */
@Data
public class DemoData {

    @ExcelProperty(value = "编号",index = 0)
    private Integer number;

    @ExcelIgnore
    private String alias;

    @ExcelProperty(value = "姓名",index = 1)
    private String name;
}

数据构造方法，导出时需要使用

/**
 * 构造数据
 * @param count
 * @return
 */
public static List<DemoData> getData(int count) {
    List<DemoData> list = new ArrayList<>();
    for (int i = 0; i < count; i++) {
        DemoData data = new DemoData();
        data.setNumber(i);
        data.setName("张" + i);
        list.add(data);
    }
    return list;
}

写出 excel 文件（本地）

/**
 * 写出 excel 文件（本地）
 * @param args
 * @throws FileNotFoundException
 */
public static void main(String[] args) throws FileNotFoundException {
    String fileName = "D:\\write.xlsx";
    File file = new File(fileName);
    OutputStream os = new FileOutputStream(file);
    // 封装 Excel 参数
    ExcelParams<DemoData> params = new ExcelParams<>("文件名", os, "sheet名", DemoData.class, getData(10));
    // 写 Excel 文件
    ExcelUtils.writeExcel(params);
}

写出多 sheet excel 文件（本地）

/**
 * 多 sheet excel 文件导出
 * @param args
 * @throws FileNotFoundException
 */
public static void main(String[] args) throws FileNotFoundException {
    String fileName = "D:\\write1.xlsx";

    File file = new File(fileName);
    OutputStream os = new FileOutputStream(file);

    List[] list = { getData(20), getData(10) };
    String[] sheet = {"sheet名称1", "sheet名称2"};
    Class[] classes = {DemoData.class, DemoData.class};

    // 封装Excel参数
    ExcelParamsMulti params = new ExcelParamsMulti("文件名", os, sheet, classes, list);
    // 写Excel文件
    ExcelUtils.writeExcelMulti(params);
}

同步读取 excel 文件（本地）

无自定义监听器版

/**
 * 同步读取本地 excel 文件（无自定义监听器）
 * @param args
 * @throws FileNotFoundException
 */
public static void main(String[] args) throws FileNotFoundException {
    String fileName = "D:\\write.xlsx";

    File file = new File(fileName);
    InputStream is = new FileInputStream(file);

    // 封装Excel参数
    ExcelParams<DemoData> params = new ExcelParams<>(is, DemoData.class);
    // 读取Excel文件
    List<DemoData> dataList = ExcelUtils.readExcelSync(params);

    for (int i = 0; i < dataList.size(); i ++) {
        DemoData demoData = dataList.get(i);
        System.out.println(demoData.toString());
    }
}

自定义监听器版

监听器可以通过泛型做到通用，自定义监听器有需要的话，可以发挥很大的用处，比如1、数据一边解析一边批量插入数据库，如果此类需求多，可以增加数据库操作的泛型，实现通用

/**
 * 自定义通用监听器
 * @param <T>
 */
public class ModelExcelListener<T> extends AnalysisEventListener<Object> {
    
	private List<T> dataList = new ArrayList<>();
    
	/**
	 * 每读取一行数据调用一次 invoke
	 */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        // 数据存储到 dataList，供后续处理
    	dataList.add((T)data);
        // 根据业需求处理，比如写入数据库
    }

    /**
     * 数据解析完最后调用
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("所有数据解析完成");
        for (int i = 0; i < dataList.size(); i ++) {
            System.out.println(dataList.get(i).toString());
        }
    }
}
/**
 * 同步读取本地 excel 文件（自定义监听器）
 * @param args
 * @throws FileNotFoundException
 */
public static void main(String[] args) throws FileNotFoundException {
    String fileName = "D:\\write.xlsx";

    File file = new File(fileName);
    InputStream is = new FileInputStream(file);

    // 封装Excel参数
    ExcelParams<DemoData> params = new ExcelParams<>(is, DemoData.class);
    params.setListener(new ModelExcelListener());
    // 读取Excel文件
    List<DemoData> dataList = ExcelUtils.readExcelSync(params);

    for (int i = 0; i < dataList.size(); i ++) {
        DemoData demoData = dataList.get(i);
        System.out.println(demoData.toString());
    }
}

异步读取 excel 文件（本地）

异步读取不会去注册一个同步读的监听器了，也就不会在最后有一个整体结果的返回，所以需要实现自定义监听器对数据进行处理

/**
 * 异步读取本地 excel 文件（自定义监听器）
 * @param args
 * @throws FileNotFoundException
 */
public static void main(String[] args) throws FileNotFoundException {
    String fileName = "D:\\write.xlsx";

    File file = new File(fileName);
    InputStream is = new FileInputStream(file);

    // 封装Excel参数
    ExcelParams<DemoData> params = new ExcelParams<>(is, DemoData.class);
    params.setListener(new ModelExcelListener());
    // 读取Excel文件
    ExcelUtils.readExcel(params);
}
/**
 * 自定义通用监听器
 * @param <T>
 */
public class ModelExcelListener<T> extends AnalysisEventListener<Object> {
    
	private List<T> dataList = new ArrayList<>();
    
	/**
	 * 每读取一行数据调用一次 invoke
	 */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        // 数据存储到 dataList，供后续处理
    	dataList.add((T)data);
        // 根据业需求处理，比如写入数据库
    }

    /**
     * 数据解析完最后调用
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("所有数据解析完成");
        for (int i = 0; i < dataList.size(); i ++) {
            System.out.println(dataList.get(i).toString());
        }
    }
}

同步读取本地多 sheet excel 文件（无自定义监听器）

/**
 * 同步读取本地多 sheet excel 文件（无自定义监听器）
 * @param args
 * @throws FileNotFoundException
 */
public static void main(String[] args) throws FileNotFoundException {
    String fileName = "D:\\write.xlsx";

    File file = new File(fileName);
    InputStream is = new FileInputStream(file);

    // 封装Excel参数
    Class[] classes = {DemoData.class, DemoData.class};
    ExcelParamsMulti paramsMulti = new ExcelParamsMulti(is, classes);
    // 读取Excel文件
    List[] dataLists = ExcelUtils.readExcelMultiSync(paramsMulti);

    List<DemoData> oneDemoDatas = (List<DemoData>) dataLists[0];
    List<DemoData> twoDemoDatas = (List<DemoData>) dataLists[1];
    System.out.println(oneDemoDatas.toString());
    System.out.println(twoDemoDatas.toString());
}

异步读取本地多 sheet excel 文件（自定义监听器）

/**
 * 异步读取本地多 sheet excel 文件（自定义监听器）
 * @param args
 * @throws FileNotFoundException
 */
public static void main(String[] args) throws FileNotFoundException {
    String fileName = "D:\\write.xlsx";

    File file = new File(fileName);
    InputStream is = new FileInputStream(file);

    // 封装Excel参数
    Class[] classes = {DemoData.class, DemoData.class};
    AnalysisEventListener[] analysisEventListeners = {new ModelExcelListener(), new ModelExcelListener()};
    ExcelParamsMulti paramsMulti = new ExcelParamsMulti(is, classes);
    paramsMulti.setListenerArray(analysisEventListeners);
    // 读取Excel文件
    ExcelUtils.readExcelMulti(paramsMulti);
}
/**
 * 自定义通用监听器
 * @param <T>
 */
public class ModelExcelListener<T> extends AnalysisEventListener<Object> {
    
	private List<T> dataList = new ArrayList<>();
    
	/**
	 * 每读取一行数据调用一次 invoke
	 */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        // 数据存储到 dataList，供后续处理
    	dataList.add((T)data);
        // 根据业需求处理，比如写入数据库
    }

    /**
     * 数据解析完最后调用
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("所有数据解析完成");
        for (int i = 0; i < dataList.size(); i ++) {
            System.out.println(dataList.get(i).toString());
        }
    }
}

导入 excle 文件（接口）

/**
 * @ClassName FileController
 * @Desciption 文件操作 控制层
 * @Author MaRui
 * @Date 2023/11/8 15:38
 * @Version 1.0
 */
@Controller
@RequestMapping(value = "/file")
public class FileController {

    /**
     * 文件导入
     */
    @PostMapping(value = "/excelImport")
    @ResponseBody
    public String excelImport(@RequestPart MultipartFile file) {
        try {
            InputStream inputStream = file.getInputStream();
            // 封装Excel参数
            ExcelParams<DemoData> params = new ExcelParams<>(inputStream, DemoData.class);
            // 读取Excel文件
            List<DemoData> dataList = ExcelUtils.readExcelSync(params);
            for (DemoData demoData : dataList) {
                System.out.println(demoData.toString());
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return "success";
    }
}

导出 excle 文件（接口）

/**
 * @ClassName FileController
 * @Desciption 文件操作 控制层
 * @Author MaRui
 * @Date 2023/11/8 15:38
 * @Version 1.0
 */
@Controller
@RequestMapping(value = "/file")
public class FileController {

    /**
     * 文件导出
     * @param response
     */
    @PostMapping(value = "/excelExport")
    @ResponseBody
    public void excelExport(HttpServletResponse response) {
        // 封装 Excel 参数
        ExcelParams<DemoData> params = new ExcelParams<>("文件名", "sheet名", DemoData.class, getData(10));
        // 写 Excel 文件
        ExcelUtils.writeExcel(params, response);
    }
}

CSV 类型文件读

/**
 * 同步读取本地 csv 文件（无自定义监听器）
 * @param args
 * @throws FileNotFoundException
 */
public static void main(String[] args) throws FileNotFoundException {
    String fileName = "D:\\write2.csv";

    File file = new File(fileName);
    InputStream is = new FileInputStream(file);

    // 封装Excel参数
    ExcelParams<DemoData> params = new ExcelParams<>(is, DemoData.class);
    // 读取Excel文件
    List<DemoData> dataList = ExcelUtils.readCsvSync(params);

    for (int i = 0; i < dataList.size(); i ++) {
        DemoData demoData = dataList.get(i);
        System.out.println(demoData.toString());
    }
}

CSV 类型文件写

/**
 * 导出 csv 到本地测试
 * @param args
 * @throws FileNotFoundException
 */
public static void main(String[] args) throws FileNotFoundException {
    String fileName = "D:\\write2.csv";
    File file = new File(fileName);
    OutputStream os = new FileOutputStream(file);
    // 封装 Excel 参数
    ExcelParams<DemoData> params = new ExcelParams<>("文件名", os, "sheet名", DemoData.class, getData(1000));
    // 写 Excel 文件
    ExcelUtils.writeCsv(params);
}

文件追加写入

数据多次写入同一个 excel 文件，当然也支持追加写入同一个 excel 文件不同 sheet

真实应用场景：业务数据量很大，如果一次性全加载到内存中写入 excel 文件，服务器内存直接回崩掉，采用分页循环查询数据，追加写入本地 excel 文件中，解决大数据量导出问题

/**
 * 多次写入 excel 文件
 * @param args
 */
public static void main(String[] args) {
    // 方法1 如果写到同一个sheet
    String fileName = "D:\\write.xlsx";
    // 这里 需要指定写用哪个class去写
    ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
    // 这里注意 如果同一个sheet只要创建一次
    WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
    // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来
    List<DemoData> data = null;
    for (int i = 0; i < 5; i++) {
        // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
        data = getData(i + 1);
        excelWriter.write(data, writeSheet);
    }
    // 千万别忘记finish 会帮忙关闭流
    excelWriter.finish();
}