EasyExcel简单使用

他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。)

官网地址

https://easyexcel.opensource.alibaba.com/

maven

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

EasyExcel导入方法

try {
				File filePath = new File(savePath + File.separator + name);
				//filePath：服务器上获取的流转换的，也可以直接使用传输MultipartFile file，
				// controller传的MultipartFile file，就这么搞InputStream inputStream = multipartFile.getInputStream();
                InputStream inputStream = new BufferedInputStream(new FileInputStream(filePath));
                Throwable var22 = null;

                try {
                    //  调用监听器 通过.headRowNumber(0)从表头开始读取TempRateListValueUploadListener监听器方法（里面可以写构造方法接收传给监听器的值，什么map
list 什么的，实体类TempRateValueUploadModel，实体类里的字段注解@ExcelProperty
                    //	如果监听器里需要用到dao，mapper，service都需要放到构造方法里才可以使用
                    EasyExcel.read(inputStream, TempRateValueUploadModel.class,
                                    new TempRateListValueUploadListener(newParameterMap,
efTransportChargeItemDao))
                            .sheet().doRead();

                    map.put("code", "200");
                    map.put("msg", "导入成功");
                    /*json.put("body", map);*/

                } catch (Throwable var19) {
                    var22 = var19;
                    throw var19;
                } finally {
                    if (inputStream != null) {
                        if (var22 != null) {
                            try {
                                inputStream.close();
                            } catch (Throwable var18) {
                                var22.addSuppressed(var18);
                            }
                        } else {
                            inputStream.close();
                        }
                    }

                }
            } catch (Throwable var21) {
				//	监听器里返回的错误信息
                if (!(var21 instanceof EasyExcelException)) {
                    log.error("parse [" + name + "] excel failed...chargeItemNo[" + chargeItemNo + "]", var21);
                }

                if (var21 instanceof NumberFormatException) {
                    String prefix = "For input string: ";
                    if (var21.getMessage().indexOf(prefix) == -1) {

                        map.put("code", "101");
                        map.put("msg", "类型转换失败");

                    } else {
                        map.put("code", "1");
                        map.put("msg", var21.getMessage());
                    }
                } else {

                    map.put("code", "1");
                    map.put("msg", var21.getMessage());

                }
            } ```

### 监听器

```java package com.maintain.data.excel.listener;

import com.alibaba.excel.context.AnalysisContext; import
com.alibaba.excel.event.AnalysisEventListener; import
com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject; import
com.fasterxml.jackson.databind.ObjectMapper; import
com.maintain.data.excel.bean.*; import
com.maintain.data.excel.dao.EfTransportChargeItemDao; import
com.maintain.data.excel.vo.OperatorInfoVo; import
com.maintain.data.excel.vo.TempRateValueUploadModel; import
com.maintain.data.excel.vo.TempRateValueVo; import
com.sinoservices.core.easyexcel.exception.EasyExcelException; import
com.sinoservices.core.util.EmptyUtils; import
com.sinoservices.core.util.Validator; import
org.apache.commons.lang3.StringUtils; import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDateUtil; import
org.springframework.beans.BeanUtils;

import java.beans.PropertyDescriptor; import java.lang.reflect.Field;
import java.lang.reflect.Method; import java.text.ParseException;
import java.text.SimpleDateFormat; import java.util.*; import
java.util.concurrent.ConcurrentHashMap;

