基于EasyExcel的读取exl并返回成功/失败数量及原因,并将导入失败exl的导出到系统路径中

首先,这个是读取和导出exl都需要用到的实体类的基类,这个是可以扩展的,读取失败的exl想要加入其它列的字段都可以加在这里
pom

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

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;

//读取exl的错误导出基础类,用来存放基础数据
public class EasyExcelErrBaseVo  extends BaseRowModel {

    @ExcelProperty(value = {"失败原因"})
    protected  String remark;

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }
}

二 读取的入口

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import lombok.extern.slf4j.Slf4j;

/**
 * 这个是读取exl的工具类
 */
@Slf4j
public class EasyExcelReadUtil {


    public static JsonMessage easyRead(EasyRealInterface easyRealInterface, String fileName, Class cls,String errfilepath) {
        try {
            // 写法2：
            easyRealInterface.setErrcla(cls);
            ExcelReader excelReader = EasyExcel.read(fileName,cls, new EasyExcelLister(easyRealInterface)).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            excelReader.read(readSheet);
            // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
            excelReader.finish();
           return easyRealInterface.finish(errfilepath);
        } catch (Exception e) {
            log.error(easyRealInterface.getreportName() + "读取excel异常", e);
            return new JsonMessage(false,"服务器异常,请重新上传");
        }
    }
}

三 配置监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * excel读取的监听器
 */
@Slf4j
public class EasyExcelLister<T> extends AnalysisEventListener<T> {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static  int BATCH_COUNT = 30;
    List<T> list = new ArrayList<T>();

    EasyRealInterface easyRealInterface;


    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param easyRealInterface ,传入批量处理的接口,每达到多少条就进行一次批量处理
     */
    public EasyExcelLister(EasyRealInterface easyRealInterface) {
       this.easyRealInterface=easyRealInterface;
    }

    /**
     *
     * @param easyRealInterface ,继承该接口进行业务逻辑的处理,每次保存多少
     * @param BATCH_COUNT ,每次解析的数量
     */
    public EasyExcelLister(EasyRealInterface easyRealInterface,int BATCH_COUNT) {
        this.easyRealInterface=easyRealInterface;
        this.BATCH_COUNT=BATCH_COUNT;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        log.debug("文档解析标题为:"+easyRealInterface.getreportName()+",解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }


    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.debug("所有数据解析完成！,读取的标题是:"+easyRealInterface.getreportName());
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.debug("{}条数据，开始存储数据库！,读取的标题是"+easyRealInterface.getreportName(), list.size());
        try {
            easyRealInterface.savelist(list);
            log.debug("存储数据库成功！读取的标题是:"+easyRealInterface.getreportName());
        } catch (Exception e) {

        }
;
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println(1);
    }

    //这边是判断读取到了表头
    @Override
    public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {

        easyRealInterface.checkexlHead(headMap);//检查表头
    }

}

四 用于处理导入的业务逻辑和成功失败数量及原因等的接口和类

import com.alibaba.excel.metadata.CellData;
import net.fxft.ascswebcommon.web.util.JsonMessage;

import java.util.List;
import java.util.Map;

//用于exl读取批量处理的接口
public interface EasyRealInterface {

    //批量保存
    public  void savelist(List list) ;

    //获取到这次批量保存的标题内容
    public String getreportName();

    //开始读表,这边如果表头正确才进行读取,
    public  JsonMessage finish( String searcherrorpath);

    //判断读取的exl的表头是否正确
    public void checkexlHead(Map<Integer, CellData> headMap);


    public void setErrcla(Class errcla);

}

import org.springframework.beans.BeanUtils;

import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

public abstract class EasyexlReadBaseService implements EasyRealInterface {

    public static String errmsg = "解析错误";
    public static String successmsg = "解析成功";
    //这边是用来控制表头是否读取正确的情况,如果是失败的情况,那么就不能进行业务逻辑操作
    private boolean issave = true;

    //失败集合
    private List errorList = new ArrayList();

    //失败导出的类
    private Class errcla;

    //失败条数
    private int failco = 0;

    //返回的提醒的内容,如果有错误,那么就会改成上传成功,失败原因下载查看
    private String message = successmsg;

