SpringBoot 整合 EasyExcel

原创已于 2025-05-08 14:14:41 修改 · 929 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

于 2023-06-28 09:07:35 首次发布

SpringBoot 专栏收录该内容

21 篇文章

订阅专栏

数据导出

一、小数据量导出

pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.6.3</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.16.1</version>
</dependency>

FileUtil

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import java.io.File;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 文件处理
 */
public class FileUtil {

    private static final String FILE_PATH = System.getProperty("user.dir") + File.separator + "export";

    /**
     * 小数据量 - 导出到单个文件
     */
    public static String exportByFile(List<List<Object>> body, List<String> headers) {
        String tableName = "用户信息表";
        String filePath = mkdir(tableName);
        EasyExcel.write(filePath)
        		.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .head(createHeader(headers))
                .sheet("用户信息")
                .doWrite(body);
        return filePath;
    }


    /**
     * 小数据量 - 导出到浏览器（前端获取，浏览器直接下载，单个文件）
     */
    public static void exportByBrowser(List<List<Object>> body, List<String> headers, OutputStream outputStream) {
        EasyExcel.write(outputStream)
        		.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .head(createHeader(headers))
                .sheet("用户信息")
                .doWrite(body);
    }


    /**
     * 创建文件目录
     */
    private static String mkdir(String tableName) {
        File path = new File(FILE_PATH);
        if (!path.exists()) {
            path.mkdirs();
        }
        return FILE_PATH + File.separator + tableName + ".xlsx";
    }


    /**
     * 构建表头 header
     */
    private static List<List<String>> createHeader(List<String> headers) {
        List<List<String>> list = new ArrayList<>();
        headers.forEach(header -> {
            List<String> cell = new ArrayList<>();
            cell.add(header);
            list.add(cell);
        });
        return list;
    }
}

TestController

package com.cnbai;

import org.springframework.http.ContentDisposition;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

@RestController
public class TestController {

    /**
     * 小数据量 - 导出到单个文件
     */
    @GetMapping("/exportByFile")
    public void exportByFile() {

        List<List<Object>> body = createBody();
        List<String> headers = createHeader();

        String path = FileUtil.exportByFile(body, headers);
        System.out.println("导出路径：" + path);
    }


