Java EasyExcel

最新推荐文章于 2024-04-01 17:11:09 发布

多云转晴%

最新推荐文章于 2024-04-01 17:11:09 发布

阅读量141

点赞数

分类专栏： JAVA STUDY 文章标签： java 开发语言

本文链接：https://blog.csdn.net/weixin_50330544/article/details/129138445

版权

JAVA STUDY 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

ExcelListener

package com.etl.appeal.common.fileutil;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.etl.commons.exception.BusinessException;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

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

@Slf4j
//@DependsOn("springContextUtils")
public class ExcelListener<T> extends AnalysisEventListener<T> {

    /**
     * 默认给一万 过大效率会变低
     */
    private static final int BATCH_COUNT = 10000;

    /**
     * 是否保存数据
     */
    Boolean saveData = false;
    /**
     * 是否仅获取表头
     */
    Boolean hasnext = true;
    List<String> headList = new ArrayList<>();

    //excel对象的反射类
    private Class<T> clazz;

    List<T> dataList = new ArrayList<>();

    List<String> messageList = new ArrayList<>();


    public ExcelListener() {
    }

    public ExcelListener(Boolean onlyhead, Class<T> clazz) {
        this.hasnext = !onlyhead;
        this.clazz = clazz;
    }

    @SneakyThrows
    @Override
    public void invoke(T data, AnalysisContext context) {
        //校验导入excel的字段长度
        String errMsg = "";
        try {
            //根据excel数据实体中的javax.validation + 正则表达式来校验excel数据
            errMsg = EasyExcelValidHelper.validateEntity(data);
            Field declaredField = data.getClass().getDeclaredField("errorMessage");
            if (declaredField != null) {
                declaredField.setAccessible(true);
                declaredField.set(data, StringUtils.isNotBlank(errMsg) ? errMsg : "");
                declaredField.setAccessible(false);
            }
        } catch (NoSuchFieldException e) {
            log.error("解析数据出错:{}", e.getMessage());
        }
        if (StringUtils.isNotBlank(errMsg)) {
            messageList.add(errMsg);
        }
        //log.info("解析到一条数据:{}", JSON.toJSONString(data));
        dataList.add(data);
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return hasnext;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (saveData) {
            // 把最后的数据统一保存起来 有可能数据不是BATCH_COUNT的整数倍
            saveData();
        }
    }

    /**
     * 读取表头 考虑表头信息如何处理
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) throws BusinessException {
/*        Stopwatch stopwatch = Stopwatch.createStarted();
        log.info("check 表头数据检查begin:{}", headMap);*/
        for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
            headList.add(entry.getValue());
        }
        if (clazz != null) {
            try {
                 Map<Integer, String> indexNameMap = this.getIndexNameMap(clazz);
                List<String> list = Lists.newArrayList();
                if (indexNameMap.size() != headMap.size()) {
                    throw new BusinessException("60001", "表列数量不一致", "表列数量不一致");
                }
                for (Integer key : indexNameMap.keySet()) {
                    String value = headMap.get(key);
                    String column = indexNameMap.get(key);
                    if (StringUtils.isBlank(value) && StringUtils.isNotBlank(column)) {
                        list.add("列名错误: 没有列名, 实际列名为: " + column);
                    } else if (value != null && !value.equals(column)) {
                        list.add("列名错误: " + value + ", 实际列名为: " + column);
                    }
                }
                if (!(CollectionUtils.isEmpty(list))){
                    throw new BusinessException("60001", "列名存在错误", list);
                }
            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            }
        }
/*        stopwatch.stop();
        log.info(MessageFormat.format(" check 表头数据检查end 耗时:{0}s",stopwatch.elapsed(TimeUnit.SECONDS)));*/
    }

    /**
     * @param clazz
     * @return java.util.Map<java.lang.Integer, java.lang.String>
     * @throws
     * @description: 获取注解里ExcelProperty的value，用作校验excel
     */
    @SuppressWarnings("rawtypes")
    public Map<Integer, String> getIndexNameMap(Class clazz) throws NoSuchFieldException {
        Map<Integer, String> result = new HashMap<>();
        Field field;
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            field = clazz.getDeclaredField(fields[i].getName());
            field.setAccessible(true);
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            if (excelProperty != null) {
                int index = excelProperty.index();
                String[] values = excelProperty.value();
                StringBuilder value = new StringBuilder();
                for (String v : values) {
                    value.append(v);
                }
                result.put(index, value.toString());
            }
        }
        return result;
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", dataList.size());
        log.info("存储数据库成功！");
    }

    public List<String> getHeadList() {
        return headList;
    }

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

    public List<String> getMessageList() {
        return messageList;
    }

}

