EasyExcel

只会一点点Java

已于 2025-02-11 09:44:30 修改

阅读量365

点赞数 4

文章标签： python windows 开发语言

于 2025-02-11 09:39:58 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_46570489/article/details/145564336

版权

EasyExcel

1.pom文件

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

2.引入文件

2.1SheetName

package com.example.demo.utils;

import java.lang.annotation.*;

/**
 * sheet名称
 * @author
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(value={ElementType.TYPE})
@Documented
@Inherited
public @interface SheetName {
    String value() default "";
}

2.2MyBeanCopy

package com.example.demo.utils;

import org.springframework.beans.*;
import org.springframework.beans.propertyeditors.CustomDateEditor;
import org.springframework.core.convert.ConversionService;
import org.springframework.core.convert.converter.Converter;
import org.springframework.core.convert.support.DefaultConversionService;
import org.springframework.core.convert.support.GenericConversionService;
import org.springframework.util.Assert;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

public class MyBeanCopy {

    private final class DateToStringConverter implements Converter<Date, String> {
        private DateFormat df;

        private DateToStringConverter(String format) {
            df = new SimpleDateFormat(format);
        }

        @Override
        public String convert(Date source) {

            return df.format(source);
        }
    }

    private static final String DATE_FORMAT = "yyyy-MM-dd";

    private static Object convertForProperty(Wrapper wrapper, Object object, Object value, String propertyName)
            throws TypeMismatchException {
        Object result;
        if (wrapper == null) {
            result = null;
        } else {
            wrapper.setWrappedInstance(object);
            result = wrapper.getBeanWrapper().convertForProperty(value, propertyName);
        }
        return result;
    }

    private static Object copyProperties(Object source, Object target) throws BeansException {
        Wrapper wrapper = new MyBeanCopy().new Wrapper(source);
        copyProperties(wrapper, source, target);
        return target;
    }

    /**
     * Copy the property values of the given source bean into the target bean.
     * <p>
     * Note: The source and target classes do not have to match or even be derived from each other, as long as the properties match. Any bean
     * properties that the source bean exposes but the target bean does not will silently be ignored.
     * <p>
     * This is just a convenience method. For more complex transfer needs, consider using a full BeanWrapper.
     *
     * @param source the source bean
     * @param target the target bean
     * @throws BeansException if the copying failed
     */
    private static void copyProperties(Wrapper wrapper, Object source, Object target) throws BeansException {
        Assert.notNull(source, "Source must not be null");
        Assert.notNull(target, "Target must not be null");

        Class<?> actualEditable = target.getClass();
        PropertyDescriptor[] targetPds = BeanUtils.getPropertyDescriptors(actualEditable);

        for (PropertyDescriptor targetPd : targetPds) {
            if (targetPd.getWriteMethod() != null) {
                PropertyDescriptor sourcePd = BeanUtils.getPropertyDescriptor(source.getClass(), targetPd.getName());
                if (sourcePd != null && sourcePd.getReadMethod() != null) {
                    try {
                        Method readMethod = sourcePd.getReadMethod();
                        if (!Modifier.isPublic(readMethod.getDeclaringClass().getModifiers())) {
                            readMethod.setAccessible(true);
                        }
                        Object value = readMethod.invoke(source);
                        // 判断是否类型不一致
                        if (value != null && !(targetPd.getPropertyType().isInstance(value))) {
                            // 数据转型
                            value = convertForProperty(wrapper, target, value, targetPd.getName());
                        }
                        Method writeMethod = targetPd.getWriteMethod();
                        if (!Modifier.isPublic(writeMethod.getDeclaringClass().getModifiers())) {
                            writeMethod.setAccessible(true);
                        }
                        writeMethod.invoke(target, value);
                    } catch (Exception ex) {
                        throw new FatalBeanException("Could not copy properties from source to target", ex);
                    }
                }
            }
        }

    }

    private final class Wrapper {

        private GenericConversionService conversion;
        private BeanWrapperImpl bean;

        private Wrapper(Object object) {
            conversion = initDefaultConversionService();
            bean = initDefaultBeanWrapper(conversion, object);
        }

        private void setWrappedInstance(Object object) {
            bean.setWrappedInstance(object);
        }

        private GenericConversionService initDefaultConversionService() {
            GenericConversionService conversionService = new DefaultConversionService();
            conversionService.addConverter(new DateToStringConverter(DATE_FORMAT));
            return conversionService;
        }

        private BeanWrapperImpl initDefaultBeanWrapper(@SuppressWarnings("hiding") ConversionService conversion,
                                                       Object object) {
            BeanWrapperImpl beanWrapper = new BeanWrapperImpl(object);
            beanWrapper.setConversionService(conversion);
            SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT);
            dateFormat.setLenient(false);
            beanWrapper.registerCustomEditor(Date.class, new CustomDateEditor(dateFormat, true));
            return beanWrapper;
        }

        private BeanWrapperImpl getBeanWrapper() {
            return bean;
        }
    }

    /**
     * 复制源对象到目的对象
     */
    private static void convert(Object source, Object target) {
        copyProperties(source, target);
    }

    public static <T> List<T> convert(List<?> sources, Class<T> targetClass) {
        List<?> sourcesObj = sources;
        if (sourcesObj == null) {
            sourcesObj = Collections.emptyList();
        }
        List<T> targets = new ArrayList<>(sourcesObj.size());
        MyBeanCopy.convert(sourcesObj, targets, targetClass);
        return targets;
    }

    private static <T> void convert(List<?> sources, List<T> targets, Class<T> targetClass) {
        if (targets == null) {
            return;
        }
        targets.clear();
        if (sources == null) {
            return;
        }
        for (Object obj : sources) {
            try {
                T target = targetClass.newInstance();
                targets.add(target);
                convert(obj, target);
            } catch (Exception e) {
                //do something
                return;
            }
        }
    }
}

