1.先创建一个excel工具类整合maven包
<!--xstream--> <dependency> <groupId>com.thoughtworks.xstream</groupId> <artifactId>xstream</artifactId> <version>1.4.4</version> </dependency> <dependency> <groupId>xom</groupId> <artifactId>xom</artifactId> <version>1.2.5</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency>
2.放入方法：根据表头字母获取excel表头对应的列号
/** * 根据表头字母获取excel表头对应的列号,从0开始 * @param headChars 如：A、B、C、AA、AB、AC * @return */ private static int getExcelColumnIndex(String headChars){ if(headChars == null || !headChars.matches("[a-zA-Z]+")){ throw new IllegalArgumentException("invalid key "+ headChars +",key must be 26 letters, for example: A、B、C...AA、AB、AC etc."); } if(headChars.length() > 2){ throw new IllegalArgumentException("key "+ headChars +" length is too long must <=2"); } headChars = headChars.toUpperCase(); // 转为大写字母 char char0 = headChars.charAt(0); if(headChars.length() ==1){ //长度为1时 return (int)char0 -65; } char char1 = headChars.charAt(1); //长度为2时 return ((int)char0 -64) * 26+ (int)char1 -65; }
3.放入方法：根据字段名给对象的字段赋值
/** * @Description : 根据字段名给对象的字段赋值 * @param fieldName 字段名 * @param fieldValue 字段值 * @param o 对象 */ private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception { Field field = getFieldByName(fieldName, o.getClass()); if (field != null) { field.setAccessible(true); // 获取字段类型 Class<?> fieldType = field.getType(); //增加判断是空串时不给属性赋值 String str = ObjectUtils.toString(fieldValue, ""); if(StringUtils.isBlank(str)){ //当前的字段是"" field.set(o, null); }else { // 根据字段类型给字段赋值 if (String.class == fieldType) { field.set(o, String.valueOf(fieldValue)); } elseif ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) { field.set(o, Integer.parseInt(fieldValue.toString())); } elseif ((Long.TYPE == fieldType) || (Long.class == fieldType)) { field.set(o, Long.valueOf(fieldValue.toString())); } elseif ((Float.TYPE == fieldType) || (Float.class == fieldType)) { field.set(o, Float.valueOf(fieldValue.toString())); } elseif ((Short.TYPE == fieldType) || (Short.class == fieldType)) { field.set(o, Short.valueOf(fieldValue.toString())); } elseif ((Double.TYPE == fieldType) || (Double.class == fieldType)) { field.set(o, Double.valueOf(fieldValue.toString())); } elseif (Character.TYPE == fieldType) { if ((fieldValue != null) && (fieldValue.toString().length() > 0)) { field.set(o, Character.valueOf(fieldValue.toString().charAt(0))); } } elseif (Date.class == fieldType) { field.set(o, DateUtils.parseDate(fieldValue.toString(), DatePtn)); } else { field.set(o, fieldValue); } } } }
4.放入方法：根据字段名获取字段
/** * @Description : 根据字段名获取字段 * @param fieldName 字段名 * @param clazz 包含该字段的类 * @return 字段 */ private static Field getFieldByName(String fieldName, Class<?> clazz) { // 拿到本类的所有字段 Field[] selfFields = clazz.getDeclaredFields(); // 如果本类中存在该字段，则返回 for (Field field : selfFields) { if (field.getName().equals(fieldName)) { return field; } } // 否则，查看父类中是否存在此字段，如果有则返回 Class<?> superClazz = clazz.getSuperclass(); if (superClazz != null && superClazz != Object.class) { return getFieldByName(fieldName, superClazz); } // 如果本类和父类都没有，则返回空 return null; }
5.放入方法：转换方法
/** * @param excelName * @param entityClass List中对象的类型（Excel中的每一行都要转化为该类型的对象） * @param fieldMap Excel中的中文列头和类的英文属性的对应关系Map,key：取值为A、B、C、D...AA、AB、AC，value：对应bean的属性名称 * @param start 开始行数 * 属性名称接受带路径的属性名，如student.department.name等 * @return * @throws ExcelException */ public static <T> List<T> excelToList(String excelName, Class<T> entityClass, LinkedHashMap<String, String> fieldMap,int start) throws ExcelException, IOException { //将文件读入 XSSFWorkbook wb; try (InputStream in= new FileInputStream(new File(excelName))) { //创建工作簿 wb = new XSSFWorkbook(in); } if(entityClass == null){ throw new IllegalArgumentException("entityClass must not be null or empty"); } if(fieldMap == null || fieldMap.isEmpty()){ throw new IllegalArgumentException("fieldMap must not be null or empty"); } HashMap<String, Integer> letter2ColMap = new HashMap<String, Integer>(); //列名与列index的对应关系 for (Map.Entry<String, String> entry : fieldMap.entrySet()) { String cnNormalName = entry.getKey(); int index = getExcelColumnIndex(cnNormalName); letter2ColMap.put(cnNormalName, index); } // 定义要返回的list List<T> resultList = new ArrayList<T>(); try { for(int i =0; i < wb.getNumberOfSheets();i++){ Sheet sheet = wb.getSheetAt(i); // 第i个sheet // 将sheet转换为list for(int r=start; r <= sheet.getLastRowNum(); r++) { Row row = sheet.getRow(r); T entity = entityClass.newInstance(); for (Map.Entry<String, String> entry : fieldMap.entrySet()) { // 获取bean 属性名称 String enNormalName = entry.getValue(); // 根据ABC获取列号 int col = letter2ColMap.get(entry.getKey()); // 获取当前单元格中的内容 String content = null; Cell cell = row.getCell(col); System.out.println(row.getCell(col)); try{ content = cell.toString().trim(); }catch(Exception e){ throw new ExcelException("error occurs on sheet: "+ sheet.getSheetName() +" ,position:"+ entry.getKey() + col,e); } // 给对象赋值 try{ setFieldValueByName(enNormalName, content, entity); }catch(Exception e){ throw new ExcelException("error occurs on sheet: "+ sheet.getSheetName() +" ,position:"+ entry.getKey() + col ,e); } } resultList.add(entity); } } } catch (Exception e) { e.printStackTrace(); throw new ExcelException(e.getMessage(),e); }finally{ if(wb != null)wb.close(); } return resultList; }
6.开始测试：创建main方法，注意fieldMap字段要与实体类对象中字段一致
public static void main(String[] args) { String filePath ="/Users/lin/webServers/test/SHOP.105001973991432.20190417.txt.xls";//绝对路径 List<Entity> entityList = null; try { LinkedHashMap<String, String> fieldMap = new LinkedHashMap<String, String>(); fieldMap.put("A", "str1"); fieldMap.put("B", "str1"); fieldMap.put("C", "str1"); fieldMap.put("D", "str1"); fieldMap.put("E", "str1"); entityList = excelToList(filePath,Entity.class,fieldMap,2); System.out.println(entityList); } catch (Exception e) { e.p }
7. 查看结果
