package cc.smtweb.framework.core.util;

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

import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {
    private static int xy(int x, int y) {
        if (y == 0) return 1;
        if (y == 1) return x;
        int f = x * x;
        for (int i = 0; i < (y - 2); ++i) {
            f *= x;
        }
        return f;
    }

    // Z==> 26, AA == > 27 , AB ==> 28
    public static int toColInt(String col) {
        col = col.toUpperCase();
        int colv = 0, left = 0;
        for (int i = col.length(); i > 0; --i) {
            char c = col.charAt(i - 1);
            colv += (c - 'A' + 1) * xy(26, left);
            ++left;
        }
        return colv - 1;  // excel是从0开始的
    }

    /**
     * 数字下标转列
     * @param index
     * @return
     */
    public static String toColExcel(int index) {
        int shang = 0;
        int yu = 0;
        List<Integer> list = new ArrayList<Integer>();   //10进制转26进制 倒序
        do {
            shang = index / 26;
            yu = index % 26;
            index = shang;
            list.add(yu);
        } while (shang != 0);
        StringBuilder sb = new StringBuilder();
        for (int j = list.size() - 1; j >= 0; j--) {
            sb.append((char) (list.get(j) + 'A' - (Math.min(j, 1))));     //倒序拼接  序号转字符 非末位 序号减去 1
        }
        return sb.toString();
    }


    // 注意col要大写字母
    public static String getStr(Row row, char col) {
        int i = col - 'A';
        return getStr(row, i);
    }

    public static String getStr(Row row, int col) {
        if(col <0) return "";
        Cell cell = row.getCell(col);
        if (null == cell) return "";
        if (cell.getCellType() == CellType.BLANK || cell.getCellType() == CellType.ERROR) {
            return "";
        }
        // AKzz : cell 是数字时，会报错误,
        if (cell.getCellType() == CellType.NUMERIC) {
            // 非日期格式
            if (!org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                double d = cell.getNumericCellValue();
//                long x = (long) d;
                return MathUtil.getDoubleStr(d);
            } else {
                return cc.smtweb.framework.core.util.DateUtil.toStdDateString(cell.getDateCellValue());
            }
        }

        if (cell.getCellType() == CellType.STRING) {
            return StringUtil.checkNull(cell.getStringCellValue());
        }

        if (cell.getCellType() == CellType.FORMULA) {
            return getFormulaStr(cell);
        }
        return "";
    }

    // 注意col要大写字母
    public static int getInt(Row row, char col) {
        int i = col - 'A';
        return getInt(row, i);
    }

    public static int getInt(Row row, int col) {
        if(col <0) return 0;
        Cell cell = row.getCell(col);
        if (null == cell) return 0;
        if (cell.getCellType() == CellType.NUMERIC && !org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
            double d = cell.getNumericCellValue();
            return (int) d;
        } else if (cell.getCellType() == CellType.STRING) {
            return MathUtil.toint(cell.getStringCellValue(), 0);
        } else if (cell.getCellType() == CellType.FORMULA) {
            return (int) getFormulaDbl(cell);
        }
        return 0;
    }

    private static String getFormulaStr(Cell cell) {
        try {
            return StringUtil.checkNull(cell.getStringCellValue());
        } catch (Exception e) {
//            UtilLogger.warn("读取数据出错："+cell, e);
            System.err.println("读取数据出错");
            String v = MathUtil.toStdNumberString(cell.getNumericCellValue());
            if (v.endsWith(".00")) v = v.substring(0, v.length() - 3);
            return v;
        }
    }

    private static double getFormulaDbl(Cell cell) {
        try {
            return cell.getNumericCellValue();
        } catch (Exception e) {
            return MathUtil.todouble(cell.getStringCellValue());
        }
    }
}
