Excel导入导出超详细工具类

本文主要介绍利用easyExcel做的excel导入导出工具类，导出时进行合并单元格，导入时进行各种校验（字符串，数字，日期，字段唯一）提示成功数据以及错误信息，显示导入失败数据的行数，错误原因等。手把手教学

1.导入依赖

<?xml version="1.0" encoding="UTF-8"?>
		<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.lkx</groupId>
    <artifactId>excel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>excel</name>
    <description>excel导入导出测试案例</description>

    <properties>
        <java.version>8</java.version>
    </properties>
    <dependencies>
        <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>
            <scope>test</scope>
        </dependency>

        <!--lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--json转换-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.83</version>
        </dependency>

        <!--校验工具类-->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
        </dependency>

        <!--swagger-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!-- hutool工具类-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.20</version>
        </dependency>

        <!-- easyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
            <exclusions>
                <exclusion>
                    <artifactId>slf4j-api</artifactId>
                    <groupId>org.slf4j</groupId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

2.自定义excel监听器

该监听器在读取excel表格时，会将excel读取到一个Map集合中，key是属性的下标，value是字段值，最后将Map放到List中

package com.lkx.excel.util;


import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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


/**
 * <p>导入监听器
 */
public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {
  private static final Logger LOGGER = LoggerFactory.getLogger(NoModelDataListener.class);
  List<Map<Integer, String>> list = new ArrayList<>();

  @Override
  public void invoke(Map<Integer, String> data, AnalysisContext context) {
    list.add(data);
  }

  @Override
  public void doAfterAllAnalysed(AnalysisContext context) {
    LOGGER.info("所有数据解析完成！");
  }

  public List<Map<Integer, String>> getList() {
    return list;
  }

  public void setList(List<Map<Integer, String>> list) {
    this.list = list;
  }
}

3.合并单元格处理器

package com.lkx.excel.util.handler;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;


@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelMergeCellHandler implements CellWriteHandler {
    // 需要合并的列，从0开始算
    private int[] mergeColIndex;
    // 从指定的行开始合并，从0开始算
    private int mergeRowIndex;
 


 
    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder 表格处理句柄
     * @param cell             当前单元格
     * @param currRowIndex     当前行
     * @param currColIndex     当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int currRowIndex, int currColIndex) {
        // 获取当前单元格数值
        Object currData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        // 获取当前单元格正上方的单元格对象
        Cell preCell = cell.getSheet().getRow(currRowIndex - 1).getCell(currColIndex);
        // 获取当前单元格正上方的单元格的数值
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
 
        // 将当前单元格数值与其正上方单元格的数值比较
        if (preData.equals(currData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            // 当前单元格的正上方单元格是否是已合并单元格
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress address = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (address.isInRange(currRowIndex - 1, currColIndex)) {
                    sheet.removeMergedRegion(i);
                    address.setLastRow(currRowIndex);
                    sheet.addMergedRegion(address);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(currRowIndex - 1, currRowIndex, currColIndex, currColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    /**
     * 在单元格上的所有操作完成后调用，遍历每一个单元格，判断是否需要向上合并
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        // 获取当前单元格行下标
        int currRowIndex = cell.getRowIndex();
        // 获取当前单元格列下标
        int currColIndex = cell.getColumnIndex();
        // 判断是否大于指定行下标，如果大于则判断列是否也在指定的需要的合并单元列集合中
        if (currRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColIndex.length; i++) {
                if (currColIndex == mergeColIndex[i]) {
                    /**
                     * 获取列表数据的唯一标识。不同集合的数据即使数值相同也不合并
                     * 注意：我这里的唯一标识为客户编号（Customer.userCode），在第一列，即下标为0。大家需要结合业务逻辑来做修改
                     */
                    // 获取当前单元格所在的行数据的唯一标识
                    Object currCode = cell.getRow().getCell(0).getStringCellValue();
                    // 获取当前单元格的正上方的单元格所在的行数据的唯一标识
                    Object preCode = cell.getSheet().getRow(currRowIndex - 1).getCell(0).getStringCellValue();
                    // 判断两条数据的是否是同一集合，只有同一集合的数据才能合并单元格
                    if(preCode.equals(currCode)){
                        // 如果都符合条件，则向上合并单元格
                        mergeWithPrevRow(writeSheetHolder, cell, currRowIndex, currColIndex);
                        break;
                    }
                }
            }
        }
    }
}