    //成功条数
    private int successco = 0;

    public void setErrcla(Class errcla) {
        this.errcla = errcla;
    }

    //开始读表,这边如果表头正确才进行读取,
    public void savelist(List list) {
        if (issave) {
            int oldfailco = failco;
            save(list);
            addsuccess(list.size() - (failco - oldfailco));//读取结束之后就能知道成功的数量了
        } else {
            if (ConverterUtils.isList(list)) {//这里是判断不为空且数量大于0
                for (Object o : list) {
                    addfailandsetremark(o, "表头标题不对应");
                }
            }
            message = errmsg;
        }
    }

    //执行存储的业务逻辑
    public abstract void save(List list);




    //读取结束的时候进行处理,如果有失败的,那么就导出到文件中
    public JsonMessage finish(String searcherrorpath) {
        String failpath = "";
        failpath = EasyExceltofileUtil.exlport(getreportName() + "导入失败内容", searcherrorpath, errorList, errcla);
        EasyexlMessage easyexlMessage = EasyexlMessage.builder().failco(failco).successco(successco).msg(message).failpath(failpath).build();
        return new JsonMessage(true, message, easyexlMessage);
    }


    @Override
    public String getreportName() {
        return "读取exl标题";
    }


    //将导出失败的原因进行反射加入
    protected void addfailandsetremark(Object o, String remark) {
        try {
            Object o1 = errcla.newInstance();
            BeanUtils.copyProperties(o, o1);
            Class<? extends Object> c = o1.getClass();
            Method methods2;
            try {
                methods2 = c.getMethod("setRemark", String.class);// 注意参数不是String,是string
                methods2.invoke(o1, remark);// 通过对象，调用有参数的方法
                // 如果这个地方需要持久保存，那么就是object类放进去。不然就是加上c.newInstance()
            } catch (Exception e) {

            }
            addfailco(1);
            errorList.add(o1);
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }

    }


    //添加成功的条数
    public void addsuccess(int suco) {
        successco = successco + suco;
    }

    //添加失败的条数
    public void addfailco(int failco) {
        this.failco = this.failco + failco;
        message = errmsg;
    }

    public void setIssave(boolean issave) {
        this.issave = issave;
    }

    public boolean isIssave() {
        return issave;
    }

    public List getErrorList() {
        return errorList;
    }

    public int getFailco() {
        return failco;
    }

    public String getMessage() {
        return message;
    }

    public int getSuccessco() {
        return successco;
    }
}

五/错误exl导出的封装类

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * easyexl导出到压缩包的的工具类
 */

public class EasyExceltofileUtil {
    private static final Logger logger = LoggerFactory.getLogger(EasyExceltofileUtil.class);


    /**
     * 根据表头，数据，文件名称，进行exl的导出，xls格式
     *
     * @param fileName 文件名
     * @param heads    标题
     * @param alldata  实际数据
     */
    public  static  String exlport(String fileName, String filepath,  List<List<Object>> alldata,Class<? extends BaseRowModel> cls) {
       return exportfile(fileName, filepath, alldata, ".xlsx", ExcelTypeEnum.XLSX,cls);
    }