public class TempRateListValueUploadListener extends
AnalysisEventListener<TempRateValueUploadModel> {


    private static Logger log = Logger.getLogger(TempRateListValueUploadListener.class);
    private List<TempRateValueUploadModel> templateListValueModel = new ArrayList<>();
    private Map<String, ResultCodeMasterBean> codeMasterCache = new ConcurrentHashMap<>();
    private JSON json = new JSONArray();
    private Map<String, String> map = new HashMap<>();
    private Map<String, String> maps = new HashMap<>();

    private EfTransportChargeItemDao efTransportChargeItemDao;

    /**
     * 调用dao 或者service需要手动传
     * 
     *
     * @param map
     * @param
     * @param efTransportChargeItemDao
     */
    public TempRateListValueUploadListener(Map<String, String> map, EfTransportChargeItemDao efTransportChargeItemDao) {
        this.map = map;
        this.efTransportChargeItemDao = efTransportChargeItemDao;

    }

    /**
     * 文件导入唯一标识
     */
    private String fileUUID = null; 	 	//	此方法每一条数据解析都会来调用
    @Override
    public void invoke(TempRateValueUploadModel object, AnalysisContext context) {
    	
        Integer currentRowNum = context.getCurrentRowNum();

        object.setChargeItemNo(map.get("chargeItemNo"));
        object.setRequestId(map.get("requestId"));
        object.setUserId(map.get("userId"));
        object.setApproverId(map.get("approverId"));
        object.setLastname(map.get("lastname"));
        object.setCreateDate(new Date().toString());
        object.setStatus("0");

        if (currentRowNum == 1) {
            Date date = new Date(System.currentTimeMillis());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyMMddHHmmss");
            String timeString = simpleDateFormat.format(date);
            UUID uuid = UUID.randomUUID();
            String uuidStr = uuid.toString().replace("-", "").toUpperCase().substring(0, 7);

            fileUUID = timeString + "-" + map.get("chargeItemNo") + "-" + uuidStr;
        }

        //保存UUID
        object.setRemark(fileUUID);
        String message = Validator.validate(object);
        if (message != null) {
            maps.put("code", "101");
            maps.put("msg", "第 " + context.getCurrentRowNum() + " 行: " + message);

        } else {
            templateListValueModel.add(object);
        }
    } 	//	所有数据解析完成都会来调用
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

        if (EmptyUtils.isNotEmpty(templateListValueModel)) {
            if (templateListValueModel.size() > 3000) {
                maps.put("code","101");
                maps.put("msg","Excel 导入不能超过3000条");
                throw new EasyExcelException(maps.get("msg"));
            }
            //	实现导入逻辑方法
            maps = save(templateListValueModel);
        }
        templateListValueModel.clear();
        if (!maps.isEmpty() && !"200".equals(maps.get("code"))) {

            throw new EasyExcelException(maps.get("msg"));

        }

    }

    /**
     * 将数据保存到中间表中
     *
     * @param customersdModel
     * @return
     */
    private Map<String, String> save(List<TempRateValueUploadModel> customersdModel) {
        //	写插入自己表的逻辑方法
    }

    

    //  错误信息
    private void insertErrormsg(EfTransportChargeItemDao efTransportChargeItemDao, List<TempRateValueUploadModel>
customersdModel) {
        efTransportChargeItemDao.insertErrormsg(customersdModel);
    }


    private Map<String, String> getColumnValue(List<EfCrTemplateListColBean> colBeans,
List<EfCrTemplateListValueModelBean> beans) {
        Map<String, String> map = new HashMap<>();
        String startDate = "";
        String endDate = "";
        for (EfCrTemplateListColBean colBean : colBeans) {
            String colName = colBean.getMclcColumnName();
            for (EfCrTemplateListValueModelBean colValBean : beans) {
                if (colName.equals(colValBean.getColumnName())) {
                    if (colName.contains("从")) {
                        startDate = colValBean.getColumnValue();
                    } else if (colName.contains("到")) {
                        endDate = colValBean.getColumnValue();
                    }
                }
            }
        }
        map.put("startDate", startDate);
        map.put("endDate", endDate);
        return map;
    }


    private void changeTime(List<EfCrTemplateListValueModelBean> beans, String changeDate, boolean flag) {
        for (EfCrTemplateListValueModelBean colValBean : beans) {
            if (flag && colValBean.getColumnName().contains("从")) {
                colValBean.setColumnValue(changeDate);
            } else if (!flag && colValBean.getColumnName().contains("到")) {
                colValBean.setColumnValue(changeDate);
            }
        }
    }

    /**
     * Description :判断是不是数字
     *
     * @param :str 1
     * @return: boolean
     * @Author: Neil
     * @Create Date: 2019-4-11
     */
    public boolean isNumber(String numberValue) {
        boolean convertSuccess = true;
        try {
            Double.parseDouble(numberValue);
        } catch (Exception e) {
            convertSuccess = false;
        }
        return convertSuccess;
    }

    /**
     * Description :通过反射获取字段值
     *
     * @param :fieldName 1
     * @param :object    2
     * @return: java.lang.String
     * @Author: Neil
     * @Create Date: 2019-4-10
     */
    private String getFieldValueByFieldName(String fieldName, Object object) {
        try {
            PropertyDescriptor propertyDescriptor = new PropertyDescriptor(fieldName, object.getClass());
            Method getMethod = propertyDescriptor.getReadMethod();//获得get方法
            Object value = getMethod.invoke(object);//执行get方法返回一个Object
            System.out.print(fieldName + "---" + value.toString());
            return value.toString();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return null;
    }

    /**
     * Description :通过反射获取列个数
     *
     * @param :object 1
     * @return: int
     * @Author: Neil
     * @Create Date: 2019-4-10
     */
    private int getFieldNumValueByClazz(Object object) {
        try {
            Class clazz = object.getClass();
            Field[] fields = object.getClass().getDeclaredFields();//获得属性
            int notNullNum = 0;
            for (Field field : fields) {
                PropertyDescriptor propertyDescriptor = new PropertyDescriptor(field.getName(), clazz);
                Method getMethod = propertyDescriptor.getReadMethod();//获得get方法
                Object value = getMethod.invoke(object);//执行get方法返回一个Object
                if (value != null && !value.equals("")) {
                    notNullNum++;
                }
            } //            log.info(notNullNum);
            
            //bean对象中字段pmCode没值，chargeItemNo 必有值。
            // 而且要忽略，所以从原来所以需要-3 改为-2
            return notNullNum;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return 0;
    } }

EasyExcel导出方法

File file = new File(fileName);
			//	第一种方法
                EasyExcel.write(file.getAbsolutePath(), TempRateValueExcel.class).sheet("excel").doWrite(modelList);

                //  上传华为云/服务器
                url = oaRequestUtil.getUploadFile(file);

                log.info("生成excel上传华为云，返回Url:{}", url);
                try {
                //	第二种方法
                WriteSheet sheet0 = EasyExcel.writerSheet(0, "导入费率错误信息").head(TempRateValueExcel.class).build();

                excelWriter = EasyExcel.write(file).build();
                excelWriter
                        .write(modelList, sheet0);
                } catch (Exception e) {
             // 错误信息 e.getMsg
             e.getMessage();
             e.printStackTrace();
        } finally {
            if (excelWriter != null) {
            //	关闭流
                excelWriter.finish();
            }
        }