一种基于注解解析EXCEL的方式

原创已于 2023-02-28 21:05:35 修改 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

于 2021-05-01 12:13:40 首次发布

java 专栏收录该内容

1 篇文章

订阅专栏

1、前言

1.本文提到的解析方式，是一种轻量的解析方式，不适用于大excel，如果excel文件比较大请用easyexcel。

2.主要用到的技术就是注解，解析时扫描dto属性对应的注解，然后验证，验证成功赋值给相应的属性。

3.所列举的场景覆盖了本人目前接触的到范围，应该能适用于大部分的场景。

4.若有缺陷请提出，谢谢。

2.1 改动记录

2021.05.14 增加数值记录。

2023.02.28 优化性能，尝试解耦，暂没找到多列去重的好方式。

2.2 、项目结构

2.2、相关依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>

3、相关类

package com.workSupport.operationEXCEL;

public abstract class IHandler {

    private StringBuilder errMsg;

    public IHandler(StringBuilder errMsg){
        assert errMsg != null;
        this.errMsg = errMsg;
    }

    public StringBuilder getErrMsg() {
        return errMsg;
    }

    /**
     * 元素重复
     * @param rowNum 从0开始
     * @param colNum 从0开始
     * @return
     */
    abstract public boolean duplicate(int rowNum, int colNum);

    /**
     * 元素为空
     * @param rowNum 从0开始
     * @param colNum 从0开始
     * @return
     */
    abstract public boolean isNull(int rowNum, int colNum);

    /**
     * 元素超出长度
     * @param rowNum 从0开始
     * @param colNum 从0开始
     * @return
     */
    abstract public boolean beyondLength(int rowNum, int colNum);

    /**
     * 正则不匹配
     * @param rowNum 从0开始
     * @param colNum 从0开始
     * @return
     */
    abstract public boolean unmatchRegex(int rowNum, int colNum);

    /**
     * 自定义处理
     * @param cellName 列名称
     * @param cellValue 具体行列的值
     * @param sheetNum 从0开始
     * @param rowNum 从0开始
     * @param colNum 从0开始
     * @return
     */
    abstract public void callback(String cellName, String cellValue, int sheetNum, int rowNum, int colNum);
}

package com.workSupport.operationEXCEL;

import java.text.MessageFormat;

public class DefaultHandler extends IHandler {

    public DefaultHandler(StringBuilder errMsg) {
        super(errMsg);
    }

    @Override
    public boolean duplicate(int rowNum, int colNum) {
        getErrMsg().append(MessageFormat.format("{0}行{1}列重复,", rowNum + 1, colNum + 1));
        return false;
    }

    @Override
    public boolean isNull(int rowNum, int colNum) {
        getErrMsg().append(MessageFormat.format("{0}行{1}列不能为空,", rowNum + 1, colNum + 1));
        return false;
    }

    @Override
    public boolean beyondLength(int rowNum, int colNum) {
        getErrMsg().append(MessageFormat.format("{0}行{1}列超长,", rowNum + 1, colNum + 1));
        return false;
    }

    @Override
    public boolean unmatchRegex(int rowNum, int colNum) {
        getErrMsg().append(MessageFormat.format("{0}行{1}列不符合正则规则,", rowNum + 1, colNum + 1));
        return false;
    }

    @Override
    public void callback(String cellName, String cellValue, int sheetNum, int rowNum, int colNum) {
        // do nothing
    }
}

package com.workSupport.operationEXCEL;

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

/**
 * excel 解析注解
 * @author yu
 *
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelResolveAnno {
	
	/**
	 * excel中的标题
	 * @return
	 */
	String headerName() ;
	
	/**
	 * 字段的最大长度
	 * @return
	 */
	int maxLenght() default -1;
	
	/**
	 * 检查非空
	 */
	boolean checkNull() default false;
	
	/**
	 * 正则校验
	 */
	String regex() default "";
	
	/**
	 * 检查是否重复
	 * @return
	 */
	boolean checkDuplicate() default false;

}

