easyExcel实现基本的文件导入导出——springboot

待我称王封你为后i 2023-10-14 14:33 70阅读 0赞

本文主要是介绍springboot + easyExcel实现基本的文件导入导出，主要是一个demo尽可能简单明了的来介绍相关功能即可。有什么问题可以在留言哦！并在文章末尾附上demo源码下载！

--------------------

## 一、前言 ##

EasyExcel是一个基于POI封装的Java库，用于简化Excel文件的读写操作。POI是Apache软件基金会的一个开源项目，提供了Java操作Microsoft Office格式文件的API。EasyExcel在POI的基础上进行了封装，提供了更简单易用的接口，使得开发者可以更方便地进行Excel文件的读写操作。因此，可以说EasyExcel是基于POI的一个扩展库。

但是单纯做数据的导出导入的话用easyExcel确实很方便；如果对数据导出有特殊要求的话，建议还是使用poi来按照需求进行设置，比如数据导出导入时数据的其他需求校验，导出含有批注信息、导出含有图片信息、导出含有图表信息等等

参考文章链接：[poi实现excel文件导入导出(基本数据导出、含批注导出、含图片图表导出)——springboot\_寒夜憨憨的博客-CSDN博客][poi_excel_springboot_-CSDN]

![c015056ee5564b078a747abca03144f8.png][]

实体类对应的excel表数据![0eb791c2abdc4396bf2f5348ca0cb2c3.png][]

## 二、导入依赖和创建实体类 ##

### 1、导入依赖 ###

此处注意依赖版本，此处的EasyExcel的版本应该时最新的版本

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.26</version>
            </dependency>
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.1.5</version>
            </dependency>

### 2、创建实体类 ###

注意注释描述

