Excel的导入导出工具类（以阿里的EasyExcel为基础）

                这个工具类的是使用的阿里的EasyExcel的包，在配置文件中导入以下依赖

        <!--easyexcel文件导入导出       -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

        导出工具类如下：

public class ExportUtils {
    /**
     * 设置excel下载响应头属性
     *
     * @param response
     * @param rawFileName
     */
    public static void setExcelRespProp(HttpServletResponse response, String rawFileName) {
        //设置响应内容类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //编码
        response.setCharacterEncoding("utf-8");
        //设置文件名
        String fileName = null;
        try {
            fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        //设置响应头
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }

    /**
     * 导出Excel文件
     *
     * @param response
     * @param dataList                要导出的数据集
     * @param fileName                文件名称
     * @param clazz                   导出的实体类
     * @param excludeColumnFieldNames 导出实体类中被排除的字段
     */
    public static <T> T downloadExportExcel(HttpServletResponse response, List<T> dataList, String fileName, Class<? extends T> clazz, Set<String> excludeColumnFieldNames) {
        setExcelRespProp(response, fileName + System.currentTimeMillis());
        try {
            EasyExcel.write(response.getOutputStream())
                    .head(clazz)
                    .excludeColumnFieldNames(excludeColumnFieldNames)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet(fileName)
                    .doWrite(dataList);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


}

导出工具类的应用方法如下：

/**
     * 下载导出数据
     *
     * @param exportDataReq
     * @param response
     */
    @Override
    public void exportData(ExportDataReq exportDataReq, HttpServletResponse response) {
        List<User> userList= userService.getList(exportDataReq.getTitleId());
        String fileName = user.getTitle();
        ExportUtils.downloadExportExcel(response, userList, fileName, User.class, null);
    }

        导入工具类的代码如下：

/**
 * @author lyh
 * @date 2023/5/14 12:49
 */
public class ImportExcelUtils {

    public static <T> ExcelReadResult<T> readExcel(InputStream inputStream, Class<? extends T> clazz, EasyExcelListener<T> readListener) {
        ExcelReadResult<T> result = new ExcelReadResult<>();
        try {
            ExcelReaderBuilder builder = EasyExcel.read(inputStream, clazz, readListener);
            builder.doReadAll();
            List<T> dataList = readListener.getData();
            List<ExcelReadError> errorList = readListener.getErrorList();
            result.setDataList(dataList);
            result.setSuccessCount(readListener.getSuccssCount());
            result.setFailCount(readListener.getFailCount());
        } catch (ExcelAnalysisException e) {
            result.setSuccessCount(0);
            result.setMsg("Excel格式错误，无法读取");
        } catch (Exception e) {
            result.setMsg("读取Excel出错：" + e.getMessage());
        }
        return result;
    }

}

        Excel读取结果类：

/**
 * Excel读取结果类
 *
 * @param <T>
 * @author lyh
 */
@Data
public class ExcelReadResult<T> {
    private List<T> dataList;
    private int successCount = 0;
    private int failCount = 0;
    private String msg;

}

        Excel读取异常类：

/**
 * Excel读取异常类
 *
 * @author lyh
 */
@Data
public class ExcelReadError {
    /**
     * 行号
     */
    private int lineNum;
    /**
     * 错误的行数据
     */
    private String rowData;
    /**
     * 错误信息
     */
    private String errorMsg;
}

        Excel监听类：

/**
 * @date 2023/4/20 9:53
 */
public class EasyExcelListener<T> implements ReadListener<T> {
    /**
     * 每隔100存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static int BATCH_COUNT = 3000;
    private final Class<? extends T> clazz;
    //存放异常数据的集合
    private final List<T> dataList = new ArrayList<>();
    //存放要存储数据的集合
    private final List<User> savedDataList = new ArrayList<>();
    private final List<User> updateDataList = new ArrayList<>();
    private final List<ExcelReadError> errorList = new ArrayList<>();
    private final Logger logger = LoggerFactory.getLogger(EasyExcelListener.class);
    private int titleId;
    private UserService userService;
    private List<String> codes;
    private int succssCount = 0;
    private int failCount = 0;
    private int saveCount = 0;
    private int updateCount = 0;

    public EasyExcelListener(Class<? extends T> clazz, int titleId, UserService userService, List<String> codes) {
        this.titleId = titleId;
        this.clazz = clazz;
        this.userService= userService;
        this.codes = codes;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        //读取到数据时的回调函数
        User user= new User();
        if (data instanceof User) {
            BeanUtil.copyProperties(data, User);
            saveCount++;
            succssCount++;
        }
        screenDataPo.setTitleId(titleId);
        savedDataList.add(User);

        //到最大上传数量就检查数据并更新到数据库
        if (saveCount >= BATCH_COUNT) {
            if (CollUtil.isNotEmpty(savedDataList)) {
                for (int i = savedDataList.size() - 1; i >= 0; i--) {
                    //如果有学号相等的数据，就放进更新的数据集合中
                    if (codes.contains(savedDataList.get(i).getCode())) {
                        updateDataList.add(savedDataList.get(i));
                        savedDataList.remove(i);
                        saveCount--;
                        updateCount++;
                    }
                }
            }
            saveData(savedDataList);
            savedDataList.clear();
            saveCount = 0;
        }
        if (updateCount >= BATCH_COUNT) {
            updateData(updateDataList);
            updateDataList.clear();
            updateCount = 0;
        }
    }

    public void saveData(List<User> user) {
        userService.saveBatch(user);
    }

    public void updateData(List<User> user) {
        userService.updateListByCode(user);
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (CollUtil.isNotEmpty(savedDataList)) {
            for (int i = savedDataList.size() - 1; i >= 0; i--) {
                //如果有学号相等的数据，就放进更新的数据集合中
                if (codes.contains(savedDataList.get(i).getCode())) {
                    updateDataList.add(savedDataList.get(i));
                    savedDataList.remove(i);
                    saveCount--;
                    updateCount++;
                }
            }
            saveData(savedDataList);
            savedDataList.clear();
            saveCount = 0;
        }
        if (CollUtil.isNotEmpty(updateDataList)) {
            updateData(updateDataList);
            updateDataList.clear();
            updateCount = 0;
        }
        System.out.println("所有数据读取完毕");
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) {
        //读取时发生异常的回调函数
        ExcelReadError error = new ExcelReadError();
        error.setLineNum(context.readRowHolder().getRowIndex());
        error.setRowData(JSON.toJSONString(context.readRowHolder().getCurrentRowAnalysisResult()));
        error.setErrorMsg(exception.getMessage());
        errorList.add(error);
    }


    public List<T> getData() {
        return dataList;
    }

    public int getSuccssCount() {
        return succssCount;
    }

    public int getFailCount() {
        return failCount;
    }

    public List<ExcelReadError> getErrorList() {
        return errorList;
    }
}

        导入工具类的应用：

    /**
     * 导入数据
     *
     * @param file
     * @return
     */
    @Override
    public ExcelReadResult<User> importEmployment(MultipartFile file, int titleId,int codes) {

        ExcelReadResult<User> result = null;
        try {
            result = ImportExcelUtils.readExcel(file.getInputStream(), User.class, new EasyExcelListener<User>(User.class, titleId, userService, codes));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return result;
    }