Excel导入功能接口

一、通过easyexcel实现数据导入

1、简单实现数据导入

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.event.SyncReadListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.meirit.dong.excel.entity.ProductImportEntity;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;

/**
 * @author dxj
 * @since 2022-12-14 11:15
 */
@Service
public class ImportExceltService {
    public List<ProductImportEntity> importExcel(MultipartFile multipartFile) throws IOException {
        SyncReadListener syncReadListener = new SyncReadListener();
        EasyExcel.read(multipartFile.getInputStream(), ProductImportEntity.class, syncReadListener).sheet().doRead();
        List<Object> list = syncReadListener.getList();
        List<ProductImportEntity> products = JSONArray.parseArray(JSON.toJSONString(list), ProductImportEntity.class);
        return products;
    }
}

2、具备信息验证功能的Excel导入

   1）导入实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import org.hibernate.validator.constraints.Length;

import javax.validation.constraints.Max;
import javax.validation.constraints.NotNull;

/**
 * @author dxj
 * @since 2022-12-14 13:48
 */
@Data
public class ProductImportEntity {
    @ExcelProperty(value = {"姓名"},order = 1)
    @ColumnWidth(value = 20)
    @Length(message = "姓名长度超过限制长度",max = 20)
    @NotNull(message = "姓名不能为空")
    private String name;

    @ExcelProperty(value = {"类型"},order = 2)
    @ColumnWidth(value = 20)
    @Length(message = "类型长度超过限制长度",max = 20)
    @NotNull(message = "类型不能为空")
    private String type;

    @ExcelProperty(value = {"价格"},order = 3)
    @ColumnWidth(value = 20)
    @Max(message = "价格大小超过限制",value = 9999)
    private Long price;

    @ExcelProperty(value = {"地址"},order = 4)
    @ColumnWidth(value = 40)
    @Length(message = "地址长度超过限制长度",max = 1000)
    private String address;

    @ExcelIgnore
    private String description;
}

@ExcelProperty中的value就是表头字段值，index指的是具体Excel的列数，从1开始@ColumnWidth 是定义列宽
@Length 注解，message用来提示信息，max为导入值限制
@NotNull 注解，非空校验

 2）导入监听类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.google.common.collect.Maps;
import com.meirit.dong.excel.entity.ImportExcelModel;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.ReflectionUtils;

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

/**
 * excel导入监听
 *
 * @author dongxiajun
 * @since 2022-12-16 17:40
 */
@Data
@NoArgsConstructor
public class EasyExcelListener<T extends ImportExcelModel> extends AnalysisEventListener<T> {
    private static final String ERROR_TIP = "第%d行，第%d列类型有误";
    List<T> list = Lists.newArrayList();
    List<T> failList = Lists.newArrayList();
    boolean isSuccess = false;
    Class aClass;

    public EasyExcelListener(Class aClass) {
        super();
        this.aClass = aClass;
    }

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        t.setRowNum(analysisContext.readRowHolder().getRowIndex());
        list.add(t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        isSuccess = true;
    }

    /**
     * 异常处理
     *
     * @param exception exception
     * @param context   context
     * @throws Exception Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        isSuccess = false;
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            ImportExcelModel importExcelModel = new ImportExcelModel();
            importExcelModel.setRowNum(context.readRowHolder().getRowIndex());
            importExcelModel.setErrorMsg(String.format(ERROR_TIP, excelDataConvertException.getRowIndex() + 1, excelDataConvertException.getColumnIndex() + 1));
        }
        super.onException(exception, context);
    }

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        super.invokeHead(headMap, context);
        try {
            Map<Integer, String> excelHeadMap = getExcelHeadMap();
            for (Integer key : headMap.keySet()) {
                String value = headMap.get(key).getStringValue();
                if (StringUtils.isEmpty(value) || !StringUtils.equals(value, excelHeadMap.get(key))) {
                    throw new ExcelAnalysisException("excel 表头有误");
                }
            }
        } catch (NoSuchFieldException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    public Map<Integer, String> getExcelHeadMap() throws NoSuchFieldException {
        Field[] fields = aClass.getDeclaredFields();
        Map<Integer, String> excelHeadMap = Maps.newTreeMap();
        for (int i = 0; i < fields.length; ++i) {
            Field field = aClass.getDeclaredField(fields[i].getName());
            ReflectionUtils.makeAccessible(field);
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            if (excelProperty != null) {
                int order = excelProperty.order();
                String[] values = excelProperty.value();
                excelHeadMap.put(order, String.join("", values));
            }
        }
        return excelHeadMap;
    }

}

3）业务逻辑处理