4.Excel读取处理器

package com.lkx.excel.util.handler;

import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

public class MySheetWriteHandler implements com.alibaba.excel.write.handler.SheetWriteHandler {
    private String date;
    private int lastCol;
    //标题名
    private String titleName;
    public MySheetWriteHandler(String date, int lastCol, String titleName){
        this.date=date;
        this.lastCol=lastCol;
        this.titleName=titleName;
    }
    public MySheetWriteHandler() {

    }


    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
 
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 1280);
        Cell cell = row1.createCell(0);

        //设置标题
        cell.setCellValue(StringUtils.isNotBlank(date) ?
                titleName+ "("+ date +")" : titleName);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 500);
        font.setFontName("宋体");
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, lastCol));

    }
}

5.属性赋值工具类

package com.lkx.excel.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import org.springframework.beans.BeanUtils;

/**
 * @Author: lkx
 * @Date: 2024/6/29/12:38
 * @Description:
 */
public class BeanUtilExt extends BeanUtils {
    public static <k> k copyBeanSuper(Object source,Class<k> target) {
        String s = JSON.toJSONString(source);
        return JSON.parseObject(s,target);
    }

}

6.Excel通用工具类

package com.lkx.excel.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import lombok.extern.slf4j.Slf4j;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import static java.util.stream.Collectors.toList;

@Slf4j
public class ExcelCommonUtil {

  /**
   * 不创建对象的都
   *
   * @param excelInputStream
   * @param fileName
   * @return
   */
  public static List<Map<Integer, String>> readExcel(InputStream excelInputStream,
                                                     String fileName,
                                                     Integer headRowNumber) {
    NoModelDataListener excelListener = new NoModelDataListener();
    ExcelReader excelReader = getReader(excelInputStream, fileName, excelListener,headRowNumber);
    return  getExcelMap(excelListener,excelReader);
  }
  
  /**
  *通过监听器将数据读取到List里的Map中
  */
  public static List<Map<Integer, String>> getExcelMap(NoModelDataListener excelListener,ExcelReader excelReader){
      if (excelReader == null) {
        return new ArrayList<>();
      }
      List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
      for (ReadSheet readSheet : readSheetList) {
        excelReader.read(readSheet);
      }

    return excelListener.getList();
  }


  /**
   * 获取ExcelReader
   *
   * @param excelListener
   */
  private static ExcelReader getReader(InputStream inputStream, String filename, AnalysisEventListener excelListener
  ,Integer index) {
    try {
      if (filename == null ||
          (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
        return null;
      }
      ExcelReader excelReader = EasyExcel.read(inputStream, excelListener)
          .headRowNumber(index)
          .build();
      inputStream.close();
      return excelReader;
    } catch (Exception e) {
      StackTraceElement[] stackTrace = e.getStackTrace();
          String exceptionMsg = "错误类名: "+ stackTrace[0].getClassName()
              +",错误方法名：" +stackTrace[0].getMethodName()
              +",错误行号："+stackTrace[0].getLineNumber()
              +",异常原因"+ e.toString();
          log.error("exceptionMsg = {}",exceptionMsg);
    }
    return null;
  }
  /**
  * 通过反射获取class属性
  **/
  public static Field[] getAllFields(Class<?> clazz) {
    List<Field> fieldList = new ArrayList<>();
    while (clazz != null) {
      fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
      clazz = clazz.getSuperclass();
    }
    Field[] fields = new Field[fieldList.size()];
    return fieldList.toArray(fields);
  }
}

7.文件导入结果集封装

package com.lkx.excel.util;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.List;

/**
 * @Author: lkx
 * @Description: 文件导入返回结果封装
 */
@Data
@ApiModel(description = "Excel文件导入返回结果")
public class ExcelImportResult<T> {

    @ApiModelProperty("导入错误详情")
    private List<String> error;

    @ApiModelProperty("导入错误条数")
    private Integer errorNum;

    @ApiModelProperty("导入成功的数据")
    private List<T> successData;

}

8.Excel导入导出工具类

package com.lkx.excel.util;

import cn.hutool.core.convert.Convert;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.common.collect.Lists;
import com.lkx.excel.util.handler.ExcelMergeCellHandler;
import com.lkx.excel.util.handler.MySheetWriteHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author: lkx
 * @Description:
 */
@Slf4j
public class ExcelUtil {

