ExcelUtil借助反射和POI对Excel读取,省略了以往读取Excel的繁琐步骤

package com.lkx.util; import com.lkx.util.strategy.Context; import com.lkx.util.strategy.ExcelVersionStrategy; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ooxml.POIXMLDocumentPart; import org.apache.poi.openxml4j.opc.PackagePartName; import org.apache.poi.openxml4j.opc.PackageRelationship; import org.apache.poi.openxml4j.opc.TargetMode; import org.apache.poi.ss.formula.functions.T; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellAddress; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFRelation; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.BeanUtils; import javax.imageio.ImageIO; import java.awt.image.BufferedImage; import java.io.*; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.math.BigDecimal; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; import java.util.concurrent.CountDownLatch; import java.util.regex.Pattern; /** * ClassName: ExcelUtilBase * Function: ExcelUtil基础类 * Date: 2019/7/13 9:55 * @author likaixuan * version V1.0 */ public class ExcelUtilBase { /** * @Function 把传进指定格式的字符串解析到Map中 * @author likaixuan * @Date 2019-07-05 15:09 * @param keyValue * @return java.util.Map<java.lang.String,java.lang.String> */ public static Map<String, String> getMap(String keyValue) { Map<String, String> map = new HashMap<String, String>(20); if (keyValue != null) { String[] str = keyValue.split(","); for (String element : str) { String[] str2 = element.split(":"); map.put(str2[0], str2[1]); } } return map; } /** * @Function 把传进指定格式的字符串解析到Map中 * @author likaixuan * @Date 2019-07-05 15:09 * @param clazz * @return java.util.Map<java.lang.String,java.lang.String> */ public static Map<String, String> getMap(Class<T> clazz) throws NoSuchFieldException { Map<String, String> map = new HashMap<String, String>(20); Map<String, String> noExcelMap = new HashMap<String, String>(20); Field field; Field[] fields=clazz.getDeclaredFields(); for (int i = 0; i <fields.length ; i++) { fields[i].setAccessible(true); } for (int i = 0; i <fields.length ; i++) { field=clazz.getDeclaredField(fields[i].getName()); Excel column=field.getAnnotation(Excel.class); if(column!=null){ map.put(column.title(),field.getName()); } noExcelMap.put(fields[i].getName(),fields[i].getName()); } if(!map.isEmpty()) { return map; } return noExcelMap; } /** * * @param obj * @return * @throws NoSuchFieldException */ public static Map<String, Object> getMap(Object obj) { Map<String, Object> map = new HashMap(20); Field[] fields=obj.getClass().getDeclaredFields(); for (int j = 0; j < fields.length; j++) { fields[j].setAccessible(true); // 字段值 try { map.put(fields[j].getName(),fields[j].get(obj)); } catch (Exception e) { System.out.println("字段["+fields[j].getName()+"]解析异常"); } } return map; } /** * @Function 把传进指定格式的字符串解析到List中 * @author likaixuan * @Date 2019-07-05 15:08 * @param keyValue * @return java.util.List<java.lang.String> */ public static List<String> getList(String keyValue) { List<String> list = new ArrayList<String>(); if (keyValue != null) { String[] str = keyValue.split(","); for (String element : str) { String[] str2 = element.split(":"); list.add(str2[0]); } } return list; } /** * @Function 把传进指定格式的字符串解析到List中 * @author likaixuan * @Date 2019-07-05 15:08 * @param clazz * @return java.util.List<java.lang.String> */ public static List<String> getList(Class<T> clazz) throws NoSuchFieldException { List<String> list = new ArrayList<String>(); List<String> noExcellist = new ArrayList<String>(); Field field; Field[] fields=clazz.getDeclaredFields(); for (int i = 0; i <fields.length ; i++) { fields[i].setAccessible(true); } for (int i = 0; i <fields.length ; i++) { field=clazz.getDeclaredField(fields[i].getName()); Excel column=field.getAnnotation(Excel.class); if(column!=null){ list.add(column.title()); } noExcellist.add(fields[i].getName()); } if(list.size()>0){ return list; } return noExcellist; } public static List getResult(ExcelParam excelParam) throws Exception { Set keySet = null; //新加入了注解，如果map为空，则自动从class中的注解自动查找 if(excelParam.getMap() == null || excelParam.getMap().size()==0){ excelParam.setMap(getMap(excelParam.getClazz())); keySet = excelParam.getMap().keySet(); }else { // 返回键的集合 keySet = excelParam.getMap().keySet(); } List<Object> list = new ArrayList(); String fileType = ""; InputStream is = null; Workbook wb = null; if(excelParam.getStream()){ is = new ByteArrayInputStream(excelParam.getBuf()); wb = WorkbookFactory.create(is); }else{ fileType = excelParam.getFilePath().substring(excelParam.getFilePath().lastIndexOf(".") + 1, excelParam.getFilePath().length()); is = new FileInputStream(excelParam.getFilePath()); Context context = new Context(new ExcelVersionStrategy()); wb = context.executeStrategy(fileType,is,wb); } int startSheetNum = 0; int endSheetNum = 1; if (null != excelParam.getSheetIndex()) { startSheetNum = excelParam.getSheetIndex() - 1; endSheetNum = excelParam.getSheetIndex(); } // 获取每个Sheet表 for (int sheetNum = startSheetNum; sheetNum < endSheetNum; sheetNum++) { // 记录第x行为表头 int rowNum_x = -1; // 存放每一个field字段对应所在的列的序号 Map<String, Integer> cellmap = new HashMap<String, Integer>(20); // 存放所有的表头字段信息 List<String> headlist = new ArrayList(); Sheet hssfSheet = wb.getSheetAt(sheetNum); //判断表中是否含有合并单元格-为了忽略导出时候带的表头 if(hssfSheet.getNumMergedRegions() > 0 ? true : false){ excelParam.setRowNumIndex(2); } // 设置默认最大行为50w行 if (hssfSheet != null && hssfSheet.getLastRowNum() > 500000) { throw new Exception("Excel 数据超过50w行,请检查是否有空行,或分批导入"); } // 循环行Row for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { // 如果传值指定从第几行开始读，就从指定行寻找，否则自动寻找 if (excelParam.getRowNumIndex() != null && rowNum_x == -1) { rowNum = excelParam.getRowNumIndex() - 1; Row hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { throw