    /**
     * 小数据量 - 导出到浏览器（前端获取，浏览器直接下载，单个文件）
     */
    @GetMapping("/exportByBrowser")
    public void exportByBrowser(HttpServletResponse response) {

        try {
            List<List<Object>> body = createBody();
            List<String> headers = createHeader();

            response.addHeader("Content-Type", MediaType.MULTIPART_FORM_DATA_VALUE);
            ContentDisposition disposition = ContentDisposition.builder("attachment")
                    .filename(new String(URLEncoder.encode("用户信息表.xlsx", "UTF-8")
                            .getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1))
                    .build();
            response.addHeader("Content-Disposition", disposition.toString());

            FileUtil.exportByBrowser(body, headers, response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private List<List<Object>> createBody() {
        List<List<Object>> body = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            List<Object> list = new ArrayList<>();
            list.add("cnbai" + i);
            list.add("24");
            list.add("西安");
            body.add(list);
        }
        return body;
    }


    private List<String> createHeader() {
        List<String> headers = new ArrayList<>();
        headers.add("姓名");
        headers.add("年龄");
        headers.add("地址");
        return headers;
    }
}

二、大数据量导出

pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.6.3</version>
</dependency>
<dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.8.1</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.16.1</version>
</dependency>

TestController

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@RestController
public class TestController {

    private static final Logger logger = LoggerFactory.getLogger(TestController.class);

    // 配置好的数据源
    @Resource(name = "userDataSource")
    private DataSource dataSource;

    /**
     * 大数据量 - 导出到多个文件
     */
    @GetMapping("/exportBigData")
    public Long exportBigData() {
        String sql = "select * from t_user";
        QueryRunner runner = new QueryRunner(dataSource);
        return execute(runner, sql, new ExcelHandler());
    }


    /**
     * 执行查询语句
     */
    private <T> T execute(QueryRunner runner, String sql, ResultSetHandler<T> resultType) {
        try {
            return runner.query(sql, resultType);
        } catch (Exception e) {
            return null;
        }
    }


    /**
     * 写入文件
     */
    private static class ExcelHandler implements ResultSetHandler<Long> {

        // sheet 最大行数 20w
        private final Long sheetMaxRows = 200000L;
        // excel 最大总行数 100w
        private final Long excelMaxSize = 1000000L;
        // 导出数据总数 500w
        private final Long exportSize = 5000000L;

        private Long count = 0L;
        private int fileCount = 1;
        private int sheetCount = 1;
        private ExcelWriter writer = null;
        private WriteSheet sheet = null;
        private List<List<Object>> data;
        private Long time;

        public ExcelHandler() {
            this.data = new ArrayList<>(sheetMaxRows.intValue());
            this.time = System.currentTimeMillis();
        }

        @Override
        public Long handle(ResultSet resultSet) throws SQLException {

            List<String> headers = new ArrayList<>();
            headers.add("姓名");
            headers.add("年龄");
            headers.add("地址");

            while (resultSet.next()) {

                // 生成新文件
                if (count % excelMaxSize == 0) {
                    File file = new File(getPath() + getFileName());
                    writer = EasyExcel.write(file)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                            .build();
                    this.sheetCount = 1;
                    logger.info("开始写入新文件：{}，路径：{}", file.getName(), file.getAbsolutePath());
                }

                // 生成新 sheet，并写入 excel，每个 excel 写入 5 个 sheet，每个 sheet 20w 行
                if (count % sheetMaxRows == 0) {
                    String sheetName = String.format("用户信息Sheet_%d", sheetCount);
                    sheet = EasyExcel.writerSheet(sheetCount - 1, sheetName)
                            .head(createHeader(headers))
                            .build();
                    this.sheetCount++;
                    logger.info("开始写入新sheet：{}", sheetName);
                }

                // 解析数据，构建集合
                List<Object> list = new ArrayList<>();
                list.add(getStringValue(resultSet, "name"));
                list.add(getStringValue(resultSet, "age"));
                list.add(getStringValue(resultSet, "address"));
                this.data.add(list);
                count++;

                // 写入
                doWrite(false);

                // 本次要导出的数据总量, 导出全部注释此代码
                if (count.equals(exportSize)) {
                    break;
                }
            }

            // 补充写入
            doWrite(true);
            logger.info("写入 {} 数据总耗时：{} ms", count, System.currentTimeMillis() - time);
            return count;
        }


        /**
         * 获取文件路径
         */
        private String getPath() {
            String dirPath = System.getProperty("user.dir") + File.separator + "export" + File.separator;
            File dir = new File(dirPath);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            return dirPath;
        }


        /**
         * 获取文件名称
         */
        private String getFileName() {
            String name = "用户信息_" + fileCount + ".xlsx";
            fileCount++;
            return name;
        }


        /**
         * 获取查询到的数据
         */
        private String getStringValue(ResultSet ret, String key) {
            try {
                return ret.getString(key);
            } catch (Exception e) {
                return null;
            }
        }


        /**
         * 写入数据
         */
        private void doWrite(boolean isLastSave) {
            if (this.data != null && !this.data.isEmpty()) {
                // 是否最后一次写入
                if (isLastSave) {
                    writer.write(this.data, this.sheet);
                    // 结束写入
                    this.writer.finish();
                    this.writer = null;
                    this.data = null;
                    this.sheet = null;
                } else {
                    if (0 != count && count % sheetMaxRows == 0) {
                        writer.write(this.data, this.sheet);
                        this.data.clear();
                        // 写入 100w 数据要结束写入这个 excel 文件
                        if (count % excelMaxSize == 0) {
                            this.writer.finish();
                        }
                    }
                }
            }
        }


        /**
         * 构建表头
         */
        private static List<List<String>> createHeader(List<String> headers) {
            List<List<String>> list = new ArrayList<>();
            headers.forEach(header -> {
                List<String> cell = new ArrayList<>();
                cell.add(header);
                list.add(cell);
            });
            return list;
        }
    }
}

三、根据模板文件导出

pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.14.0</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.6.3</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.16.1</version>
</dependency>

TestController

import org.apache.commons.io.FileUtils;
import org.springframework.http.ContentDisposition;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

@RestController
public class TestController {

    /**
     * 导出到单个文件
     */
    @GetMapping("/exportFileByTemplate")
    public void exportFileByTemplate() {
        try {
            List<List<String>> body = createBody();
            File templateFile = FileUtils.getFile("./template/用户信息模板.xlsx");
            String path = FileUtil.exportFileByTemplate(templateFile, body);
            System.out.println("导出路径：" + path);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出到浏览器（前端获取，浏览器直接下载，单个文件）
     */
    @GetMapping("/exportBrowserByTemplate")
    public void exportBrowserByTemplate(HttpServletResponse response) {

        try {
            List<List<String>> body = createBody();

            response.addHeader("Content-Type", MediaType.MULTIPART_FORM_DATA_VALUE);
            ContentDisposition disposition = ContentDisposition.builder("attachment")
                    .filename(new String(URLEncoder.encode("用户信息表.xlsx", "UTF-8")
                            .getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1))
                    .build();
            response.addHeader("Content-Disposition", disposition.toString());

            File templateFile = FileUtils.getFile("./template/用户信息模板.xlsx");
            FileUtil.exportBrowserByTemplate(templateFile, body, response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private List<List<String>> createBody() {
        List<List<String>> body = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            List<String> list = new ArrayList<>();
            list.add("cnbai" + i);
            list.add("24");
            list.add("西安");
            body.add(list);
        }
        return body;
    }
}

FileUtil

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.File;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;
import java.util.function.Consumer;

/**
 * 文件处理
 */
public class FileUtil {

    private static final String FILE_PATH = System.getProperty("user.dir") + File.separator + "export";

    /**
     * 导出到单个文件
     */
    public static String exportFileByTemplate(File templateFile, List<List<String>> content) throws IllegalAccessException {
        String tableName = "用户信息表";
        String filePath = mkdir(tableName);

        ExcelWriterBuilder writer = EasyExcel.write(FileUtils.getFile(filePath))
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        writeTo(writer, templateFile, content);
        return filePath;
    }


    /**
     * 导出到浏览器（前端获取，浏览器直接下载，单个文件）
     */
    public static void exportBrowserByTemplate(File templateFile, List<List<String>> content, OutputStream outputStream) throws IllegalAccessException {
        // 加边框，自适应格子大小
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy();
        horizontalCellStyleStrategy.setContentWriteCellStyleList(Arrays.asList(writeCellStyle));

        ExcelWriterBuilder writer = EasyExcel.write(outputStream)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(horizontalCellStyleStrategy);

        writeTo(writer, templateFile, content);
    }


    /**
     * 删除旧数据
     */
    private static void writeTo(ExcelWriterBuilder writer, File template, List<List<String>> content) throws IllegalAccessException {
        writeTo(writer, template, 0, sheetAt -> {
            for (int i = 0; i <= sheetAt.getLastRowNum(); i++) {
                Row row = sheetAt.getRow(i);
                // 表头第 1 行开始，删除其余样例数据
                if (i > 0 && null != row) {
                    sheetAt.removeRow(row);
                }
            }
        }, content);
    }


    /**
     * 写入数据
     */
    private static void writeTo(ExcelWriterBuilder writer, File template, int templateSheetNum, Consumer<Sheet> sheetConsumer, List<List<String>> content) throws IllegalAccessException {
        writer.withTemplate(template);
        ExcelWriterSheetBuilder sheet = writer.sheet(templateSheetNum);
        Field field = FieldUtils.getDeclaredField(ExcelWriterSheetBuilder.class, "excelWriter", true);
        ExcelWriter excelWriter = (ExcelWriter) field.get(sheet);
        Sheet sheetAt = excelWriter.writeContext().writeWorkbookHolder().getCachedWorkbook().getSheetAt(templateSheetNum);
        if (null != sheetAt) {
            sheetConsumer.accept(sheetAt);
        }
        sheet.doWrite(content);
    }


    /**
     * 创建文件目录
     */
    private static String mkdir(String tableName) {
        File path = new File(FILE_PATH);
        if (!path.exists()) {
            path.mkdirs();
        }
        return FILE_PATH + File.separator + tableName + ".xlsx";
    }
}

用户信息模板.xlsx

在这里插入图片描述

四、固定表头数据导出

pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.6.3</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.34</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.16.1</version>
</dependency>

TestController

import com.cnbai.dto.UserExport;
import com.cnbai.utils.ExcelUtil;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

@RestController
public class TestController {
    
    /**
     * 测试导出
     */
    @PostMapping("/export")
    public void export(HttpServletResponse response) {
        // TODO 从数据库查询数据
        List<UserExport> list = new ArrayList<>();
        ExcelUtil.exportExcel(list, UserExport.class, response);
    }
}

FileUtil

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.springframework.http.ContentDisposition;
import org.springframework.http.MediaType;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;

/**
 * Excel 工具类
 */
public class FileUtil {

    /**
     * 导出 excel
     *
     * @param list  导出数据集合
     * @param clazz 实体类
     */
    public static <T> void exportExcel(List<T> list, Class<T> clazz, HttpServletResponse response) {
        try {
            response.addHeader("Content-Type", MediaType.MULTIPART_FORM_DATA_VALUE);
            ContentDisposition disposition = ContentDisposition.builder("attachment")
                    .filename(new String(URLEncoder.encode("用户信息表.xlsx", "UTF-8")
                            .getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1))
                    .build();
            response.addHeader("Content-Disposition", disposition.toString());

            exportExcel(list, clazz, response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException("导出Excel异常");
        }
    }


    /**
     * 导出 excel
     *
     * @param list  导出数据集合
     * @param clazz 实体类
     */
    public static <T> void exportExcel(List<T> list, Class<T> clazz, OutputStream os) {
        EasyExcel.write(os, clazz)
                .autoCloseStream(false)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet("用户信息")
                .doWrite(list);
    }
}

UserExport

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

/**
 * 用户导出对象
 */
@Data
@ExcelIgnoreUnannotated
public class UserExport {

    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "年龄")
    private Integer age;

    @ExcelProperty(value = "地址")
    private String address;
}

数据导入

一、小数据量导入

pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.16.1</version>
</dependency>

FileUtil

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

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class FileUtil {

    /**
     * 读取 excel - 不固定表头
     */
    public static List<Object> readExcel(String filePath) {
        return EasyExcel.read(filePath).sheet().doReadSync();
    }

    /**
     * 读取 excel - 不固定表头
     */
    public static List<Map<String, Object>> readExcelToMap(String filePath) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        EasyExcel.read(filePath, new AnalysisEventListener<Map<String, Object>>() {

            private Map<Integer, String> headMap;

            @Override
            public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                this.headMap = headMap;
            }

            @Override
            public void invoke(Map<String, Object> valueData, AnalysisContext context) {
                HashMap<String, Object> paramsMap = new HashMap<>();
                for (int i = 0; i < valueData.size(); i++) {
                    String key = headMap.get(i);
                    Object value = valueData.get(i);
                    //将表头作为 map 的 key，每行每个单元格的数据作为 map 的 value
                    paramsMap.put(key, value);
                }
                dataList.add(paramsMap);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                System.out.println("Excel读取完成");

            }
        }).sheet().doRead();
        return dataList;
    }

    /**
     * 读取 excel - 固定表头
     */
    public static <T> List<T> readExcel(InputStream is, Class<T> clazz) {
        return EasyExcel.read(is).head(clazz).autoCloseStream(false).sheet().doReadSync();
    }


    public static void main(String[] args) {
        String filePath = "D:\\用户信息模板.xlsx";

        // [{0=zhangsan, 1=12, 2=xian}, {0=lisi, 1=12, 2=xian}]
        List<Object> objects = readExcel(filePath);
        System.out.println(objects);

        /*
         *  Excel读取完成
         *  {姓名=zhangsan, 地址=xian, 年龄=12}
         *  {姓名=lisi, 地址=xian, 年龄=12}
         */
        List<Map<String, Object>> list = readExcelToMap(filePath);
        for (Map<String, Object> map : list) {
            System.out.println(map);
        }

		// [{0=zhangsan, 1=12, 2=xian}, {0=lisi, 1=12, 2=xian}]
        List<User> userList = readExcel(Files.newInputStream(Paths.get(filePath)), User.class);
		System.out.println(userList);
    }
}

用户信息模板.xlsx

在这里插入图片描述

二、数据入库去重

pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.6.3</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.34</version>
</dependency>
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-core</artifactId>
    <version>5.8.27</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.16.1</version>
</dependency>

TestController

import com.cnbai.dto.UserImport;
import com.cnbai.utils.FileUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.util.List;

@RestController
public class TestController {

    @Resource
    private TestService testService;

    /**
     * 测试导入
     */
    @GetMapping(path = "/import")
    public void test(@RequestPart("file") MultipartFile file) throws IOException {
        List<UserImport> result = FileUtil.importExcel(file.getInputStream(), UserImport.class);
        testService.importData(result);
    }
}

TestService

import cn.hutool.core.collection.CollectionUtil;
import com.cnbai.dto.UserImport;
import org.springframework.stereotype.Component;

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

/**
 * 导入业务
 */
@Component
public class TestService {

    public void importData(List<UserImport> list) {

        // 导入数据为空
        if (CollectionUtil.isEmpty(list)) {
            System.out.println("共0条，成功导入0条");
        }

        // 错误数据
        List<UserImport> errorData = new ArrayList<>();
        // 入库数据
        List<UserImport> rightData = new ArrayList<>();

        // 对监听器解析校验后的数据进行空值校验
        for (UserImport userImport : list) {
            if (Objects.isNull(userImport.getName())
                    || Objects.isNull(userImport.getAge())
                    || Objects.isNull(userImport.getAddress())) {
                userImport.setErrorMsg("数据为空");
                errorData.add(userImport);
            } else if (userService.getByName(userImport.getName()) != null) {
                userImport.setErrorMsg("数据重复");
                errorData.add(userImport);
            } else {
                rightData.add(userImport);
            }
        }

		// 最终要导入的数据
        List<User> importData = new ArrayList<>();
        for (UserImport data : rightData) {
            User user = BeanUtil.toBean(data, User.class);
            importData.add(user);
        }
        
        // 执行入库操作
        baseMapper.insertBatch(importData);

        if (CollectionUtil.isNotEmpty(errorData)) {
            // TODO 导出或存储错误数据
            System.out.println(errorData);

            System.out.println("共" + list.size() + "条，成功导入" + importData.size() + "条，" +
                    "失败" + (list.size() - importData.size()) + "条");
        } else {
            System.out.println("共" + list.size() + "条，成功导入" + importData.size() + "条");
        }
    }
}

UserImport

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

/**
 * 用户导入对象
 */
@Data
@ExcelIgnoreUnannotated
public class UserImport {

    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "年龄")
    private Integer age;

    @ExcelProperty(value = "地址")
    private String address;

    /** 错误信息 */
    private String errorMsg;
}

CustomExcelListener

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import lombok.Getter;

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

/**
 * Excel 导入监听
 */
@Getter
public class CustomExcelListener<T> extends AnalysisEventListener<T> {

    /**
     * excel 表头数据
     */
    private Map<Integer, String> headMap;

    /**
     * 导入结果
     */
    private final List<T> excelResult;

    public CustomExcelListener() {
        this.excelResult = new ArrayList<>();
    }

    /**
     * 处理异常
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        String errMsg = null;
        if (exception instanceof ExcelDataConvertException) {
            // 表头解析异常处理：如果是某一个单元格的转换异常 能获取到具体行号
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            Integer rowIndex = excelDataConvertException.getRowIndex();
            Integer columnIndex = excelDataConvertException.getColumnIndex();
            errMsg = StrUtil.format("第{}行-第{}列-表头{}: 解析异常<br/>",
                    rowIndex + 1, columnIndex + 1, headMap.get(columnIndex));
        }
        System.out.println("表头解析异常：" + errMsg);
        throw new ExcelAnalysisException(errMsg);
    }


    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        this.headMap = headMap;
    }


    @Override
    public void invoke(T data, AnalysisContext context) {
        excelResult.add(data);
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("数据解析完成！");
    }
}

FileUtil

import com.alibaba.excel.EasyExcel;
import com.cnbai.listener.CustomExcelListener;

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

/**
 * Excel 工具类
 */
public class FileUtil {

    /**
     * 导入数据
     *
     * @param is    输入流
     * @param clazz 对象类型
     */
    public static <T> List<T> importExcel(InputStream is, Class<T> clazz) {
        CustomExcelListener<T> listener = new CustomExcelListener<>();
        EasyExcel.read(is, clazz, listener).sheet().doRead();
        return listener.getExcelResult();
    }
}

用户信息模板.xlsx

在这里插入图片描述

三、多模版数据入库去重（统一接口）

1. 项目目录

在这里插入图片描述

2. pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.14.0</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.4</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.6.3</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.16.1</version>
</dependency>

3. 创建测试类

TestController

package com.cnbai.controller;

import com.cnbai.dto.TemplateFileEnum;
import com.cnbai.listener.HandleResult;
import com.cnbai.utils.FileUtil;
import org.apache.commons.io.FileUtils;
import org.springframework.http.MediaType;
import org.springframework.util.ClassUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;

@RestController
public class TestController {

    /**
     * Excel 导入 -> 上传文件导入
     */
    @PostMapping(path = "excelImportByFile", produces = MediaType.APPLICATION_JSON_VALUE)
    public HandleResult excelImportByFile(@RequestPart("file") MultipartFile file,
                                          @RequestParam("type") TemplateFileEnum templateType) {
        try {
            String filePath = "d:/" + file.getOriginalFilename();
            File writeFile = new File(filePath);
            FileUtils.copyInputStreamToFile(file.getInputStream(), writeFile);
            return excelImport(writeFile, templateType);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }


    /**
     * Excel 导入 -> 根据文件路径导入
     */
    @PostMapping(path = "excelImportByPath", produces = MediaType.APPLICATION_JSON_VALUE)
    public HandleResult excelImportByPath(@RequestParam("path") String path,
                                          @RequestParam("type") TemplateFileEnum templateType) {
        File writeFile = new File(path);
        return excelImport(writeFile, templateType);
    }


    /**
     * 执行导入流程
     *
     * TODO: 需要传参 -> FileUtil.readExcel(writeFile, aClass, new Object[]{caseId, 24, xxx, ... });
     *                 根据业务确定参数个数，新增参数或修改类型需要在 ExcelAnalysisEventListener 中也添加修改
     *                 有的方法参数少或无参数，填充 null 即可
     *       此处以最多需要两个参数为例：caseId , dataId
     *       
     *       不需要传参 -> FileUtil.readExcel(writeFile, aClass);
     *                    所有导出业务都无需传参时这样修改，其他地方也对应修改
     */
    private HandleResult excelImport(File writeFile, TemplateFileEnum templateType) {
        try {
            Class<?> aClass = ClassUtils.forName(templateType.getClassName(), Thread.currentThread().getClass().getClassLoader());
            return FileUtil.readExcel(writeFile, aClass, new Object[]{"caseId123", "dataId123"});
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }


    /**
     * 测试导入
     */
    @GetMapping(path = "test")
    public HandleResult test() {
        return excelImport(TemplateFileEnum.USER_FILE.getTemplateFile(), TemplateFileEnum.USER_FILE);
    }
}

4. 创建 Service

UserService

package com.cnbai.controller;

import com.cnbai.dto.UserDTO;
import com.cnbai.exception.DuplicateParameterException;
import com.cnbai.exception.IllegalParameterException;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

/**
 * 用户业务处理类
 */
@Service
public class UserService {

    /**
     * 将数据写入数据库
     */
    public void importUser(UserDTO userDTO, String caseId, String dataId) {
        // 业务处理
        System.out.println("caseId==> " + caseId);
        System.out.println("dataId==> " + dataId);

        // 筛选错误数据
        validate(userDTO.getName(), "姓名不能为空");
        validate(userDTO.getAge(), "年龄不能为空");
        validate(userDTO.getAddress(), "地址不能为空");

        // 筛选是否重复，查询是否存在, 自定义异常
        UserDTO user = new UserDTO();
        if (user != null) {
            throw new DuplicateParameterException("用户已存在");
        }

        // 执行新增操作
        System.out.println("新增成功");
    }


    /**
     * 数据校验
     */
    private void validate(String value, String errorMsg) {
        if (StringUtils.isBlank(value)) {
            throw new IllegalParameterException(StringUtils.defaultString(errorMsg, "数据不能为空"));
        }
    }
}

5. 创建实体类

TemplateFileEnum

package com.cnbai.dto;

import org.apache.commons.io.FileUtils;

import java.io.File;

/**
 * 导入文件枚举
 */
public enum TemplateFileEnum {

    USER_FILE("用户信息模板.xlsx", "com.cnbai.listener.handle.UserExcelEventListener");

    private String name;
    private String className;

    TemplateFileEnum(String name, String className) {
        this.name = name;
        this.className = className;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getClassName() {
        return className;
    }

    public void setClassName(String className) {
        this.className = className;
    }

    public File getTemplateFile() {
        return FileUtils.getFile("./template/" + getName());
    }
}

UserDTO

package com.cnbai.dto;

/**
 * 用户实体类
 */
public class UserDTO {

    private String name;
    private String age;
    private String address;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

6. 创建自定义异常

DuplicateParameterException

package com.cnbai.exception;

/**
 * 自定义数据重复异常
 */
public class DuplicateParameterException extends RuntimeException {

    private String message;

    private Throwable throwable;

    public DuplicateParameterException() {
        super();
    }

    public DuplicateParameterException(String message) {
        super(message);
        this.message = message;
    }

    public DuplicateParameterException(String message, Throwable throwable) {
        super(message);
        this.message = message;
        this.throwable = throwable;
    }

    @Override
    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public Throwable getThrowable() {
        return throwable;
    }

    public void setThrowable(Throwable throwable) {
        this.throwable = throwable;
    }
}

IllegalParameterException

package com.cnbai.exception;

/**
 * 自定义数据缺失异常
 */
public class IllegalParameterException extends IllegalArgumentException {

    private String message;

    private Throwable throwable;

    public IllegalParameterException() {
        super();
    }

    public IllegalParameterException(String message) {
        super(message);
        this.message = message;
    }

    public IllegalParameterException(String message, Throwable throwable) {
        super(message);
        this.message = message;
        this.throwable = throwable;
    }

    @Override
    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public Throwable getThrowable() {
        return throwable;
    }

    public void setThrowable(Throwable throwable) {
        this.throwable = throwable;
    }
}

7. 创建监听器

UserExcelEventListener

package com.cnbai.listener.handle;

import com.alibaba.excel.context.AnalysisContext;
import com.cnbai.controller.UserService;
import com.cnbai.dto.UserDTO;
import com.cnbai.exception.DuplicateParameterException;
import com.cnbai.exception.IllegalParameterException;
import com.cnbai.listener.ExcelAnalysisEventListener;
import com.cnbai.utils.SpringUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * 用户模板监听器
 */
public class UserExcelEventListener extends ExcelAnalysisEventListener {

    // 跳过的行数（表头行数 - 1），跳过不需要解析的数据
    private static final int skipRow = 0;
    // 错误数据缓存
    private final List<Map<Integer, String>> errorCache;
    // 行计数器
    private static int rowNum = 0;
    // 错误原因写入的列，一般写在数据下一列，从 0 开始计算
    private static final int col = 2;
    // 正确数据缓存
    private final List<UserDTO> users;
    // 正确转换的源数据
    private final List<Map<Integer, String>> excelReadRows;

    private final UserService userService;

    public UserExcelEventListener(String caseId, String dataId) {
        super(caseId, dataId);
        this.errorCache = new ArrayList<>(1);
        this.users = new LinkedList<>();
        this.excelReadRows = new LinkedList<>();
        this.userService = SpringUtils.getBean(UserService.class);
        rowNum = 0;
    }

    @Override
    public void rowHandle(Map<Integer, String> row, AnalysisContext analysisContext) {
        rowNum++;
        if (rowNum <= skipRow) {
            return;
        }

        if (StringUtils.isBlank(row.get(0))) {
            row.put(col, "姓名为空");
            this.errorCache.add(row);
            return;
        }

        if (StringUtils.isBlank(row.get(1))) {
            row.put(col, "年龄为空");
            this.errorCache.add(row);
            return;
        }

        if (StringUtils.isBlank(row.get(2))) {
            row.put(col, "地址为空");
            this.errorCache.add(row);
            return;
        }

        UserDTO user = new UserDTO();
        user.setName(row.get(0));
        user.setAge(row.get(1));
        user.setAddress(row.get(2));

        this.excelReadRows.add(row);
        this.users.add(user);
    }

    @Override
    public void readComplete(AnalysisContext analysisContext) {
        if (CollectionUtils.isNotEmpty(this.users)) {
            int success = 0;
            int repeat = 0;
            for (int i = 0; i < this.users.size(); i++) {
                UserDTO user = this.users.get(i);
                try {
                    this.userService.importUser(user, getCaseId(), getDataId());
                    success++;
                } catch (IllegalParameterException e) {
                    Map<Integer, String> errorRow = this.excelReadRows.get(i);
                    errorRow.put(col, e.getMessage());
                    this.errorCache.add(errorRow);
                } catch (DuplicateParameterException e) {
                    repeat++;
                } catch (Exception e) {
                    Map<Integer, String> errorRow = this.excelReadRows.get(i);
                    errorRow.put(col, "数据写入失败");
                    this.errorCache.add(errorRow);
                }
            }

            getHandleResult().successStat(success);
            getHandleResult().repeatStat(repeat);
            getHandleResult().errorStat(this.errorCache.size());
            getHandleResult().addErrorData(this.errorCache);
        }
        this.clean();
    }

    private void clean() {
        this.errorCache.clear();
        this.users.clear();
        this.excelReadRows.clear();
    }
}

ExcelAnalysisEventListener

package com.cnbai.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.apache.commons.lang3.StringUtils;

import java.util.Iterator;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.concurrent.TimeUnit;

/**
 * excel 解析监听器父类
 */
public abstract class ExcelAnalysisEventListener extends AnalysisEventListener<Map<Integer, String>> implements Callable<HandleResult> {

    private String sheetName;
    private Integer sheetNumber;
    private boolean finished = false;
    private HandleResult handleResult = new HandleResult();

    // TODO: 传入的参数, 根据业务确定参数个数，新增参数或修改类型需要在此处中也添加修改
    private String caseId;
    private String dataId;

    // TODO: 同理，构造方法也要修改
    public ExcelAnalysisEventListener(String caseId, String dataId) {
        this.caseId = caseId;
        this.dataId = dataId;
    }

    @Override
    public void invoke(Map<Integer, String> map, AnalysisContext analysisContext) {
        rowHandle(map, analysisContext);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        try {
            readComplete(analysisContext);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.finished = true;
        }
    }

    /**
     * 行处理
     */
    public abstract void rowHandle(Map<Integer, String> map, AnalysisContext analysisContext);

    /**
     * 读取完成
     */
    public abstract void readComplete(AnalysisContext analysisContext);

    /**
     * 判断是否是空行
     */
    protected boolean hasValue(Map<Integer, String> map) {
        Iterator<String> iterator = map.values().iterator();
        boolean bool = false;
        while (iterator.hasNext()) {
            String next = iterator.next();
            bool = StringUtils.isNotBlank(next) || bool;
        }
        return bool;
    }

    public HandleResult call() {
        while (!this.finished) {
            try {
                TimeUnit.MILLISECONDS.sleep(500);
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
            }
        }
        return this.handleResult;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public Integer getSheetNumber() {
        return sheetNumber;
    }

    public void setSheetNumber(Integer sheetNumber) {
        this.sheetNumber = sheetNumber;
    }

    public String getCaseId() {
        return caseId;
    }

    public void setCaseId(String caseId) {
        this.caseId = caseId;
    }

    public String getDataId() {
        return dataId;
    }

    public void setDataId(String dataId) {
        this.dataId = dataId;
    }

    public HandleResult getHandleResult() {
        return handleResult;
    }

    public void setHandleResult(HandleResult handleResult) {
        this.handleResult = handleResult;
    }
}

8. 创建统计结果类

HandleResult

package com.cnbai.listener;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.util.*;

/**
 * 返回结果
 */
public class HandleResult extends HashMap<String, Object> {

    // 正确入库数据
    private static final String SUCCESS_COUNT = "successCount";
    // 错误数据
    private static final String ERROR_COUNT = "errorCount";
    // 重复数据
    private static final String REPEAT_COUNT = "repeatCount";
    // 错误文件路径
    private static final String ERROR_FILE_PATH = "errorFilePath";
    // 错误文件生成路径
    private String errorFilePath;
    // 错误数据
    private final List<Map<Integer, String>> errorData;

    public HandleResult() {
        initErrorFilePath();
        this.errorData = new ArrayList<>();
        this.put(ERROR_FILE_PATH, getErrorFilePath());
        this.put(SUCCESS_COUNT, 0);
        this.put(ERROR_COUNT, 0);
        this.put(REPEAT_COUNT, 0);
    }


    /**
     * 正确数据统计
     */
    public void successStat(Integer count) {
        this.put(SUCCESS_COUNT, count);
    }


    /**
     * 错误数据统计
     */
    public void errorStat(Integer count) {
        this.put(ERROR_COUNT, count);
    }


    /**
     * 重复数据统计
     */
    public void repeatStat(Integer count) {
        this.put(REPEAT_COUNT, count);
    }


    /**
     * 记录错误数据
     */
    public void addErrorData(List<Map<Integer, String>> data) {
        if (CollectionUtils.isNotEmpty(data)) {
            this.errorData.addAll(data);
        }
    }


    /**
     * 获取错误数据
     */
    public List<Map<Integer, String>> getErrorData() {
        return errorData;
    }


    /**
     * 初始化错误文件
     */
    private void initErrorFilePath() {
        if (StringUtils.isBlank(this.errorFilePath)) {
            this.errorFilePath = System.getProperty("java.io.tmpdir");
        }

        File file = new File(this.errorFilePath, DigestUtils.md5Hex(UUID.randomUUID().toString()));
        if (!file.exists()) {
            file.mkdirs();
        }
        this.errorFilePath = file.getAbsolutePath() + File.separator + "导入失败记录.xlsx";
    }


    /**
     * 获取错误文件路径
     */
    public String getErrorFilePath() {
        return errorFilePath;
    }


    /**
     * 是否含有错误数据
     */
    public boolean hasErrorCount() {
        Integer error = MapUtils.getInteger(this, ERROR_COUNT);
        return error > 0;
    }


    /**
     * 删除错误文件路径
     */
    public void removeErrorPath() {
        this.remove(ERROR_FILE_PATH);
    }


    /**
     * 合并数据
     */
    public void merge(HandleResult result) {
        add(result, SUCCESS_COUNT);
        add(result, ERROR_COUNT);
        add(result, REPEAT_COUNT);
    }


    /**
     * 合并数据
     */
    private void add(HandleResult result, String key) {
        Integer oldValue = ObjectUtils.defaultIfNull(MapUtils.getInteger(this, key), 0);
        Integer newValue = ObjectUtils.defaultIfNull(MapUtils.getInteger(result, key), 0);
        this.put(key, oldValue + newValue);
    }
}

9. 创建工具类

FileUtil

package com.cnbai.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.cache.MapCache;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.cnbai.listener.ExcelAnalysisEventListener;
import com.cnbai.listener.HandleResult;
import org.apache.commons.lang3.reflect.ConstructorUtils;

import java.io.File;
import java.io.FileNotFoundException;
import java.lang.reflect.Constructor;
import java.util.List;

/**
 * 文件工具类
 */
public class FileUtil {

    /**
     * 读取 Excel -> 无参数
     *
     * TODO: 无参数，填充 null 即可
     *       参数少，readExcel(file, aClass, new Object[]{caseId, null});
     */
    public static HandleResult readExcel(File file, Class<?> aClass) throws Exception {
        return readExcel(file, aClass, new Object[]{null, null});
    }


    /**
     * 读取 Excel -> 有参数
     */
    public static HandleResult readExcel(File file, Class<?> aClass, Object[] params) throws Exception {
        if (!file.exists()) {
            throw new FileNotFoundException("文件不存在");
        }
        ExcelReader excelReader = EasyExcel.read(file).build();
        List<ReadSheet> readSheets = excelReader.excelExecutor().sheetList();
        HandleResult handleResult = null;

        // 解析数据
        for (int i = 0; i < readSheets.size(); i++) {
            ReadSheet readSheet = readSheets.get(i);
            // TODO: 反射获取class信息并创建新对象，构造方法参数和参数类型需要一致
            // 其他类型 -> getAccessibleConstructor(aClass, String.class, Integer.class)，新增参数或修改类型需要在此处中也添加修改
            Constructor<?> constructor = ConstructorUtils.getAccessibleConstructor(aClass, String.class, String.class);
            ExcelAnalysisEventListener listener = (ExcelAnalysisEventListener) constructor.newInstance(params);
            listener.setSheetName(readSheet.getSheetName());
            listener.setSheetNumber(readSheet.getSheetNo());
            EasyExcel.read(file, listener)
                    .readCache(new MapCache())
                    .sheet(i)
                    .autoTrim(true)
                    .doRead();
            if (null == handleResult) {
                handleResult = listener.call();
            } else {
                handleResult.merge(listener.call());
            }
        }
        return handleResult;
    }
}

SpringUtils

package com.cnbai.utils;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * 获取 JavaBean
 */
@Component
public class SpringUtils implements ApplicationContextAware {

    private static ApplicationContext context;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringUtils.context = applicationContext;
    }

    public static <T> T getBean(Class<T> tClass) {
        return SpringUtils.context.getBean(tClass);
    }

    public static <T> T getBean(String name, Class<T> tClass) {
        return SpringUtils.context.getBean(name, tClass);
    }
}

10. 创建启动类

application

package com.cnbai;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class application {

    public static void main(String[] args) {
        SpringApplication.run(application.class, args);
    }
}