    /**
     * 导入excel并校验格式
     * @param excelInputStream 文件输入流
     * @param fileName 文件名
     * @param clazz excel类对象
     * @param notNullList 不为空字段下标集合
     * @param numberList 必须为数字字段的下标集合
     * @param dateList 日期格式校验 必须为 yyyy-MM-dd 格式，需要更改验证格式更改正则表达式即可
     * @param distinctMap 需要字段不重复集合 K-V = 字段下标-数据库中该字段的集合
     * @param headRowNumber
     * @param <T>
     * @return
     */
    public static <T> ExcelImportResult<T> indexReadExcel(InputStream excelInputStream,
                                                          String fileName,
                                                          Class<T> clazz,
                                                          List<Integer> notNullList,
                                                          List<Integer> numberList,
                                                          List<Integer> dateList,
                                                          Map<Integer, List<String>> distinctMap,
                                                          Integer headRowNumber) {
        List<Map<Integer, String>> maps = ExcelCommonUtil.readExcel(excelInputStream, fileName ,headRowNumber);

        return getReadExcelEntityFxfx(clazz, notNullList,numberList,dateList,distinctMap, maps);//所有相关功能自动补齐工厂
    }

    public static <T> ExcelImportResult<T> getReadExcelEntityFxfx(Class<T> clazz,
                                                                  List<Integer> notNullList,
                                                                  List<Integer> numberList,
                                                                  List<Integer> dateList,
                                                                  Map<Integer, List<String>> distinctMap,
                                                                  List<Map<Integer, String>> maps) {
        //常规实现excel导入
        ExcelImportResult<T> readExcelEntity = new ExcelImportResult<>();

        // 导入成功的数据集合
        List<T> successData = new ArrayList<>();
        // 错误信息集合
        List<String> errorList = new ArrayList<>();
        // excel实体类属性
        Field[] fields = ExcelCommonUtil.getAllFields(clazz);
        // 第二行开始读取数据 第一行为表格标题
        int row = 2;
        // 错误条数
        int num = 0;
        for (Map<Integer, String> integerStringMap : maps) {
            Map<String, Object> objectMap = new HashMap<>();
            // 当前行数据校验是否通过标识
            boolean flag = true;

            try {
                // 遍历excel实体类属性
                for (Field field : fields) {
                    // 实体类必须有@ExcelProperty注解
                    if (!field.isAnnotationPresent(ExcelProperty.class)) {
                        continue;
                    }
                    // 获取注解中的配置
                    ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                    // 根据下标从读取到的信息中获取表格中的实际值
                    String value = integerStringMap.get(annotation.index());

                    //不为空校验
                    if (notNullList.contains(annotation.index()) && StringUtils.isBlank(value)) {
                        errorList.add("第" + row + "行的[" + annotation.value()[0] + "]不能为空！");
                        flag = false;
                    }
                    //数字检验
                    if (numberList.contains(annotation.index()) && StringUtils.isNotBlank(value) && !NumberUtils.isCreatable(value)) {
                        errorList.add("第" + row + "行的[" + annotation.value()[0] + "]不是一个数字！");
                        flag = false;
                    }

                    // 值重复校验
                    if (distinctMap != null && !distinctMap.isEmpty()) {
                        Set<Integer> keySet = distinctMap.keySet();

                        if (keySet.contains(annotation.index())) {
                            List<String> list = distinctMap.get(annotation.index());
                            if (list.contains(value)) {
                                errorList.add("第" + row + "行的[" + annotation.value()[0] + "]值重复！");
                                flag = false;
                            } else {
                                if (flag) {
                                    list.add(value);
                                    distinctMap.put(annotation.index(),list);
                                }
                            }
                        }
                    }
                    // 日期格式校验
                    if (!CollectionUtils.isEmpty(dateList)) {
                        if (dateList.contains(annotation.index())) {
                            String str = "([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]|[0-9][1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8])))";
                            if (!value.matches(str)) {
                                errorList.add("第" + row + "行的[" + annotation.value()[0] + "]不是一个日期格式！");
                                flag = false;
                            }
                        }
                    }
					// 全部验证通过，将属性字段放入map中
                    if (flag) {
                        objectMap.put(field.getName(), value);
                    }
                }
				// 当且仅当，一整条数据的flag为true时，才算导入成功
                if (flag) {
                    T t = BeanUtilExt.copyBeanSuper(objectMap, clazz);
                    successData.add(t);
                } else {
                    num++;
                }
            } catch (Exception e) {
                log.error("解析数据对象出现错误！", e);
            }

            row++;
        }

        readExcelEntity.setSuccessData(successData);
        readExcelEntity.setError(errorList);
        readExcelEntity.setErrorNum(num);

        return readExcelEntity;
    }

