Easy Excel官方文档：https://www.yuque.com/easyexcel/doc/easyexcel

表格导入导出工具类：EasyExcelUtils

package com.ai.hdl.market.infrastructure.utils.easyexcel;

import com.ai.hdl.base.exception.BusinessException;
import com.ai.hdl.market.infrastructure.constants.NumConsts;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.beust.jcommander.internal.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.math.NumberUtils;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Slf4j
public class EasyExcelUtils {
    /**
     * 表格模板下载
     *
     * @param response 响应
     * @param fileName 文件名
     * @param clazz    模板对象
     * @param       模板对象
     */
    public static  void downloadExcelTemplate(HttpServletResponse response, String fileName, Class clazz) {
        setResponseHeader(response, fileName);
        ExplicitWriteHandler handler = new ExplicitWriteHandler(clazz);
        OutputStream outputStream = getOutputStream(response);
        List data = Lists.newArrayList();
        try {
            EasyExcel.write(outputStream, clazz).registerWriteHandler(handler)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .excelType(ExcelTypeEnum.XLSX).sheet("模板").doWrite(data);
        } catch (Exception e) {
            log.error(fileName + "下载失败：", e);
            throw new BusinessException("模板下载失败！");
        }
    }

    /**
     * 批量导出数据
     *
     * @param response   用于返回数据流
     * @param fileName   文件名称
     * @param         导出数据对应实体对象
     */
    public static  void exportExcel(HttpServletResponse response, String fileName, Class clazz, List dataList) {
        if (!CollectionUtils.isEmpty(dataList)) {
            setResponseHeader(response, fileName);
            OutputStream outputStream = getOutputStream(response);
            ExplicitWriteHandler handler = new ExplicitWriteHandler(clazz);
            EasyExcel.write(outputStream, clazz).registerWriteHandler(handler)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .excelType(ExcelTypeEnum.XLSX).sheet(fileName).doWrite(dataList);
        }
    }

    public static  void exportExcel(HttpServletResponse response, String fileName, Object params, IDataBatch iDataBatch) {
        String iDataBatchClassName = IDataBatch.class.getName();
        Type genericInterfaces = iDataBatch.getClass().getGenericInterfaces()[0];
        if (iDataBatchClassName.equals(genericInterfaces.getTypeName())) {
            throw new BusinessException("请传入导出数据实体对象类型！");
        }
        Class<?> clazz = (Class<?>) ((ParameterizedType) genericInterfaces).getActualTypeArguments()[0];
        setResponseHeader(response, fileName);
        int pageSize = NumConsts.INT_K, ZERO = NumberUtils.INTEGER_ZERO, ONE = NumberUtils.INTEGER_ONE;
        int totalRows = iDataBatch.getTotalRows(params);
        if (totalRows > ZERO) {
            OutputStream outputStream = getOutputStream(response);
            int totalPage = totalRows % pageSize == ZERO ? totalRows / pageSize : (totalRows / pageSize) + ONE;
            WriteSheet writeSheet = EasyExcel.writerSheet(ZERO).build();
            ExplicitWriteHandler handler = new ExplicitWriteHandler(clazz);
            ExcelWriter excelWriter = null;
            try {
                excelWriter = EasyExcel.write(outputStream, clazz)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .registerWriteHandler(handler).excelType(ExcelTypeEnum.XLSX).build();
                for (int pageNum = ONE; pageNum <= totalPage; pageNum++) {
                    List subData = iDataBatch.getBatch(params, pageNum, pageSize);
                    excelWriter.write(subData, writeSheet);
                }
            } finally {
                if (Objects.nonNull(excelWriter)) excelWriter.finish();
            }
        }
    }

    /**
     * 表格数据读取
     *
     * @param file  文件
     * @param clazz 数据对象
     * @param    数据对象
     * @return 数据对象集合
     */
    public static  List readExcel(MultipartFile file, Class clazz) {
        return readExcel(file, clazz, null, null);
    }

    /**
     * 表格数据读取
     *
     * @param file       文件
     * @param clazz      数据对象
     * @param maxRowsNum 最大行数
     * @param         数据对象
     * @return 数据对象集合
     */
    public static  List readExcel(MultipartFile file, Class clazz, Integer maxRowsNum) {
        return readExcel(file, clazz, maxRowsNum, null);
    }