package com.jdh.easyexcel.pojo;
    
    import com.alibaba.excel.annotation.ExcelIgnore;
    import com.alibaba.excel.annotation.ExcelProperty;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import java.util.Date;
    
    /**
     * @ProjectName: springboot_java_study
     * @Author: jdh
     * @CreateTime: 2022-03-10
     * @Description: 针对easyExcel需要导入导出对应的实体类
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class User {
    
        //通过index来定位实在excel中第几列，从0开始
        //如果不指定index，则按照定义的顺序从0开始往下依次解析，但是类型必须和excel中的类型对应得上，否则解析会报错
        //value只是在导出为excel的时候才会用到，即表头数据
        @ExcelProperty(value = "编号", index = 0)
        private Integer id;
    
        @ExcelProperty(value = "姓名", index = 1)
        private String name;
    
        @ExcelProperty(value = "年龄", index = 2)
        private Integer age;
    
        @ExcelProperty(value = "性别", index = 3)
        private String sex;
    
        @ExcelProperty(value = "电话号码", index = 4)
        private String mobile;
    
        @ExcelProperty(value = "薪资", index = 5)
        private Double sal;
    
        @ExcelProperty(value = "创建时间", index = 6)
        private Date createTime;
    
        @ExcelProperty(value = "备注", index = 7)
        private String remake;
    
        @ExcelIgnore //隐私数据，在读取的时候忽略这个字段
    //    @ExcelProperty(value = "爱好", index = 8)
        private String hobby;
    
    }

## 三、excel导出导入的功能基本实现 ##

采用原始的导入导出的实现

package com.jdh.easyexcel;
    
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.EasyExcelFactory;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.read.listener.ReadListener;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.jdh.easyexcel.pojo.User;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.junit.jupiter.api.Test;
    import org.springframework.boot.test.context.SpringBootTest;
    
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.Date;
    import java.util.List;
    
    import static org.junit.jupiter.api.Assertions.*;
    
    @SpringBootTest
    class EasyExcelOperationTest {
    
        private static final String path = System.getProperty("user.dir") + "\\Doc\\";
    
        //这个数据是模拟从数据库查询的数据
        public static final List<User> data = new ArrayList<>(Arrays.asList(
                new User(1, "LiuYi", 15, "男", "12345678901", 8500.05, new Date(), "sheet表1", "刘一的数据"),
                new User(2, "ChenEr", 16, "女", "12345678902", 8600.05, new Date(), "sheet表1", "陈二的数据"),
                new User(3, "ZhangSan", 17, "男", "12345678903", 8700.05, new Date(), "sheet表1", "张三的数据"),
                new User(4, "LiSi", 18, "女", "12345678904", 8800.05, new Date(), "sheet表1", "李四的数据"),
                new User(5, "WangWu", 19, "男", "12345678905", 8900.05, new Date(), "sheet表1", "王五的数据"),
                new User(6, "ZhaoLiu", 20, "女", "12345678906", 9500.05, new Date(), "sheet表1", "赵六的数据"),
                new User(7, "SunQi", 21, "男", "12345678907", 9600.05, new Date(), "sheet表1", "孙七的数据"),
                new User(8, "ZhouBa", 22, "女", "12345678908", 9700.05, new Date(), "sheet表1", "周八的数据"),
                new User(9, "WuJiu", 23, "女", "12345678909", 9800.05, new Date(), "sheet表1", "吴九的数据"),
                new User(10, "ZhengShi", 24, "男", "12345678910", 9900.05, new Date(), "sheet表1", "郑十的数据")
        ));
    
        /**
         * 获取excel的基本数据信息，如sheet，行，列等
         * @throws Exception
         */
        @Test
        void excelReadBasicInfo() throws Exception {
            FileInputStream inputStream = new FileInputStream(path + "user_basic.xlsx");
    
            //下面就用到poi的api了
            Workbook workbook = WorkbookFactory.create(inputStream);
    //        Workbook workbook = new XSSFWorkbook(inputStream);
            int sheetCount = workbook.getNumberOfSheets();
            System.out.println("Sheet数量：" + sheetCount);
    
            for (int i = 0; i < sheetCount; i++) {
                Sheet sheet = workbook.getSheetAt(i);
    
                // 获取行数
                int rowCount = sheet.getLastRowNum() + 1;
    
                //获取表头列数
                Row row0 = sheet.getRow(0);
                int firstCellNum = row0.getLastCellNum();
    
                // 获取表中最大列数
                int columnMaxCount = 0;
                for (int j = 0; j < rowCount; j++) {
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        int currentColumnCount = row.getLastCellNum();
                        if (currentColumnCount > columnMaxCount) {
                            columnMaxCount = currentColumnCount;
                        }
                    }
                }
    
                //注意：若读取行列数，如果单元格设置了单元格格式或者写入数据又给删掉，那么这种情况会被认为时有效单元格，在统计行或列的时候会被统计出来，但是肉眼看则时空行或列
                //在解析数据的时候也会去解析该单元格数据，会解析出一个空数据，所以在解析数据的时候就需要增加单元格数据类型校验
                System.out.println("Sheet名称:" + sheet.getSheetName() + ";行数:" + rowCount + ";表头列数:" + firstCellNum + ";最大列数:" + columnMaxCount);
            }
    
            workbook.close();
            inputStream.close();
    
        }
    
        /**
         * 同步读取excel数据
         * @throws FileNotFoundException
         */
        @Test
        void excelReadSync() throws FileNotFoundException {
            FileInputStream inputStream = new FileInputStream(path + "user_basic.xlsx");
            List<User> userList = EasyExcelFactory.read(inputStream).sheet().head(User.class).doReadSync();
            userList.forEach(System.out::println);
        }
    
        /**
         * 异步读取excel数据，使用ReadListener进行监听每一行数据读取情况
         * @throws FileNotFoundException
         */
        @Test
        void excelReadASync() throws FileNotFoundException {
            FileInputStream inputStream = new FileInputStream(path + "user_basic.xlsx");
            List<User> userList = new ArrayList<>();
            EasyExcelFactory.read(inputStream, User.class, new ReadListener<User>() {
                @Override
                public void invoke(User data, AnalysisContext context) {
                    userList.add(data);
                }
    
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    System.out.println("当前sheet读取完毕");
                }
            }).doReadAll();
            userList.forEach(System.out::println);
        }
    
        /**
         * 将已有的数据导出为excel文件
         * @throws FileNotFoundException
         */
        @Test
        void excelWrite() throws IOException {
            String fileName = "user_导出.xlsx";
            String sheetName = "Sheet";
    
            //获取一个文件输出流
            FileOutputStream fileOutputStream = new FileOutputStream(path + fileName);
    
            //先构建一个excel导出的构建器
            ExcelWriter excelWriter = EasyExcel.write(fileOutputStream, User.class).build();
    
            for (int i = 1; i <= 5; i++) {
                WriteSheet writeSheet = new WriteSheet();
                writeSheet.setSheetNo(i);//对应excel表格的第几张表
                writeSheet.setSheetName(sheetName + "" + i);//对应的表名
                excelWriter.write(data, writeSheet);//开始写入
            }
    
            //写入完毕
            excelWriter.finish();
            fileOutputStream.close();
        }
    
        /**
         * 将一个实体类按照一定需求导出为excel模板，只含表头数据
         * 如果需要创建个性化模板，还是使用poi的相关属性来设置
         * 当然在easyExcel中也可以直接使用poi的相关api
         * @throws FileNotFoundException
         */
        @Test
        void excelTempWrite() throws FileNotFoundException {
            String fileName = "user模板导出.xlsx";
            String sheetName = "Sheet";
    
            //获取一个文件输出流
            FileOutputStream fileOutputStream = new FileOutputStream(path + fileName);
    
            //需要导出的对象数据，若为空集合，则根据对应的对象设置一个表头模板
            ArrayList<User> data = new ArrayList<>();
    
            //先构建一个excel导出的构建器
            ExcelWriter excelWriter = EasyExcel.write(fileOutputStream,User.class).build();
    
            //创建一个sheet实例
            WriteSheet writeSheet = new WriteSheet();
            writeSheet.setSheetNo(1);//对应excel表格的第几张表
            writeSheet.setSheetName(sheetName);//对应的表名
    
            //主要在excel导出构造器中没有指明需要导出的模板实体类的时候，可以通过table来完成一些指定的表头列属性值
    //        WriteTable writeTable = new WriteTable();
    //        List<List<String>> head = new ArrayList<>();
    //        List<String> col1 = new ArrayList<>();
    //        col1.add("列名1");
    //        head.add(col1);
    //        List<String> col2 = new ArrayList<>();
    //        col2.add("列名2");
    //        head.add(col2);
    //        List<String> col3 = new ArrayList<>();
    //        col3.add("列名3");
    //        col3.add("8485");//如果同时指定两个列名的话，则为垂直合并
    //        head.add(col3);
    //        writeTable.setHead(head);
    //        excelWriter.write(data, writeSheet, writeTable);
    
            excelWriter.write(data, writeSheet);
    
            excelWriter.finish();//写入完毕
        }
    }