    /**
     * 通用自定义标题头和合并导出
     *
     * @param response            response
     * @param list                数据集合
     * @param fileName            文件名
     * @param sheetName           sheet名
     * @param clazz               导出类
     * @param mergeRowIndex       合并开始的行
     * @param mergeColumnIndex    合并的列
     * @param mySheetWriteHandler 自定义处理器
     */
    public static <T> void writeExcelRow(HttpServletResponse response,
                                         List<T> list,
                                         String fileName,
                                         String sheetName,
                                         Class<T> clazz,
                                         int mergeRowIndex,
                                         int[] mergeColumnIndex, MySheetWriteHandler mySheetWriteHandler) {


        OutputStream outputStream = getOutputStream(response, fileName);
        WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();

        ExcelWriter writer = EasyExcel.write(outputStream, clazz).needHead(true)
                .relativeHeadRowIndex(1)
                .registerWriteHandler(new ExcelMergeCellHandler(mergeColumnIndex, mergeRowIndex))
                .registerWriteHandler(mySheetWriteHandler)
                .build();
        writer.write(list, sheet);
        writer.finish();
    }

    // 验证excel格式
    public static final boolean checkExtension(String extension) {
        return Lists.newArrayList("xls", "xlsx", "XLS", "XLSX").contains(extension);
    }

    /**
     * 导出时生成OutputStream
     */
    private static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
        //创建本地文件
        fileName = fileName + ".xlsx";
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName);
            response.setHeader("myExcel", "202");
            return response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

}

9.swagger配置类

package com.lkx.excel.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.DefaultServletHandlerConfigurer;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

import java.util.Collections;

/**
 * @author lkx
 * @date:
 */
@Configuration
@EnableSwagger2
public class SwaggerConfig implements WebMvcConfigurer {
    /**
     * api接口包扫描路径
     */
    private static final String SWAGGER_SCAN_BASE_PACKAGE = "com.lkx.excel";

    /**
     * 版本号
     */
    private static final String VERSION = "1.0.0";

    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .protocols(Collections.singleton("http"))
                .apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.basePackage(SWAGGER_SCAN_BASE_PACKAGE))
                // 可以根据url路径设置哪些请求加入文档，忽略哪些请求
                .paths(PathSelectors.any())
                .build();
    }

    private ApiInfo apiInfo() {
        String title = "excel - " + "测试" + "模块";
        return new ApiInfoBuilder()
                //设置文档的标题
                .title(title)
                // 设置文档的描述
                .description(title)
                // 设置文档的版本信息-> 1.0.0 Version information
                .version(VERSION)
                .build();
    }

    /**
     * 防止@EnableMvc把默认的静态资源路径覆盖了，手动设置的方式
     *
     * @param registry
     */
    @Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) {
        registry.addResourceHandler("/static/**").addResourceLocations("classpath:/static/");
        registry.addResourceHandler("swagger-ui.html")
            .addResourceLocations("classpath:/META-INF/resources/");
        registry.addResourceHandler("doc.html")
            .addResourceLocations("classpath:/META-INF/resources/");
        registry.addResourceHandler("/webjars/**")
            .addResourceLocations("classpath:/META-INF/resources/webjars/");
        registry.addResourceHandler("doc.html")
            .addResourceLocations("classpath:/META-INF/resources/");

    }

    /**
     * 配置servlet处理
     */
    @Override
    public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
        configurer.enable();
    }

}

10.模拟学生excel实体类

package com.lkx.excel.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


/**
* 学生信息
*/
@Data
@ColumnWidth(20)
@NoArgsConstructor
@AllArgsConstructor
public class StudentInfoExcel {

    @ApiModelProperty("姓名")
    @ExcelProperty(index = 0,value = "姓名")
    private String name;