    /**
     * 表格数据读取
     *
     * @param file              文件
     * @param clazz             数据对象
     * @param maxRowsNum        最大行数
     * @param errorMsgFieldName clazz中存放数据错误信息的字段名称
     * @param                数据对象
     * @return 数据对象集合
     */
    public static  List readExcel(MultipartFile file, Class clazz, Integer maxRowsNum, String errorMsgFieldName) {
        List data;
        ExcelListener excelListener = new ExcelListener<>(maxRowsNum, errorMsgFieldName);
        InputStream in = null;
        try {
            in = file.getInputStream();
            ExcelReader excelReader = EasyExcel.read(in, clazz, excelListener).build();
            excelReader.readAll();
            data = excelListener.getRows();
        } catch (IOException e) {
            log.error("文件读取失败：", e);
            throw new BusinessException("文件读取失败！");
        } finally {
            closeInputStream(in);
        }
        if (data == null) {
            data = new ArrayList<>();
        }
        return data;
    }

    private static void setResponseHeader(HttpServletResponse response, String fileName) {
        response.setContentType("multipart/form-data;charset=gbk");
        response.setHeader("Accept-Ranges", "bytes");
        String headerFileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        response.setHeader("Content-Disposition", "attachment;filename=" + headerFileName + ".xlsx");
    }

    private static OutputStream getOutputStream(HttpServletResponse response) {
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            log.error("获取输出流失败：", e);
        }
        if (outputStream == null) {
            throw new BusinessException("获取输出流失败！");
        }
        return outputStream;
    }

    private static void closeInputStream(InputStream in) {
        if (in != null) {
            try {
                in.close();
            } catch (IOException e) {
                log.error("文件输入流关闭失败！");
            }
        }
    }

    public interface IDataBatch {
        int getTotalRows(Object params);

        List getBatch(Object params, int pageNum, int pageSize);
    }
}

package com.ai.hdl.market.infrastructure.utils.easyexcel;

import com.ai.hdl.base.exception.BusinessException;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.data.ReadCellData;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class ExcelListener extends AnalysisEventListener {
    private final List rows = new ArrayList<>();
    private static final Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
    //最大行数
    private static Integer maxRowsNum;
    //bean中存放该条数据错误信息的字段名称
    private String errorMsgFieldName;

    private static final Integer defaultMaxRowsNum = 10000;
    public ExcelListener() {
        maxRowsNum = defaultMaxRowsNum;
    }

    public ExcelListener(Integer num) {
        maxRowsNum = num;
    }

    public ExcelListener(Integer num, String errorMsgFieldName) {
        maxRowsNum = num == null ? defaultMaxRowsNum : num;
        this.errorMsgFieldName = errorMsgFieldName;
    }

    @Override
    public void invokeHead(Map> headMap, AnalysisContext context) {
        List headList = headMap.values().stream().map(ReadCellData :: getStringValue).collect(Collectors.toList());
        log.info("导入表格头:{}", StringUtils.join(headList, "|"));
        int totalRowNumber = context.readSheetHolder().getApproximateTotalRowNumber() - 1;
        log.info("总行数:{}", totalRowNumber);
        if (totalRowNumber <= NumberUtils.INTEGER_ZERO) {
            throw new BusinessException("导入文件无数据！");
        }
        if (totalRowNumber > maxRowsNum) {
            throw new BusinessException("最多导入" + maxRowsNum + "条数据！");
        }
    }

    @Override
    public void invoke(T object, AnalysisContext context) {
        boolean allNull = isAllNullRow(object);
        if (!allNull) {
            if (StringUtils.isNotBlank(errorMsgFieldName)) {
                validateRowData(object);
            }
            rows.add(object);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("表格读取完成,共{}条数据", rows.size());
    }

    public List getRows() {
        return rows;
    }

    private boolean isAllNullRow(T object) {
        boolean allNull = true;
        Field[] fields = object.getClass().getDeclaredFields();
        for (Field field : fields) {
            if ("serialVersionUID".equals(field.getName())) {
                continue;
            }
            field.setAccessible(true);
            try {
                Object val = field.get(object);
                if (Objects.nonNull(val)) {
                    allNull = false;
                    break;
                }
            } catch (IllegalAccessException e) {
                log.error("数据解析失败：{}", e.getMessage());
            }
        }
        return allNull;
    }

    private void validateRowData(T object) {
        if (StringUtils.isNotBlank(errorMsgFieldName)) {
            Field errorField = null;
            try {
                errorField = object.getClass().getDeclaredField(errorMsgFieldName);
            } catch (Exception e) {
                log.error("获取存放错误信息字段[{}]失败!", errorMsgFieldName);
            }
            if (errorField != null) {
                errorField.setAccessible(true);
                String errorMsg = getErrorMsg(object);
                if (StringUtils.isNotBlank(errorMsg)) {
                    try {
                        errorField.set(object, errorMsg);
                    } catch (IllegalAccessException e) {
                        log.error("校验数据时出错：{}", e.getMessage());
                    }
                }
            }
        } else {
            String errorMsg = getErrorMsg(object);
            if (StringUtils.isNotBlank(errorMsg)) {
                throw new BusinessException(errorMsg);
            }
        }
    }

    private String getErrorMsg(T object) {
        String errorMsg = StringUtils.EMPTY;
        Set> set = validator.validate(object);
        if (!CollectionUtils.isEmpty(set)) {
            for (ConstraintViolation cv : set) {
                String fieldErrMsg = cv.getMessage();
                if (StringUtils.isNotBlank(errorMsg)) {
                    errorMsg = errorMsg + "," + fieldErrMsg;
                } else {
                    errorMsg = fieldErrMsg;
                }
            }
        }
        return errorMsg;
    }
}

package com.ai.hdl.market.infrastructure.utils.easyexcel;

import java.lang.annotation.*;

/**
 * 在下载的表给数据对象字段上添加该注解,设置表格下拉框
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExplicitConstraint {
    //定义固定下拉内容
    String[] source() default {};
    //定义动态下拉内容
    Class[] sourceClass() default {};

    /**
     * 动态下拉框实现该接口
     */
    public interface IExplicitInterface {
        /**
         * 下拉列表的内容数组
         * @return String[]
         */
        String[] source();
    }
}

