package cn.quantgroup.customer.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ：dongjianhua
 * @date ：Created in 2019/10/28 10:20
 * @description：excel处理工具类
 * @modified By：
 * @version: 1.0
 */
@Component
public class ExcelUtil {

    /**
     * 总行数
     */
    private int totalRows = 0;
    /**
     * 总列数
     */
    private int totalCells = 0;
    /**
     * 错误信息
     */
    private String errorInfo;

    public boolean validateExcel(String filePath) {
        /** 检查文件名是否为空或者是否是Excel格式的文件 */
        if (filePath == null
                || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorInfo = "文件名不是excel格式";
            return false;
        }
        /** 检查文件是否存在 */
//        File file = new File(filePath);
//        if (file == null || !file.exists()) {
//            errorInfo = "文件不存在";
//            return false;
//        }
        return true;
    }







    public  <T>List<T> read(String filename, InputStream is,Class<T> t) throws IOException {
        /** 验证文件是否合法 */
        if (!validateExcel(filename)) {
            throw new RuntimeException("文件格式不合法 ");
        }
        /** 判断文件的类型，是2003还是2007 */
        Workbook wb = null;
        if (!isExcel2007(filename)) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        List<T> dataLst = new ArrayList<>();
        /** 得到第一个shell */
        Sheet sheet = wb.getSheetAt(0);
        /** 得到Excel的行数 */
        this.totalRows = sheet.getPhysicalNumberOfRows();
        /** 得到Excel的列数 */
        if (this.totalRows >= 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<String> fields  = new ArrayList<>();
        /** 循环Excel的行 */
        for (int r = 0; r < this.totalRows; r++) {

            if(r == 0){
                continue;
            }
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            T rowLst = null;
            /** 循环Excel的列 */
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {
                    // 以下是判断数据的类型
                    switch (cell.getCellType()) {

                        case 0: // 数字
                            cellValue = cell.getNumericCellValue() + "";
                            break;
                        case 1: // 字符串
                            cellValue = cell.getStringCellValue();
                            break;
                        case  4: // Boolean
                            cellValue = cell.getBooleanCellValue() + "";
                            break;
                        case 2: // 公式
                            cellValue = cell.getCellFormula() + "";
                            break;
                        case 3: // 空值
                            cellValue = "";
                            break;
                        case 5: // 故障
                            cellValue = "非法字符";
                            break;
                        default:
                            cellValue = "未知类型";
                            break;
                    }
                }

                if(r == 1){
                    fields.add(cellValue);
                    continue;
                }
                if (StringUtils.isEmpty(cellValue)) {
                    continue;
                }
                if(rowLst == null){
                    try {
                        rowLst =   t.newInstance();
                    } catch (InstantiationException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }


                }
                if (c == 2||c == 5||c == 4) {
                    BigDecimal bd = new BigDecimal(cellValue);//要修改的值，需要string类型
                    cellValue = bd.setScale(0, BigDecimal.ROUND_HALF_UP).toPlainString();
                }
                setValue(fields.get(c),cellValue,rowLst);
            }
            /** 保存第r行的第c列 */
            if(null != rowLst){
                dataLst.add(rowLst);
            }

        }
        return dataLst;
    }

    private void setValue(String field,String val, Object obj){
        for (Method method : obj.getClass().getMethods()) {
            if(method.getName().equalsIgnoreCase("set"+field.replace("_",""))){
                try {
                    method.invoke(obj,val);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
                break;
            }
        }
    }

    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
}