    @ApiModelProperty("班级")
    @ExcelProperty(index = 1,value = "班级")
    private String className;

    @ApiModelProperty("学号")
    @ExcelProperty(index = 2,value = "学号")
    private String stuNo;

    @ApiModelProperty("籍贯")
    @ExcelProperty(index = 3,value = "籍贯")
    private String nativePlace;

    @ApiModelProperty("民族")
    @ExcelProperty(index = 4,value = "民族")
    private String nation;

    @ApiModelProperty("性别")
    @ExcelProperty(index = 5,value = "性别")
    private String sex;

    @ApiModelProperty("年龄")
    @ExcelProperty(index = 6,value = "年龄")
    private Integer age;

    @ApiModelProperty("家庭住址")
    @ExcelProperty(index = 7,value = "家庭住址")
    private String address;

}

11.controller测试

package com.lkx.excel.controller;

import com.lkx.excel.entity.StudentInfoExcel;
import com.lkx.excel.util.ExcelImportResult;
import com.lkx.excel.util.ExcelUtil;
import com.lkx.excel.util.handler.MySheetWriteHandler;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiModelProperty;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

/**
 * @Author: lkx
 * @Date: 2024/6/29/14:56
 * @Description:
 */
@RestController
@Slf4j
@RequestMapping(value = "/test")
@Api(tags = "excel测试")
public class TestController {

    @ApiModelProperty("导出excel")
    @PostMapping("/exportExcel")
    public void export(HttpServletResponse response) {

        List<StudentInfoExcel> list = new ArrayList<>();

        StudentInfoExcel studentInfoExcel1 = new StudentInfoExcel("小明", "高三一班", "202401001", "四川成都",
                "汉族", "男", 17, "花果山水帘洞1号");
        StudentInfoExcel studentInfoExcel2 = new StudentInfoExcel("小红", "高三二班", "202402001", "四川乐山",
                "汉族", "女", 18, "乐山市乐山大佛");

        list.add(studentInfoExcel1);
        list.add(studentInfoExcel2);

        ExcelUtil.writeExcelRow(response, list, "学生信息表",
                "学生信息表", StudentInfoExcel.class, 1, new int[]{0, 1, 2, 3, 4, 5, 6, 7},
                new MySheetWriteHandler(null, 7, "学生信息表"));
    }

    /**
     * @param file
     * @return
     */
    @PostMapping("/importStudentInfoExcel")
    @ApiOperation("导入数据")
    public ExcelImportResult<StudentInfoExcel> importStudentInfoExcel(MultipartFile file) throws Exception {
        String fileName = file.getOriginalFilename();
        // 文件名称（不含后缀名）
        String type = fileName.indexOf(".") != -1 ? (fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length())) : null;
        if (!ExcelUtil.checkExtension(type)) {
            throw new Exception("文件格式错误！");
        }

        ExcelImportResult<StudentInfoExcel> result = new ExcelImportResult<>();

        // 假设数据库中已经有一些学号数据了
        HashMap<Integer, List<String>> distinctMap = new HashMap<>();
        ArrayList<String> list = new ArrayList<>();
        list.add("202401001");
        list.add("202401002");
        list.add("202401003");
        distinctMap.put(2,list);


        try {
            result = ExcelUtil.indexReadExcel(file.getInputStream(),
                    fileName,
                    StudentInfoExcel.class,
                    new ArrayList<>(Arrays.asList(0,1, 2, 3,4,5,7)),
                    new ArrayList<>(Arrays.asList(6)),
                    null,
                    distinctMap,
                    2);
        } catch (IOException e) {
            log.error("导入出错：{}", e.getMessage());
        }

        return result;
    }
}

12.启动类

package com.lkx.excel;

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

/**
 * @Author: lkx
 * @Date: 2024/6/29/14:41
 * @Description:
 */
@SpringBootApplication
public class ExcelApplication {
    public static void main(String[] args) {
        SpringApplication.run(ExcelApplication.class,args);
    }
}

总结

这里主要是通过自定义监听器 NoModelDataListener 读取数据到map中，然后通过MySheetWriteHandler处理sheet格式，用反射获取实体类属性，然后遍历获取到的数据，再遍历反射时获取到的属性，只要使用的注解@ExcelProperty的，下标在验证集合当中的，进行格式校验。