## 四、对读取excel数据方法封装 ##

### 1、读取excel的方法封装 ###

读取excel的封装，其中包括同步含实体类模型读取、同步不含实体类(Object)模型读取、异步含实体类模型读取、异步不含实体类模型(Object)读取；其中异步的情况需要创建一个监听器来完成封装功能需求；注意看注释

package com.jdh.easyexcel.utils;
    
    import com.alibaba.excel.EasyExcelFactory;
    import com.alibaba.excel.read.listener.ReadListener;
    
    import java.io.InputStream;
    import java.util.List;
    
    /**
     * @ClassName: EasyExcelReadOperation
     * @Author: jdh
     * @CreateTime: 2022-08-04
     * @Description: 关于easyExcel读取excel文档的同步读取和异步读取封装的相关方法
     */
    public class EasyExcelReadOperation {
    
        /**
         * 采用默认读取数据 默认只读取第一个sheet且从第二行读取
         * 即默认第一行为表头但不读取表头数据
         *
         * @param inputStream 文件输入流
         * @return 以列数为key，单元格数据为value 格式的map数据集合
         */
        public static List<Object> excelReadSync(InputStream inputStream) {
            List<Object> objects = EasyExcelFactory.read(inputStream).sheet().doReadSync();
            return objects;
        }
    
        /**
         * 指定转换为类的字段数据格式读取数据，且采用默认数据位置读取
         * 即第一个sheet和第二行开始，默认第一行为表头且不读取
         *
         * @param inputStream 文件输入流
         * @param clazz       需要转换的类对象
         * @return 以clazz类对应格式的数据集合
         */
        public static <T> List<T> excelReadSync(InputStream inputStream, Class<T> clazz) {
            List<T> objects = EasyExcelFactory.read(inputStream).sheet().head(clazz).doReadSync();
            return objects;
        }
    
        /**
         * 指定转换为类的字段数据格式读取数据，且采用自定义第几个sheet和默认第二行数据位置读取
         * 默认第一行为表头且不读取
         *
         * @param inputStream 文件输入流
         * @param sheetNo     第几个sheet
         * @return 以clazz类对应格式的数据集合
         */
        public static List<Object> excelReadSync(InputStream inputStream, Integer sheetNo) {
            List<Object> objects = EasyExcelFactory.read(inputStream).sheet(sheetNo).doReadSync();
            return objects;
        }
    
        /**
         * 指定转换为类的字段数据格式读取数据，且采用自定义第几个sheet和默认第二行数据位置读取
         * 默认第一行为表头且不读取
         *
         * @param inputStream 文件输入流
         * @param sheetNo     第几个sheet
         * @param clazz       需要转换的类对象
         * @return 以clazz类对应格式的数据集合
         */
        public static <T> List<T> excelReadSync(InputStream inputStream, Integer sheetNo, Class<T> clazz) {
            List<T> objects = EasyExcelFactory.read(inputStream).sheet(sheetNo).head(clazz).doReadSync();
            return objects;
        }
    
        /**
         * 采用自定义第几个sheet和第几行读取数据
         *
         * @param inputStream 文件输入流
         * @param sheetNo     第几个sheet 从0开始为第一个sheet
         * @param rowNo       从几行读取 从0开始为表头(excel第一行)
         * @return 以列数为key，单元格数据为value 格式的map数据集合
         */
        public static List<Object> excelReadSync(InputStream inputStream, Integer sheetNo, Integer rowNo) {
            List<Object> objects = EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(rowNo).doReadSync();
            return objects;
        }
    
        /**
         * 指定转换为类的字段数据格式读取数据，且采用自定义第几个sheet和第几行数据位置读取
         *
         * @param inputStream 文件输入流
         * @param sheetNo     第几个sheet
         * @param rowNo       第几行
         * @param clazz       需要转换的类对象
         * @return 以clazz类对应格式的数据集合
         */
        public static <T> List<T> excelReadSync(InputStream inputStream, Integer sheetNo, Integer rowNo, Class<T> clazz) {
            List<T> objects = EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(rowNo).head(clazz).doReadSync();
            return objects;
        }
    
        /*----------------------------------------上面是同步读取excel中的数据，即都多少写多少-------------------------------------*/
        /*----------------------------------------上面是异步读取excel中的数据，即一条一条数据读取----------------------------------*/
    
        /**
         * 异步读取excel数据，采用默认读取方式
         * 默认只读取第一个sheet和第二行开始，即不读取表头数据
         * 采用默认Object.class
         *
         * @param inputStream  文件输入流
         * @param readListener 异步excel读取监听器
         */
        public static <T> void excelReadAsyncFirstSheet(InputStream inputStream, ReadListener<T> readListener) {
            EasyExcelFactory.read(inputStream, readListener).sheet().doRead();
        }
    
        /**
         * 异步读取excel数据，采用默认读取方式
         * 默认只读取第一个sheet和第二行开始，即不读取表头数据
         * 采用指定T.class
         *
         * @param inputStream  文件输入流
         * @param readListener 异步excel读取监听器
         * @param clazz        指定类型
         */
        public static <T> void excelReadAsyncFirstSheet(InputStream inputStream, ReadListener<T> readListener, Class<T> clazz) {
            EasyExcelFactory.read(inputStream, clazz, readListener).sheet().doRead();
        }
    
        /**
         * 异步读取excel数据，采用默认读取方式
         * 默认读取所有sheet以及每个sheet的第二行开始，即不读取表头数据
         * 采用默认Object.class
         *
         * @param inputStream  文件输入流
         * @param readListener 异步excel读取监听器
         */
        public static <T> void excelReadAsyncAllSheet(InputStream inputStream, ReadListener<T> readListener) {
            EasyExcelFactory.read(inputStream, readListener).doReadAll();
        }
    
        /**
         * 异步读取excel数据，采用默认读取方式
         * 默认读取所有sheet以及每个sheet的第二行开始，即不读取表头数据
         * 采用指定T.class
         *
         * @param inputStream  文件输入流
         * @param readListener 异步excel读取监听器
         * @param clazz        指定类型
         */
        public static <T> void excelReadAsyncAllSheet(InputStream inputStream, ReadListener<T> readListener, Class<T> clazz) {
            EasyExcelFactory.read(inputStream, clazz, readListener).doReadAll();
        }
    
        /**
         * 异步读取excel数据，采用指定sheet读取
         * 读取指定sheet以及对应sheet的第二行开始，即不读取表头数据
         * 采用默认Object.class
         *
         * @param inputStream  文件输入流
         * @param readListener 异步excel读取监听器
         * @param sheetNo      第几个sheet
         */
        public static <T> void excelReadAsyncSheet(InputStream inputStream, ReadListener<T> readListener, Integer sheetNo) {
            EasyExcelFactory.read(inputStream, readListener).sheet(sheetNo).doRead();
        }
    
        /**
         * 异步读取excel数据，采用指定sheet读取
         * 读取指定sheet以及对应sheet的第二行开始，即不读取表头数据
         * 采用指定T.class
         *
         * @param inputStream  文件输入流
         * @param readListener 异步excel读取监听器
         * @param clazz        指定类型
         * @param sheetNo      第几个sheet
         */
        public static <T> void excelReadAsyncSheet(InputStream inputStream, ReadListener<T> readListener, Class<T> clazz, Integer sheetNo) {
            EasyExcelFactory.read(inputStream, clazz, readListener).sheet(sheetNo).doRead();
        }
    
        /**
         * 异步读取excel数据，采用指定sheet读取和指定行读取
         * 采用默认Object.class
         *
         * @param inputStream  文件输入流
         * @param readListener 异步excel读取监听器
         * @param sheetNo      第几个sheet
         * @param rowNo        第几行开始
         */
        public static <T> void excelReadAsyncSheet(InputStream inputStream, ReadListener<T> readListener, Integer sheetNo, Integer rowNo) {
            EasyExcelFactory.read(inputStream, readListener).sheet(sheetNo).headRowNumber(rowNo).doRead();
        }
    
        /**
         * 异步读取excel数据，采用指定sheet读取和指定行读取
         * 采用指定T.class
         *
         * @param inputStream  文件输入流
         * @param readListener 异步excel读取监听器
         * @param clazz        指定类型
         * @param sheetNo      第几个sheet
         * @param rowNo        第几行开始
         */
        public static <T> void excelReadAsyncSheet(InputStream inputStream, ReadListener<T> readListener, Class<T> clazz, Integer sheetNo, Integer rowNo) {
            EasyExcelFactory.read(inputStream, clazz, readListener).sheet(sheetNo).headRowNumber(rowNo).doRead();
        }
    
    }

