利用注解和POI导入Excel

声明：前段时间，我在开源中国码云上面，看到项目jeesite中导入Excel是利用注解来操作，加上之前我在工作中也有批量处理导入Excel的功能，但是没有使用注解，代码不简洁。所以，借鉴这种方式，自己有码一遍代码，不算原创。

先了解下，java注解，这是为博客园的帖子：http://www.cnblogs.com/peida/archive/2013/04/24/3036689.html

因此定义以下注解：

@Target({ElementType.METHOD,ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
    int sort() default 0;
}

Excel工具类

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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 poi.demo.annotation.ExcelField;

public class ExcelUtil {

    private Workbook wb;

    private Sheet sheet;

    private int headerNum;//标题行号

    public ExcelUtil(String fileName, int headerNum, int sheetIndex) throws FileNotFoundException, IOException{
        this(new File(fileName), headerNum, sheetIndex);

    }

    public ExcelUtil(File file, int headerNum, int sheetIndex) throws FileNotFoundException, IOException {
        this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);

    }

    public ExcelUtil(String fileName, InputStream is, int headerNum, int sheetIndex) throws IOException {
        // TODO Auto-generated constructor stub
        if(fileName.toLowerCase().endsWith("xls")){    
            this.wb = new HSSFWorkbook(is);    
        }else if(fileName.toLowerCase().endsWith("xlsx")){  
            this.wb = new XSSFWorkbook(is);
        }else{  
            throw new RuntimeException("文档格式不正确!");
        }  
        this.sheet= this.wb.getSheetAt(sheetIndex);
        this.headerNum=headerNum;
    }

    /** *@title getDataList *@description 获取转化后的Excel数据列表 *@param clazz *@return *@throws InstantiationException *@throws IllegalAccessException List<E> */
    public <E> List<E> getDataList(Class<E> clazz) throws InstantiationException, IllegalAccessException{
        List<Object[]> annotationList = new ArrayList<Object[]>();
        //获取带有注解的属性
        Field[] fs = clazz.getDeclaredFields();
        for(Field f : fs){
            ExcelField ef = f.getAnnotation(ExcelField.class);
            if(ef != null){
                annotationList.add(new Object[]{ef,f});
            }
        }
        //排序
        Collections.sort(annotationList, new Comparator<Object[]>() {
            public int compare(Object[] o1, Object[] o2) {
                return new Integer(((ExcelField)o1[0]).sort()).compareTo(new Integer(((ExcelField)o2[0]).sort()));
            }
        });

        List<E> dataList = new ArrayList<E>();

        for(int i=this.getDataRowNum();i<=this.getLastDataRowNum();i++){
            E e = (E)clazz.newInstance();
            int column = 0;
            Row row = this.getRow(i);
            for(Object[] obj : annotationList){
                Object val = this.getCellValue(row, column++);
                if(val != null){
                    @SuppressWarnings("unused")
                    ExcelField ef = (ExcelField)obj[0];
                    if(obj[1] instanceof Field){
                        ReflectionUtils.invokeSetter(e, ((Field)obj[1]).getName(), val);
                    }
                }
            }
            dataList.add(e);
        }
        return dataList;
    }

    /** *@title getRow *@description 获取行数据 *@param rowNum *@return Row */
    public Row getRow(int rowNum){
        return this.sheet.getRow(rowNum);

    }
    /** *@title getDataRowNum *@description 获取数据行号 *@return int */
    public int getDataRowNum(){
        return this.headerNum+1;
    }

    /** *@title getLastDataRowNum *@description 获取数据最后一行行号 *@return int */
    public int getLastDataRowNum(){
        return this.sheet.getLastRowNum()+headerNum;
    }

    /** *@title getCellValue *@description 获取单元格的值 *@param row *@param column *@return Object */
    public Object getCellValue(Row row, int column){
        Object val = "";
        try{
            Cell cell = row.getCell(column);
            if (cell != null){
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
                    val = cell.getNumericCellValue();
                }else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
                    val = cell.getStringCellValue();
                }else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
                    val = cell.getCellFormula();
                }else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
                    val = cell.getBooleanCellValue();
                }else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
                    val = cell.getErrorCellValue();
                }
            }
        }catch (Exception e) {
            return val;
        }
        return val; 

    }

    public Workbook getWb() {
        return wb;
    }

    public void setWb(Workbook wb) {
        this.wb = wb;
    }

    public Sheet getSheet() {
        return sheet;
    }

    public void setSheet(Sheet sheet) {
        this.sheet = sheet;
    }

    public int getHeaderNum() {
        return headerNum;
    }

    public void setHeaderNum(int headerNum) {
        this.headerNum = headerNum;
    }

}

