package com.sy.coinage.core.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.sy.coinage.core.exception.RetException;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;

/**
 * -操作excel的工具类
 */
public class ExcelUtil {

    /**
     * -excel后缀
     */
    public static final String EXCEL_PREFIX = ".xlsx";
    public static final String EXCEL_PREFIX_1 = ".xls";
    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");
    private static final SimpleDateFormat DATE_FORMAT_1 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

    /**
     * -读取excel
     *
     * @param filepath
     * @return
     */
    public static Workbook readExcel(String filepath) {
        Workbook wb = null;
        if (filepath == null) {
            return null;
        }
        String extString = filepath.substring(filepath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filepath);
            if (extString.equals(EXCEL_PREFIX_1)) {
                wb = new HSSFWorkbook(is);
            } else if (extString.equals(EXCEL_PREFIX)) {
                wb = new XSSFWorkbook(is);
            }
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }


    /**
     * -读取excel
     *
     * @return
     */
    public static Workbook readExcel(MultipartFile file) {
        Workbook wb = null;
        if (file == null) {
            return null;
        }
        if (!file.getOriginalFilename().endsWith(EXCEL_PREFIX)) {
            throw new RetException("错误的文件类型：" + file.getOriginalFilename());
        }
        InputStream is = null;
        try {
            is = file.getInputStream();
            if (file.getOriginalFilename().endsWith(EXCEL_PREFIX_1)) {
                wb = new HSSFWorkbook(is);
            } else if (file.getOriginalFilename().endsWith(EXCEL_PREFIX)) {
                wb = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            throw new RetException("读取文件出错");
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }

    /**
     * -获取cell的值 不能处理日期类型
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 判断类型
            switch (cell.getCellType()) {
                case NUMERIC: { // 改成这样
                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                        cellValue = DATE_FORMAT.format(cell.getDateCellValue());
                    } else {
                        cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                    }
                    break;
                }
                case STRING: {// string类型
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                case FORMULA: {
                    // 日期类型
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        // 数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                default:
                    cellValue = null;
                    break;
            }

        } else {
            cellValue = null;
        }

        return cellValue;
    }

    /**
     * 获取单元格内容
     *
     * @param cell
     * @return
     */
    public static String getAllCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        // 解决日期读入后的问题，POI读取后变成“02-十一月-2018”格式
        if (cell.toString().contains("-") && checkDate(cell.toString())) {
            String str = DATE_FORMAT_1.format(cell.getDateCellValue());
            str = str.toString();
            return str;
        }
        // 解决日期读入后的问题，POI读取后变成“02-十一月-2018”格式
        if (cell.toString().contains("-") && checkDate(cell.toString())) {
            String str = DATE_FORMAT_1.format(cell.getDateCellValue());
            str = str.toString();
            return str;
        }
        CellType cellType = cell.getCellType();
        if (cellType == CellType.NUMERIC) {
            double val = cell.getNumericCellValue();
            // stripTrailingZeros() 去除结尾多余的0
            // toPlainString() 输出时不使用科学读数法
            return new BigDecimal(String.valueOf(val)).stripTrailingZeros().toPlainString();
        } else {
            String val = cell.getStringCellValue();
            return val.trim().replaceAll(" ", "").replaceAll("/t", "").replaceAll(" ", "");
        }
    }

    /**
     * 判断单元格是否是日期
     *
     * @param str
     * @return
     */
    public static boolean checkDate(String str) {
        String[] dataArr = str.split("-");
        try {
            if (dataArr.length == 3) {
                int x = Integer.parseInt(dataArr[0]);
                String y = dataArr[1];
                Integer z = Integer.parseInt(dataArr[2]);
                if (x > 0 && x < 32 && z > 0 && z < 10000 && y.endsWith("月")) {
                    return true;
                }
            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }

    public static Workbook readExcelClassPath(String filepath) {
        Workbook wb = null;
        if (filepath == null) {
            return null;
        }
        String extString = filepath.substring(filepath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = ExcelUtil.class.getResourceAsStream(filepath);
            if (extString.equals(EXCEL_PREFIX_1)) {
                wb = new HSSFWorkbook(is);
            } else if (extString.equals(EXCEL_PREFIX)) {
                wb = new XSSFWorkbook(is);
            }
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }

}