2.3ExcelListener

package com.example.demo.utils;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author
 */
public class ExcelListener<T> extends AnalysisEventListener<T> {

    private final List<Object> dataList = new ArrayList<>();

    private static final Integer maxSize = 10000;

    private Class<T> rowModel;

    public ExcelListener(Class<T> rowModel) {
        this.rowModel = rowModel;
    }

    /**
     * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
     */
    @Override
    public void invoke(T object, AnalysisContext context) {
        if(!checkObjAllFieldsIsNull(object)) {
            dataList.add(object);
        }
        if (dataList.size() > maxSize) {
            throw new RuntimeException("最大只能导入10000行数据");
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //do something
    }

    private static final String SERIAL_VERSION_UID = "serialVersionUID";

    /**
     * 判断对象中属性值是否全为空
     */
    private static boolean checkObjAllFieldsIsNull(Object object) {
        if (null == object) {
            return true;
        }
        try {
            for (Field f : object.getClass().getDeclaredFields()) {
                f.setAccessible(true);
                //只校验带ExcelProperty注解的属性
                ExcelProperty property = f.getAnnotation(ExcelProperty.class);
                if(property == null || SERIAL_VERSION_UID.equals(f.getName())){
                    continue;
                }
                if (f.get(object) != null && EasyExcelUtil.isNotBlank(f.get(object).toString())) {
                    return false;
                }
            }
        } catch (Exception e) {
            //do something
        }
        return true;
    }

    public List<?> getDataList() {
        return dataList;
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        Field[] fields = rowModel.getDeclaredFields();
        int length = fields.length;
        List<String> headNameList = new ArrayList<>();
        for (int i = 0; i < length; i++) {
            ExcelProperty annotation = fields[i].getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                headNameList.add(annotation.value()[0]);
            }
        }
        int size = headNameList.size();
        if (headMap.size() != size) {
            throw new RuntimeException("导入模板错误！请下载正确的导入模板");
        }
        for (int i = 0; i < size; i++) {
            if (!headNameList.get(i).equals(headMap.get(i))) {
                throw new RuntimeException("导入模板错误！请下载正确的导入模板");
            }
        }

    }
}

