java 读取excel

package com.icss.mdm.maintain.data;import java.io.BufferedReader;import java.io.FileInputStream;import java.io.FileReader;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.beanutils.BeanUtils;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.poifs.filesystem.POIFSFileSystem;import com.icss.mdm.vo.MdmCodepropVO;import com.icss.pangu.db.DBBeanBase;import com.icss.pangu.logging.Log;import com.icss.pangu.logging.LogFactory;public class ReadExcel {private static Log log = LogFactory.getLog(ReadTxt.class);public static Object[] getExcelList(String id, DBBeanBase dbBase, String roleCode,String status, String ignore, Object[] addTag,String filePath,String personName) {        int count = 1;        int propLength = 0;        String codeID = "";        String code = "";        String value = "";        String s1 = null;        FileReader reader = null;        FileReader reader1 = null;        BufferedReader br = null;        BufferedReader br1 = null;        List list = new ArrayList();        String[] columnValue = null;        String[] propValue = null;        String[] codeValue = new String[50];        int row = 0 ;        // 记录应该添加几次        int timer = 0 ;        // 记录是第几次执行添加        int timers = 0;        // 记录如果不能被整除的时候最后一次是多少条数据        int timecount = 0 ;        try {            InputStream is = new FileInputStream(filePath);            HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));            HSSFSheet sheet = wb.getSheetAt(0);            int firstRowNum = sheet.getFirstRowNum();            int lastRowNum = sheet.getLastRowNum();            // 取得文件行数            row = lastRowNum + 1;            if((row - 3)% ErrorTypes.NUM_COUNT == 0)                timer = (row - 3) / ErrorTypes.NUM_COUNT;            else{                timer = (row - 3) / ErrorTypes.NUM_COUNT + 1;                timecount = (row - 3 )% ErrorTypes.NUM_COUNT;            }            // 读取文件            HSSFCell cell = null;                        for ( int rowIndex = firstRowNum; rowIndex <= lastRowNum; rowIndex++ ) {                Map map = null;                HSSFRow currentRow = sheet.getRow(rowIndex);                // 第一行放表名                if (rowIndex == 0) {                    cell = currentRow.getCell((short)0);                    codeID = cell.getStringCellValue();                    if (!codeID.equals(id)) {                        ((Map)addTag[2]).put("1",ErrorTypes.ID_ERROR);                        return addTag;                    }                    //第二行放属性中文名称                } else if (rowIndex == 1) {                                        //第三行放属性                } else if (rowIndex == 2) {                    columnValue = new String[sheet.getRow(rowIndex).getPhysicalNumberOfCells()];                        for (int i = 0; i < sheet.getRow(rowIndex).getPhysicalNumberOfCells(); i++) {                            if ("".equals(currentRow.getCell((short)i))) {                                ((Map)addTag[2]).put("2",ErrorTypes.PROP_NULL);                                return addTag;                            } else {                                columnValue[i] = (currentRow.getCell((short)i).getStringCellValue());                            }                         }                                                //TODO 字段名转换为属性名                        propValue = getQueryList(id,dbBase,columnValue,sheet.getRow(rowIndex).getPhysicalNumberOfCells());                        //TODO                        propLength = propValue.length;                        // 验证属性                        if (!JudgeValidate.judgeProp(propValue)) {                            ((Map) addTag[2]).put("2", ErrorTypes.PROP_NAME_ERROR);                            return addTag;                        }                } else {                    codeValue = new String[sheet.getRow(rowIndex).getPhysicalNumberOfCells()];                    for (int i = 0; i < sheet.getRow(rowIndex).getPhysicalNumberOfCells(); i++) {                        try{                            codeValue[i] = currentRow.getCell((short)i).getStringCellValue();                        }catch(Exception e){                            codeValue[i] = currentRow.getCell((short)i).getNumericCellValue()+"";                        }                    }                    // 属性与相应的值不对应                    if (propLength < codeValue.length) {                        ((Map)addTag[2]).put((new Integer(count)).toString(),ErrorTypes.VALUE_LENGTH_MATCH);                        if(ignore.equals("0")){                            continue;                        }else{                            return addTag;                        }                    }                    map = new HashMap();                    for (int i = 0; i < propValue.length; i++) {                        // 把属性对应的value放到map中                        if (map.get(propValue[i]) == null) {                            map.put(propValue[i], codeValue[i].trim());                        } else {                            ((Map)addTag[2]).put("2",ErrorTypes.PROP_REPEAT);                            return addTag;                        }                    }                    list.add(map);                }                       if((count - 3) % ErrorTypes.NUM_COUNT  != 0 && count != 3){                    addTag =  JudgeValidate.judgeValue(list, dbBase, codeID, roleCode, status, ignore, addTag,personName,"xls",timers);                             list.removeAll(list);                    timers++;                    // 如果本次执行出错，且不忽略错误就直接返回                    if(((Map)addTag[2]).size() != 0 && "1".equals(ignore))                        return addTag;                }else if((count - 3) % ErrorTypes.NUM_COUNT != timecount && (timers + 1 == timer)){                    //timer记录应该添加几次 timers = 0 记录是第几次执行添加timecount 记录如果不能被整除的时候最后一次是多少条数据                    addTag =  JudgeValidate.judgeValue(list, dbBase, codeID, roleCode, status, ignore, addTag,personName,"xls",timers);                    list.removeAll(list);                }                   count++;            }            return addTag;                    } catch (Exception e) {            log.error(e);            return addTag;        }finally{            try {                if(br!=null)                    br.close();                if(br1!=null)                    br1.close();                if(reader!=null)                    reader.close();                if(reader1!=null)                    reader1.close();            } catch (IOException e) {                log.error(e);            }        }    }/** * 字段名称转化为属性名称 * @param id * @param dbBase * @param columnValue * @param maxCols * @return * @throws Exception */public static String[] getQueryList(String id, DBBeanBase dbBase, String[] columnValue,int maxCols) throws Exception {       List list = new ArrayList();    String[] aStr = new String[columnValue.length];        Statement state = dbBase.getPrivateCon().createStatement();//        ResultSet rs = null;        String selectProp = "";        selectProp ="SELECT COL_SEQUENCE,COL_NAME FROM MDM_CODEPROP WHERE CODE_ID = '" +  id.trim() + "'";        /*for (int i = 0; i < columnValue.length; i++) {            selectProp ="SELECT COL_SEQUENCE FROM MDM_CODEPROP WHERE CODE_ID = '" +  id.trim() + "' AND COL_NAME = '" + columnValue[i] + "'";            rs = state.executeQuery(selectProp);        }*/                ResultSet rs = state.executeQuery(selectProp);        list = populate(rs,MdmCodepropVO.class);        //用columnValue匹配结果集COL_NAME，取得COL_SEQUENCE        //转换成PropXX（COL_SEQUENCE<10? "Prop0"+String.valueof(COL_SEQUENCE):"Prop"+String.valueof(COL_SEQUENCE)）        //转换结果以String[]方式返回        MdmCodepropVO mdmCodeprop = null;        for (int m = 0; m < columnValue.length; m++) {            for (int i = 0; i < list.size(); i++) {                mdmCodeprop = new MdmCodepropVO();                mdmCodeprop =  (MdmCodepropVO)list.get(i);                if (columnValue[m].equalsIgnoreCase(mdmCodeprop.getColName())) {                    if (mdmCodeprop.getColSequence() < 10){                        aStr[m] = "Prop0" + mdmCodeprop.getColSequence().toString();                    } else {                        aStr[m] = "Prop" + mdmCodeprop.getColSequence().toString();                    }                }            }        }                if(log.isDebugEnabled()){            log.debug("selectPropSql:"+selectProp);          }                return aStr;        }/***********把resultset结果集转化为list集合************/    public static List populate(ResultSet rs, Class clazz) throws Exception {        ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素        int colCount = metaData.getColumnCount(); // 取得所有列的个数        List ret = new ArrayList(); // 存放返回结果的容器        Field[] fields = clazz.getDeclaredFields(); // 取得业务对象的属性        while (rs.next()) {            Object newInstance = clazz.newInstance(); // 构造业务对象实例            // 将结果集中每一条记录，每一个字段取出，根据命名规则，对对应的业务对象的属性进行赋值            for (int i = 1; i <= colCount; i++) { // 对于该记录的每一列                try {                    Object value = rs.getObject(i);                    for (int j = 0; j < fields.length; j++) {                        Field f = fields[j];                        if (f.getName().equalsIgnoreCase(metaData.getColumnName(i).replaceAll("_", ""))) {                            BeanUtils.copyProperty(newInstance, f.getName(), value);                        }                    }                } catch (Exception e) {                    // TODO: handle exception                    e.printStackTrace();                }            }            ret.add(newInstance);        }        return ret;    } }