java 解析excel

packagecom.berchina.iec.agency.util.execl;

 

importjava.io.FileInputStream;

importjava.io.InputStream;

importjava.lang.reflect.Method;

importjava.math.BigDecimal;

importjava.text.DecimalFormat;

importjava.text.SimpleDateFormat;

importjava.util.ArrayList;

importjava.util.Date;

importjava.util.HashMap;

importjava.util.List;

importjava.util.Map;

 

importorg.apache.log4j.LogManager;

importorg.apache.log4j.Logger;

importorg.apache.poi.ss.usermodel.Cell;

importorg.apache.poi.ss.usermodel.CellStyle;

importorg.apache.poi.ss.usermodel.DateUtil;

importorg.apache.poi.ss.usermodel.Row;

importorg.apache.poi.ss.usermodel.Sheet;

importorg.apache.poi.ss.usermodel.Workbook;

 

importcom.berchina.iec.agency.util.ConvertUtil;

importcom.berchina.iec.agency.util.StringUtils;

 

publicclass T123 {

     

    privatestatic Logger logger = LogManager.getLogger(ReaderFileUtil.class);

     

    publicstatic final String READER_BASE_ROOT = "io";

     

    publicstatic final String START_INDEX = "startRow";

     

    publicstatic final String TITLES = "titles";

 

    /**

     * fullFilePath 目标excel的磁盘路径

     * clz 需要转换对象的class

     * @param fullFilePath

     * @param clz

     * @return

     * @throws Exception

     */

    @SuppressWarnings("unchecked")

    publicstatic <T> List<T> transToObject(String fullFilePath,Class<T> clz) throwsException{

        InputStream is = null;

        try{

            is = newFileInputStream(fullFilePath);

            Workbook wb = ReaderFileUtil.loadWorkBookByPath(fullFilePath,is);

            Sheet sheet = wb.getSheetAt(0);

            Map<String,Object> map = readExcelTitle(sheet);

            intstart = Integer.parseInt(map.get(START_INDEX).toString());

            List<String> titles = (List<String>)map.get(TITLES);

            intlast = sheet.getLastRowNum();

            List<T> lst = newArrayList<T>();

            for(inti = (start+1);i<=last;i++){

                List<String> values = getRowValues(sheet, i);

                T t = transToObject(titles, values, clz);

                lst.add(t);

            }

            returnlst;

        }catch(Exception e) {

            throwe;

        }finally{

            if(is!=null){

                is.close();

            }

        }

    }

     

    privatestatic <T> T transToObject(List<String> titles,List<String> values,Class<T> clz) throwsException{

        T t = clz.newInstance();

        intsize = titles.size();

        for(inti = 0;i<size;i++){

            if(values.size()<=i){

                break;

            }

            String title = titles.get(i);

            String value = values.get(i);

            setValue(t,clz,title,value);

        }

        returnt;

    }

     

    privatestatic void setValue(Object o,Class<?>clz,String title,String value) throwsException{

        Method m = null;

        if(title.indexOf(".")!=-1){

            String[] titleSplit = title.split("\\.");

            m = getSetMethod(titleSplit<img src="http://www.oschina.net/js/ke/plugins/emoticons/images/0.gif"alt="">, clz);

        }else{

            m = getSetMethod(title, clz);

        }

        if(m == null){

            logger.info(title+"在"+clz.getName()+"中不存在");

            return;

        }

        setValue(o, m, title,value);

    }

     

    privatestatic void setValue(Object o,Method method,String title,String value) throwsException{

        Class<?>[] clazz = method.getParameterTypes(); 

        String type = clazz<img src="http://www.oschina.net/js/ke/plugins/emoticons/images/0.gif"alt="">.getName();

        if(StringUtils.isEmpty(value)){

            return;

        }

        if("java.lang.String".equals(type)){

            method.invoke(o, value);

        }elseif("java.util.Date".equals(type)){

            Date d = null;

            if(value.length()>10){

                d = ConvertUtil.convertObj2Time(value);

            }else{

                d = ConvertUtil.convertObj2Date(value);

            }

            method.invoke(o, d);

        }elseif("java.lang.Integer".equals(type)||"int".equals(type)){

            Integer i = ConvertUtil.convertObj2Int(value);

            method.invoke(o, i);

        }elseif("java.lang.Long".equals(type)||"long".equals(type)){

            Long l = ConvertUtil.convertObj2Long(value);

            method.invoke(o, l);

        }elseif("java.lang.Short".equals(type)||"short".equals(type)){

            Short s = ConvertUtil.convertObj2Short(value);

            method.invoke(o, s);

        }elseif("java.lang.Boolean".equals(type)||"boolean".equals(type)){

            Boolean b = ConvertUtil.convertObj2Boolean(value);

            method.invoke(o, b);

        }elseif("java.math.BigDecimal".equals(type)){

            BigDecimal b = ConvertUtil.convertObj2BigDecimal(value);

            method.invoke(o, b);

        }else{

            Method getMethodName = o.getClass().getMethod(method.getName().replace("set","get"));

            Object returnValue = getMethodName.invoke(o);

            Class<?> returnClass = Class.forName(type);

            if(returnValue == null){

                returnValue = returnClass.newInstance();

                method.invoke(o, returnValue);

            }

            title = title.substring(title.indexOf(".")+1);

            setValue(returnValue, returnClass, title, value);

        }

    }

     

    privatestatic Method getSetMethod(String propName,Class<?> clz){

        Method[]methods = clz.getMethods();

        for(Method method : methods){

            if(method.getName().toLowerCase().equals("set"+propName.toLowerCase())){

                Class<?>[] clazz = method.getParameterTypes();

                if(clazz.length == 1){

                    returnmethod;

                }

            }

        }

        returnnull;

    }

     

    privatestatic Map<String,Object> readExcelTitle(Sheet sheet) throwsException{

        intm = 0;

        Map<String,Object> map = newHashMap<String,Object>();

        intlast = sheet.getLastRowNum();

        while(m<=last){

            Cell cell = sheet.getRow(m).getCell(0);

            if(cell!=null){

                String cellValue = cell.getStringCellValue();

                if(!cellValue.startsWith("#")){

                    List<String> lstStr = getRowValues(sheet, m);

                    map.put(START_INDEX, m);

                    map.put(TITLES, lstStr);

                    returnmap;

                }

            }

            m++;

        }

        thrownew Exception("Excel格式不正确");

    }

     

    /**

     * 获得行数据

     * @param sheet

     * @param rowIndex

     * @return

     */

    privatestatic List<String> getRowValues(Sheet sheet,introwIndex){

        List<String> lstStr = newArrayList<String>();

        Row row = sheet.getRow(rowIndex);

        intlast = row.getLastCellNum();

        for(inti = 0;i<last;i++){

            lstStr.add(getCellValue(row, i));

        }

        returnlstStr;

    }

     

    /**

     * 获得列数据

     * @param row

     * @param colIndex

     * @return

     */

    privatestatic String getCellValue(Row row,intcolIndex){

        String cellValue = "";

        if(colIndex < row.getFirstCellNum()){

            cellValue = "";

        }else{

            Cell cell = row.getCell(colIndex);

            if(cell == null){

                cellValue = "";

            }else{

                intcellType = cell.getCellType();

                if(Cell.CELL_TYPE_FORMULA == cellType){

                    cellType = cell.getCachedFormulaResultType();

                }

                if(Cell.CELL_TYPE_BLANK ==cellType){

                    cellValue = "";

                }elseif(Cell.CELL_TYPE_BOOLEAN == cellType){

                    Boolean b = cell.getBooleanCellValue();

                    cellValue = b.toString();

                }elseif(Cell.CELL_TYPE_ERROR == cellType){

                    cellValue = "";

                }elseif(Cell.CELL_TYPE_NUMERIC == cellType){

                    //判断cell是否为日期格式

                    if(isCellDateFormatted(cell)){

                        SimpleDateFormat sdf = newSimpleDateFormat("yyyy-MM-dd");

                        if(cell.getDateCellValue()!=null){

                            cellValue = sdf.format(cell.getDateCellValue());

                        }

                    }elseif(isCellTimeFormatted(cell)){

                        SimpleDateFormat sdf = newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");

                        if(cell.getDateCellValue()!=null){

                            cellValue = sdf.format(cell.getDateCellValue());

                        }

                    }else{

                        doubled = cell.getNumericCellValue();

                        cellValue = String.format("%.2f", d);

                        if(cellValue.matches("^\\d+\\.0+$")){

                            DecimalFormat df = newDecimalFormat("#");

                            cellValue = df.format(d);

                        }

                    }

                }elseif(Cell.CELL_TYPE_STRING == cellType){

                    cellValue = cell.getStringCellValue();

                }

            }

        }

        if(cellValue!=null){

            cellValue = cellValue.trim();

        }

        returncellValue;

    }

     

    // 判断cell是否为日期格式

    publicstatic boolean isCellDateFormatted(Cell cell) {

        if(cell == null)

            returnfalse;

        booleanbDate = false;

 

        doubled = cell.getNumericCellValue();

        if(DateUtil.isValidExcelDate(d)) {

            CellStyle style = cell.getCellStyle();

            if(style == null)

                returnfalse;

            inti = style.getDataFormat();

            String f = style.getDataFormatString();

            if(f.indexOf("y") > -1&& f.indexOf("m") > -1

                    && f.indexOf("d") > -1) {

                returntrue;

            }

        }

        returnbDate;

    }

     

    publicstatic boolean isCellTimeFormatted(Cell cell) {

        if(cell == null)

            returnfalse;

        booleanbDate = false;

 

        doubled = cell.getNumericCellValue();

        if(DateUtil.isValidExcelDate(d)) {

            CellStyle style = cell.getCellStyle();

            if(style == null)

                returnfalse;

            inti = style.getDataFormat();

            String f = style.getDataFormatString();

            if(f.indexOf("mm") > -1&& f.indexOf("ss") > -1

                    && f.indexOf("h") > -1) {

                returntrue;

            }

        }

        returnbDate;

    }

     

}