2.4EasyExcelUtil

package com.joyoung.logistics.workbench.basic.util;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.joyoung.logistics.workbench.basic.common.exception.AppException;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * ExcelUtil
 * 基于easyExcel的开源框架，poi版本3.17
 * BeanCopy AppException 属于自定义数据，属于可自定义依赖
 * 工具类尽可能还是需要减少对其他java的包的依赖
 *
 * @author
 */
public class EasyExcelUtil {
    /**
     * 私有化构造方法
     */
    private EasyExcelUtil() {
    }

    /**
     * 读取 Excel(多个 sheet)
     */
    public static <T> List<T> readExcel(ExcelReader reader, Class<T> rowModel, int sheetCount) {
        if (reader == null) {
            return new ArrayList<>();
        }
        List<ReadSheet> readSheetList = new ArrayList<>();
        ExcelListener<T> excelListener = new ExcelListener<>(rowModel);
        ReadSheet readSheet = EasyExcel.readSheet(sheetCount)
                .head(rowModel)
                .registerReadListener(excelListener)
                .build();
        readSheetList.add(readSheet);
        reader.read(readSheetList);
        return getExtendsBeanList(excelListener.getDataList(), rowModel);
    }

    /**
     * 读取 Excel(多个 sheet)
     * 将多sheet合并成一个list数据集，通过自定义ExcelReader继承AnalysisEventListener
     * 重写invoke doAfterAllAnalysed方法
     * getExtendsBeanList 主要是做Bean的属性拷贝 ，可以通过ExcelReader中添加的数据集直接获取
     *
     * @param excel    文件
     * @param rowModel 实体类映射
     */
    private static List[] readExcel(MultipartFile excel, Integer sheetNo, Class<?>[] rowModel) {
        ExcelReader reader = getReader(excel);
        if (reader == null) {
            return new ArrayList[rowModel.length];
        }
        List[] result = new ArrayList[rowModel.length];
        for (int sheetCount = 0; sheetCount < rowModel.length; sheetCount++) {
            if (sheetNo != null && sheetNo != sheetCount) {
                continue;
            }
            result[sheetCount].addAll(readExcel(reader, rowModel[sheetCount], sheetCount));
        }
        return result;
    }

    /**
     * 读取 Excel(多个 sheet)
     * 将多sheet合并成一个list数据集，通过自定义ExcelReader继承AnalysisEventListener
     * 重写invoke doAfterAllAnalysed方法
     * getExtendsBeanList 主要是做Bean的属性拷贝 ，可以通过ExcelReader中添加的数据集直接获取
     *
     * @param excel    文件
     * @param rowModel 实体类映射
     */
    public static List[] readExcel(MultipartFile excel, Class<?>... rowModel) {
        ExcelReader reader = getReader(excel);
        if (reader == null) {
            return new ArrayList[rowModel.length];
        }
        List[] result = new ArrayList[rowModel.length];
        for (int sheetCount = 0; sheetCount < rowModel.length; sheetCount++) {
            result[sheetCount] = new ArrayList<>(readExcel(reader, rowModel[sheetCount], sheetCount));
        }
        return result;
    }

