package com.moral.api.service.impl;


import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.moral.api.entity.TbExcel;
import com.moral.api.mapper.ExcelMapper;
import com.moral.api.pojo.bo.ExcelBO;

import com.moral.api.pojo.vo.excel.ExcelVo;
import com.moral.api.service.ExcelService;
import com.moral.api.utils.ExcelUtils;

import com.moral.util.DateUtils;


@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private ExcelMapper excelMapper;

    /**
     * 高新区导入
     * @param files
     * @param params
     * @return
     * @throws IOException
     */
    @Override
    public ExcelBO importTemplate(List<MultipartFile> files, Map<String, Object> params) throws IOException {
        Date date2 = new Date();
        String time = (String) params.get("time");
        String code = (String) params.get("code");
//        String time1 = (String) params.get("date");
        Date date = DateUtils.getDate(time, "yyyy-MM-dd");
//        Date date1 = DateUtils.getDate(time1, "yyyy-MM-dd");
        ExcelBO excelBO = new ExcelBO();
        QueryWrapper<TbExcel> wrapper = new QueryWrapper<>();
        wrapper.eq("time",date).eq("code",code);
        TbExcel excel = excelMapper.selectOne(wrapper);
        if (excel==null){
            TbExcel excel1 = new TbExcel();
            HashMap<String, Object> map = new HashMap<>();
            MultipartFile file1 = files.get(0);
            Map<String, Object> map1 = getMap1(file1);
            map.put("c1",map1);
            MultipartFile file2 = files.get(1);
            Map<String, Object> map2 = getMap2(file2);
            map.put("c2",map2);
            MultipartFile file3 = files.get(2);
            Map<String, Object> map3 = getMap3(file3);
            map.put("c3",map3);
            MultipartFile file4 = files.get(3);
            Map<String, Object> map4 = getMap4(file4);
            map.put("c4",map4);
            MultipartFile file5 = files.get(4);
            Map<String, Object> map5 = getMap5(file5);
            map.put("c5",map5);
            if (files.size()>5){
                MultipartFile file6 = files.get(5);
                Map<String, Object> map6 = getMap6(file6);
                map.put("c6",map6);
            }
            String s = JSON.toJSONString(map);
            excel1.setValue(s);
            excel1.setTime(date);
            excel1.setCode(code);
            excel1.setDate(date2);
            excelMapper.insert(excel1);
            excelBO.setId(excel1.getId());
            excelBO.setTime(excel1.getTime());
            excelBO.setDate(date2);

        }else {
            HashMap<String, Object> map = new HashMap<>();
            MultipartFile file1 = files.get(0);
            Map<String, Object> map1 = getMap1(file1);
            map.put("c1",map1);
            MultipartFile file2 = files.get(1);
            Map<String, Object> map2 = getMap2(file2);
            map.put("c2",map2);
            MultipartFile file3 = files.get(2);
            Map<String, Object> map3 = getMap3(file3);
            map.put("c3",map3);
            MultipartFile file4 = files.get(3);
            Map<String, Object> map4 = getMap4(file4);
            map.put("c4",map4);
            MultipartFile file5 = files.get(4);
            Map<String, Object> map5 = getMap5(file5);
            map.put("c5",map5);
            if (files.size()>5){
                MultipartFile file6 = files.get(5);
                Map<String, Object> map6 = getMap6(file6);
                map.put("c6",map6);
            }
            String s = JSON.toJSONString(map);
            excel.setValue(s);
            excelMapper.updateById(excel);
            excelBO.setId(excel.getId());
            excelBO.setTime(excel.getTime());
            excelBO.setDate(date2);
        }
        return excelBO;
    }

    /**
     * 高新区导出
     * @param id
     * @return
     */
    @Override
    public ExcelVo export(Integer id) {
        HashMap<String, Object> map1 = new HashMap<>();
        ArrayList<Map<String,Object>> list1 = new ArrayList<>();
        ArrayList<Map<String,Object>> list2 = new ArrayList<>();
        ArrayList<Map<String,Object>> list3 = new ArrayList<>();
        ArrayList<Map<String,Object>> list4 = new ArrayList<>();
        ArrayList<Map<String,Object>> list5= new ArrayList<>();
        ExcelVo excelVo = new ExcelVo();
        //类型转换
        TbExcel excel = excelMapper.selectById(id);
        String value = excel.getValue();

        JSONObject jsonObject = JSON.parseObject(value);
        Map map4 = JSON.parseObject(value, Map.class);
        Set sets = map4.keySet();
        for (Object set : sets) {
            if (set.equals("c1")){
                Object o = map4.get(set);
                String s = JSON.toJSONString(o);
                Map map5 = JSON.parseObject(s, Map.class);
                Set set1s = map5.keySet();
                for (Object set1 : set1s) {
                    Object o1 = map5.get(set1);
                    String s1 = JSON.toJSONString(o1);
                    Map map6 = JSON.parseObject(s1, Map.class);
                    list1.add(map6);
                }
            }
            if (set.equals("c2")){
                Object o = map4.get(set);
                String s = JSON.toJSONString(o);
                Map map5 = JSON.parseObject(s, Map.class);
                Set set1s = map5.keySet();
                for (Object set1 : set1s) {
                    Object o1 = map5.get(set1);
                    String s1 = JSON.toJSONString(o1);
                    Map map6 = JSON.parseObject(s1, Map.class);
                    list2.add(map6);
                }
            }
            if (set.equals("c3")){
                Object o = map4.get(set);
                String s = JSON.toJSONString(o);
                Map map5 = JSON.parseObject(s, Map.class);
                Set set1s = map5.keySet();
                for (Object set1 : set1s) {
                    if (set1.equals("高新区")){
                        Object o1 = map5.get(set1);
                        String s1 = JSON.toJSONString(o1);
                        Map map6 = JSON.parseObject(s1, Map.class);
                        map6.put("place","国控站");
                        list3.add(map6);
                    }else {
                        Object o1 = map5.get(set1);
                        String s1 = JSON.toJSONString(o1);
                        Map map6 = JSON.parseObject(s1, Map.class);
                        map6.put("place",set1.toString().substring(3));
                        list3.add(map6);
                    }
                }
            }
            if (set.equals("c4")){
                Object o = map4.get(set);
                String s = JSON.toJSONString(o);
                Map map5 = JSON.parseObject(s, Map.class);
                Set set1s = map5.keySet();
                for (Object set1 : set1s) {
                    Object o1 = map5.get(set1);
                    String s1 = JSON.toJSONString(o1);
                    Map map6 = JSON.parseObject(s1, Map.class);
                    String o2 = String.valueOf(map6.get("PM2_5m"));

                    String place = String.valueOf(map6.get("place"));
                    String o3m = String.valueOf(map6.get("O3m"));
                    String aqIm = String.valueOf(map6.get("AQIm")) ;
                    String o2s =String.valueOf(map6.get("PM2_5y"));
                    String O3ms = String.valueOf(map6.get("O3y")) ;
                    String aqIms = String.valueOf(map6.get("AQIy"));
                    String result = o2+"、"+o3m+"、"+aqIm+"、"+o2s+"、"+O3ms+"、"+aqIms;
                    map1.put(place,result);
                    list4.add(map6);
                }
            }
            if (set.equals("c5")){
                Object o = map4.get(set);
                String s = JSON.toJSONString(o);
                Map map5 = JSON.parseObject(s, Map.class);
                Set set1s = map5.keySet();
                for (Object set1 : set1s) {
                    Object o1 = map5.get(set1);
                    String s1 = JSON.toJSONString(o1);
                    Map map6 = JSON.parseObject(s1, Map.class);
                    list5.add(map6);
                }
            }
        }

        //第四个文件
        String c4 = jsonObject.getString("c4");
        JSONObject jsonObject3 = JSON.parseObject(c4);
        String gxq = jsonObject3.getString("高新区");
        JSONObject jsonObject4 = JSON.parseObject(gxq);

        String PM25m = jsonObject4.getString("PM2_5m");
        String PM25ms = jsonObject4.getString("PM2_5ms");
        String code1 = getString(PM25ms);
        String O3m = jsonObject4.getString("O3m");
        String O3ms = jsonObject4.getString("O3ms");
        String code2 = getString(O3ms);
        String AQIm = jsonObject4.getString("AQIm");
        String AQIms = jsonObject4.getString("AQIms");
        String code3 = getString(AQIms);
        String PM25y = jsonObject4.getString("PM2_5y");
        String PM25ys = jsonObject4.getString("PM2_5ys");
        String code4 = getString(PM25ys);
        String O3y = jsonObject4.getString("O3y");
        String O3ys = jsonObject4.getString("O3ys");
        String code5 = getString(O3ys);
        String AQIy = jsonObject4.getString("AQIy");
        String AQIys = jsonObject4.getString("AQIys");
        String code6 = getString(AQIys);

        StringBuilder sb1 = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();
        StringBuilder sb3 = new StringBuilder();
        StringBuilder sb4 = new StringBuilder();
        StringBuilder sb5 = new StringBuilder();
        StringBuilder sb6 = new StringBuilder();
        Set<String> trims = map1.keySet();
        for (String trim : trims) {
            String o = (String) map1.get(trim);
            String[] split = o.split("、");
            if (Double.parseDouble(split[0])>Double.parseDouble(PM25m)){
                sb1.append(trim.substring(3)+"、");
            }
            if (Double.parseDouble(split[1])>Double.parseDouble(O3m)){
                sb2.append(trim.substring(3)+"、");
            }
            if (Double.parseDouble(split[2])<Double.parseDouble(AQIm)){
                sb3.append(trim.substring(3)+"、");
            }
            if (Double.parseDouble(split[3])>Double.parseDouble(PM25y)){
                sb4.append(trim.substring(3)+"、");
            }
            if (Double.parseDouble(split[4])>Double.parseDouble(O3y)){
                sb5.append(trim.substring(3)+"、");
            }
            if (Double.parseDouble(split[5])<Double.parseDouble(AQIy)){
                sb6.append(trim.substring(3)+"、");
            }
        }
        String builder1 = getBuilder(sb1);
        String builder2 = getBuilder(sb2);
        String builder3 = getBuilder(sb3);
        String builder4 = getBuilder(sb4);
        String builder5 = getBuilder(sb5);
        String builder6 = getBuilder(sb6);
        excelVo.setName1(builder1);
        excelVo.setName2(builder2);
        excelVo.setName3(builder3);
        excelVo.setName4(builder4);
        excelVo.setName5(builder5);
        excelVo.setName6(builder6);

        //第五个文件
        String c5 = jsonObject.getString("c5");
        JSONObject jsonObject5 = JSON.parseObject(c5);
        String cxq = jsonObject5.getString("彩香");
        JSONObject jsonObject6 = JSON.parseObject(cxq);
        String cxp = jsonObject6.getString("PM2_5y");
        double CX = 0.0;
        if (!cxp.equals("--")){
           CX = Double.parseDouble(cxp);
        }
        double aDouble = 0.0;
        if (!PM25y.equals("--")){
             aDouble = Double.parseDouble(PM25y);
        }
        double v = aDouble - CX;
        if (v>=0){
            excelVo.setDiff1("高于");
            excelVo.setCode1(String.valueOf(new BigDecimal(v).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue()));
        }else {
            excelVo.setDiff1("低于");
            excelVo.setCode1(String.valueOf(new BigDecimal(Math.abs(v)).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue()));
        }
        String xcq = jsonObject5.getString("相城区");
        JSONObject jsonObject7 = JSON.parseObject(xcq);
        String xcp = jsonObject7.getString("PM2_5y");
         double XCP = 0.0;
        if (!xcp.equals("--")){
            XCP = Double.parseDouble(xcp);
        }
        double v1 = aDouble - XCP;
        if (v1>=0){
            excelVo.setDiff2("高于");
            excelVo.setCode2(String.valueOf(new BigDecimal(v1).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue()));
        }else {
            excelVo.setDiff2("低于");
            excelVo.setCode2(String.valueOf(new BigDecimal(Math.abs(v1)).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue()));

        }



        //第一个文件
        String c1 = jsonObject.getString("c1");
        JSONObject jsonObject1 = JSON.parseObject(c1);
        //高新区
        String cx = jsonObject1.getString("高新区");
        JSONObject jsonObject2 = JSON.parseObject(cx);
        String reTime = jsonObject2.getString("日期");
        Date reDate = DateUtils.getDate(reTime, "yyyy-MM-dd");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日");
        String format = simpleDateFormat.format(reDate);
        String substring = format.substring(5);
        String CO = jsonObject2.getString("CO");
        String NO2 = jsonObject2.getString("NO2");
        String SO2 = jsonObject2.getString("SO2");
        String PM10 = jsonObject2.getString("PM10");
        String PM25 = jsonObject2.getString("PM2_5");
        String s1 = jsonObject2.getString("SU");
        String O8 = jsonObject2.getString("O3_8H");
        String s3 = jsonObject2.getString("空气质量类别");
        String s4 = jsonObject2.getString("空气质量级别");
        int count = 0;
        if (Double.parseDouble(PM10)<150){
            count++;
        }
        if (Double.parseDouble(PM25)<75){
            count++;
        }
        if (Double.parseDouble(SO2)<150){
            count++;
        }
        if (Double.parseDouble(NO2)<80){
            count++;
        }
        if (Double.parseDouble(CO)<4){
            count++;
        }
        if (Double.parseDouble(O8)<160){
            count++;
        }



//        String res =  substring+"，我区空气质量为"+s3+s4+"，首要污染物为："+s1+"。从空气6因子看，" +
//                 count+"项达标：PM10为"+PM10+"ug/m³（标准值150μg/m³);PM2.5为"+PM25+"μg/m³(标准值75μg/m³);" +
//                 "SO2为"+SO2+"μg/m³(标准值150μg/m³);NO2为"+NO2+"μg/m³(标准值80μg/m³);CO为"+CO+"mg/m³" +
//                 "(标准值4mg/m³);O3-8H为"+O8+"μg/m³(标准值160μg/m³）。";
//        String month ="我区月度（9月1日-13日）PM2.5浓度为"+PM25m+"微克/立方米，同比"+code1+"%。" +
//                "臭氧8小时滑动平均值90百分位数浓度为"+O3m+"微克/立方米，同比"+code2+"%。" +
//                "空气质量优良天数比例为"+AQIm+"%，同比"+code3+"%。";
//        String  years= "今年以来（01月1日-"+substring+"），我区PM2.5浓度为"+PM25y+"微克/立方米，同比"+code4+"%。" +
//                "臭氧8小时滑动平均值90百分位数浓度为"+O3y+"微克/立方米，同比"+code5+"%。" +
//                "空气质量优良天数比例为"+AQIy+"%，同比"+code6+"%。";

        excelVo.setQuality(s4+s3);
        excelVo.setSu(s1);
        excelVo.setCount(count);
        excelVo.setPM10(PM10);
        excelVo.setPM25(PM25);
        excelVo.setSO2(SO2);
        excelVo.setNO2(NO2);
        excelVo.setCO(CO);
        excelVo.setO3_8H(O8);
        excelVo.setPM25m(PM25m);
        excelVo.setPM25ms(code1);
        excelVo.setO3m(O3m);
        excelVo.setO3ms(code2);
        excelVo.setAQIm(AQIm);
        excelVo.setAQIms(code3);
        excelVo.setPM25y(PM25y);
        excelVo.setPM25ys(code4);
        excelVo.setO3y(O3y);
        excelVo.setO3ys(code5);
        excelVo.setAQIy(AQIy);
        excelVo.setAQIys(code6);
        excelVo.setTime1(format);
        excelVo.setTime(substring);

        excelVo.setList1(list1);
        excelVo.setList2(list2);
        excelVo.setList3(list3);
        excelVo.setList4(list4);
        excelVo.setList5(list5);

        return excelVo;
    }

    /**
     * 查询
     * @param startTime
     * @param code
     * @return
     */
    @Override
    public List<ExcelBO> excelSelect(String startTime, String code,String endTime) {
        ArrayList<ExcelBO> excelBOS = new ArrayList<>();
        QueryWrapper<TbExcel> wrapper = new QueryWrapper<>();
        wrapper.between("time",startTime,endTime);
        wrapper.eq("code",code);
        List<TbExcel> tbExcels = excelMapper.selectList(wrapper);
        if (tbExcels==null){
            return null;
        }
        for (TbExcel tbExcel : tbExcels) {
            ExcelBO excelBO = new ExcelBO();
            BeanUtils.copyProperties(tbExcel,excelBO);
            excelBOS.add(excelBO);
        }

        return excelBOS;
    }


    private String getBuilder(StringBuilder sb) {
        if (sb.length() > 0 && sb.charAt(sb.length() - 1) == '、') {
            sb.deleteCharAt(sb.length() -1 );
        }else {
            sb.append("无");
        }
        String s = sb.toString();
        return s;
    }

    private String getString(String value) {
        String code =null;
        double aDouble = 0.0;
        if (!value.equals("--")){
             aDouble = Double.parseDouble(value);
        }

        if (aDouble>=0){
            code = "上升"+ value;
        }else {
            String s = value.substring(1);
            code = "下降"+ s;
        }
        return code;
    }


    private Map<String, Object> getMap4(MultipartFile file) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        HashMap<String, Object> rsMap = new HashMap<>();
        XSSFSheet sheetAt = workbook.getSheetAt(0);
        //一共有多少行
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 5; i <= lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum < 10) {
                continue;
            }
            Object[] objects = new Object[lastCellNum];
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);

                Object value = ExcelUtils.getValue(cell);
                objects[j] = value;
            }
            HashMap<String, Object> map = new HashMap<>();
            map.put("place", objects[0]);
            map.put("PM2_5d", objects[1]);
            map.put("PM2_5m", objects[2]);
            map.put("PM2_5y", objects[3]);
            map.put("PM2_5r", objects[4]);
            map.put("PM2_5ms", objects[5]);
            map.put("PM2_5ys", objects[6]);
            map.put("PM2_5rs", objects[7]);
            map.put("AQId", objects[8]);
            map.put("AQIm", objects[9]);
            map.put("AQIy", objects[10]);
            map.put("AQIr", objects[11]);
            map.put("AQIms", objects[12]);
            map.put("AQIys", objects[13]);
            map.put("AQIrs", objects[14]);
            map.put("O3d", objects[15]);
            map.put("O3m", objects[16]);
            map.put("O3y", objects[17]);
            map.put("O3r", objects[18]);
            map.put("O3ms", objects[19]);
            map.put("O3ys", objects[20]);
            map.put("O3rs", objects[21]);
            rsMap.put(objects[0].toString(), map);
        }
        return rsMap;
    }
    private Map<String, Object> getMap1(MultipartFile file) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        HashMap<String, Object> rsMap = new HashMap<>();
        XSSFSheet sheetAt = workbook.getSheetAt(0);
        //一共有多少行
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 3; i <= lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum < 10) {
                continue;
            }
            Object[] objects = new Object[lastCellNum];
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                Object value = ExcelUtils.getValue(cell);
                objects[j] = value;
            }
            HashMap<String, Object> map = new HashMap<>();
            map.put("place", objects[0]);
            map.put("日期", objects[1]);
            map.put("SO2", objects[2]);
            map.put("SO2分指数", objects[3]);
            map.put("NO2", objects[4]);
            map.put("NO2分指数", objects[5]);
            map.put("PM10", objects[6]);
            map.put("PM10分指数", objects[7]);
            map.put("CO", objects[8]);
            map.put("CO分指数", objects[9]);
            map.put("O3_1h", objects[10]);
            map.put("O3一小时分指数", objects[11]);
            map.put("O3_8H", objects[12]);
            map.put("O3八小时分指数", objects[13]);
            map.put("PM2_5", objects[14]);
            map.put("PM2_5分指数", objects[15]);
            map.put("AQI", objects[16]);
            map.put("SU", objects[17]);
            map.put("空气质量级别", objects[18]);
            map.put("空气质量类别", objects[19]);

            rsMap.put(objects[0].toString(), map);
        }
        return rsMap;
    }


    private Map<String, Object> getMap2(MultipartFile file) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        HashMap<String, Object> rsMap = new HashMap<>();
        XSSFSheet sheetAt = workbook.getSheetAt(0);
        //一共有多少行
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 3; i <= lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum < 10) {
                continue;
            }
            Object[] objects = new Object[lastCellNum];
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                Object value = ExcelUtils.getValue(cell);
                objects[j] = value;
            }
            HashMap<String, Object> map = new HashMap<>();
            if (objects[0].toString().equals("高新区")){
                map.put("place", "国控站");
            }else {
                map.put("place",objects[0].toString().substring(3));
            }
            map.put("日期", objects[1]);
            map.put("SO2", objects[2]);
            map.put("SO2分指数", objects[3]);
            map.put("NO2", objects[4]);
            map.put("NO2分指数", objects[5]);
            map.put("PM10", objects[6]);
            map.put("PM10分指数", objects[7]);
            map.put("CO", objects[8]);
            map.put("CO分指数", objects[9]);
            map.put("O3_1h", objects[10]);
            map.put("O3一小时分指数", objects[11]);
            map.put("O3_8H", objects[12]);
            map.put("O3八小时分指数", objects[13]);
            map.put("PM2_5", objects[14]);
            map.put("PM2_5分指数", objects[15]);
            map.put("AQI", objects[16]);
            map.put("SU", objects[17]);
            map.put("空气质量级别", objects[18]);
            map.put("空气质量类别", objects[19]);

            rsMap.put(objects[0].toString(), map);
        }
        return rsMap;
    }

    private Map<String, Object> getMap3(MultipartFile file) throws IOException {
        
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        HashMap<String, List<Double>> rsMap = new HashMap<>();
        HashMap<String, Map<String, List<Double>>> maps = new HashMap<>();
        HashMap<String, List<Double>> map1 = new HashMap<>();
        HashMap<String, List<Double>> map2 = new HashMap<>();



        XSSFSheet sheetAt = workbook.getSheetAt(0);
        //一共有多少行
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 3; i <= lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum < 10) {
                continue;
            }
            Object[] objects = new Object[lastCellNum];
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                Object value = ExcelUtils.getValue(cell);
                objects[j] = value;
            }


            //CO
            double CO=0.0;
            if (!(objects[8].toString()).equals("--")){
                CO = Double.parseDouble(objects[8].toString());
            }

            //o3
            double O3=0.0;
            if (!(objects[12].toString()).equals("--")){
               O3 = Double.parseDouble(objects[12].toString());
            }





            if (map1.get(objects[0].toString())==null){
                ArrayList<Double> doubles1 = new ArrayList<>();
                ArrayList<Double> doubles2 = new ArrayList<>();
                doubles1.add(CO);
                doubles2.add(O3);
                map1.put(objects[0].toString(),doubles1);
                map2.put(objects[0].toString(),doubles2);
            }else {
                List<Double> doubles = map1.get(objects[0].toString());
                doubles.add(CO);
                List<Double> lists = map2.get(objects[0].toString());
                lists.add(O3);
            }

            if (maps.get(objects[0].toString())==null){
                ArrayList<Double> list1 = new ArrayList<>();
                ArrayList<Double> list2 = new ArrayList<>();
                ArrayList<Double> list4 = new ArrayList<>();
                ArrayList<Double> list3 = new ArrayList<>();
                HashMap<String, List<Double>> ListHashMap = new HashMap<>();
                if (!(objects[2].toString()).equals("--")){
                    double SO21 = Double.parseDouble(objects[2].toString());
                    list1.add(SO21);
                }
                if (!(objects[4].toString()).equals("--")){
                    double NO21 = Double.parseDouble(objects[4].toString());
                    list2.add(NO21);
                }
                if (!(objects[6].toString()).equals("--")){
                  double  PM101 = Double.parseDouble(objects[6].toString());
                  list3.add(PM101);
                }
                if (!(objects[14].toString()).equals("--")){
                  double PM2_51 = Double.parseDouble(objects[14].toString());
                    list4.add(PM2_51);
               }

                ListHashMap.put("SO2",list1);
                ListHashMap.put("NO2",list2);
                ListHashMap.put("PM10",list3);
                ListHashMap.put("PM2_5",list4);
                maps.put(objects[0].toString(),ListHashMap);

            }else {
                Map<String, List<Double>> stringListMap = maps.get(objects[0].toString());
                List<Double> SO2List = stringListMap.get("SO2");
                if (!(objects[2].toString()).equals("--")){
                    double SO21 = Double.parseDouble(objects[2].toString());
                    SO2List.add(SO21);
                }

                List<Double> NO2List = stringListMap.get("NO2");
                if (!(objects[4].toString()).equals("--")){
                    double NO21 = Double.parseDouble(objects[4].toString());
                    NO2List.add(NO21);
                }

                List<Double> PM10List = stringListMap.get("PM10");
                if (!(objects[6].toString()).equals("--")){
                    double  PM101 = Double.parseDouble(objects[6].toString());
                    PM10List.add(PM101);
                }

                List<Double> PM2_5List = stringListMap.get("PM2_5");
                if (!(objects[14].toString()).equals("--")){
                    double PM2_51 = Double.parseDouble(objects[14].toString());
                    PM2_5List.add(PM2_51);
                }

            }
        }
        Set<String> strings2 = maps.keySet();
        for (String s : strings2) {
            Map<String, List<Double>> stringListMap = maps.get(s);
            ArrayList<Double> list = new ArrayList<>();
            Double so2 = getYz(stringListMap, "SO2");
            Double no2 = getYz(stringListMap, "NO2");
            Double pm10 = getYz(stringListMap, "PM10");
            Double pm2_5 = getYz(stringListMap, "PM2_5");
            list.add(so2);
            list.add(no2);
            list.add(pm10);
            list.add(pm2_5);
            rsMap.put(s,list);
        }

        HashMap<String, List<Double>> ListHashMap = new HashMap<>();
        Set<String> strings = rsMap.keySet();
        for (String string : strings) {
            List<Double> list = getList(rsMap, map1, map2, string);
            ListHashMap.put(string,list);
        }
        //计算aqi和首要污染物
        HashMap<String, Object> resultMap = new HashMap<>();
        Set<String> strings1 = ListHashMap.keySet();
        for (String s : strings1) {
            HashMap<String, Object> map = new HashMap<>();
            List<Double> doubles = ListHashMap.get(s);

            ArrayList<Double> list = new ArrayList<>();
            double co = getCo(doubles.get(0));
            double so2 = getSo2(doubles.get(1));
            double no2 = getNo2(doubles.get(2));
            double pm10 = getPm10(doubles.get(3));
            double pm2_5 = getPm2_5(doubles.get(4));
            double o3 = getO3(doubles.get(5));
            list.add(co);
            list.add(so2);
            list.add(no2);
            list.add(pm10);
            list.add(pm2_5);
            list.add(o3);

            Double aqi = Collections.max(list);

            map.put("a21005",doubles.get(0));
            map.put("a21026",doubles.get(1));
            map.put("a21004",doubles.get(2));
            map.put("a34002",doubles.get(3));
            map.put("a34004",doubles.get(4));
            map.put("a05024",doubles.get(5));
            map.put("AQI",aqi);
            map.put("SU", getSU(aqi,list));
            resultMap.put(s,map);
        }
        return resultMap;
    }



    /**
     * 计算平均值
     * @param stringListMap
     */
    private Double getYz(Map<String, List<Double>> stringListMap,String code) {
        List<Double> doubles = stringListMap.get(code);
        double v = new BigDecimal(doubles.stream().collect(Collectors.averagingDouble(Double::doubleValue))).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
        return v;
    }

    /**
     * 计算月累计参数
     * @param rsMap
     * @param map1
     * @param map2
     * @param code
     * @return
     */
    private List<Double> getList(HashMap<String, List<Double>> rsMap, HashMap<String, List<Double>> map1, HashMap<String, List<Double>> map2,String code) {
        ArrayList<Double> list = new ArrayList<>();
        List<Double> doubles1 = map1.get(code);
        double percentiles1 = getPercentiles(doubles1, 0.95);
        double co = getDouble(percentiles1);
        list.add(co);
        List<Double> doubles2 = map2.get(code);
        double percentiles2 = getPercentiles(doubles2, 0.90);
        List<Double> doubles3 = rsMap.get(code);
        for (int i = 0; i < doubles3.size(); i++) {
           double anInt = getInt(doubles3.get(i));
            list.add(anInt);
        }
        double anInt = getInt(percentiles2);
        list.add(anInt);
        return list;
    }

    private Map<String, Object> getMap5(MultipartFile file) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        HashMap<String, Object> rsMap = new HashMap<>();
        XSSFSheet sheetAt = workbook.getSheetAt(0);
        //一共有多少行
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 5; i <= lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            if (lastCellNum < 10) {
                continue;
            }
            Object[] objects = new Object[lastCellNum];
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                Object value = ExcelUtils.getValue(cell);
                objects[j] = value;
            }
            HashMap<String, Object> map = new HashMap<>();
            map.put("place", objects[0]);
            map.put("PM2_5d", objects[1]);
            map.put("PM2_5m", objects[2]);
            map.put("PM2_5y", objects[3]);
            map.put("PM2_5r", objects[4]);
            map.put("PM2_5ms", objects[5]);
            map.put("PM2_5ys", objects[6]);
            map.put("PM2_5rs", objects[7]);
            map.put("AQId", objects[8]);
            map.put("AQIm", objects[9]);
            map.put("AQIy", objects[10]);
            map.put("AQIr", objects[11]);
            map.put("AQIms", objects[12]);
            map.put("AQIys", objects[13]);
            map.put("AQIrs", objects[14]);
            map.put("O3d", objects[15]);
            map.put("O3m", objects[16]);
            map.put("O3y", objects[17]);
            map.put("O3r", objects[18]);
            map.put("O3ms", objects[19]);
            map.put("O3ys", objects[20]);
            map.put("O3rs", objects[21]);
            rsMap.put(objects[0].toString(), map);
        }
        return rsMap;
    }


    private Map<String, Object> getMap6(MultipartFile file) throws IOException {
//        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        HSSFWorkbook workbook= new HSSFWorkbook(file.getInputStream());
        HashMap<String, Object> rsMap = new HashMap<>();
//        XSSFSheet sheetAt = workbook.getSheetAt(0);
        HSSFSheet sheetAt = workbook.getSheetAt(0);
        //一共有多少行
        int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 1; i <= lastRowNum; i++) {
//            XSSFRow row = sheetAt.getRow(i);
            HSSFRow row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();

            Object[] objects = new Object[lastCellNum];
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                Object value = ExcelUtils.getValue(cell);
                objects[j] = value;
            }
            HashMap<String, Object> map = new HashMap<>();
            map.put("企业名称", objects[0]);
            map.put("设备名称", objects[1]);
            map.put("合计", objects[2]);
            map.put("油烟", objects[3]);
            map.put("颗粒物", objects[4]);
            map.put("设备离线", objects[5]);

            rsMap.put(objects[0].toString()+i, map);
        }
        return rsMap;
    }

    /**
     * excel计算公式
     * @param list
     * @param percentile
     * @return
     */
    private static double getPercentiles(List<Double> list, double percentile) {
        Collections.sort(list);
        double x = (list.size()-1) * percentile;
        int i = (int) x;
        double j = x - i;
        return (1 - j) * list.get(i) + j * list.get(i+1);
    }


    private  static double  getDouble(Double  code1){
        double floor1 = Math.floor(code1 * 10);
        //保留一位小数四舍五入
        double result = new BigDecimal(code1).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue();


        if (code1*100-floor1*10==5 && (result*10) % 2 !=0){
            result= result-0.1;
        }else {
            result = result-0;
        }

        return result;
    }



    private  static double  getInt(Double  code){
        //向下取整数
        double floor = Math.floor(code);
        //四舍五入
        double round = Math.round(code);


        if (code*10-(floor*10)==5  && round % 2 !=0){
            round=round-1;
        }else {
            round=round-0;
        }
        return  round;
    }
    /**
     * 计算搜要污染物
     * @param aqi
     * @param list
     * @return
     */
    private String getSU(Double aqi,ArrayList list){
        String SU=null;
        if (aqi<=50){
            SU="-";
        } else if (aqi==list.get(1)){
            SU="SO2";
        }else if (aqi==list.get(0)){
            SU="CO";
        }else if (aqi==list.get(2)){
            SU="NO2";
        }else if (aqi==list.get(3)){
            SU="PM10";
        }else if (aqi==list.get(4)){
            SU="PM2.5";
        }else if (aqi==list.get(5)){
            SU="O3";
        }
        return SU;
    }

    private  static  double getSo2(double so2){
        double v = 0.0;
        if (so2<50){
            v = 50 * so2 / 50;
        }else if (so2<150  && so2 >= 50){
            v = 50 * (so2 - 50) / 100 + 50;
        }else if (so2<475  && so2 >= 150){
            v = 50 * (so2 - 150) / 325 + 100;
        }else if (so2<800  && so2 >= 475){
            v = 50 * (so2 - 475) / 325 + 150;
        }else {
            v = 100 * (so2 - 800) / 800 + 200;
        }
        return Math.round(v);
    }

    private  static  double getNo2(double no2){
        double v = 0.0;
        if (no2<40){
            v = 50 * no2 / 40;
        }else if (no2<80  && no2 >= 40){
            v = 50 * (no2 - 40) / 40 + 50;
        }else if (no2<180  && no2 >= 80){
            v = 50 * (no2 - 80) / 100 + 100;
        }else if (no2<280  && no2 >= 180){
            v = 50 * (no2 - 180) / 100 + 150;
        }else if (no2<565  && no2 >= 280){
            v = 100 * (no2 - 280) / 285 + 200;
        }else if (no2<750  && no2 >= 565){
            v = 100 * (no2 - 565) / 185 + 300;
        }
        else {
            v = 100 * (no2 - 750) / 190 + 400;
        }
        return Math.round(v);
    }


    private  static  double getCo(double co){
        double v = 0.0;
        if (co<2){
            v = 50 * co / 2;
        }else if (co<4  && co >= 2){
            v = 50 * (co - 2) / 2 + 50;
        }else if (co<14  && co >= 4){
            v = 50 * (co - 4) / 10 + 100;
        }else if (co<24  && co >= 14){
            v = 50 * (co - 14) / 10 + 150;
        }else if (co<36  && co >= 24){
            v = 100 * (co - 24) / 12 + 200;
        }else if (co<48  && co >= 36){
            v = 100 * (co - 36) / 12 + 300;
        }
        else {
            v = 100 * (co - 48) / 12 + 400;
        }
        return Math.round(v);
    }

    private  static  double getPm10(double pm10){
        double v = 0.0;
        if (pm10<50){
            v = 50 * pm10 / 50;
        }else if (pm10<150  && pm10 >= 50){
            v = 50 * (pm10 - 50) / 100 + 50;
        }else if (pm10<250  && pm10 >= 150){
            v = 50 * (pm10 - 150) / 100 + 100;
        }else if (pm10<350  && pm10 >= 250){
            v = 50 * (pm10 - 250) / 100 + 150;
        }else if (pm10<420  && pm10 >= 350){
            v = 100 * (pm10 - 350) / 70 + 200;
        }else if (pm10<500  && pm10 >= 420){
            v = 100 * (pm10 - 420) / 80 + 300;
        }
        else {
            v = 100 * (pm10 - 500) / 100 + 400;
        }
        return Math.round(v);
    }

    private  static  double getPm2_5(double pm2_5){
        double v = 0.0;
        if (pm2_5<35){
            v = 50 * pm2_5 / 35;
        }else if (pm2_5<75  && pm2_5 >= 35){
            v = 50 * (pm2_5 - 35) / 40 + 50;
        }else if (pm2_5<115  && pm2_5 >= 75){
            v = 50 * (pm2_5 - 75) / 40 + 100;
        }else if (pm2_5<150  && pm2_5 >= 115){
            v = 50 * (pm2_5 - 115) / 35 + 150;
        }else if (pm2_5<250  && pm2_5 >= 150){
            v = 100 * (pm2_5 - 150) / 100 + 200;
        }else if (pm2_5<350  && pm2_5 >= 250){
            v = 100 * (pm2_5 - 250) / 100 + 300;
        }
        else {
            v = 100 * (pm2_5 - 350) / 150 + 400;
        }
        return Math.round(v);
    }


    private  static  double getO3(double o3){
        double v = 0.0;
        if (o3<100){
            v = 50 * o3 / 100;
        }else if (o3<160  && o3 >= 100){
            v = 50 * (o3 - 100) / 60 + 50;
        }else if (o3<215  && o3 >= 160){
            v = 50 * (o3 - 160) / 55 + 100;
        }else if (o3<265  && o3 >= 215){
            v = 50 * (o3 - 215) / 50 + 150;
        }else if (o3<800  && o3 >= 265){
            v = 100 * (o3 - 265) / 535 + 200;
        }else if (o3<1000  && o3 >= 800){
            v = 100 * (o3 - 800) / 200 + 300;
        }
        return Math.round(v);
    }
}