反射工具类

package poi.demo;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.Validate;

public class ReflectionUtils {

    private static final String SETTER_PREFIX = "set";

    private static final String GETTER_PREFIX = "get";

    /** *@title invokeSetter *@description 调用Setter方法, 仅匹配方法名。 * 支持多级，如：对象名.对象名.方法 *@param obj *@param propertyName *@param value void */
    public static void invokeSetter(Object obj, String propertyName, Object value){
        Object object = obj;
        String[] names = StringUtils.split(propertyName, ".");
        for (int i=0; i<names.length; i++){
            if(i<names.length-1){
                String getterMethodName = GETTER_PREFIX + StringUtils.capitalize(names[i]);
                object = invokeMethod(object, getterMethodName, new Class[] {}, new Object[] {});
            }else{
                String setterMethodName = SETTER_PREFIX + StringUtils.capitalize(names[i]);
                invokeMethodByName(object, setterMethodName, new Object[] { value });
            }
        }
    }

    public static Object invokeMethod(final Object obj, final String methodName, final Class<?>[] parameterTypes,
            final Object[] args) {
        Method method = getAccessibleMethod(obj, methodName, parameterTypes);
        if (method == null) {
            throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + obj + "]");
        }

        try {
            return method.invoke(obj, args);
        } catch (Exception e) {
            //throw new convertReflectionExceptionToUnchecked(e);
        }
        return null;
    }

    /** * 直接调用对象方法, 无视private/protected修饰符， * 用于一次性调用的情况，否则应使用getAccessibleMethodByName()函数获得Method后反复调用. * 只匹配函数名，如果有多个同名函数调用第一个。 */
    public static Object invokeMethodByName(final Object obj, final String methodName, final Object[] args) {
        Method method = getAccessibleMethodByName(obj, methodName);
        if (method == null) {
            throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + obj + "]");
        }

        try {
            return method.invoke(obj, args);
        } catch (Exception e) {
            //throw convertReflectionExceptionToUnchecked(e);
        }
        return null;
    }

    public static Method getAccessibleMethod(final Object obj, final String methodName,
            final Class<?>... parameterTypes) {
        //Validate.notNull(obj, "object can't be null");
        //Validate.notBlank(methodName, "methodName can't be blank");

        for (Class<?> searchType = obj.getClass(); searchType != Object.class; searchType = searchType.getSuperclass()) {
            try {
                Method method = searchType.getDeclaredMethod(methodName, parameterTypes);
                makeAccessible(method);
                return method;
            } catch (NoSuchMethodException e) {
                // Method不在当前类定义,继续向上转型
                continue;// new add
            }
        }
        return null;
    }

    public static Method getAccessibleMethodByName(final Object obj, final String methodName) {
        Validate.notNull(obj, "object can't be null");
        Validate.notBlank(methodName, "methodName can't be blank");

        for (Class<?> searchType = obj.getClass(); searchType != Object.class; searchType = searchType.getSuperclass()) {
            Method[] methods = searchType.getDeclaredMethods();
            for (Method method : methods) {
                if (method.getName().equals(methodName)) {
                    makeAccessible(method);
                    return method;
                }
            }
        }
        return null;
    }

    public static void makeAccessible(Method method) {
        if ((!Modifier.isPublic(method.getModifiers()) || !Modifier.isPublic(method.getDeclaringClass().getModifiers()))
                && !method.isAccessible()) {
            method.setAccessible(true);
        }
    }

    public static void makeAccessible(Field field) {
        if ((!Modifier.isPublic(field.getModifiers()) || !Modifier.isPublic(field.getDeclaringClass().getModifiers()) || Modifier
                .isFinal(field.getModifiers())) && !field.isAccessible()) {
            field.setAccessible(true);
        }
    }
}

例子：

import poi.demo.annotation.ExcelField;

public class People {
    @ExcelField(sort=0)
    private String name;
    @ExcelField(sort=1)
    private String sex;
    @ExcelField(sort=2)
    private String color;

    private int age;

    public String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getColor() {
        return color;
    }

    public void setColor(String color) {
        this.color = color;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

}

测试用例：

package poi.demo;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import poi.demo.annotation.ExcelField;

public class ImportTest {

    public static void main(String[] args) throws FileNotFoundException, IOException, InstantiationException, IllegalAccessException {
        ExcelUtil ex = new ExcelUtil("D://test.xlsx",0,0);

        List<People> list = ex.getDataList(People.class);
        for(People p : list){
            //业务代码         System.out.println(p.getName()+"是"+p.getColor()+"人，性别为"+p.getSex());
        }
    }

}

转载于:https://my.oschina.net/kevin1992/blog/913524