封装poi库，利用反射读取Excel（通用）

2019独角兽企业重金招聘Python工程师标准>>>

 maven 导入

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.9</version>
</dependency>

Excel 导入工具类

package com.qf58.supplier.util;


import com.qf58.supplier.entity.excel.SupplierExcel;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: zhubo
 * Date: 2017-11-30
 * Time: 19:24
 */
public class ExcelReaderUtils {

    /**
     * suffix of excel 2003
     */
    public static final String OFFICE_EXCEL_V2003_SUFFIX = "xls";
    /**
     * suffix of excel 2007
     */
    public static final String OFFICE_EXCEL_V2007_SUFFIX = "xlsx";
    /**
     * suffix of excel 2010
     */
    public static final String OFFICE_EXCEL_V2010_SUFFIX = "xlsx";

    public static final String EMPTY = "";
    public static final String DOT = ".";
    public static final String NOT_EXCEL_FILE = " is Not a Excel file!";
    public static final String PROCESSING = "Processing...";


    public static void main(String[] args) throws IOException {
        try {
            List<SupplierExcel> excels = readExcel("D:\\SRM数据导入1201更新.xlsx",new SupplierExcel(),null);
            System.out.println(excels.size());
            System.out.println(excels);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Check which version of The Excel file is. Throw exception if Excel file path is illegal.
     * @param path the Excel file
     * @param obj obj of convert
     * @param num column index array of Excel
     * @param <T> Type of obj
     * @return a list that contains T from Excel.
     * @throws Exception
     */
    public static <T> List<T> readExcel(String path,T obj,Integer ... num) throws Exception {
        if (StringUtils.isBlank(path)) {
            throw new IllegalArgumentException(path + " excel file path is either null or empty");
        } else {
            String suffiex = getSuffiex(path);
            if(StringUtils.isBlank(suffiex)){
                throw new IllegalArgumentException(path + " suffiex is either null or empty");
            }
            if(num == null || (num != null && num.length <= 0)){
                num = new Integer[obj.getClass().getDeclaredFields().length];
                for(int i = 0 ; i < num.length ; i++){
                    num[i] = i;
                }
            }
            if (OFFICE_EXCEL_V2003_SUFFIX.equals(suffiex)) {
                return readXls2003(path,obj,num);
            } else if (OFFICE_EXCEL_V2007_SUFFIX.equals(suffiex)) {
                return readXlsx2007_2010(path,obj,num);
            } else if (OFFICE_EXCEL_V2010_SUFFIX.equals(suffiex)) {
                return readXlsx2007_2010(path,obj,num);
            } else {
                throw new IllegalArgumentException(path + NOT_EXCEL_FILE);
            }
        }
    }



    public static <T> List<T> readExcelIO(String realName,InputStream is,T obj,Integer ... num) throws Exception {
        if (StringUtils.isBlank(realName)) {
            throw new IllegalArgumentException(realName + " excel file path is either null or empty");
        } else {
            String suffiex = getSuffiex(realName);
            if(StringUtils.isBlank(suffiex)){
                throw new IllegalArgumentException(realName + " suffiex is either null or empty");
            }
            if(num == null || (num != null && num.length <= 0)){
                num = new Integer[obj.getClass().getDeclaredFields().length];
                for(int i = 0 ; i < num.length ; i++){
                    num[i] = i;
                }
            }
            if (OFFICE_EXCEL_V2003_SUFFIX.equals(suffiex)) {
                return readXlsIO2003(is,obj,num);
            } else if (OFFICE_EXCEL_V2007_SUFFIX.equals(suffiex)) {
                return readXlsxIO2007_2010(is,obj,num);
            } else if (OFFICE_EXCEL_V2010_SUFFIX.equals(suffiex)) {
                return readXlsxIO2007_2010(is,obj,num);
            } else {
                throw new IllegalArgumentException(realName + NOT_EXCEL_FILE);
            }
        }
    }



    /**
     *
     * @param path the path of the Excel
     * @param obj  文件实体类
     * @param num  对应Excel中列对应标识 计数从0开始
     * @param <T>  泛型类型
     * @return
     * @throws Exception
     */
    public static <T> List<T> readXlsx2007_2010(String path,T obj,Integer... num) throws Exception{
        System.out.println(PROCESSING + path);
        InputStream is = new FileInputStream(path);
        return readXlsxIO2007_2010(is,obj,num);
    }



    /**
     *
     * @param is the path of the Excel
     * @param obj  文件实体类
     * @param num  对应Excel中列对应标识 计数从0开始
     * @param <T>  泛型类型
     * @return
     * @throws Exception
     */
    public static <T> List<T> readXlsxIO2007_2010(InputStream is,T obj,Integer... num) throws Exception{
        System.out.println(PROCESSING);
        List<T> list = new ArrayList<T>();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    T o = (T)obj.getClass().newInstance();
                    Field[] fields = obj.getClass().getDeclaredFields();
                    for(int i = 0 ; i < fields.length ; i++){
                        XSSFCell cell = xssfRow.getCell(num[i]);
                        setFieldData(o,fields[i] ,cell);
                    }
                    list.add(o);
                }
            }
            return list;
        }
        return null;
    }


    /**
     *
     * @param path
     * @param obj
     * @param num
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T>  List<T> readXls2003(String path,T obj,Integer ... num) throws Exception {
        System.out.println(PROCESSING + path);
        List<T> list = new ArrayList<T>();
        InputStream is = new FileInputStream(path);
        return readXlsIO2003(is,obj,num);
    }


    public static <T>  List<T> readXlsIO2003(InputStream is,T obj,Integer ... num) throws Exception {
        System.out.println(PROCESSING);
        List<T> list = new ArrayList<T>();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    T o = (T)obj.getClass().newInstance();
                    Field[] fields = obj.getClass().getDeclaredFields();
                    for(int i = 0 ; i < fields.length ; i++){
                        HSSFCell cell = hssfRow.getCell(num[i]);
                        setFieldData(o,fields[i] ,cell);
                    }
                    list.add(o);
                }
            }
            return list;
        }
        return null;
    }




    public static <T> void setFieldData(T model,Field field ,Cell cell) throws Exception{
        String type = field.getGenericType().toString();
        String name = getFieldName(field.getName());
        if (type.equals("class java.lang.String")) { // 如果type是类类型，则前面包含"class "，后面跟类名
            Method m = model.getClass().getMethod("set"+name,String.class);
            m.invoke(model, getValue(cell)); // 调用setter方法获取属性值
        }
        if (type.equals("class java.lang.Byte")) {
            Method m = model.getClass().getMethod("set"+name,Byte.class);
            m.invoke(model, new Byte(getValue(cell)));
        }
        if (type.equals("class java.lang.Integer")) {
            Method m = model.getClass().getMethod("set"+name,Integer.class);
            m.invoke(model, new Integer(getValue(cell)));
        }
        if (type.equals("class java.lang.Long")) {
            Method m = model.getClass().getMethod("set"+name,Long.class);
            m.invoke(model, new Long(getValue(cell)));
        }
        if (type.equals("class java.lang.Double")) {
            Method m = model.getClass().getMethod("set"+name,Double.class);
            m.invoke(model, new Double(getValue(cell)));
        }
        if (type.equals("class java.lang.Boolean")) {
            Method m = model.getClass().getMethod("set"+name,Boolean.class);
            m.invoke(model, new Boolean(getValue(cell)));
        }
        if (type.equals("class java.util.Date")) {
            Method m = model.getClass().getMethod("set"+name,Date.class);
            m.invoke(model, new Date());
        }
    }

    public static String getFieldName(String name){
        String prefix = name.substring(0,1).toUpperCase();
        return prefix + name.substring(1,name.length());
    }

    @SuppressWarnings("static-access")
    private static String getValue(Cell cell) {
        if(cell instanceof XSSFCell){
            if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
                return String.valueOf(cell.getBooleanCellValue());
            } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                return String.valueOf(cell.getNumericCellValue());
            } else {
                return String.valueOf(cell.getStringCellValue());
            }
        }else if(cell instanceof HSSFCell){
            if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                return String.valueOf(cell.getBooleanCellValue());
            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                return String.valueOf(cell.getNumericCellValue());
            } else {
                return String.valueOf(cell.getStringCellValue());
            }
        }
        return "";
    }

    public static String getSuffiex(String path) {
        if(StringUtils.isBlank(path)){
            return EMPTY;
        }
        int index = path.lastIndexOf(DOT);
        if (index == -1) {
            return EMPTY;
        }
        return path.substring(index + 1, path.length());
    }
}

 

package com.qf58.supplier.entity.others;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: zhubo
 * Date: 2017-11-30
 * Time: 21:34
 */
public class Std {

    private String name ;
    private String simpleName;

    private Long number;

    public String getName() {
        return name;
    }

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

    public String getSimpleName() {
        return simpleName;
    }

    public void setSimpleName(String simpleName) {
        this.simpleName = simpleName;
    }

    public Long getNumber() {
        return number;
    }

    public void setNumber(Long number) {
        this.number = number;
    }

    @Override
    public String toString() {
        return "Std{" +
                "name='" + name + '\'' +
                ", simpleName='" + simpleName + '\'' +
                ", number=" + number +
                '}';
    }
}

 

 

转载于:https://my.oschina.net/LucasZhu/blog/1582723