Java 注解配合Spring AOP 导入Excel文件

Java 注解配合Spring AOP 导入Excel文件

这个就是把上一篇，封装了一层；根据注解中配置的变量名和方法名，通过JoinPoint获取到对应的对象和方法

注解

import static java.lang.annotation.ElementType.METHOD;
import static java.lang.annotation.RetentionPolicy.RUNTIME;

import java.lang.annotation.Retention;
import java.lang.annotation.Target;

@Target({METHOD})
@Retention(RUNTIME)
public @interface ExcelImport {
	
	/**
	 * 要处理的实体类全限名
	 * @return
	 */
	public String clazzName();
	
	/**
	 * 文件参数名称
	 * 参数类型为输入流
	 * @return
	 */
	public String fileParamName();
	
	/**
	 * 获取标题的方法
	 * 方法返回值要是一个Map；  key 表头， value 实体类字段
	 * @return
	 */
	public String titles();
	
	/**
	 * 操作数据方法
	 * 为空则不会操作数据
	 * @return
	 */
	public String checkData() default "";
	
	/**
	 * 结果数据集合  clazzName实体的List集合
	 * @return
	 */
	public String retParam();
	
	/**
	 * 操作数据方法中的冗余参数； 参数类型为 Object
	 * @return
	 */
	public String extraParam() default "";
}

AOP类
导包

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.math.RoundingMode;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.collect.Lists;

代码

@Aspect
@Component
public class ExcelImportAspect {
	
	@Before("@annotation(com.excel.aop.annotation.ExcelImport)")
	public void before(JoinPoint point) throws Exception {
		MethodSignature methodSignature = (MethodSignature)point.getSignature();
		Method method = methodSignature.getMethod();
		// 获取注解Action 
		ExcelImport annotation = method.getAnnotation(ExcelImport.class);
		// 获取注解Action的value参数的值
        String clazzName = annotation.clazzName();
        String fileParamName = annotation.fileParamName();
        String titles = annotation.titles();
        String checkDataFunName = annotation.checkData();
        String retParamName = annotation.retParam();
        String extraParamName = annotation.extraParam();
		
        List<Object> retParam = new ArrayList<>();
        Object extraParam = null;
        InputStream is = null;
        
        // 获取切点方法入参列表
        Object[] objArray = point.getArgs();
        String[] parameterNames = methodSignature.getParameterNames();
        for (int i = 0; i < parameterNames.length; i++) {
			if (fileParamName.equals(parameterNames[i])) {
				Object obj = objArray[i];
				if(obj instanceof InputStream){
					is = (InputStream) objArray[i];
				} else if (obj instanceof MultipartFile) {
					MultipartFile file = (MultipartFile)objArray[i];
					is = file.getInputStream();
				} else if (obj instanceof File) {
					File file = (File)objArray[i];
					is = new FileInputStream(file);
				}
			}
			
			if (retParamName.equals(parameterNames[i])) {
				Object obj = objArray[i];
				if(obj instanceof List){
					retParam = (List<Object>) obj;
				}
			}
			
			if (StringUtils.isNotBlank(checkDataFunName) && extraParamName.equals(parameterNames[i])) {
				extraParam = objArray[i];
			}
		}
        
        Class<?> clazz = methodSignature.getDeclaringType();
        Object pointClazz = clazz.newInstance();
        Method[] m = pointClazz.getClass().getMethods();
        
        List<Object> excelList = new ArrayList<>();
        InputStream stream = FileMagic.prepareToCheckMagic(is);
        FileMagic fm = FileMagic.valueOf(stream);
		switch (fm) {
			case OLE2:
				excelList = readXls(stream, Class.forName(clazzName), pointClazz, m, titles, checkDataFunName, extraParam);
				break;
			case OOXML:
				excelList = readXlsx(stream, Class.forName(clazzName), pointClazz, m, titles, checkDataFunName, extraParam);
				break;
			default:
		}
		retParam.addAll(excelList);
        
	}
	
	private List<Object> readXls(InputStream stream, Class<?> calzz, Object obj, Method[] m, String titleName, String checkDataFunName, Object o) throws Exception {
		try (Workbook wk = new HSSFWorkbook(stream)) {
			return getDataList(wk, calzz, obj, m, titleName, checkDataFunName, o);
		}
	}
	
	private List<Object> readXlsx(InputStream stream, Class<?> calzz, Object obj, Method[] m, String titleName, String checkDataFunName, Object o) throws Exception {
		try (Workbook wk = new XSSFWorkbook(stream)) {
			return getDataList(wk, calzz, obj, m, titleName, checkDataFunName, o);
		}
	}
	
