import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
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;
import org.apache.poi.ss.usermodel.Cell;

public class ExcelUtils {

    private static final int rowStart = 2;
    private static final int colListNo = 1;
    private static final int colKey = 3;
    private static final int colType = 4;
    private static final int colValue = 5;

    public HSSFWorkbook openWorkbook() {

        FileInputStream fins = null;
        HSSFWorkbook workbook = null;

        //ファイル読込
        try {
            fins = new FileInputStream("D:\\D_DATA\\DATA\\JavaWS\\toolsCommon\\test.xls");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            POIFSFileSystem fs = new POIFSFileSystem(fins);
            workbook = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return workbook;
    }

    public void closeWorkbook(HSSFWorkbook workbook) {

        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public HSSFSheet getSheet(HSSFWorkbook workbook, String sheetName) {

        HSSFSheet sheet = workbook.getSheet(sheetName);

        return sheet;
    }

    public int getRowLimit(HSSFSheet sheet) {
        HSSFRow row = sheet.getRow(sheet.getFirstRowNum());
        System.out.println("【" + sheet.getSheetName() + "】開始位置(Y:" + String.valueOf(sheet.getFirstRowNum()) + " , X:" + String.valueOf(row.getFirstCellNum()) + ")");
        System.out.println("【" + sheet.getSheetName() + "】終了位置(Y:" + String.valueOf(sheet.getLastRowNum()) + " , X:" + String.valueOf(row.getLastCellNum()) + ")");

        return sheet.getLastRowNum();
    }

    private String getValue(HSSFCell cell) {

        String retValue = null;

        if (cell != null) {

            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                retValue = cell.getStringCellValue();
            case Cell.CELL_TYPE_NUMERIC:
                retValue = Double.toString(cell.getNumericCellValue());
            case Cell.CELL_TYPE_BOOLEAN:
                retValue = Boolean.toString(cell.getBooleanCellValue());
            default:
                System.out.println("セルタイプ不明（" + cell.getCellType() + "  ）");
            }
        }

        return retValue;
    }

    public Map<String,Object> getMainMap(HSSFWorkbook workbook, String sheetName) {

        HSSFSheet sheet = getSheet(workbook,sheetName);

        int rowEnd = getRowLimit(sheet);

        Map<String, Object> map = getMap(workbook,sheetName,rowStart,rowEnd);

        return map;
    }

    private Map<String,Object> getMap(HSSFWorkbook workbook, String sheetName, int rawBgn,int rawFin) {

        HSSFSheet sheet = getSheet(workbook,sheetName);

        Map<String,Object> retMap = new HashMap<String,Object>();

        for (int rowIndex =rowStart; rowIndex <= rawFin; rowIndex++) {

            HSSFRow row = sheet.getRow(rowIndex);
            HSSFCell cellKey = row.getCell(colKey);
            HSSFCell cellType = row.getCell(colType);
            HSSFCell cellValue = row.getCell(colValue);
            StringBuilder sb = new StringBuilder("line." + String.valueOf(rowIndex) + " : ");
            if  (cellKey != null) {
                String cellKeyValue = getValue(cellKey);
                sb.append(cellKeyValue);
                if  (cellType != null) {
                    String cellTypeValue = getValue(cellType);
                    sb.append("[");
                    sb.append(cellTypeValue);
                    sb.append("]");
                    if  (cellValue != null) {
                        String cellValueValue = getValue(cellValue);
                        sb.append(cellValueValue);

                        // type : String
                        if (cellTypeValue.startsWith("String")) {
                            retMap.put(cellKeyValue, cellValueValue);
                        }

                        // type : Integer
                        if (cellTypeValue.startsWith("Integer")) {
                            retMap.put(cellKeyValue, Integer.valueOf(cellValueValue));
                        }

                        // type : Bigdecimal
                        if (cellTypeValue.startsWith("BigDecimal")) {
                            retMap.put(cellKeyValue, new BigDecimal(cellValueValue));
                        }

                        // type : Date
                        if (cellTypeValue.startsWith("Date")) {
                            try {
                                SimpleDateFormat sdFormat = new SimpleDateFormat("yyyyMMdd");
                                retMap.put(cellKeyValue, sdFormat.parse(cellValueValue));
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                        }

                        // type : Map<String, Object>
                        if (cellTypeValue.startsWith("Map")) {
                            int rowFin = getRowLimit(workbook.getSheet(cellValueValue));
                            Map<String, Object> map = getMap(workbook,cellValueValue,rowStart,rowFin);
                            retMap.put(cellKeyValue, map);
                        }

                        // type : List<Map<String, Object>>
                        if (cellTypeValue.startsWith("List")) {
                            List<Map<String, Object>> ListMap = getListMap(workbook,cellValueValue);
                            retMap.put(cellKeyValue, ListMap);
                        }
                    }
                }
            }
            System.out.println(sb.toString());
        }

        return retMap;
    }

    private List<Map<String, Object>> getListMap(HSSFWorkbook workbook, String sheetName) {

        HSSFSheet sheet = getSheet(workbook,sheetName);

        int rowEnd = getRowLimit(sheet);

        // Listの終了位置リストを作成する
        List<Integer> rawEndList = new ArrayList<Integer>();
        for (int i = rowStart; i <= rowEnd; i++) {
            boolean isAdded = false;

            HSSFRow rowNow = sheet.getRow(i);
            HSSFCell cellNow = rowNow.getCell(colListNo);
            HSSFRow rowNext = sheet.getRow(i+1);
            HSSFCell cellNext = null;
            if (rowNext != null) {
                cellNext = sheet.getRow(i+1).getCell(colListNo);
            }

            if (cellNow != null || cellNext != null) {
                if ((cellNow != null && cellNext == null) || (cellNow == null && cellNext != null)) {
                    rawEndList.add(i);
                    isAdded = true;
                } else {
                    String cellNowValue = getValue(cellNow);
                    String cellNextValue = getValue(cellNext);
                    if (cellNowValue != cellNextValue) {
                        rawEndList.add(i);
                        isAdded = true;
                    }
                }
            }

            if (!isAdded && i == rowEnd) {
                rawEndList.add(i);
            }
        }

        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();

        int rawBgn = rowStart;
        for (Integer rawFin: rawEndList) {
            Map<String, Object> map = getMap(workbook,sheetName,rawBgn,rawFin);
            listMap.add(map);
        }

        return listMap;
    }
}