Excel导入功能接口

ゝ一世哀愁。 2024-02-18 20:48 62阅读 0赞

## **一、通过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）业务逻辑处理**