### 2、异步读取监听器创建 ###

当使用异步读取数据的时候，需要创建一个监听器来完成；如下

package com.jdh.easyexcel.utils;
    
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.metadata.CellExtra;
    import com.alibaba.excel.metadata.data.ReadCellData;
    import com.alibaba.excel.read.listener.ReadListener;
    import lombok.Data;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.ss.formula.functions.T;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    import java.util.concurrent.ArrayBlockingQueue;
    import java.util.concurrent.atomic.AtomicInteger;
    
    /**
     * @ClassName: ExcelReadListener
     * @Author: jdh
     * @CreateTime: 2022-08-04
     * @Description: 关于easyExcel读取excel文档的异步读取的监听器的相关方法
     */
    @Data
    @Slf4j
    public class ExcelReadListener<T> implements ReadListener<T> {
    
        /**
         * 当前读取的数据集合
         */
        private List<T> data = new ArrayList<>();
    
        /**
         * 当前读取的sheet个数
         */
        private AtomicInteger readSheetCount = new AtomicInteger(0);
    
        public ExcelReadListener() {
        }
    
        public ExcelReadListener(List<T> data) {
            this.data = data;
        }
    
        /**
         * 读取每一行数据进行回调
         *
         * @param t 需要转换的类型，不指定则为object
         * @param analysisContext 分析上下文
         */
        @Override
        public void invoke(T t, AnalysisContext analysisContext) {
            //这里进行数据校验，比如某个数据格式是否满足需求、可以在这里校验完的数据直接写入数据库等等
            //当然也可以直接先添加到对应的集合中，最终在获取这个数据集合单独做校验
            //如果导入的excel的数据比较庞大，那么建议这里校验并根据拆分数据条数来使用多线程对后续数据操作
            data.add(t);
        }
    
        /**
         * 当读完一张sheet的时候回调
         *
         * @param analysisContext 分析上下文
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            readSheetCount.incrementAndGet();
        }
    
        /**
         * 当出现读取数据异常的时候回调该方法
         *
         * @param exception 异常行为
         * @param context   分析上下文
         * @throws Exception
         */
        @Override
        public void onException(Exception exception, AnalysisContext context) throws Exception {
            log.info("onException => {}", exception.getMessage());
        }
    
        /**
         * 是对当前sheet中当前列的相关属性的读取,即默认表头数据
         *
         * @param headMap 所有有效数据列的map数据
         * @param context 分析上下文
         */
        @Override
        public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
    //        log.info("invokeHead => {}",headMap);
        }
    
        /**
         * 当返回额外信息时，调用当前方法
         *
         * @param extra   额外信息
         * @param context 分析上下文
         */
        @Override
        public void extra(CellExtra extra, AnalysisContext context) {
            log.info("extra => {}", extra);
        }
    
        /**
         * 验证是否存在另一条数据。可以通过返回false来停止读取
         *
         * @param context 分析上下文
         * @return
         */
        @Override
        public boolean hasNext(AnalysisContext context) {
            return true;
        }
    }

