java解析excel--并反射到实体类

今天分享一下使用java解析Excel03版和07版
参考了原文链接
使用maven需要添加两个依赖：

        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>3.11-beta1</version>        </dependency>        <!--07-->        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>3.11-beta1</version>        </dependency>

首先新建一个Excel文件，文件第一列为列名，在程序中忽略，但是列名的顺序必须和bean类中的属性顺序一致，否则反射会出错。
所有的数据类型一定要参考bean属性的方式插入。

直接上代码：
bean类

public class Users {    private String name;    private int age;    private double mm;    private String sex;    @Override    public String toString() {        return "Users [name=" + name + ", age=" + age + ", sex=" + sex                + ", mm=" + mm + "]";    }    public double getMm() {        return mm;    }    public void setMm(double mm) {        this.mm = mm;    }    public String getName() {        return name;    }    public void setName(String name) {        System.err.println(name);        this.name = name;    }    public int getAge() {        return age;    }    public void setAge(int age) {        this.age = age;    }    public String getSex() {        return sex;    }    public void setSex(String sex) {        this.sex = sex;    }}

解析excel类03：

public class XslPaese {    @SuppressWarnings("rawtypes")    public LinkedList<Object> parse(String filePath , Class classObject) throws FileNotFoundException, IOException, IntrospectionException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException{        LinkedList<Object> list = new LinkedList<Object>();        Reflect reflect = new Reflect();        File file = new File(filePath);//获取文件        //解析xsl文件        POIFSFileSystem pfs = new POIFSFileSystem(new FileInputStream(file));        HSSFWorkbook hwb = new HSSFWorkbook(pfs);        HSSFSheet hs = hwb.getSheetAt(0);//获取第0个sheet页的数据        int rowStart = hs.getFirstRowNum();//获取第一行的行数 0        int rowEnd = hs.getLastRowNum();//获取最后一行的行数-1        for(int i = rowStart+1; i <= rowEnd ; i++){//行的遍历            Object obj = classObject.newInstance();            HSSFRow hr = hs.getRow(i);//获取行            if(hr == null){                continue;            }            int cellStart = hr.getFirstCellNum();//获取第一列            int cellEnd = hr.getLastCellNum();//获取列的总数            for(int k = cellStart ; k < cellEnd ; k++ ){//列的遍历                HSSFCell hc = hr.getCell(k);//获取列                if(hc == null || hc.equals("")){                    continue;                }                switch(hc.getCellType()){                case HSSFCell.CELL_TYPE_STRING://字符串                    obj = reflect.reflect(obj, k, hc.getStringCellValue());                    break;                case HSSFCell.CELL_TYPE_BOOLEAN://boolean类型                    obj = reflect.reflect(obj, k, hc.getBooleanCellValue());                    break;                case HSSFCell.CELL_TYPE_NUMERIC://数字                    obj = reflect.reflect(obj, k, hc.getNumericCellValue());                    break;                case HSSFCell.CELL_TYPE_FORMULA://公式                    obj = reflect.reflect(obj, k, hc.getCellFormula());                    break;                case HSSFCell.CELL_TYPE_BLANK://空值                    obj = reflect.reflect(obj, k, null);                    break;                case HSSFCell.CELL_TYPE_ERROR://错误                    obj = reflect.reflect(obj, k, null);                    break;                    default:                        break;                }            }            list.add(obj);        }        return list;    }}

解析excel类07：

public class XlsxPaese {    @SuppressWarnings("rawtypes")    public LinkedList<Object> parse(String filePath , Class classObject) throws FileNotFoundException, IOException, InvalidFormatException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, IntrospectionException{        LinkedList<Object> list = new LinkedList<Object>();        Reflect reflect = new Reflect();        File file = new File(filePath);//获取文件        XSSFWorkbook xw = new XSSFWorkbook(file);        XSSFSheet xs = xw.getSheetAt(0);//获取xsl        int rowStart = xs.getFirstRowNum();//获取第一行的行数 0        int rowEnd = xs.getLastRowNum();//获取最后一行的行数-1        for(int i = rowStart+1; i <= rowEnd ; i++){//行的遍历            Object obj = classObject.newInstance();            XSSFRow hr = xs.getRow(i);//获取行            if(hr == null){                continue;            }            int cellStart = hr.getFirstCellNum();//获取第一列            int cellEnd = hr.getLastCellNum();//获取列的总数            for(int k = cellStart ; k < cellEnd ; k++ ){//列的遍历                XSSFCell hc = hr.getCell(k);//获取列                if(hc == null || hc.equals("")){                    continue;                }                switch(hc.getCellType()){                case HSSFCell.CELL_TYPE_STRING://字符串                    obj = reflect.reflect(obj, k, hc.getStringCellValue());                    break;                case HSSFCell.CELL_TYPE_BOOLEAN://boolean类型                    obj = reflect.reflect(obj, k, hc.getBooleanCellValue());                    break;                case HSSFCell.CELL_TYPE_NUMERIC://数字                    obj = reflect.reflect(obj, k, hc.getNumericCellValue());                    break;                case HSSFCell.CELL_TYPE_FORMULA://公式                    obj = reflect.reflect(obj, k, hc.getCellFormula());                    break;                case HSSFCell.CELL_TYPE_BLANK://空值                    obj = reflect.reflect(obj, k, null);                    break;                case HSSFCell.CELL_TYPE_ERROR://错误                    obj = reflect.reflect(obj, k, null);                    break;                    default:                        break;                }            }            list.add(obj);        }        return list;    }}

从上面的代码应该看的出03解析和07解析只是一两个类的区别：
03：

POIFSFileSystem pfs = new POIFSFileSystem(new FileInputStream(file));HSSFWorkbook hwb = new HSSFWorkbook(pfs);//获取工作页面HSSFSheet hs = hwb.getSheetAt(0);//获取第0个sheet页的数据

07：

XSSFWorkbook xw = new XSSFWorkbook(file);XSSFSheet xs = xw.getSheetAt(0);//获取xsl

获取行列的类也是有区别的：
03与07其实就是 HSSFRow/HSSFCell改为XSSFRow/XSSFCell而已，其他的逻辑不变

反射类：

public class Reflect {    public Object reflect(Object obj,int paramNumber,Object value) throws InstantiationException, IllegalAccessException, IntrospectionException, IllegalArgumentException, InvocationTargetException{        Field[] fields = obj.getClass().getDeclaredFields();//获取属性名        //返回的是一个参数类型        String type = fields[paramNumber].getGenericType().toString();        //返回的是一个类对象 //      Class classType = field.getType();        PropertyDescriptor pd = new PropertyDescriptor(fields[paramNumber].getName(), obj.getClass());        Method setmd = pd.getWriteMethod();//获取某个属性的set方法        if(type.equals("class java.lang.String")){            setmd.invoke(obj, value);//激活        }else if(type.equals("int") || type.equals("class java.lang.Integer")){                String str = value.toString();                if(str.indexOf(".") == -1){                    setmd.invoke(obj, value);                }else{                    setmd.invoke(obj, Integer.parseInt(str.substring(0, str.indexOf("."))));                }        }else if("double".equals(type) || "class java.lang.Double".equals(type)){                setmd.invoke(obj, value);        }else{            setmd.invoke(obj, value);        }        return obj;    }}

处理类：
为了能方便

public class Handler {    @SuppressWarnings("rawtypes")    public void handle(String filePath,Class clazz) throws FileNotFoundException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException, IOException, IntrospectionException, InvalidFormatException{        String str = filePath.substring(filePath.lastIndexOf(".")+1);        if(str.equals("xls")){            //03            XslPaese xp = new XslPaese();            xp.parse(filePath, clazz);        }else{            //07            XlsxPaese xp = new XlsxPaese();            xp.parse(filePath, clazz);        }    }}

测试类：

 public void testApp()    {        Handler hd = new Handler();        try {            hd.handle("C:/Users/seaskylight/Desktop/Book1.xlsx", Users.class);        } catch (FileNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (IllegalAccessException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (IllegalArgumentException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (InvocationTargetException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (InstantiationException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (InvalidFormatException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (IntrospectionException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }

试试，理解一下，其实这样写还是存在很多的不合理，在实际使用上和目前使用上还是有很大的区别的