ExcelHelper

package com.etl.appeal.common.fileutil;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.cache.MapCache;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;

import java.io.InputStream;
import java.util.List;

@Slf4j
public class ExcelHelper {

    public static <T> List<String> getHeads(InputStream stream, String fileExtension, Class<T> T) {
        ExcelListener<T> listener = doRead(stream, fileExtension, true, T);
        return listener.headList;
    }

    public static <T> ExcelListener<T> getListener(InputStream stream, String fileExtension, Class<T> T) {
        ExcelListener<T> listener = doRead(stream, fileExtension, false, T);
        return listener;
    }

    public static <T> ExcelListener<T> getListenerV2(InputStream stream, String fileExtension, Class<T> T) {
        ExcelListener<T> listener = doReadV2(stream, fileExtension, false, T);
        return listener;
    }

    public static <T> ExcelListener<T> getListenerV3(InputStream stream, Class<T> T) {
        ExcelListener<T> listener = doReadV3(stream, false, T);
        return listener;
    }

    /**
     * 校验列头
     *
     * @param stream
     * @param fileExtension
     * @param T
     * @param <T>
     * @return
     */
//    public static <T> String CheckHead(InputStream stream, String fileExtension, Class<T> T) {
//        try {
//            List<String> heads = getHeads(stream, fileExtension, T);
//            Map<Integer, String> columns = getColumnNames(T);
//            if (heads.stream().count() != columns.size())
//                return "表列数量不一致";
//
//            for (Integer key : columns.keySet()) {
//                String value = heads.get(key);
//                String column = columns.get(key);
//                if (!value.equals(column)) {
//                    log.error("列名错误：{}，实际列名为：{}", value, column);
//                }
//            }
//        } catch (Exception exception) {
//            return exception.getMessage();
//        }
//        return "";
//    }

//    public static Map<Integer, String> getColumnNames(Class<?> clazz) throws NoSuchFieldException {
//        Map<Integer, String> map = new HashMap();
//        //获取所有属性
//        Field[] fields = clazz.getDeclaredFields();
//        Boolean b = false;
//        for (int i = 0; i < fields.length; i++) {
//            Field field = clazz.getDeclaredField(fields[i].getName());
//            boolean annotationPresent = field.isAnnotationPresent(ExcelField.class);
//            if (annotationPresent) {
//                //获取注解值
//                Integer index = fields[i].getAnnotation(ExcelField.class).index();
//                String name = fields[i].getAnnotation(ExcelField.class).column();
//                map.put(index, name);
//            }
//        }
//        return map;
//    }
    private static <T> ExcelListener<T> doRead(InputStream stream, String fileExtension, Boolean onlyhead, Class<T> T) {
        ExcelListener<T> listener = new ExcelListener<>(onlyhead, T);
        if (fileExtension.equals("csv")) {
            EasyExcel.read(stream, T, listener).excelType(ExcelTypeEnum.CSV).sheet().doRead();
        } else {
            EasyExcel.read(stream, T, listener).sheet().doRead();
        }
        return listener;
    }
    private static <T> ExcelListener<T> doReadV2(InputStream stream, String fileExtension, Boolean onlyhead, Class<T> T) {
        ExcelListener<T> listener = new ExcelListener<>(onlyhead, T);
        if (fileExtension.equals("csv")) {
            ExcelReaderBuilder builder=EasyExcel.read(stream, T, listener);
            builder.readCache(new MapCache());
            builder.excelType(ExcelTypeEnum.CSV).sheet().doRead();
        } else {
            ExcelReaderBuilder builder=EasyExcel.read(stream, T, listener);
            builder.readCache(new MapCache());
            builder.sheet().doRead();
        }
        return listener;
    }