### 3、读取数据的测试 ###

package com.jdh.easyexcel.utils;
    
    import com.alibaba.excel.EasyExcelFactory;
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.read.listener.ReadListener;
    import com.jdh.easyexcel.pojo.User;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.junit.jupiter.api.Test;
    import org.springframework.boot.test.context.SpringBootTest;
    
    import java.io.*;
    import java.util.ArrayList;
    import java.util.List;
    
    @SpringBootTest
    class EasyExcelReadOperationTest {
    
        private static final String path = System.getProperty("user.dir") + "\\Doc\\";
    
        private static ByteArrayOutputStream byteArrayOutputStream = null;
    
        static {
            try {
                FileInputStream inputStream = new FileInputStream(path + "user_basic.xlsx");
    
                byteArrayOutputStream = new ByteArrayOutputStream();
                // 读取InputStream的内容到缓冲区
                byte[] buffer = new byte[1024];
                int length;
                while ((length = inputStream.read(buffer)) != -1) {
                    byteArrayOutputStream.write(buffer, 0, length);
                }
    
                // 关闭InputStream
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 获取当前excel的基本信息；包括sheet数、行数、列数等等
         **/
        @Test
        void excelReadBasicInfo() throws IOException, InvalidFormatException {
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
    
            //下面就用到poi的api了
            Workbook workbook = WorkbookFactory.create(inputStream);
            int sheetCount = workbook.getNumberOfSheets();
            System.out.println("Sheet数量：" + sheetCount);
    
            for (int i = 0; i < sheetCount; i++) {
                Sheet sheet = workbook.getSheetAt(i);
    
                // 获取行数
                int rowCount = sheet.getLastRowNum() + 1;
    
                //获取表头列数
                Row row0 = sheet.getRow(0);
                int firstCellNum = row0.getLastCellNum();
    
                // 获取表中最大列数
                int columnMaxCount = 0;
                for (int j = 0; j < rowCount; j++) {
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        int currentColumnCount = row.getLastCellNum();
                        if (currentColumnCount > columnMaxCount) {
                            columnMaxCount = currentColumnCount;
                        }
                    }
                }
    
                //注意：若读取行列数，如果单元格设置了单元格格式或者写入数据又给删掉，那么这种情况会被认为时有效单元格，在统计行或列的时候会被统计出来，但是肉眼看则时空行或列
                //在解析数据的时候也会去解析该单元格数据，会解析出一个空数据，所以在解析数据的时候就需要增加单元格数据类型校验
                System.out.println("Sheet名称:" + sheet.getSheetName() + ";行数:" + rowCount + ";表头列数:" + firstCellNum + ";最大列数:" + columnMaxCount);
            }
    
            workbook.close();
            inputStream.close();
        }
    
        /*----------------------------------------下面时解析excel数据,并读取出来-------------------------------------*/
    
        /**
         * 采用默认读取数据 默认只读取第一个sheet且从第二行读取;即默认第一行为表头但不读取表头数据
         **/
        @Test
        void excelReadSync1() throws IOException {
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            List<Object> objectData = EasyExcelReadOperation.excelReadSync(inputStream);
            objectData.forEach(System.out::println);
    
            System.out.println("=================");
    
            inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            List<User> userData = EasyExcelReadOperation.excelReadSync(inputStream, User.class);
            userData.forEach(System.out::println);
    
            inputStream.close();
        }
    
        /**
         * 采指定转换为类的字段数据格式读取数据，且采用自定义第几个sheet和默认第二行数据位置读取;默认第一行为表头且不读取
         */
        @Test
        void excelReadSync2() throws IOException {
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            List<Object> objectData = EasyExcelReadOperation.excelReadSync(inputStream, 1);
            objectData.forEach(System.out::println);
    
            System.out.println("=================");
    
            inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            List<User> userData = EasyExcelReadOperation.excelReadSync(inputStream, 1, User.class);
            userData.forEach(System.out::println);
    
            inputStream.close();
        }
    
        /**
         * 指定转换为类的字段数据格式读取数据，且采用自定义第几个sheet和第几行数据位置读取
         */
        @Test
        void excelReadSync3() throws IOException {
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            List<?> objectData = EasyExcelReadOperation.excelReadSync(inputStream, 1, 1);
            objectData.forEach(System.out::println);
    
            System.out.println("=================");
    
            inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            List<User> userData = EasyExcelReadOperation.excelReadSync(inputStream, 1, 1, User.class);
            userData.forEach(System.out::println);
    
    
            inputStream.close();
        }
    
        /*----------------------------------------上面是同步读取excel中的数据，即都多少写多少-------------------------------------*/
    
        /**
         * 异步读取数据的原始写法
         *
         * @throws IOException
         */
        @Test
        void excelReadASync() throws IOException {
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
    
            ArrayList<User> list = new ArrayList<>();
            EasyExcelFactory.read(inputStream, User.class, new ReadListener<User>() {
                @Override
                public void invoke(User data, AnalysisContext context) {
                    list.add(data);
                }
    
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    System.out.println("当前sheet读取完毕");
                }
            }).doReadAll();
            inputStream.close();
    
            list.forEach(System.out::println);
        }
    
        /*----------------------------------------下面是异步读取excel中的数据，即一条一条数据读取----------------------------------*/
    
        /**
         * 异步读取excel数据，采用默认只读取第一个sheet和第二行开始，即不读取表头数据
         */
        @Test
        void excelReadAsyncFirstSheet() throws IOException {
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            //默认解析
            ExcelReadListener<Object> readListenerByObject = new ExcelReadListener<>();
            EasyExcelReadOperation.excelReadAsyncFirstSheet(inputStream, readListenerByObject);
            System.out.println("当前读取的sheet个数:" + readListenerByObject.getReadSheetCount());
            List<Object> objectData = readListenerByObject.getData();
            objectData.forEach(System.out::println);
    
            System.out.println("=================");
    
            inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            //指定类型解析
            ExcelReadListener<User> readListenerByUser = new ExcelReadListener<>();
            EasyExcelReadOperation.excelReadAsyncFirstSheet(inputStream, readListenerByUser, User.class);
            System.out.println("当前读取的sheet个数:" + readListenerByUser.getReadSheetCount());
            List<User> userData = readListenerByUser.getData();
            userData.forEach(System.out::println);
    
            inputStream.close();
        }
    
        /**
         * 异步读取excel数据，采用默认读取所有sheet以及每个sheet的第二行开始，即不读取表头数据
         */
        @Test
        void excelReadAsyncAllSheet() throws IOException {
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            //默认解析
            ExcelReadListener<Object> readListenerByObject = new ExcelReadListener<>();
            EasyExcelReadOperation.excelReadAsyncAllSheet(inputStream, readListenerByObject);
            System.out.println("当前读取的sheet个数:" + readListenerByObject.getReadSheetCount());
            List<Object> objectData = readListenerByObject.getData();
            objectData.forEach(System.out::println);
    
            System.out.println("=================");
    
            inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            //指定类型解析
            ExcelReadListener<User> readListenerByUser = new ExcelReadListener<>();
            EasyExcelReadOperation.excelReadAsyncAllSheet(inputStream, readListenerByUser, User.class);
            System.out.println("当前读取的sheet个数:" + readListenerByUser.getReadSheetCount());
            List<User> userData = readListenerByUser.getData();
            userData.forEach(System.out::println);
    
            inputStream.close();
        }
    
        /**
         * 异步读取excel数据，采用指定sheet读取指定sheet以及对应sheet的第二行开始，即不读取表头数据
         */
        @Test
        void excelReadAsyncSheet1() throws IOException {
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            ExcelReadListener<Object> readListenerByObject = new ExcelReadListener<>();
            EasyExcelReadOperation.excelReadAsyncSheet(inputStream, readListenerByObject, 1);
            System.out.println("当前读取的sheet个数:" + readListenerByObject.getReadSheetCount());
            List<Object> objectData = readListenerByObject.getData();
            objectData.forEach(System.out::println);
    
            System.out.println("=================");
    
            inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            //指定类型解析
            ExcelReadListener<User> readListenerByUser = new ExcelReadListener<>();
            EasyExcelReadOperation.excelReadAsyncSheet(inputStream, readListenerByUser, User.class, 1);
            System.out.println("当前读取的sheet个数:" + readListenerByUser.getReadSheetCount());
            List<User> userData = readListenerByUser.getData();
            userData.forEach(System.out::println);
    
            inputStream.close();
        }
    
        /**
         * 异步读取excel数据，采用指定sheet读取和指定行读取
         */
        @Test
        void excelReadAsyncSheet2() throws IOException {
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            ExcelReadListener<Object> readListenerByObject = new ExcelReadListener<>();
            EasyExcelReadOperation.excelReadAsyncSheet(inputStream, readListenerByObject, 1, 1);
            System.out.println("当前读取的sheet个数:" + readListenerByObject.getReadSheetCount());
            List<Object> objectData = readListenerByObject.getData();
            objectData.forEach(System.out::println);
    
            System.out.println("=================");
    
            inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            //指定类型解析
            ExcelReadListener<User> readListenerByUser = new ExcelReadListener<>();
            EasyExcelReadOperation.excelReadAsyncSheet(inputStream, readListenerByUser, User.class, 1, 1);
            System.out.println("当前读取的sheet个数:" + readListenerByUser.getReadSheetCount());
            List<User> userData = readListenerByUser.getData();
            userData.forEach(System.out::println);
    
            inputStream.close();
        }
    }

