package com.boot.utils;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

public class POIExcel {

    private int totalRows = ;// 总行数

    private int totalCells = ;// 总列数

    public Map<String, List<List<String>>> read(String fileName) {

        Map<String, List<List<String>>> maps = new HashMap<String, List<List<String>>>();

        if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$"))

            return maps;

        File file = new File(fileName);

        if (file == null || !file.exists())

            return maps;

        try {

            Workbook wb = WorkbookFactory.create(new FileInputStream(file));

            maps = read(wb);

        } catch (FileNotFoundException e) {

            e.printStackTrace();

        } catch (InvalidFormatException e) {

            e.printStackTrace();

        } catch (IOException e) {

            e.printStackTrace();

        }

        return maps;

    }

    public int getTotalRows() {

        return totalRows;

    }

    public int getTotalCells() {

        return totalCells;

    }

    private Map<String, List<List<String>>> read(Workbook wb) {

        Map<String, List<List<String>>> maps = new HashMap<String, List<List<String>>>();

        int number = wb.getNumberOfSheets();

        if (number > ) {

            for (int i = ; i < number; i++) { // 循环每个工作表

                List<List<String>> list = new ArrayList<List<String>>();

                int delnumber = ;// 第一页去除行数

                Sheet sheet = wb.getSheetAt(i);

                this.totalRows = sheet.getPhysicalNumberOfRows() - delnumber; // 获取工作表中行数

                if (this.totalRows >=  && sheet.getRow(delnumber) != null) {

                    this.totalCells = sheet.getRow()

                            .getPhysicalNumberOfCells(); // 得到当前行的所有单元格

                    for (int j = ; j < totalRows; j++) {

                        List<String> rowLst = new ArrayList<String>();

                        for (int f = ; f < totalCells; f++) {

                            if (totalCells > ) {

                                String value = getCell(sheet.getRow(j).getCell(f));

                                rowLst.add(value);

                            }

                        }

                        list.add(rowLst);

                    }

                }

                maps.put(sheet.getSheetName(), list);

            }

        }

        return maps;

    }

    /*

     * private String getRightStr(String sNum) { DecimalFormat decimalFormat =

     * new DecimalFormat("##.00"); String resultStr = decimalFormat.format(new

     * Double(sNum)); if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) { resultStr =

     * resultStr.substring(0, sNum.indexOf(".")); } return resultStr; }

     */

    public String getCell(Cell cell) {

        String cellValue = null;

        /*

         * if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { if

         * (HSSFDateUtil.isCellDateFormatted(cell)) { cellValue =

         * getRightStr(cell.getDateCellValue() + ""); } else {

         *

         * cellValue = getRightStr(cell.getNumericCellValue() + ""); } } else if

         * (Cell.CELL_TYPE_STRING == cell.getCellType()) { cellValue =

         * cell.getStringCellValue(); } else if (Cell.CELL_TYPE_BOOLEAN ==

         * cell.getCellType()) { cellValue = cell.getBooleanCellValue() + ""; }

         * else { cellValue = cell.getStringCellValue(); }

         */

        HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();

        cellValue = hSSFDataFormatter.formatCellValue(cell); // 使用EXCEL原来格式的方式取得值

        return cellValue;

    }

    public static void main(String[] args) {

        try {

            Map<String, List<List<String>>> map = new POIExcel()

                    .read("d:\\user.xlsx");

            System.out.println(map);

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

}