    private static <T> ExcelListener<T> doReadV3(InputStream stream, Boolean onlyhead, Class<T> T) {
        ExcelListener<T> listener = new ExcelListener<>(onlyhead, T);
            ExcelReaderBuilder builder=EasyExcel.read(stream, T, listener);
            builder.readCache(new MapCache());
            builder.sheet().doRead();
        return listener;
    }
}

ExcelField

package com.etl.appeal.common.fileutil;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelField {
    /**
     * Excel中的列名
     *
     * @return
     */
    public abstract String name() default "";

    /**
     * 列名对应的A,B,C,D...,不指定按照默认顺序排序
     *
     * @return
     */
    public abstract String column() default "";

    /**
     * 提示信息
     *
     * @return
     */
    public abstract String prompt() default "";

    /**
     * 序号 对应excel字段的位置
     *
     * @return
     */
    public abstract int index();

}

EasyExcelValidHelper

package com.etl.appeal.common.fileutil;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.extern.slf4j.Slf4j;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.groups.Default;
import java.lang.reflect.Field;
import java.util.Set;

/**
 * 文件内容校验
 */
@Slf4j
public class EasyExcelValidHelper {

    private EasyExcelValidHelper() {
    }

    private static Validator validator = Validation.buildDefaultValidatorFactory().getValidator();

    public static <T> String validateEntity(T obj) throws NoSuchFieldException {
        StringBuilder result = new StringBuilder();
        Set<ConstraintViolation<T>> set = validator.validate(obj, Default.class);
        if (set != null && !set.isEmpty()) {
            for (ConstraintViolation<T> cv : set) {
                Field declaredField = obj.getClass().getDeclaredField(cv.getPropertyPath().toString());
                ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
                //拼接错误信息，包含当前出错数据的标题名字+错误信息
                result.append(annotation.value()[0] + cv.getMessage()).append(";");
            }
        }
        return result.toString();
    }
}

impl获取excel数据进行处理

package com.etl.appeal.service.impl;

import com.etl.appeal.common.fileutil.ExcelHelper;
import com.etl.appeal.common.fileutil.ExcelListener;
import com.etl.appeal.domain.AppealBatchProcessData;
import com.etl.commons.constant.FileConstants;
import com.etl.commons.exception.BusinessException;
import com.etl.commons.utils.ExcelExportUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.util.List;

@Slf4j
@Service
public class EasyExcelServiceImpl  implements EasyExcelService {

    @Resource
    private ExcelExportUtil excelExportUtil;

    @Override
    public void uploadFile(HttpServletResponse response, MultipartFile file) throws Exception {
        String fileName = file.getOriginalFilename();
        //校验表头并获取数据
        ExcelListener listeners = this.validateFileV2(fileName, file);
        List<?> dataList = listeners.getDataList();//实体类list接
            // 上传成功后用
            response.setHeader("success-flag","1");
        }
    }



    public ExcelListener validateFileV2(String filename, MultipartFile file) {
        log.info("上传文件前的校验步骤,fileName:{},fileType :{}", filename);
        if (StringUtils.isBlank(filename)) {
            throw new BusinessException("文件不存在");
        }
        String fileExtension = filename.substring(filename.lastIndexOf(".") + 1);
        if (!FileConstants.CSV.equals(fileExtension) && !FileConstants.XLSX.equals(fileExtension)) {
            throw new BusinessException("请上传" + FileConstants.XLSX + "类型文件");
        }
        return validateAllFileV2(file, fileExtension);
    }

    @SneakyThrows
    private <T> ExcelListener validateAllFileV2(MultipartFile file, String fileExtension) {
        ExcelListener<T> listeners;
        Class clazz = AppealBatchProcessData.class;
        try(BufferedInputStream inputStream=new BufferedInputStream(file.getInputStream())) {
            long l = System.currentTimeMillis();
            listeners = ExcelHelper.getListenerV2(inputStream, fileExtension, clazz);
            log.info("校验文件头、文件内容花费的时间：{}秒", (System.currentTimeMillis() - l) / 1000L);
        } catch (BusinessException be) {
            throw be;
        }
        if (listeners.getMessageList().size() > 0) {
            throw new BusinessException("60001", "文件数据校验失败");
        }
        return listeners;
    }

}