package com.ai.hdl.market.infrastructure.utils.easyexcel;

import com.ai.hdl.market.infrastructure.utils.AppContextUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.hibernate.validator.constraints.Length;

import javax.validation.constraints.Max;
import javax.validation.constraints.Min;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;

public class ExplicitWriteHandler implements SheetWriteHandler {
    private static Class<?> clazz;

    private int lineNum;
    private final List> numClassList = Arrays.asList(Float.class, Double.class, BigDecimal.class, Integer.class,
            Short.class, Byte.class);

    public ExplicitWriteHandler(Class<?> clazz) {
        ExplicitWriteHandler.clazz = clazz;
        this.lineNum = 1000;
    }

    public void lineNum(int num) {
        this.lineNum = num;
    }

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

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        sheet.createFreezePane(0, 1, 0, 1);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        Field[] declaredFields = clazz.getDeclaredFields();
        for (int i = 0; i < declaredFields.length; i++) {
            Field field = declaredFields[i];
            int columnIndex = i;
            //解析注解信息
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            if (excelProperty != null) {
                columnIndex = excelProperty.index();
            }
            ExplicitConstraint explicitConstraint = field.getAnnotation(ExplicitConstraint.class);
            String[] explicitArray = resolveExplicitConstraint(explicitConstraint);
            if (explicitArray != null && explicitArray.length > 0) {
                explicitFormat(columnIndex, explicitArray, sheet, helper);
            }
            ExcelDateFormat format = field.getAnnotation(ExcelDateFormat.class);
            if (format != null) {
                dateCellFormat(columnIndex, format.format(), sheet, helper);
            }
            Class<?> fieldType = field.getType();
            if (numClassList.contains(fieldType)) {
                numberCellValid(columnIndex, field, fieldType, sheet, helper);
            }
            if (fieldType.equals(String.class)) {
                textCellStyle(columnIndex, sheet);
            }
            Length length = field.getAnnotation(Length.class);
            if (length != null) {
                textLengthValid(columnIndex, length.min(), length.max(), sheet, helper);
            }
        }
    }

    private void textCellStyle(Integer columnIndex, Sheet sheet) {
        Workbook book = sheet.getWorkbook();
        CellStyle cellStyle = book.createCellStyle();
        DataFormat dataFormat = book.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
        cellStyle.setWrapText(false);
        sheet.setDefaultColumnStyle(columnIndex, cellStyle);
    }

    private void textLengthValid(Integer columnIndex, Integer min, Integer max, Sheet sheet, DataValidationHelper helper) {
        CellRangeAddressList addressList = new CellRangeAddressList(1, lineNum, columnIndex, columnIndex);
        int opType = DataValidationConstraint.OperatorType.BETWEEN;
        DataValidationConstraint constraint = helper.createTextLengthConstraint(opType, Integer.toString(min), Integer.toString(max));
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        //处理Excel兼容性问题
        setDataValidation(dataValidation, "数据长度超出限制[" + min + "~" + max + "]");
        sheet.addValidationData(dataValidation);
    }

    private void explicitFormat(Integer columnIndex, String[] sources, Sheet sheet, DataValidationHelper helper) {
        // 起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(1, lineNum, columnIndex, columnIndex);
        //设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(sources);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        //处理Excel兼容性问题
        setDataValidation(dataValidation, null);
        sheet.addValidationData(dataValidation);
    }

    private void numberCellValid(Integer columnIndex, Field field, Class<?> clazz, Sheet sheet, DataValidationHelper helper) {
        int opType = DataValidationConstraint.OperatorType.BETWEEN;
        int vType = DataValidationConstraint.ValidationType.INTEGER;
        if (clazz.equals(Float.class) || clazz.equals(Double.class) || clazz.equals(BigDecimal.class)) {
            vType = DataValidationConstraint.ValidationType.DECIMAL;
        }
        Min minAnnotation = field.getAnnotation(Min.class);
        Max maxAnnotation = field.getAnnotation(Max.class);
        String min = minAnnotation == null ? "0" : Long.toString(minAnnotation.value());
        String max = maxAnnotation == null ? "99999999" : Long.toString(maxAnnotation.value());
        CellRangeAddressList addressList = new CellRangeAddressList(1, lineNum, columnIndex, columnIndex);
        DataValidationConstraint constraint = helper.createNumericConstraint(vType, opType, min, max);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        setDataValidation(dataValidation, "请输入正确的数值");
        sheet.addValidationData(dataValidation);
    }

    private void dateCellFormat(Integer columnIndex, String format, Sheet sheet, DataValidationHelper helper) {
        Workbook book = sheet.getWorkbook();
        CellStyle cellStyle = book.createCellStyle();
        short dateFmt = book.createDataFormat().getFormat(format);
        cellStyle.setDataFormat(dateFmt);
        sheet.setDefaultColumnStyle(columnIndex, cellStyle);
        int opType = DataValidationConstraint.OperatorType.BETWEEN;
        String minDate = "Date(1900, 1, 1)";
        String maxDate = "Date(3000, 1, 1)";
        CellRangeAddressList addressList = new CellRangeAddressList(1, lineNum, columnIndex, columnIndex);
        DataValidationConstraint constraint = helper.createDateConstraint(opType, minDate, maxDate, format);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        setDataValidation(dataValidation, "请输入[" + format + "]格式日期");
        sheet.addValidationData(dataValidation);
    }

    private void setDataValidation(DataValidation dataValidation, String errorMsg) {
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
            if (StringUtils.isNotBlank(errorMsg)) {
                dataValidation.createErrorBox("温馨提示", errorMsg);
            }
        } else {
            dataValidation.setSuppressDropDownArrow(false);
            if (StringUtils.isNotBlank(errorMsg)) {
                dataValidation.createPromptBox("温馨提示", errorMsg);
            }
        }
    }


    private String[] resolveExplicitConstraint(ExplicitConstraint explicitConstraint) {
        if (explicitConstraint == null) {
            return null;
        }
        //固定下拉信息
        String[] source = explicitConstraint.source();
        if (source.length <= 0) {
            //动态下拉信息
            @SuppressWarnings("unchecked")
            Class<? extends ExplicitConstraint.IExplicitInterface>[] classes = explicitConstraint.sourceClass();
            if (classes.length > 0) {
                ExplicitConstraint.IExplicitInterface explicitInterface = AppContextUtil.getBean(classes[0]);
                source = explicitInterface.source();
            }
        }
        if (source == null || source.length <= 0) {
            source = null;
        }
        return source;
    }

}