首页 > 代码库 > POI 读取 Excel 文件

POI 读取 Excel 文件

import java.io.File;import java.io.FileOutputStream;import java.io.InputStream;import java.io.OutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;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;public class ReadExcel {    private POIFSFileSystem pois ;    private HSSFWorkbook  workBook;    private HSSFSheet  sheet;    private HSSFRow  row;    private HSSFCell cell;    List<String> RegionList = new ArrayList<String>();    Map<String, String> unitMap = new HashMap<String, String>(); //存放集合 <region,unit>1:1    Map<String, List<String>> deptMap = new HashMap<String, List<String>>(); //存放集合 <unit,dept>1:n    /**     * sheetName     * @param os     * @param sheetCount     * @return     * @throws Exception     */    public List readExcelSheetName(InputStream os,int sheetCount) throws Exception{                pois =  new POIFSFileSystem(os);        workBook = new HSSFWorkbook(pois);        for(int i=0;i<sheetCount;i++){            sheet = workBook.getSheetAt(i);                        String sheetName = sheet.getSheetName();        //            System.out.println("sheetName:"+sheetName);            RegionList.add(sheetName);                List plist = readCell(sheet,sheetName);                File f = new File("F://person.txt");            OutputStream osa = new FileOutputStream(f);                    System.out.println(plist.size() + " 总数");            for(int j = 0 ;j<plist.size();j++){                Person per = (Person) plist.get(j);                StringBuffer sb = new StringBuffer();                sb.append("region:"+per.getCity()+"           "+"unit:"+per.getUnit()+"            "+"dept:"+per.getDept()+"         "+"name:"+per.getName());                sb.append("              "+"tel:"+per.getTel()+"            "+"phone:"+per.getPhone());                sb.append("\r\n");                osa.write(sb.toString().getBytes());            }            osa.close();        }                return RegionList;    }            /**     * 一个sheet上的相关信心     * @param sheet     * @return     */    public List readCell(HSSFSheet sheet,String sheetName){        List Personlist = new ArrayList();                String dept = "";        String unit = "";        int rowcount = 0;                for (Iterator iterator = sheet.iterator(); iterator.hasNext();) {            Person person = new Person();                    HSSFRow rows = (HSSFRow) iterator.next(); //获得每个sheet的行数            if(rowcount == 0 ){ //有特殊的特殊处理,第一行标题                cell = rows.getCell(1);                String title = cell.getStringCellValue();//                System.out.println("一标题:"+title.trim());            }else if(rowcount ==1 ){ //第二行,单位名称                cell = rows.getCell(0);                String unitCell = cell.getStringCellValue();                unit =  unitCell.replace("单位名称:", "");                unitMap.put(sheetName, unit);//            }else{                for(int i = 0;i<rows.getPhysicalNumberOfCells();i++){  //获得每行的cell个数                    cell = rows.getCell(i);                                if(rowcount == 2){ //第三行标题栏//                        switch (i){//                        case 1 ://                            String b1 = cell.getStringCellValue();    //                            System.out.println("三标题:"+b1);    //                            break;//                        case 2 ://                            String b2 = cell.getStringCellValue();//                            System.out.println("三标题:"+b2);    //                            break;//                        case 3://                            String b3 = cell.getStringCellValue();//                            System.out.println("三标题:"+b3);    //                            break;//                        case 4://                            String b4 = cell.getStringCellValue();//                            System.out.println("三标题:"+b4);    //                            break;//                        }                    }else{                        if(cell != null){                                                if(i == 0 && formateCell(cell) != null&& !"".equals(formateCell(cell).trim())){ //第一个单元格有合并的部分                                System.out.println(cell.getCellType()+"+++++"+cell.getStringCellValue().trim());                                dept = cell.getStringCellValue();                                dept = dept.replaceAll("\n", "").replace(" ","");                                List deplist = deptMap.get("unit");                                if(deplist == null ){                                    deplist = new ArrayList<String>();                                    deplist.add(dept);                                }                                                                deptMap.put(unit, deplist);                                                            }else{                                switch (i){                                case 1 :                                    String b1 = cell.getStringCellValue();                                            person.setName(b1);                                    break;                                case 2 :                                    String b2 = cell.getStringCellValue();                                    person.setPosition(b2);                                    break;                                case 3:                                                                        //System.out.println("-------"+cell.getCellType());                                    double b3 =cell.getNumericCellValue();    //默认的cell类型:0:double,1:string                                        person.setTel(formatDouble(b3+""));                                    break;                                case 4:                                    double b4 = cell.getNumericCellValue();                                            person.setPhone(formatDouble(b4+""));                                    break;                                }                            }                        }                    }                }                person.setCity(sheetName);                person.setDept(dept);                person.setUnit(unit);                if(rowcount !=2 ){                    Personlist.add(person);                            }           }            rowcount ++;        }        return Personlist;    }    /**     * 格式化cell里面的内容     * @param cell     * @return     */    private String formateCell(HSSFCell cell){        String strCell = "";        switch (cell.getCellType()){        case HSSFCell.CELL_TYPE_STRING:            strCell = cell.getStringCellValue();            break;        case HSSFCell.CELL_TYPE_NUMERIC:            strCell = formatDouble(String.valueOf(cell.getNumericCellValue()));            break;        case HSSFCell.CELL_TYPE_BOOLEAN:            strCell = String.valueOf(cell.getBooleanCellValue());            break;        case HSSFCell.CELL_TYPE_BLANK:            strCell = "";            break;        default:            strCell = "";            break;        }        if (strCell.equals("") || strCell == null) {            return "";        }        if (cell == null) {            return "";        }        return strCell;    }        private  String formatDouble(String str) {        if (str == null || "".equals(str.trim()))            return "";        if (str.indexOf(".") > 0) {            if (str.indexOf("E") > 0) {                str = str.substring(0, str.indexOf("E")).replace(".", "");            } else {                str = str.substring(0, str.indexOf("."));            }        }        return str;    }}

所需jar 

POI 读取 Excel 文件