easyExcel导入表格

本文章是介绍java，通过easyExcel导入较为复杂的表格

导入表格如下

在这里插入图片描述

前提工作导包

<!--     hutool--><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.6.0</version></dependency><!--     Easy Excel--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version></dependency>

数据封装工具类，继承实现AnalysisEventListener

public class EasyExcelListener extends AnalysisEventListener<Object> {// 创建list集合封装最终的数据private List<Object> list = new ArrayList<>();// sheet页索引private int sheetNo = 0;@Overridepublic void invoke(Object t, AnalysisContext context) {// 读取excle内容int currentSheetNo = context.readSheetHolder().getSheetNo();if (currentSheetNo != sheetNo) {// 如果不根据sheet页索引更新状态重新创建list，list会反复添加前面的sheet页对象值list = new ArrayList<>();sheetNo = currentSheetNo;}list.add(t);}// 读取excel表头信息@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {}// 读取完成后执行@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}/*** 获取表格内容(简单excel读取可用该方法)** @param obj 需要转化的实体* @param <T>* @return*/public <T> List<T> getList(Class<T> obj) {String jsonObj = JSONUtil.toJsonStr(list);return JSONUtil.toList(jsonObj, obj);}/*** 将表格转化为map集合（复杂excel读取用此方法）** @return map集合*/public List<LinkedHashMap> getListMap() {String jsonObj = JSONUtil.toJsonStr(list);return JSONUtil.toList(jsonObj, LinkedHashMap.class);}}

EasyExcelUtil ：表格读取工具类

public class EasyExcelUtil {private EasyExcelUtil() {}/*** 根据easyexcel注解给指定实体赋值** @param objects 读取的表格内容* @param clazz   需转化的实体* @param <T>     实体* @return 需转化的提示集合*/public static <T> List<T> convertList(List<LinkedHashMap> objects, Class<T> clazz) {List<T> results = new ArrayList<>(objects.size());try {Map<String, Field> objIndex = new HashMap<>();// 获取转化实体字段信息集合Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {// 根据实体上Easy Excel的ExcelProperty注解中的索引值对应excel读取数据的值int index = field.getAnnotation(ExcelProperty.class).index();// 设置字段可编辑field.setAccessible(true);objIndex.put(String.valueOf(index), field);}T obj = null;for (LinkedHashMap o : objects) {obj = clazz.newInstance();for (Object key : o.keySet()) {// 如果表格索引与字段注解指定索引一样则赋值if (objIndex.containsKey(key)) {Object object = o.get(key);Object value = null;Field field = objIndex.get(key);if (ObjectUtil.isEmpty(object)) {continue;}Class<?> type = field.getType();String replace = object.toString();// 有特殊需要处理的字段类型则在此进行处理if (type == BigDecimal.class) {value = "--".equals(replace) ? null : new BigDecimal(replace.replace(",", ""));} else if (type == Integer.class) {// String强转Integer会报错，所以需要单独进行转化value = "--".equals(replace) ? null : Integer.valueOf(replace.replace(",", ""));} else {value = object;}field.set(obj, value);}}results.add(obj);}} catch (Exception e) {e.printStackTrace();
//            log.error("字段解析失败", e);
//            Asserts.fail("字段解析失败:" + e.getMessage());}return results;}}

Controller层代码

  public void upload(@ApiParam(name = "file", value = "file", required = true) @RequestParam(value = "file") MultipartFile file) throws Exception {// 1. 读取excel内容EasyExcelListener easyExcelListener = new EasyExcelListener();InputStream inputStream = file.getInputStream();ExcelReaderBuilder read = EasyExcelFactory.read(file.getInputStream(), easyExcelListener);ExcelReader excelReader = read.build();// 2.获取各个sheet页信息List<ReadSheet> sheets = excelReader.excelExecutor().sheetList();// 3. 将各个Shhet页表格内容存于mapMap<Integer, List<LinkedHashMap>> sheetInfos = new HashMap<>(sheets.size());for (ReadSheet sheet : sheets) {Integer sheetNo = sheet.getSheetNo();excelReader.read(sheet);sheetInfos.put(sheetNo, easyExcelListener.getListMap());}//拿到附件地址，将文件存储业务使用String upload = FileUploadUtils.upload(file);// 调用setCompetitiongroupReport()将文档内容进行读取，// get(0)是默认读取第一个sheet页setCompetitiongroupReport(sheetInfos.get(0));}

setCompetitiongroupReport（)：将表格数据封装到对象里

