package com.ruixuan.data.utils;

import cn.hutool.core.util.CharsetUtil;
import com.alibaba.fastjson.JSONObject;
import com.ruixuan.common.utils.StringUtils;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class LiveDataExcelUtils {

    /**
     * 数组集转LIST 同时去除多余双引号
     */
    public static List<List<String>> getCsvList(List<String[]> records) {
        List<List<String>> list = new ArrayList<>();
        for (String[] record : records) {
            List<String> asList = Arrays.asList(record);
            asList.replaceAll(t -> t.replace("\"", ""));
            list.add(asList);
        }
        return list;
    }

    /**
     * 1、判断是否为数字类型（NUMBER）或数字计算公式（NUMBER_FORMULA）;
     * 2、获取解析后的值进行判断是否包含有（E、e、+等符号）;
     * 3、使用java自带数学类，将科学计算公式转换成所需类型。
     *
     * @return
     */
    public static String NUMBER_FORMULA(String E_Value) {
        String value = null;

        // 判断是否为科学计数法（包含E、e、+等符号）
        if ((("" + E_Value).indexOf("E") != -1 || ("" + E_Value).indexOf("e") != -1 || ("" + E_Value).indexOf("+") != -1) && !E_Value.contains("https")) {
            BigDecimal bd = new BigDecimal("" + E_Value);
            if (("" + E_Value).indexOf("E") != -1 || ("" + E_Value).indexOf("e") != -1 || ("" + E_Value).indexOf("+") != -1) {
                bd = new BigDecimal(Double.parseDouble(bd.toString()));
                value = bd.toString();
            } else {
                value = "" + E_Value;
            }
        } else {
            value = "" + E_Value;
        }
        return value;
    }


    public static List<JSONObject> analysisFile(MultipartFile file, String fileName) throws Exception {
        JSONObject returnJson = new JSONObject();
        Workbook workbook = null;
        String substring = fileName.substring(fileName.lastIndexOf(".") + 1).toUpperCase();
        if ("XLS".equals(substring)) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else if ("XLSX".equals(substring)) {
            workbook = new XSSFWorkbook(file.getInputStream());
        } else if ("CSV".equals(substring)) {
            return analysisCsvFile(file);
        } else {
            throw new Exception("请上传正确格式的文件，支持扩展名.xls;.xlsx;.csv");
        }
        Sheet sheet = workbook.getSheetAt(0);
        if (StringUtils.isNull(sheet)) {
            throw new Exception("未读取到Sheet页内容！");
        }
        int rows = sheet.getLastRowNum();//行数，一共有多少行+
        if (rows == 0) {
            throw new Exception("请填写数据");
        }
        ExportExcelUtils eeu = new ExportExcelUtils();
        //标题行
        Row titleRow = sheet.getRow(0);
        //最后一列（列数）
        int lastCellNum = titleRow.getLastCellNum();

        // list存储数据集
        List<JSONObject> list = new ArrayList<>();
        for (int i = 1; i <= rows + 1; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                //每行数据对象，按照顺序从0到（lastCellNum-1）记录
                JSONObject cellEntity = new JSONObject();
                for (int j = 0; j < lastCellNum; j++) {
                    cellEntity.put(String.valueOf(j), eeu.getCellValue(row.getCell(j)));
                }
                list.add(cellEntity);
            }
        }
        return list;
    }

    /**
     * 解析 CSV 格式文件
     */
    private static List<JSONObject> analysisCsvFile(MultipartFile file) throws Exception {
        List<String[]> records = new ArrayList<String[]>();
        BufferedReader files = new BufferedReader(new InputStreamReader(file.getInputStream(), CharsetUtil.CHARSET_GBK));
        // file.readLine(); //跳过表头所在的行

        String record;
        // 遍历数据行并存储在名为records的ArrayList中，每一行records中存储的对象为一个String数组
        while ((record = files.readLine()) != null) {
            String fields[] = record.split(",");
            records.add(fields);
        }
        // 关闭文件
        files.close();

        List<List<String>> cellList = getCsvList(records);
        // list存储数据集
        List<JSONObject> list = new ArrayList<>();
        for (int i = 1; i < cellList.size(); i++) {
            List<String> values = cellList.get(i);
            //每行数据对象，按照顺序从0到（lastCellNum-1）记录
            JSONObject cellEntity = new JSONObject();
            for (int j = 0; j < values.size(); j++) {
                cellEntity.put(String.valueOf(j), values.get(j));
            }
            list.add(cellEntity);
        }
        return list;
    }

}