Java POI注解方式---Excel文件导入工具类实现

具体代码实现

自定义java注解

package com.oa.manager.vcms.util;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
 * 自定义Excel注解
 * @author 
 * @since 2019/10/15
 *
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAttribute {
    /** 对应的列名称 */
    String name() default "";

    /** 列序号 */
    int sort();

    /** 字段类型对应的格式 */
    String format() default "";
    
    /** 是否需要校验 */
    boolean isCheck() default false;
    
    /** 校验字段长度 */
    int fieldLength() default 50;
    
    /** 校验是否可以为空 */
    boolean isEmpty() default true;

}

工具类

package com.oa.manager.vcms.util;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class ExcelImportUtil<T> {
 
    private Class<T> clazz;
    private  Field fields[];
 
    public ExcelImportUtil(Class<T> clazz) {
        this.clazz = clazz;
        fields = clazz.getDeclaredFields();
    }
 
    /**
     * 读取excel
     * @param is
     * @param rowIndex
     * @param cellIndex
     * @return
     */
    @SuppressWarnings({ "unused" })
	public List<T> readExcel(String filePath, int rowIndex,int cellIndex) {
        InputStream is = null;
        try {
        	is = new FileInputStream(filePath);
        	//WorkbookFactory工厂类生产出对应版本的Excel的workbook具体对象
            Workbook workbook = WorkbookFactory.create(is);
            Sheet sheet = workbook.getSheetAt(0);
            // 不准确
            int rowLength = sheet.getLastRowNum()+1;

            System.out.println("sheet总行数："+(sheet.getLastRowNum()+1));
            return this.readExcelData(sheet, rowIndex, cellIndex);
            
        } catch (Exception e) {
            e.printStackTrace();
        }
		return null;
    }
    
    /**
     * 收集Excel中的数据
     */
    public List<T> readExcelData(Sheet sheet, int rowIndex,int cellIndex) {
    	List<T> list = new ArrayList<T>();
    	for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            T entity = null;
				try {
					entity = this.covertObject(row, cellIndex);
					list.add(entity);
				} catch (Exception e) {
				}
        }
		return list;
    }
    
    /**
     * Excel每一行数据转换为对象
     * @param row
     * @param cellIndex
     * @return
     * @throws Exception
     */
    public T covertObject(Row row,int cellIndex) throws Exception {
    	T entity = (T) clazz.newInstance();
    	for (int cellNum = cellIndex; cellNum < row.getLastCellNum(); cellNum++) {
            Cell cell = row.getCell(cellNum);
            for (Field field : fields) {
                if(field.isAnnotationPresent(ExcelAttribute.class)){
                    field.setAccessible(true);//设置为可以访问私有变量
                    ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                    if(cellNum == ea.sort()) {
                        field.set(entity, covertAttrType(field, cell));
                    }
                }
            }
        }
    	return entity;
    }
 

    /**
     * 类型转换 将cell 单元格格式转为 字段类型
     * @throws ParseException 
     */
    private Object covertAttrType(Field field, Cell cell) throws NumberFormatException, ParseException {
        String fieldType = field.getType().getSimpleName();
        if ("String".equals(fieldType)) {
            return getValue(cell);
        }else if ("Date".equals(fieldType)) {
            return new SimpleDateFormat("yyyy-MM-dd").parse(getValue(cell)) ;
        }else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
        	return Integer.parseInt(getValue(cell));
        }else if ("long".equals(fieldType) || "Long".equals(fieldType)) {
        	return Long.parseLong(getValue(cell));
        }else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return Double.parseDouble(getValue(cell));
        }else {
            return null;
        }
    }
 
 
    /**
     * 格式转为String
     * @param cell
     * @return
     */
    public String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return cell.getRichStringCellValue().getString().trim();
            // 数字类型 小数,整数,日期
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dt = cell.getDateCellValue();
                    //DateUtil.getJavaDate(cell.getNumericCellValue());
                    System.out.println("日期："+new SimpleDateFormat("yyyy-MM-dd").format(dt));
                    return new SimpleDateFormat("yyyy-MM-dd").format(dt);
                } else {
                    // 防止数值变成科学计数法
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(num.toString());
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case Cell.CELL_TYPE_BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case Cell.CELL_TYPE_FORMULA://公式类型
            	return cell.getCellFormula();
            //其余的单元格类型统一返回""
            default:
                return "";
        }
    }
}