import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.log4j.Logger;

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

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

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

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

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

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

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**

 * excel解析工具类

 * @author Marydon

 * @createTime 2018年2月1日下午12:54:09

 * @updateTime

 * @Email:Marydon20170307@163.com

 * @version:1.0.0

 */

public class ReadExcelUtils {

    private Logger logger = Logger.getLogger(this.getClass());

    private Workbook wb;

    private Sheet sheet;

    private Row row;

    public ReadExcelUtils(String filepath) {

        if (filepath == null) {

            return;

        }

        String fileType = filepath.substring(filepath.lastIndexOf("."));

        try {

            InputStream is = new FileInputStream(filepath);

            if (".xls".equals(fileType)) {

                wb = new HSSFWorkbook(is);

            } else if (".xlsx".equals(fileType)) {

                wb = new XSSFWorkbook(is);

            } else {

                wb = null;

            }

        } catch (FileNotFoundException e) {

            logger.error("FileNotFoundException", e);

        } catch (IOException e) {

            logger.error("IOException", e);

        }

    }

    /**

     * 读取Excel表格表头的内容

     *

     * @param InputStream

     * @return String 表头内容的数组

     * @author zengwendong

     */

    public String[] readExcelTitle() throws Exception {

        if (wb == null) {

            throw new Exception("Workbook对象为空！");

        }

        sheet = wb.getSheetAt(0);

        row = sheet.getRow(0);

        // 标题总列数

        int colNum = row.getPhysicalNumberOfCells();

        System.out.println("colNum:" + colNum);

        String[] title = new String[colNum];

        for (int i = 0; i < colNum; i++) {

            title[i] = row.getCell(i).getCellFormula();

        }

        return title;

    }

    /**

     * 读取Excel数据内容

     * @description

     * @return 包含单元格数据内容的List对象

     * @throws Exception

     */

    public List<Map> readExcelContent(List<String> columnsList) throws Exception {

        if (wb == null) {

            throw new Exception("Workbook对象为空！");

        }

        //

        List<Map> content = new ArrayList<Map>();

        sheet = wb.getSheetAt(0);

        // 得到总行数

        int rowNum = sheet.getLastRowNum();

        row = sheet.getRow(0);

        int colNum = row.getPhysicalNumberOfCells();

        //

        Map<String, Object> cellValue = null;

        //

        if (null == columnsList || columnsList.size() != colNum || columnsList.isEmpty()) {

            // 正文内容应该从第二行开始,第一行为表头的标题

            for (int i = 1; i <= rowNum; i++) {

                // 获取当前行

                row = sheet.getRow(i);

                int j = 0;

                cellValue = new HashMap<String, Object>();

                while (j < colNum) {

                    Object obj = getCellFormatValue(row.getCell(j));

                    cellValue.put(String.valueOf(j), obj);

                    j++;

                }

                content.add(cellValue);

            }

        } else {

            // 正文内容应该从第二行开始,第一行为表头的标题

            for (int i = 1; i <= rowNum; i++) {

                // 获取当前行

                row = sheet.getRow(i);

                int j = 0;

                cellValue = new HashMap<String, Object>();

                while (j < colNum) {

                    Object obj = getCellFormatValue(row.getCell(j));

                    cellValue.put(columnsList.get(j), obj);

                    j++;

                }

                content.add(cellValue);

            }

        }

        return content;

    }

    /**

     *

     * 根据Cell类型设置数据

     *

     * @param cell

     * @return

     * @author zengwendong

     */

    private Object getCellFormatValue(Cell cell) {

        Object cellvalue = "";

        // 非空

        if (null != cell && (!"".equals(cell.toString()))) {

            // 判断当前Cell的Type

            switch (cell.getCellType()) {

            case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC

            case Cell.CELL_TYPE_FORMULA: {

                // 判断当前的cell是否为Date

                if (DateUtil.isCellDateFormatted(cell)) {

                    // 如果是Date类型则，转化为Data格式

                    // data格式是带时分秒的：2013-7-10 0:00:00

                    // cellvalue = cell.getDateCellValue().toLocaleString();

                    // data格式是不带带时分秒的：2013-7-10

                    Date date = cell.getDateCellValue();

                    cellvalue = date;

                } else {// 如果是纯数字

                    // 取得当前Cell的数值

                    cellvalue = String.valueOf(cell.getNumericCellValue());

                }

                break;

            }

            case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING

                // 取得当前的Cell字符串

                cellvalue = cell.getRichStringCellValue().getString();

                break;

            default:// 默认的Cell值

                cellvalue = "";

            }

        } else {

            cellvalue = "";

        }

        return cellvalue;

    }

}

public static void main(String[] args) {

    try {

        String filepath = "C:\\Users\\Marydon\\Desktop\\值班名单汇总 (20170714).xlsx";

        ReadExcelUtils excelReader = new ReadExcelUtils(filepath);

        // 说明：excel的列的所代表的字段的顺序必须与list集合的字段名一致（excel的列名和list的元素名没必要相同）

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

        columnsList.add("ORG_ID");

        columnsList.add("DEPENT_ID");

        columnsList.add("DEPENT_NAME");

        columnsList.add("DOCTOR_ID");

        columnsList.add("DOCTOR_NAME");

        columnsList.add("DOCTOR_PHONE");

        columnsList.add("SCHEDULE_DATE");

        columnsList.add("WEEK_TXT");

        columnsList.add("WB_TYPE");

        columnsList.add("CLOSE_TZ");

        columnsList.add("REPLACE_TZ");

        columnsList.add("DOCTOR_ID_TZ");

        columnsList.add("DOCTOR_NAME_TZ");

        columnsList.add("REMARK");

        // 对读取Excel表格内容测试

        List<Map> list = excelReader.readExcelContent(columnsList);

        System.out.println("获得Excel表格的内容:");

        for (int i = 0; i < list.size(); i++) {

            System.out.println(list.get(i));

        }

    } catch (FileNotFoundException e) {

        System.out.println("未找到指定路径的文件!");

        e.printStackTrace();

    } catch (Exception e) {

        e.printStackTrace();

    }

}