package com.workSupport.operationEXCEL;

import java.util.Date;

/**
 * 测试用的dto
 * @author yu
 *
 */
public class TestDTO {

	@ExcelResolveAnno(headerName = "姓名" , maxLength = 50 , checkNull = true , checkDuplicate = true)
	private String name;
	
	private Date birthDate;
	
	@ExcelResolveAnno(headerName = "出生日期" , maxLength = 10 , checkNull = true , regex = "\\d{4}-\\d{2}-\\d{2}")
	private String birthDateStr;

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Date getBirthDate() {
		return birthDate;
	}

	public void setBirthDate(Date birthDate) {
		this.birthDate = birthDate;
	}

	public String getBirthDateStr() {
		return birthDateStr;
	}

	public void setBirthDateStr(String birthDateStr) {
		this.birthDateStr = birthDateStr;
	}

	@Override
	public String toString() {
		return "ExcelResolveDTO [name=" + name + ", birthDate=" + birthDate + ", birthDateStr=" + birthDateStr + "]";
	}
	
	
}

package com.workSupport.operationEXCEL;

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 解析excel工具
 * @author yu
 *
 * @param <T>
 */
public class ExcelResolveUtil<T> {

	/**
	 * 解析excel
	 * @param errMsg
	 * @param excelFile
	 * @param clazz
	 * @return
	 * @throws Exception
	 */
	public List<T> resolveExcel(StringBuilder errMsg, File excelFile, Class<T> clazz) throws Exception
	{
		return resolveExcel(new DefaultHandler(errMsg), excelFile, clazz);
	}

