package com.yeejoin.amos.kgd.config;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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 java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.*;


public class ExcelTool {

    private static NumberFormat numberFormat = null;

    /**
     * 解析xls和xlsx不兼容问题
     *
     * @param pfs
     * @param workbook
     * @param inputStream
     * @return
     */
    public static Workbook getWorkBook(POIFSFileSystem pfs, Workbook workbook, InputStream inputStream, String filename) throws IOException {
        if (filename.endsWith("xls")) {
            pfs = new POIFSFileSystem(inputStream);
            workbook = new HSSFWorkbook(pfs);
            return workbook;
        } else if (filename.endsWith("xlsx")) {
            try {
                workbook = new XSSFWorkbook(inputStream);
                return workbook;
            } catch (IOException e) {
                return null;
            }
        } else {
            return null;
        }
    }

    /**
     * 解析Excel
     *
     * @param inputStream 文件
     * @return
     */
    public static Map<String, Object> readFromExcel(InputStream inputStream, String filename) throws Exception {
        Map<String, Object> resultTableMap = new HashMap<>();
        POIFSFileSystem pfs = null;
        Workbook workbook = null;
        try {
            // 解析xls和xlsx不兼容问题
            workbook = getWorkBook(pfs, workbook, inputStream, filename);
        } catch (IOException e) {
            e.printStackTrace();
            throw new Exception("模板保存异常。");
        }
        if (workbook == null) {
            throw new Exception("请使用模板上传文件");
        }
        // 拿第一个sheet表
        Sheet sheet = workbook.getSheetAt(0);
        // 校验sheet是否合法
        if (Objects.isNull(sheet)) {
            return null;
        }

        List<String> titleList = new LinkedList<>();
        numberFormat = NumberFormat.getNumberInstance();
        // 创建一个集合根据下标来确定每个单元格对应对象的什么属性
        List<List<String>> dataList = new ArrayList<>();

        // 获取第一行数据（假如第一行就是列名）
        Row sheetTitleRow = sheet.getRow(sheet.getFirstRowNum());
        // 取出最后一列
        short lastCellNum = sheetTitleRow.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            // 取出每一列的名
            String cellValue = sheetTitleRow.getCell(i).getStringCellValue();
            titleList.add(cellValue);
        }
        resultTableMap.put("tableColumns", titleList);

        // 获得sheet总行数
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum < 1) {
            throw new Exception("数据错误");
        }
        // 开始读取,不读取表头所以从第二行开始
        for (int i = 1; i <= lastRowNum; i++) {
            // 获取每一行
            Row row = sheet.getRow(i);
            // 行为空不读取
            if (row == null) {
                continue;
            }
            Cell cell = row.getCell(0);
            //列为空不读取
            if (cell == null || StringUtils.isEmpty(convertData(cell))) {
                continue;
            }
            List<String> rowList = new ArrayList<>();
            //添加数据
            short lastCellNum2 = row.getLastCellNum();
            for (int j = 0; j < lastCellNum2; j++) {
                Cell cellOne = row.getCell(j);
                try {
                    String item = convertData(cellOne);
                    rowList.add(item.trim());
                } catch (Exception e) {
                    System.out.println("-------------------Err-----------------------");
                    System.out.println(i + "行" + j + "列数据转换出现异常");
                    rowList.add("");
                }
            }
            dataList.add(rowList);
            //规避行数数据后几行为空
            if (rowList.size() < lastCellNum2) {
                for (int k = 0; k < 15 - rowList.size(); k++) {
                    rowList.add("");
                }
            }
        }
        resultTableMap.put("tableDatas", dataList);
        return resultTableMap;
    }

    /**
     * 表格数据转换
     *
     * @param cell
     * @return
     */
    public static String convertData(Cell cell) {
        String str = "";
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                //判断是否是整数
                str = numberFormat.format(cell.getNumericCellValue());
                break;
            case STRING:
                str = cell.getStringCellValue();
                break;
            case FORMULA:
                try {
                    str = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalArgumentException e) {
                    str = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            default:
                str = "";
        }
        return str;
    }

}
