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=;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 =  ;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 = ;

        for (Iterator iterator = sheet.iterator(); iterator.hasNext();) {

            Person person = new Person();

            HSSFRow rows = (HSSFRow) iterator.next(); //获得每个sheet的行数

            if(rowcount ==  ){ //有特殊的特殊处理，第一行标题

                cell = rows.getCell();

                String title = cell.getStringCellValue();

//                System.out.println("一标题："+title.trim());

            }else if(rowcount == ){ //第二行，单位名称

                cell = rows.getCell();

                String unitCell = cell.getStringCellValue();

                unit =  unitCell.replace("单位名称：", "");

                unitMap.put(sheetName, unit);//

            }else{

                for(int i = ;i<rows.getPhysicalNumberOfCells();i++){  //获得每行的cell个数

                    cell = rows.getCell(i);

                    if(rowcount == ){ //第三行标题栏

//                        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 ==  && 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  :

                                    String b1 = cell.getStringCellValue();

                                    person.setName(b1);

                                    break;

                                case  :

                                    String b2 = cell.getStringCellValue();

                                    person.setPosition(b2);

                                    break;

                                case :

                                    //System.out.println("-------"+cell.getCellType());

                                    double b3 =cell.getNumericCellValue();    //默认的cell类型：0:double,1:string

                                    person.setTel(formatDouble(b3+""));

                                    break;

                                case :

                                    double b4 = cell.getNumericCellValue();

                                    person.setPhone(formatDouble(b4+""));

                                    break;

                                }

                            }

                        }

                    }

                }

                person.setCity(sheetName);

                person.setDept(dept);

                person.setUnit(unit);

                if(rowcount != ){

                    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(".") > ) {

            if (str.indexOf("E") > ) {

                str = str.substring(, str.indexOf("E")).replace(".", "");

            } else {

                str = str.substring(, str.indexOf("."));

            }

        }

        return str;

    }

}