    private  static  String exportfile(String fileName, String filepath,  List<List<Object>> alldata, String filetype, ExcelTypeEnum exltype,Class<? extends BaseRowModel> cls) {
        FileOutputStream fos = null;
        String filespathresult="";
        try {
             filespathresult=filepath +File.separator + fileName+getnowtime() + filetype;
            //如果文件夹不存在就创建文件夹
            File folder = new File(filepath);
            if (!folder.exists() && !folder.isDirectory()) {
                folder.mkdirs();
            }
            File file = new File(filespathresult);
            if (!file.exists()) {
                file.createNewFile();
            }
            fos = new FileOutputStream(file);
            ExcelWriter writer = EasyExcelFactory.getWriter(fos, exltype, true);

            // 动态添加表头，适用一些表头动态变化的场景
            com.alibaba.excel.metadata.Sheet sheet1 = new com.alibaba.excel.metadata.Sheet(1, 0,cls);
            sheet1.setSheetName("sheet1");
            // 写数据
            writer.write1(alldata, sheet1);
            // 将上下文中的最终 outputStream 写入到指定文件中
            writer.finish();
        } catch (Exception e) {
            logger.error("导出exl失败" + fileName, e);
        } finally {
            try {
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return filespathresult;
    }

    private static String getnowtime(){
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
        String dateString = formatter.format(new Date());
        return dateString;
    }

}

六 导入结果返回的实体类

import lombok.Builder;
import lombok.Data;

//上传exl读取的返回值
@Data
@Builder
public class EasyexlMessage {
    private String msg;//提示
    private long successco;//成功数量
    private long failco;//失败数量
    private String failpath; //失败文件路径


}

public class JsonMessage implements Serializable {
    private boolean success;
    private int code;
    private String message;
    private Object data;
    private long total;
    private static String JSON = "json";

    public long getTotal() {
        return this.total;
    }

    public void setTotal(long total) {
        this.total = total;
    }

    public JsonMessage(boolean _success, Object data) {
        this.success = _success;
        if (_success) {
            this.code = 10000;
        } else {
            this.code = 500;
        }

        this.data = data;
    }

    public JsonMessage() {
    }

    public JsonMessage(boolean _success, String msg) {
        this(_success, msg, (Object)null);
    }

    public JsonMessage(boolean _success, long total, Object data) {
        this.success = _success;
        if (_success) {
            this.code = 10000;
        } else {
            this.code = 500;
        }

        this.total = total;
        this.data = data;
    }

    public JsonMessage(boolean _success, String msg, Object data) {
        this.success = _success;
        if (_success) {
            this.code = 10000;
        } else {
            this.code = 500;
        }

        this.message = msg;
        this.data = data;
    }

    public JsonMessage(boolean _success, int code, String msg, Object data) {
        this.success = _success;
        this.code = code;
        this.message = msg;
        this.data = data;
    }

    public JsonMessage(int code, String msg) {
        this.success = false;
        this.code = code;
        this.message = msg;
    }

    public int getCode() {
        return this.code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public Object getData() {
        return this.data;
    }

    public void setData(Object data) {
        this.data = data;
    }

    public String getMessage() {
        return this.message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public boolean isSuccess() {
        return this.success;
    }

    public void setSuccess(boolean success) {
        this.success = success;
    }
}

最后,测试类

//继承读取的类,用于导出导入错误行的的时候可以添加列名,例如错误原因
@Data
@ColumnWidth(25)
@HeadRowHeight(25)
public class DriverbyVehicleExportErrorVo extends EasyExcelErrBaseVo {

    @ExcelProperty(value = {"司机姓名"} )
    private String driverName;

}

//这个是导入之后的处理业务逻辑的类
public class easyreadDao extends EasyexlReadBaseService {


    @Override
    public void save(List list) {
        for (Object o : list) {
            if (o != null) {
                DriverbyVehicleExportErrorVo driverbyVehicleExportErrorVo = new DriverbyVehicleExportErrorVo();
                driverbyVehicleExportErrorVo = (DriverbyVehicleExportErrorVo) o;
                String remark = "";
                if (StringUtil.isNullOrEmpty(driverbyVehicleExportErrorVo.getIdentityCard())) {
                    remark += "身份证号码不能为空";
                }
                if(!StringUtil.isNullOrEmpty(remark)){
                    addfailandsetremark(o, remark);
                }else{//这边进行保存

                }
            }
        }

    }

  //导入的标题
    @Override
    public String getreportName() {
        return "exl测试名称";
    }
	
	//表头检查
    @Override
    public void checkexlHead(Map<Integer, CellData> headMap) {
        if (headMap != null && headMap.size() > 0) {
            headMap.forEach((p, v) -> {
                System.out.println(v);
            });
        } else {
            setIssave(false);
        }
    }
}

public class test {


    public static void main(String[] args) {
        new test().init();
    }
    public void init() {
        JsonMessage jsonMessage= EasyExcelReadUtil.easyRead(new easyreadDao(),"C:\\testss.xls",DriverbyVehicleExportErrorVo.class,"D:\\test\\err");
        System.out.println(JSON.toJSONString(jsonMessage));
     }
}