    private void setCompetitiongroupReport(List<LinkedHashMap> maps) {//1.参赛单位信息，实体类直接set处理，SysCompetitiongroup sysCompetitiongroup=new SysCompetitiongroup();// 2.教练员信息数组List<LinkedHashMap> optionCoachDetails = new ArrayList<>();// 3.教练员信息类，需要进行注解处理SysCompetitiongroupCoachs sysCompetitiongroupCoachs =new SysCompetitiongroupCoachs();// 4.参赛运动员信息，需要进行注解处理SysCompetitiongroupStudents sysCompetitiongroupStudents=new SysCompetitiongroupStudents();// 5.参赛运动员信息数组List<LinkedHashMap> optionStudentsDetails = new ArrayList<>();// 参赛单位信息boolean groupinfo = true;// 教练员信息boolean Coachinfo = false;boolean Studentsinfo = false;for (LinkedHashMap map : maps) {// 1.判断信息是否是属于参赛单位信息，默认为true;if (groupinfo) {boolean dealBaseInfo = dealBaseInfo(map, sysCompetitiongroup);// 参赛单位信息读取完成 设置为false，再次遍历将不读取if(!dealBaseInfo){groupinfo=false;}}// 教练员信息if (map.containsValue("教练员信息")) {Coachinfo=true;}if (Coachinfo){Boolean aBooleanCoach = dealCoachDetails(map, optionCoachDetails);if (!aBooleanCoach){Coachinfo=false;}}// 参赛运动员信息if (map.containsValue("参赛运动员信息")) {Studentsinfo=true;}if(Studentsinfo){dealStudentDetails(map, optionStudentsDetails);}}//将List里面的数据转换到实体类当中List<SysCompetitiongroupCoachs> Coach = EasyExcelUtil.convertList(optionCoachDetails, SysCompetitiongroupCoachs.class);//将List里面的数据转换到实体类当中List<SysCompetitiongroupStudents> student = EasyExcelUtil.convertList(optionStudentsDetails, SysCompetitiongroupStudents.class);}

@ExcelProperty(value = “姓名”, index = 1)，value代表名称，index表格对应下标位置

@ApiModel("教练员信息")
public class SysCompetitiongroupCoachs {@ApiModelProperty(value = "序号")@ExcelProperty(value = "序号", index = 0)private String num;/** 教练员姓名 */@ApiModelProperty(value = "姓名")@ExcelProperty(value = "姓名", index = 1)private String username;/** 教练员性别 */@ApiModelProperty(value = "性别")@ExcelProperty(value = "性别", index = 2)private String sex;/** 身份证号 */@ApiModelProperty(value = "身份证号")@ExcelProperty(value = "身份证号", index = 3)private String number;/** 手机号 */@ApiModelProperty(value = "电话（手机）")@ExcelProperty(value = "电话（手机）", index = 4)private String phone;
}

dealBaseInfo（）参赛单位信息模块处理，直接比对set就好了

 /***  参赛单位信息筛选*/private boolean dealBaseInfo(LinkedHashMap map, SysCompetitiongroup baseInfo) {// 是否基础信息内容if (map.containsValue("教练员信息")) {return false;}Object obj = map.get("3");String str = null;if (ObjectUtil.isNotEmpty(obj)) {str = obj.toString();}if (ObjectUtil.isNotEmpty(obj)) {str = obj.toString();}if (map.containsValue("中体协滑雪会员学校代码:")) {baseInfo.setSchoolCode(str);}if (map.containsValue("报名单位全称:")) {baseInfo.setGroupName(str);}if (map.containsValue("报名单位所属省、市:")) {baseInfo.setGroupCity(str);}if (map.containsValue("报名经办人姓名:")) {Object obj2 = map.get("2");String str2 = null;if (ObjectUtil.isNotEmpty(obj2)) {str2 = obj2.toString();}Object obj4 = map.get("4");String str4 = null;if (ObjectUtil.isNotEmpty(obj4)) {str4 = obj4.toString();}baseInfo.setGroupUsername(str2);baseInfo.setGroupPhone(str4);}if (map.containsValue("姓名:")) {Object obj2 = map.get("2");String str2 = null;if (ObjectUtil.isNotEmpty(obj2)) {str2 = obj2.toString();}Object obj4 = map.get("4");String str4 = null;if (ObjectUtil.isNotEmpty(obj4)) {str4 = obj4.toString();}baseInfo.setCoachname(str2);baseInfo.setCoachphone(str4);}return true;}

dealCoachDetails（）处理教练员信息，根据表格情况进行处理

 /*** 处理教练员信息**/private Boolean dealCoachDetails(LinkedHashMap map, List<LinkedHashMap> optionAccountsDetails) {// 如果读取数据，已经不是当前表格，直接false，不在进行读取if (map.containsValue("参赛运动员信息")) {return false;}// 将序号和教练员信息进行过滤if(map.size()>0) {if (!map.containsValue("序号") && !map.containsValue("教练员信息")) {optionAccountsDetails.add(map);}}return true;}

dealStudentDetails()：处理参赛运动员信息模块，根据表格情况进行处理

  /*** 处理参赛运动员信息*/private Boolean dealStudentDetails(LinkedHashMap map, List<LinkedHashMap> optionAccountsDetails) {if (map.size()>0) {if (!map.containsValue("序号") && !map.containsValue("参赛运动员信息") && !map.containsValue("团体赛")) {optionAccountsDetails.add(map);}}return true;}