## 五、将数据导出为excel的方法封装 ##

### 1、导出excel方法封装 ###

package com.jdh.easyexcel.utils;
    
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.EasyExcelFactory;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.read.listener.ReadListener;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.alibaba.excel.write.metadata.WriteTable;
    import com.jdh.easyexcel.pojo.User;
    
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.List;
    import java.util.Map;
    
    /**
     * @ClassName: EasyExcelReadOperation
     * @Author: jdh
     * @CreateTime: 2022-08-04
     * @Description: 关于easyExcel将实体数据写入到excel的相关方法
     */
    public class EasyExcelWriteOperation {
    
        /**
         * 将数据读取并导出到excel中
         *
         * @param outputStream 输出流
         * @param data         需要导出的数据集合
         * @param clazz        需要导出集合的元素类对象
         * @param sheetCond    sheet表条件
         * @param <T>
         */
        public static <T> void excelWrite(OutputStream outputStream, List<T> data, Class<T> clazz, Map<String, Object> sheetCond) {
            //先构建一个excel导出的构建器
            ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build();
    
            WriteSheet writeSheet = new WriteSheet();
            writeSheet.setSheetNo(Integer.parseInt(sheetCond.get("sheetNo").toString()));//对应excel表格的第几张表
            writeSheet.setSheetName(sheetCond.get("sheetName").toString());//对应的表名
    
            excelWriter.write(data, writeSheet);//开始写入
    
            excelWriter.finish();//写入完毕
        }
    
        /**
         * 将数据读取并导出到excel中
         *
         * @param outputStream 输出流
         * @param data         需要导出的数据集合
         * @param sheetCond    sheet表条件
         * @param tableCond    table表格条件
         * @param <T>
         */
        public static <T> void excelWrite(OutputStream outputStream, List<T> data, Map<String, Object> sheetCond, Map<String, Object> tableCond) {
            //先构建一个excel导出的构建器
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
    
            WriteSheet writeSheet = new WriteSheet();
            writeSheet.setSheetNo(Integer.parseInt(sheetCond.get("sheetNo").toString()));//对应excel表格的第几张表
            writeSheet.setSheetName(sheetCond.get("sheetName").toString());//对应的表名
    
            WriteTable writeTable = new WriteTable();
            writeTable.setHead((List<List<String>>) tableCond.get("head"));
    
            excelWriter.write(data, writeSheet, writeTable);//开始写入
    
            excelWriter.finish();//写入完毕
        }
    
    }