	private List<Object> getDataList(Workbook wk, Class<?> clazz, Object obj, Method[] m, String titleName, String checkDataFunName, Object o) throws Exception {
		List<Object> list = Lists.newArrayList();

        Map<String, String> titleMaps = new HashMap<>();
		for (int i = 0; i < m.length; i++) {
        	if (titleName.equals(m[i].getName())) {
        		titleMaps = (Map<String, String>) m[i].invoke(obj);
        		break;
        	}
        }
		
		if (titleMaps == null || titleMaps.size() == 0) {
			return new ArrayList<>();
		}
		
		// 只解析第一页的数据
		Sheet sheet = wk.getSheetAt(0);
		// 第一行是标题
		Row titles = sheet.getRow(0);
		Map<Integer, String> titMap = new HashMap<>();
		Iterator<Cell> tit = titles.cellIterator();
		while (tit.hasNext()) {
			Cell cell = tit.next();
			String s = String.valueOf(getCellValue(sheet, cell));
			if (StringUtils.isNotBlank(s) && StringUtils.isNotBlank(titleMaps.get(s))) {
				titMap.put(cell.getColumnIndex(), titleMaps.get(s));
			}
		}
		
		Iterator<Row> rit = sheet.rowIterator();
		while (rit.hasNext()) {
			Row row = rit.next();
			if (row.getRowNum() == 0) {
				// 表头跳过
				continue;
			}
			Iterator<Cell> cit = row.cellIterator();
			Object t = clazz.newInstance();
			while (cit.hasNext()) {
				Cell cell = cit.next();
				setValue(t, titMap.get(cell.getColumnIndex()), String.valueOf(getCellValue(sheet, cell)));
			}

            if (StringUtils.isNotBlank(checkDataFunName)) {
            	for (int i = 0; i < m.length; i++) {
                	if (checkDataFunName.equals(m[i].getName())) {
                		m[i].invoke(obj, t, o);
                		break;
                	}
                }
            }
			list.add(t);
		}
		return list;
	}
	
	private static Object getCellValue(Sheet sheet, Cell cell) {
        Object cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    //解决当读取的单元格的内容，被自动加上".0"后缀
                    cell.setCellType(CellType.STRING); //若读取的单元格的值没有".0",则可吧把cell类型转为String，则会去掉".0"
                    cellValue = cell.getStringCellValue();
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue();
                break;
            case FORMULA: // 公式
                cellValue = getExcelForFORMULAEva(sheet,cell);
                break;
            case BLANK: // 空值
                break;
            case ERROR: // 故障
                break;
            default:
                break;
        }
        return cellValue;
    }
	
	private static Object getExcelForFORMULAEva(Sheet sheet,Cell cell) {
        Object cellValue = "";
        FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
        cell = evaluator.evaluateInCell(cell);   //计算结果的类型替换单元格的类型
        cellValue = getCellValue(sheet, cell);
        return cellValue;
    }

	private void setValue(Object t, String name, String value) throws Exception {
		Method[] m = t.getClass().getMethods();
    	Class<?> typeCal = null;
        for (int i = 0; i < m.length; i++) {
        	if (("get" + name).toLowerCase().equals(m[i].getName().toLowerCase())) {
        		typeCal = m[i].getReturnType();
        		break;
        	}
        }
    	for (int i = 0; i < m.length; i++) {
            if (("set" + name).toLowerCase().equals(m[i].getName().toLowerCase())) {
            	Object v = typeConversion(value, typeCal);
                m[i].invoke(t, v);
                break;
            }
        }
    }
	
	private static Object typeConversion(String value, Class<?> typeCalzz) throws Exception {
		Object v = value; 
    	if (Date.class.equals(typeCalzz)) {
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		v = sdf.parse(value);
    	}
    	if (Integer.class.equals(typeCalzz)) {
    		v = Integer.valueOf(value);
    	}
    	if (Long.class.equals(typeCalzz)) {
    		v = Long.valueOf(value);
    	}
    	if (Float.class.equals(typeCalzz)) {
    		v = Float.valueOf(value);
    	}
    	if (Double.class.equals(typeCalzz)) {
    		v = Double.valueOf(value);
    	}
    	if (Boolean.class.equals(typeCalzz)) {
    		v = Boolean.valueOf(value);
    	}
    	if (Short.class.equals(typeCalzz)) {
    		v = Short.valueOf(value);
    	}
    	return v;
	}
}

测试

@Service
public class TestService {
	
	@ExcelImport(clazzName="com.excel.test.TestEntity", 
			 fileParamName = "file", retParam = "dataList", titles = "getTitles", checkData = "checkData")
	public void upload(MultipartFile file, List<TestEntity> dataList, HttpServletResponse response) {
		System.out.println("数据集合");
		System.out.println(dataList);	
	}
	
	public Map<String, String> getTitles() {
		Map<String, String> titles = new LinkedHashMap<>();
		titles.put("测试String", "uuid");
		titles.put("测试int", "num");
		titles.put("测试long", "num1");
		titles.put("测试boolean", "bool");
		titles.put("测试Date", "time");
		return titles;
	}

	public void checkData(TestEntity entity, Object o) {
		System.out.println("数据");
		System.out.println(entity);
	}
}