    /**
     * 读取 Excel(单个 sheet)
     * 将多sheet合并成一个list数据集，通过自定义ExcelReader继承AnalysisEventListener
     * 重写invoke doAfterAllAnalysed方法
     * getExtendsBeanList 主要是做Bean的属性拷贝 ，可以通过ExcelReader中添加的数据集直接获取
     */
    public static <T> List<T> readFirstSheetExcel(MultipartFile excel, Class<T> rowType) {
        ExcelReader reader = getReader(excel);
        if (reader == null) {
            return new ArrayList<>();
        }
        return readExcel(reader, rowType, 0);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static <T> List readExcel(MultipartFile excel, Class<T> rowModel, int sheetNo) {
        Class[] classes = {rowModel};
        return EasyExcelUtil.readExcel(excel, sheetNo, classes)[0];
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     * 自定义WriterHandler 可以定制行列数据进行灵活化操作
     *
     * @param response  HttpServletResponse
     * @param list      数据 list
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     */
    public static <T> void writeExcel(HttpServletResponse response, List<T> list,
                                      String fileName, String sheetName, ExcelTypeEnum excelTypeEnum) {
        if (sheetName == null || "".equals(sheetName)) {
            sheetName = "sheet1";
        }
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        EasyExcel.write(getOutputStream(fileName, response, excelTypeEnum), list.get(0).getClass()).sheet(sheetName).doWrite(list);
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     * 自定义WriterHandler 可以定制行列数据进行灵活化操作
     *
     * @param response HttpServletResponse
     * @param list     数据 list
     * @param fileName 导出的文件名
     */
    public static <T> void writeExcel(HttpServletResponse response, List<T> list,
                                      String fileName, ExcelTypeEnum excelTypeEnum) {
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        String sheetName = list.get(0).getClass().getAnnotation(SheetName.class).value();
        sheetName = EasyExcelUtil.isNotBlank(sheetName) ? sheetName : "sheet1";
        EasyExcel.write(getOutputStream(fileName, response, excelTypeEnum), list.get(0).getClass()).sheet(sheetName).doWrite(list);
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     * 自定义WriterHandler 可以定制行列数据进行灵活化操作
     *
     * @param response HttpServletResponse
     * @param list     数据 list
     * @param fileName 导出的文件名
     */
    public static <T> void writeExcelPrint(HttpServletResponse response, List<T> list,
                                      String fileName, ExcelTypeEnum excelTypeEnum) {
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        String sheetName = list.get(0).getClass().getAnnotation(SheetName.class).value();
        sheetName = EasyExcelUtil.isNotBlank(sheetName) ? sheetName : "sheet1";
        EasyExcel.write(getOutputStreamPrint(fileName, response, excelTypeEnum), list.get(0).getClass()).sheet(sheetName).doWrite(list);
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     * 自定义WriterHandler 可以定制行列数据进行灵活化操作
     *
     * @param response HttpServletResponse
     * @param fileName 导出的文件名
     */
    public static void writeExcel(HttpServletResponse response, String fileName,
                                  ExcelTypeEnum excelTypeEnum, List... lists) {
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(getOutputStream(fileName, response, excelTypeEnum)).build();
            for (int count = 0; count < lists.length; count++) {
                if (CollectionUtils.isEmpty(lists[count])) {
                    continue;
                }
                String sheetName = lists[count].get(0).getClass().getAnnotation(SheetName.class).value();
                sheetName = EasyExcelUtil.isNotBlank(sheetName) ? sheetName : "sheet" + (count + 1);
                WriteSheet writeSheet = EasyExcel.writerSheet(count, sheetName)
                        .head(lists[count].get(0).getClass())
                        .build();
                excelWriter.write(lists[count], writeSheet);
            }
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

    }


    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response, ExcelTypeEnum excelTypeEnum) {
        //创建本地文件
        String filePath = fileName + excelTypeEnum.getValue();
        try {
//            response.setContentType("application/vnd.ms-excel");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filePath, "UTF-8"));
            return response.getOutputStream();
        } catch (IOException e) {
            throw new AppException("创建文件失败！");
        }
    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStreamPrint(String fileName, HttpServletResponse response, ExcelTypeEnum excelTypeEnum) {
        //创建本地文件
        String filePath = fileName + excelTypeEnum.getValue();
        try {
            response.setContentType("application/vnd.ms-excel");
            //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filePath, "UTF-8"));
            return response.getOutputStream();
        } catch (IOException e) {
            throw new AppException("创建文件失败！");
        }
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel 需要解析的 Excel 文件
     */
    public static ExcelReader getReader(MultipartFile excel) {
        String fileName = excel.getOriginalFilename();
        if (fileName == null) {
            throw new AppException("文件格式错误！");
        }
        if (!fileName.toLowerCase().endsWith(ExcelTypeEnum.XLS.getValue()) && !fileName.toLowerCase().endsWith(ExcelTypeEnum.XLSX.getValue())) {
            throw new AppException("文件格式错误！");
        }
        InputStream inputStream;
        try {
            inputStream = excel.getInputStream();
            return EasyExcel.read(inputStream).build();
        } catch (IOException e) {
            //do something
        }
        return null;
    }

    /**
     * 利用BeanCopy转换list
     */
    public static <T> List<T> getExtendsBeanList(List<?> list, Class<T> typeClazz) {
        return MyBeanCopy.convert(list, typeClazz);
    }

    private static boolean isBlank(final CharSequence cs) {
        int strLen;
        if (cs == null || (strLen = cs.length()) == 0) {
            return true;
        }
        for (int i = 0; i < strLen; i++) {
            if (!Character.isWhitespace(cs.charAt(i))) {
                return false;
            }
        }
        return true;
    }

    public static boolean isNotBlank(final CharSequence cs) {
        return !isBlank(cs);
    }



	/**
	 * 导出 Excel ：一个 sheet，带表头
	 *
	 *
	 * @param response  HttpServletResponse
	 * @param list      数据 list
	 * @param fileName  导出的文件名
	 * @param writeHandler  自定义WriterHandler 可以定制行列数据进行灵活化操作
	 */
	public static <T> void writeExcelWithWriteHandler(HttpServletResponse response, List<T> list,
											  String fileName,ExcelTypeEnum excelTypeEnum, WriteHandler writeHandler) {
		if (CollectionUtils.isEmpty(list)) {
			return;
		}
		String sheetName = list.get(0).getClass().getAnnotation(SheetName.class).value();
		if (sheetName == null || "".equals(sheetName)) {
			sheetName = "sheet1";
		}
		EasyExcel.write(getOutputStream(fileName, response, excelTypeEnum), list.get(0).getClass()).sheet(sheetName).registerWriteHandler(writeHandler).doWrite(list);
	}
}

2.5LeftAlignCellWriteHandler（自定义样式）

package com.dahuatech.dms.utils;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Workbook;

import java.util.List;

public class LeftAlignCellWriteHandler implements CellWriteHandler {

    @Override
    public int order() {
        return 50001;
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            // 获取Workbook对象
            WriteWorkbookHolder writeWorkbookHolder = writeSheetHolder.getParentWriteWorkbookHolder();
            Workbook workbook = writeWorkbookHolder.getWorkbook();
            // 创建一个新的CellStyle
            CellStyle cellStyle = workbook.createCellStyle();
            // 设置水平对齐为左对齐
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            // 设置自动换行
            cellStyle.setWrapText(true);
            // 应用CellStyle到当前单元格
            cell.setCellStyle(cellStyle);
    }

}

3.使用

3.1下载导入模板

/**
 * 下载导入模板
 *
 * @param
 * @return
 */
@ApiResponse(description = "下载导入模板", content = @Content(mediaType = "application/json"))
@GetMapping("/importTemplate")
public void importTemplate(HttpServletResponse response){
    String fileName = "货主导入模板";
    List<SysCargoOwnerTemplate> sysCargoOwnerTemplates = new ArrayList<>();
    sysCargoOwnerTemplates.add(new SysCargoOwnerTemplate());
    EasyExcelUtil.writeExcel(response, sysCargoOwnerTemplates, fileName, ExcelTypeEnum.XLSX);
}

3.2导出

/**
 * 导出
 */
@ApiResponse(description = "导出", content = @Content(mediaType = "application/json"))
@GetMapping("/export")
public void export(HttpServletResponse response,SysCargoOwnerDTO sysCargoOwnerDTO)  {
    String param = JSON.toJSONString(sysCargoOwnerDTO);
    List<SysCargoOwner> export = sysCargoOwnerService.export(sysCargoOwnerDTO);
    List<SysCargoOwnerExcel> excels = DozerUtil.convertList(export, SysCargoOwnerExcel.class);
    EasyExcelUtil.writeExcel(response, excels, "货主导出", ExcelTypeEnum.XLSX);
}

3.3导入

/**
 * 导入
 *
 * @param
 * @return
 */
@ApiResponse(description = "导入", content = @Content(mediaType = "application/json"))
@PostMapping("/imports")
@ResponseBody
public Result<Boolean> imports(MultipartHttpServletRequest request) {
    Iterator<String> itr = request.getFileNames();
    String uploadedFile = itr.next();
    List<MultipartFile> files = request.getFiles(uploadedFile);
    if (CollectionUtils.isEmpty(files)) {
        return Result.error("文件为空");
    }
    // 1.读取第一个sheet文件
    List<SysCargoOwnerTemplate> list = EasyExcelUtil.readFirstSheetExcel(files.get(0), SysCargoOwnerTemplate.class);
    if (list.size() == 0) {
        return Result.error( "读取数据为空!");
    }
    return Result.success(sysCargoOwnerService.imports(list));
}

3.4导入特殊需求

3.4.1需求：excel的第一行是文字备注，第二行是标题，要求删除或不删除备注都可以导入成功。

3.4.2实现Controller层

/**
     * 导入
     *
     * @param
     * @return
     */
    @PostMapping("/imports")
    @ResponseBody
    public Boolean imports(MultipartHttpServletRequest request) throws IOException {
        Iterator<String> itr = request.getFileNames();
        String uploadedFile = itr.next();
        List<MultipartFile> files = request.getFiles(uploadedFile);
        if (CollectionUtils.isEmpty(files)) {
            throw new RuntimeException("aa");
        }
        MultipartFile file = files.get(0);
        int startRow = checkTitleRow(file); // 检查哪一行包含 "id"
        // 1.读取第一个sheet文件
        List<entiten2> list = EasyExcelUtil.readFirstSheetExcel(files.get(0), entiten2.class,startRow ,startRow);
        if (list.size() == 0) {
            throw new RuntimeException("aa");
        }
        System.out.println(list);
        return true;
    }

    /**
     * 通过监听器判断标题行是第一行还是第二行
     * @param excel
     * @return
     * @throws IOException
     */
    public static int checkTitleRow(MultipartFile excel) throws IOException {
        ExcelHeadListener listener = new ExcelHeadListener();
        EasyExcel.read(excel.getInputStream(), entiten2.class, listener)
                .headRowNumber(1) // 检查前两行
                .sheet().doRead();
        return listener.isIdFound() ? 1 : 2; // 如果第一行找到id，返回1；否则返回2
    }

3.4.3ExcelHeadListener 监听标题在第几行

package com.example.demo.utils;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.demo.entiten2;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author
 */
public class ExcelHeadListener<T> extends AnalysisEventListener<entiten2> {

    private boolean idFound = false;

    @Override
    public void invoke(entiten2 data, AnalysisContext context) {
        // 这个方法在这里不会被调用，因为我们只关心标题行
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        if (!idFound) {
            //获取excel第一行数据判断第一行是否是表头
            for (String head : headMap.values()) {
                if ("id".equals(head)) {      // 通过标题名判断，是否是标题行
                    idFound = true;
                    break;
                }
            }
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // Do nothing
    }

    public boolean isIdFound() {
        return idFound;
    }
}

3.4.4在工具类EasyExcelUtil中添加新的实现readFirstSheetExcel方法

    public static <T> List<T> readFirstSheetExcel(MultipartFile excel, Class<T> rowType,int headRowNumber,int startRow) {
        ExcelReader reader = getReader(excel);
        if (reader == null) {
            return new ArrayList<>();
        }
        return readExcel(reader, rowType, 0,headRowNumber,startRow);//headRowNumber是从excel第几行开始读标题，startRow是监听校验标题字段从第行开始
    }

3.4.5在工具类EasyExcelUtil中添加新的实现readExcel方法

    public static <T> List<T> readExcel(ExcelReader reader, Class<T> rowModel, int sheetNo, int headRowNumber,int startRow) {
        if (reader == null) {
            return new ArrayList<>();
        }
        ExcelListener<T> excelListener = new ExcelListener<>(rowModel,startRow);
        reader.read(new ArrayList<ReadSheet>() {{
            add(EasyExcel.readSheet(sheetNo)
                    .headRowNumber(headRowNumber) // 从第几行开始读数据
                    .head(rowModel)
                    .registerReadListener(excelListener)
                    .build());
        }});
        return getExtendsBeanList(excelListener.getDataList(), rowModel);
    }

3.4.6在ExcelListener监听中添加判断标题的属性，和构造方法，获取标题行

package com.example.demo.utils;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author
 */
public class ExcelListener<T> extends AnalysisEventListener<T> {

    private final List<Object> dataList = new ArrayList<>();

    private static final Integer maxSize = 10000;

    private Class<T> rowModel;

    private int startRow = 1; //改动位置

    public ExcelListener(Class<T> rowModel) {
        this.rowModel = rowModel;
    }

    /**
     * 改动位置，用来获取导入的标题行
     * @param rowModel
     * @param startRow
     */
    public ExcelListener(Class<T> rowModel,int startRow) {
        this.rowModel = rowModel;
        this.startRow = startRow;
    }

    /**
     * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
     */
    @Override
    public void invoke(T object, AnalysisContext context) {


        if(!checkObjAllFieldsIsNull(object)) {
            dataList.add(object);
        }
        if (dataList.size() > maxSize) {
            throw new RuntimeException("最大只能导入10000行数据");
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //do something
    }

    private static final String SERIAL_VERSION_UID = "serialVersionUID";

    /**
     * 判断对象中属性值是否全为空
     */
    private static boolean checkObjAllFieldsIsNull(Object object) {
        if (null == object) {
            return true;
        }
        try {
            for (Field f : object.getClass().getDeclaredFields()) {
                f.setAccessible(true);
                //只校验带ExcelProperty注解的属性
                ExcelProperty property = f.getAnnotation(ExcelProperty.class);
                if(property == null || SERIAL_VERSION_UID.equals(f.getName())){
                    continue;
                }
                if (f.get(object) != null && EasyExcelUtil.isNotBlank(f.get(object).toString())) {
                    return false;
                }
            }
        } catch (Exception e) {
            //do something
        }
        return true;
    }

    public List<?> getDataList() {
        return dataList;
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        int rowIndex = context.readRowHolder().getRowIndex();  //改动位置
        if (rowIndex == startRow - 1) {                         //改动位置
            Field[] fields = rowModel.getDeclaredFields();
            int length = fields.length;
            List<String> headNameList = new ArrayList<>();
            for (int i = 0; i < length; i++) {
                ExcelProperty annotation = fields[i].getAnnotation(ExcelProperty.class);
                if (annotation != null) {
                    headNameList.add(annotation.value()[0]);
                }
            }
            int size = headNameList.size();
            if (headMap.size() != size) {
                throw new RuntimeException("导入模板错误！请下载正确的导入模板");
            }
            for (int i = 0; i < size; i++) {
                if (!headNameList.get(i).equals(headMap.get(i))) {
                    throw new RuntimeException("导入模板错误！请下载正确的导入模板");
                }
            }
        }   //改动位置
    }
}

3.4.7（注意：需要创新新的对应标题行的实体类，下面是例子）

package com.example.demo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.example.demo.utils.SheetName;
import lombok.Data;

@Data
@SheetName("moban")
public class entiten2 {
    private static final String aaa = "biatossa";

//    @ExcelProperty({aaa,"id"})
    @ExcelProperty("id")
    private String id;
//    @ExcelProperty({aaa,"name"})
    @ExcelProperty("name")
    private String name;
//    @ExcelProperty({aaa,"age"})
    @ExcelProperty("age")
    private String age;
}