### 2、导出excel测试 ###

package com.jdh.easyexcel.utils;
    
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.EasyExcelFactory;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.write.builder.ExcelWriterBuilder;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.alibaba.excel.write.metadata.WriteTable;
    import com.jdh.easyexcel.pojo.User;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.SheetBuilder;
    import org.junit.jupiter.api.Test;
    import org.springframework.boot.test.context.SpringBootTest;
    
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.*;
    
    import static org.junit.jupiter.api.Assertions.*;
    
    @SpringBootTest
    class EasyExcelWriteOperationTest {
    
        private static final String path = System.getProperty("user.dir") + "\\Doc\\";
    
        //这个数据是模拟从数据库查询的数据
        public static final List<User> data = new ArrayList<>(Arrays.asList(
                new User(1, "LiuYi", 15, "男", "12345678901", 8500.05, new Date(), "sheet表1", "刘一的数据"),
                new User(2, "ChenEr", 16, "女", "12345678902", 8600.05, new Date(), "sheet表1", "陈二的数据"),
                new User(3, "ZhangSan", 17, "男", "12345678903", 8700.05, new Date(), "sheet表1", "张三的数据"),
                new User(4, "LiSi", 18, "女", "12345678904", 8800.05, new Date(), "sheet表1", "李四的数据"),
                new User(5, "WangWu", 19, "男", "12345678905", 8900.05, new Date(), "sheet表1", "王五的数据"),
                new User(6, "ZhaoLiu", 20, "女", "12345678906", 9500.05, new Date(), "sheet表1", "赵六的数据"),
                new User(7, "SunQi", 21, "男", "12345678907", 9600.05, new Date(), "sheet表1", "孙七的数据"),
                new User(8, "ZhouBa", 22, "女", "12345678908", 9700.05, new Date(), "sheet表1", "周八的数据"),
                new User(9, "WuJiu", 23, "女", "12345678909", 9800.05, new Date(), "sheet表1", "吴九的数据"),
                new User(10, "ZhengShi", 24, "男", "12345678910", 9900.05, new Date(), "sheet表1", "郑十的数据")
        ));
    
        @Test
        void excelWrite() throws FileNotFoundException {
            String fileName = "user导出EasyExcel.xlsx";
            String sheetName = "Sheet";
    
            //获取一个文件输出流
            FileOutputStream outputStream = new FileOutputStream(path + fileName);
            HashMap<String, Object> sheetMap = new HashMap<>();
            sheetMap.put("sheetNo",1);
            sheetMap.put("sheetName","sheet1");
            EasyExcelWriteOperation.excelWrite(outputStream,data,User.class,sheetMap);
        }
    
        @Test
        void excelWrite1() throws FileNotFoundException {
            String fileName = "user导出EasyExcel.xlsx";
            String sheetName = "Sheet";
    
            //获取一个文件输出流
            FileOutputStream outputStream = new FileOutputStream(path + fileName);
            HashMap<String, Object> sheetMap = new HashMap<>();
            sheetMap.put("sheetNo",1);
            sheetMap.put("sheetName","sheet1");
    
            HashMap<String, Object> tableMap = new HashMap<>();
    //        tableMap.put("sheetNo",1);
    //        tableMap.put("sheetName","sheet1");
            EasyExcelWriteOperation.excelWrite(outputStream,data,sheetMap,tableMap);
    
        }
    }

## 六、结尾 ##

源码gitee地址：

[poi_excel_springboot_-CSDN]: https://blog.csdn.net/qq_45778079/article/details/132133692
[c015056ee5564b078a747abca03144f8.png]: https://img-blog.csdnimg.cn/c015056ee5564b078a747abca03144f8.png
[0eb791c2abdc4396bf2f5348ca0cb2c3.png]: https://img-blog.csdnimg.cn/0eb791c2abdc4396bf2f5348ca0cb2c3.png