	public List<T> resolveExcel(IHandler handler, File excelFile, Class<T> clazz) throws Exception
	{

		List<T> resolveList = new LinkedList<T>();
		if(excelFile != null)
		{
			Map<String , Integer>  cellIndexMap = new HashMap<String , Integer>();
			XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(excelFile));
            for (int sheetNum = 0, totalSheetNum = workbook.getNumberOfSheets(); sheetNum < totalSheetNum; ++sheetNum) {
                XSSFSheet xssfSheet = workbook.getSheetAt(sheetNum);
                if (xssfSheet == null) {
					handler.getErrMsg().append("sheet为空!");
                	break;
                }
                
                boolean addFlag = true;                
                Map<String , Set<String>> checkDuplicateMap = new HashMap<>();
                
                for (int rowNum = 0, totalRowNum = xssfSheet.getLastRowNum(); rowNum <= totalRowNum; ++ rowNum)
                {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if(xssfRow != null) 
                    {
                    	T t = clazz.newInstance();
                    	fieldEach : for(Field field : clazz.getDeclaredFields())
                    	{
                    		ExcelResolveAnno excelResolveAnno = field.getAnnotation(ExcelResolveAnno.class);
                    		if(excelResolveAnno != null)
                    		{
                    			String cellName = field.getName();
								if(rowNum == 0) {    //将excle中的表头和dto的属性建立联系
									cellEach:
									for (int cellNum = 0, totalCellNum = xssfRow.getLastCellNum(); cellNum < totalCellNum; ++cellNum) {

										String cellValue = getCellValue(xssfRow, cellNum);
										if (excelResolveAnno.headerName().equals(cellValue)) {
											cellIndexMap.put(cellName, cellNum);
										}

										// 如果校验非空，生成重复校验的set
										if (excelResolveAnno.checkDuplicate()) {
											checkDuplicateMap.put(cellName, new HashSet<String>());
										}
									}
								}
								else
								{ 	//content
									addFlag = true;
									int colNum = cellIndexMap.get(cellName);
									String cellValue = getCellValue(xssfRow , colNum);
									if(excelResolveAnno.checkNull() && StringUtils.isBlank(cellValue))
									{
										addFlag = handler.isNull(rowNum, colNum);
										break fieldEach;
									}

									if(cellValue == null)
									{
										continue;
									}
									else if(cellValue.length() > excelResolveAnno.maxLength())
									{
										addFlag = handler.beyondLength(rowNum, colNum);
										break fieldEach;
									}

									if(excelResolveAnno.regex() != null && excelResolveAnno.regex().length() > 0)
									{
										if(!cellValue.matches(excelResolveAnno.regex()))
										{
											addFlag = handler.unmatchRegex(rowNum, colNum);
											break fieldEach;
										}
									}
									if(excelResolveAnno.checkDuplicate())
									{
										Set<String> duplicateSet = checkDuplicateMap.get(cellName);
										if(duplicateSet.contains(cellValue))
										{
											addFlag = handler.duplicate(rowNum, colNum);
											break fieldEach;
										}
									}

									Field declaredField = t.getClass().getDeclaredField(cellName);
									declaredField.setAccessible(true);
									declaredField.set(t, cellValue);
								}
							}
                    		else
                    		{
                    			addFlag = false;
                    		}
	                    }
                    	
                    	if(addFlag && t != null)
                    	{
                    		resolveList.add(t);
                    		
                    		for(Field field : t.getClass().getDeclaredFields())
                    		{
                    			ExcelResolveAnno excelResolveAnno = field.getAnnotation(ExcelResolveAnno.class);
                    			if(excelResolveAnno != null)
                    			{
                    				if(excelResolveAnno.checkDuplicate())
                    				{
                    					String fieldName = field.getName();
                    					field.setAccessible(true);
                    					Set<String> duplicateSet = checkDuplicateMap.get(fieldName);
                    					duplicateSet.add((String)field.get(t));
                    				}
                    			}
                    		}
                    	}
                    }
                }
            }
		}
		else
		{
			handler.getErrMsg().append("文件为空！");
		}
		return resolveList;
	}
	
	/**
	 * 浠tring鐨勬柟寮忚繑鍥瀋ell涓殑鍐呭
	 * @param xssfRow
	 * @param cellNum
	 * @return
	 */
	private String getCellValue(XSSFRow xssfRow, int cellNum) 
	{
		// TODO Auto-generated method stub
		if(xssfRow != null)
		{
			XSSFCell xssfCell = xssfRow.getCell(cellNum);
			CellType cellType = xssfCell.getCellType();
			if(CellType._NONE.equals(cellType))
			{
				return null;
			}
			else if(CellType.BLANK.equals(cellType))
			{
				return null;
			}
			else if(CellType.ERROR.equals(cellType))
			{
				return null;
			}
			else if(CellType.BOOLEAN.equals(cellType))
			{
				boolean cellValue = xssfCell.getBooleanCellValue();
				return Boolean.toString(cellValue);
			}
			else if(CellType.FORMULA.equals(cellType))
			{
				return xssfCell.getCellFormula();
			}
			else if(CellType.STRING.equals(cellType))
			{
				return xssfCell.getStringCellValue();
			}
			else if(CellType.NUMERIC.equals(cellType))
			{
				double cellValue = xssfCell.getNumericCellValue();
				return String.valueOf(cellValue);
			}
		}
		return null;
	}
	
	public void mannual_free(List<T> result)
	{
		if(result != null)
		{
			result.clear();
		}
	}

	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		ExcelResolveUtil<TestDTO> excelResolveMain = new ExcelResolveUtil<>();
		StringBuilder stringBuilder = new StringBuilder();
		List<TestDTO> result = excelResolveMain.resolveExcel(stringBuilder,
				new File("E:\\javaFile\\thinkTest_space\\ThinkTestInMaven\\src\\main\\resources\\com\\workSupport\\operExcel\\ExcelResolve.xlsx"),
				TestDTO.class);
		System.out.println(result);
		excelResolveMain.mannual_free(result);
		System.out.println(stringBuilder);
	}

}