package com.fufan.mtsaas.tools;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel读取
 *
 * @version 1.0
 * @auther Liby
 * @create 2018/4/28
 **/
@Slf4j
public class ExcelRead {


    /**
     * 读取EXCEL2007
     *
     * @param file
     * @param startRow
     * @param startCell
     * @return
     */
    public List<ArrayList<String>> readExcel2007(MultipartFile file, int startRow, int startCell) {
        startRow--;
        startCell--;
        if (file.isEmpty()) {
            return null;
        }
        List<ArrayList<String>> list = new ArrayList<>();
        // IO流读取文件
        InputStream input = null;
        XSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        int totalRows; //sheet中总行数
        int totalCells; //每一行总单元格数
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new XSSFWorkbook(input);

            //读取sheet(页)
            XSSFSheet hssfSheet = wb.getSheetAt(0);
            totalRows = hssfSheet.getPhysicalNumberOfRows();
            //Liby 2020-04-14 取第一行的最大列数
            int firstRowCell = hssfSheet.getRow(0).getPhysicalNumberOfCells();
            for (int rowNum = startRow; rowNum <= totalRows; rowNum++) {
                XSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    rowList = new ArrayList<>();
                    totalCells = hssfRow.getPhysicalNumberOfCells();
                    //Liby 2020-04-14 取第一行的最大列数
                    if(totalCells < firstRowCell){
                        totalCells = firstRowCell;
                    }
                    boolean isNotNull =false;
                    for (int cellNum = startCell; cellNum <= totalCells; cellNum++) {
                        XSSFCell cell = hssfRow.getCell(cellNum);
                        if (cell != null) {
                            String cellValue = getCellValue2007(cell);
                            if (cellValue != null) {
                                rowList.add(cellValue);
                                isNotNull =true;
                            }else{
                                rowList.add("");
                            }
                        }else{
                            rowList.add("");
                        }
                    }
                    if(isNotNull){
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            log.error("Excel读取失败：", e);
        } finally {
            try {
                input.close();
            } catch (IOException e) {
                log.error("Excel读取失败：", e);
            }
        }
        return null;
    }

    /**
     * 读取EXCEL2003
     *
     * @param file
     * @param startRow
     * @param startCell
     * @return
     */
    public List<ArrayList<String>> readExcel(MultipartFile file, int startRow, int startCell) {
        if (file.getOriginalFilename().indexOf(".xlsx") != -1) {
            return readExcel2007(file, startRow, startCell);
        }

        startRow--;
        startCell--;
        if (file.isEmpty()) {
            return null;
        }
        List<ArrayList<String>> list = new ArrayList<>();
        // IO流读取文件
        InputStream input = null;
        HSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        int totalRows; //sheet中总行数
        int totalCells; //每一行总单元格数
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new HSSFWorkbook(input);

            //读取sheet(页)
            HSSFSheet hssfSheet = wb.getSheetAt(0);
            totalRows = hssfSheet.getPhysicalNumberOfRows();
            //Liby 2020-04-14 取第一行的最大列数
            int firstRowCell = hssfSheet.getRow(0).getPhysicalNumberOfCells();
            for (int rowNum = startRow; rowNum <= totalRows; rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    rowList = new ArrayList<>();
                    totalCells = hssfRow.getPhysicalNumberOfCells();
                    //Liby 2020-04-14 取第一行的最大列数
                    if(totalCells < firstRowCell){
                        totalCells = firstRowCell;
                    }
                    boolean isNotNull =false;
                    for (int cellNum = startCell; cellNum <= totalCells; cellNum++) {
                        HSSFCell cell = hssfRow.getCell(cellNum);
                        if (cell != null) {
                            String cellValue = getCellValue(cell);
                            if (cellValue != null) {
                                rowList.add(cellValue);
                                isNotNull =true;
                            }else{
                                rowList.add("");
                            }
                        }else{
                            rowList.add("");
                        }
                    }
                    if(isNotNull){
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            log.error("Excel读取失败：", e);
//            return readExcel2007(file, startRow, startCell);
        } finally {
            try {
                input.close();
            } catch (IOException e) {
                log.error("Excel读取失败：", e);
            }
        }

        return null;
    }

    private static String getCellValue(HSSFCell cell) {
        DecimalFormat df = new DecimalFormat("#");
        String cellValue = null;
        if (cell == null) {
            return null;
        }
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    break;
                }
                //数值类型没有小数位
                cellValue = String.valueOf(cell.getNumericCellValue());
                if(Objects.nonNull(cellValue) && cellValue.contains("E")) {
                    cellValue = df.format(cell.getNumericCellValue());
                }
                break;
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
            case BLANK:
                cellValue = null;
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
        }
        if (cellValue != null && cellValue.trim().length() <= 0) {
            cellValue = null;
        }
        return cellValue;
    }

    private static String getCellValue2007(XSSFCell cell) {
        DecimalFormat df = new DecimalFormat("#");
        String cellValue = null;
        if (cell == null) {
            return null;
        }
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    break;
                }
                //数值类型没有小数位
                cellValue = String.valueOf(cell.getNumericCellValue());
                if(Objects.nonNull(cellValue) && cellValue.contains("E")) {
                    cellValue = df.format(cell.getNumericCellValue());
                }
                break;
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
            case BLANK:
                cellValue = null;
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
        }
        if (cellValue != null && cellValue.trim().length() <= 0) {
            cellValue = null;
        }
        return cellValue;
    }


    /**
     * 从excel文件读取数据
     * @param path
     * @param sheet
     * @param row
     * @return
     * @throws Exception
     */

    public List<List<Object>> getRowFromExcel(String path, int sheet, int row) throws Exception {
        // 创建输入流，读取Excel
        InputStream is = new FileInputStream(path);
        // jxl提供的Workbook类
        Workbook wb = WorkbookFactory.create(is);

        //创建一个Sheet对象
        Sheet sheet1 = wb.getSheetAt(sheet);
        Row row1 = sheet1.getRow(row);
        int rowNum = row1.getLastCellNum();

        List<List<Object>> allData = new ArrayList<List<Object>>();
        // 越过第一行 它是列名称
        for (int j = 1; j < rowNum; j++) {

            List<Object> oneData = new ArrayList<Object>();
            // 得到每一行的单元格的数据
            Row cells = sheet1.getRow(j);
            for (int k = 0; k < cells.getPhysicalNumberOfCells(); k++) {
                oneData.add(getCellValueByCell(cells.getCell(k)));
            }
            // 存储每一条数据
            allData.add(oneData);
        }

        return allData;
    }

    /**
     * 获取单元格各类型值，返回字符串类型
     * @param cell
     * @return
     */
    private static Object getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType = cell.getCellType();

        // 以下是判断数据的类型
        switch (cellType) {
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字

                if (0 == cell.getCellType()) {//判断单元格的类型是否则NUMERIC类型
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat(
                                "yyyy-MM-dd HH:mm");
                        cellValue = formater.format(date);
                    } else {
                        cellValue = cell.getNumericCellValue() + "";
                    }
                }
                break;


            case HSSFCell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;


            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;


            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;


            case HSSFCell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;


            case HSSFCell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;


            default:
                cellValue = "未知类型";
                break;

        }
        return cellValue;
    }

}
