update excel多个工作表导出

ZhuDongming

2020-04-26 dba54bf8c8f9299d61c05bf3a941708ea399c998

 src/main/java/com/moral/service/impl/HistoryMinutelyServiceImpl.java

@@ -1,13 +1,7 @@
package com.moral.service.impl;



import static com.moral.common.bean.Constants.NULL_VALUE;

import static org.springframework.util.ObjectUtils.isEmpty;



import java.io.FileNotFoundException;

import java.io.IOException;

import java.math.BigDecimal;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.time.format.DateTimeFormatter;

@@ -46,7 +40,6 @@
import com.moral.common.util.ParameterUtils;

import com.moral.common.util.ReportTimeFormat;

import com.moral.common.util.ResourceUtil;

import com.moral.common.util.StringUtils;

import com.moral.common.util.ValidateUtil;

import com.moral.entity.Sensor;

import com.moral.entity.charts.DataCondition;

@@ -58,9 +51,9 @@
import com.moral.mapper.HistoryMinutelyMapper;

import com.moral.mapper.SensorMapper;

import com.moral.service.HistoryMinutelyService;

import com.moral.service.SensorService;



import lombok.val;

import static com.moral.common.bean.Constants.NULL_VALUE;

import static org.springframework.util.ObjectUtils.isEmpty;



@Service

@SuppressWarnings({"unchecked", "unused", "rawtypes"})

@@ -84,12 +77,26 @@
    @Override

    public Map<String, Object> getDayAQIByDevice(Map<String, Object> parameters) {

        //ValidateUtil.notNull(parameters.get("mac"), "param.is.null");

        LocalDate localDate = LocalDate.now();

        LocalDate time = LocalDate.now();

        int year = time.getYear();

        int month = time.getMonthValue();

        int day = time.getDayOfMonth();

        if (day == 1) {

            if (month == 1) {

                month = 12;

                year = year - 1;

            } else {

                month = month - 1;

            }

        }

        String monthStr = month < 10 ? ("0" + month) : month + "";

        String yearAndMonth = year + monthStr;

        // 昨日00:00:00

        parameters.put("start", localDate.minusDays(1));

        parameters.put("start", time.minusDays(1));



        // 今日00:00:00

        parameters.put("end", localDate);

        parameters.put("end", time);

        parameters.put("yearAndMonth", yearAndMonth);

        parameters.put("sensorKeys", Arrays.asList("e1", "e2", "e10", "e11", "e15", "e16"));

        Map<String, Double> average = historyMinutelyMapper.getSersionAvgByDevice(parameters);

        return getAQIByDataMap(average);

@@ -99,16 +106,13 @@
    public Map<String, Object> getHourAQIByDevice(Map<String, Object> parameters) {

        //ValidateUtil.notNull(parameters.get("mac"), "param.is.null");

        LocalDate localDate = LocalDate.now();

        System.out.println("localDate:" + localDate);

        // 昨日00:00:00

        //parameters.put("start", localDate.minusDays(1));



        Calendar calendar = Calendar.getInstance();

        calendar.setTime(new Date());

        calendar.set(Calendar.HOUR, calendar.get(Calendar.HOUR) - 1);

        System.out.println("++++++" + calendar);

        int hour = calendar.get(Calendar.HOUR) - 1;

        System.out.println("当前小时减一" + hour);



        // 今日00:00:00

        parameters.put("end", localDate);

@@ -544,46 +548,130 @@
     * @return

     */

    @Override

    public List<Map<String, Object>> getSensorData(Map<String, Object> parameters) {

        String startTime=parameters.get("startTime").toString();

        Boolean flag=null;

        try {

            flag = DateUtils.isSameDay(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(startTime), new Date());

        }catch (ParseException e) {

            e.printStackTrace();

        }

        List<Sensor> sensors=sensorMapper.getSensorsByMac(parameters);

        List<String> sensorKeys=new ArrayList<>();

        for(Sensor sensor:sensors){

    public List<List<Map<String, Object>>> getSensorData(Map<String, Object> parameters) {

        String startTime = parameters.get("startTime").toString();

        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        LocalDateTime startTimeLocalDateTime = LocalDateTime.parse(startTime, dateTimeFormatter);

        int year = startTimeLocalDateTime.getYear();

        int month = startTimeLocalDateTime.getMonthValue();

        String monthStr = month < 10 ? ("0" + month) : month + "";

        String yearAndMonth = year + monthStr;

        List<Sensor> sensors = sensorMapper.getSensorsByMac(parameters);

        List<String> sensorKeys = new ArrayList<>();

        for (Sensor sensor : sensors) {

            sensorKeys.add(sensor.getSensorKey());

        }

        parameters.put("sensorKeys", sensorKeys);

        List<Map<String, Object>> listMap=null;

        if(flag){

            listMap = historyMapper.getSensorDataToday(parameters);

        }else{

        List<Map<String, Object>> listMap = null;

        if (year <= 2019) {

            listMap = historyMinutelyMapper.getSensorData(parameters);

        } else {

            parameters.put("yearAndMonth", yearAndMonth);

            listMap = historyMinutelyMapper.getSensorData2020(parameters);

        }

        List<Map<String, Object>> listMapNew = new ArrayList<>();

        Map<String, Object> new_map = new LinkedHashMap<>();

        List<List<Map<String, Object>>> listMaps = new ArrayList<>();

        List<Map<String, Object>> listMapAvg = new ArrayList<>();

        List<Map<String, Object>> listMapMin = new ArrayList<>();

        List<Map<String, Object>> listMapMax = new ArrayList<>();

        if (CollectionUtils.isNotEmpty(listMap)) {

            for (Map<String, Object> map : listMap) {

                new_map.put("时间", map.get("time"));

                for (String key : map.keySet()) {

                Map<String, Object> mapAvg = new LinkedHashMap<>();

                Map<String, Object> mapMin = new LinkedHashMap<>();

                Map<String, Object> mapMax = new LinkedHashMap<>();

                mapAvg.put("time", map.get("time"));

                mapMin.put("time", map.get("time"));

                mapMax.put("time", map.get("time"));

                for (Entry<String, Object> entry : map.entrySet()) {

                    for (Sensor sensor : sensors) {

                        if (sensor.getSensorKey().equals(key)) {

                            if("e79".equals(key)||"e80".equals(key)||"e81".equals(key)){

                                new_map.put(sensor.getDescription(), StringUtils.scientificCountingConversion(map.get(key).toString())+sensor.getUnit());

                            }else{

                                new_map.put(sensor.getDescription(), map.get(key)+sensor.getUnit());

                            }

                        if (sensor.getSensorKey().equals(entry.getKey())) {

                            mapAvg.put(entry.getKey(), new BigDecimal(entry.getValue().toString()).stripTrailingZeros().toPlainString() + sensor.getUnit());

                        } else if (("min" + sensor.getSensorKey()).equals(entry.getKey())) {

                            mapMin.put(entry.getKey().substring(3), new BigDecimal(entry.getValue().toString().replace("\"", "")).stripTrailingZeros().toPlainString());

                        } else if (("max" + sensor.getSensorKey()).equals(entry.getKey())) {

                            mapMax.put(entry.getKey().substring(3), new BigDecimal(entry.getValue().toString().replace("\"", "")).stripTrailingZeros().toPlainString());

                        }

                    }

                }

                listMapNew.add(new_map);

                new_map = new LinkedHashMap<>();

                if ("0°".equals(mapAvg.get("e76")) || "0".equals(mapMin.get("e76")) || "0".equals(mapMax.get("e76")) || "0°".equals(mapAvg.get("e77")) || "0".equals(mapMin.get("e77")) || "0".equals(mapMax.get("e77"))) {

                    continue;

                }

                listMapAvg.add(mapAvg);

                listMapMin.add(mapMin);

                listMapMax.add(mapMax);

            }

            listMaps.add(listMapAvg);

            listMaps.add(listMapMin);

            listMaps.add(listMapMax);

        }

        return listMapNew;

        return listMaps;

    }



    @Override

    public List<Map<String, Object>> getDevicesAvgDataToExcel(Map<String, Object> parameters) throws Exception {

        if ("month".equals(parameters.get("type"))) {

            parameters.put("timeUnits", "daily");

            parameters.put("typeFormat", "%Y-%m-%d");

            String time = parameters.get("time") + "-01T00:00:00";

            LocalDateTime value = LocalDateTime.parse(time);

            LocalDateTime start = value.with(TemporalAdjusters.firstDayOfMonth());

            LocalDateTime end = value.with(TemporalAdjusters.lastDayOfMonth());

            parameters.put("start", start);

            parameters.put("end", end);

            int day = end.getDayOfMonth();

            List<String> timeList = new ArrayList<>();

            for (int i = 0; i <= day - 1; i++) {

                timeList.add(start.plusDays(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));

            }

            parameters.put("timeList", timeList);

        } else if ("day".equals(parameters.get("type"))) {

            String time = parameters.get("time") + "T00:00:00";

            LocalDateTime value = LocalDateTime.parse(time);

            LocalDateTime end = value.plusHours(23);

            parameters.put("timeUnits", "hourly");

            parameters.put("typeFormat", "%Y-%m-%d %H:%i");

            parameters.put("start", time);

            parameters.put("end", end);

            List<String> timeList = new ArrayList<>();

            for (int i = 0; i <= 23; i++) {

                timeList.add(value.plusHours(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm")));

            }

            parameters.put("timeList", timeList);

        }

        return historyMinutelyMapper.getDevicesAvgDataToExcel(parameters);

    }



    @Override

    public List<Map<String, Object>> getDevicesSensorsAvgDataToExcel(Map<String, Object> parameters) throws Exception {

        if ("month".equals(parameters.get("type"))) {

            parameters.put("timeUnits", "daily");

            parameters.put("typeFormat", "%Y-%m-%d");

            String time = parameters.get("time") + "-01T00:00:00";

            LocalDateTime value = LocalDateTime.parse(time);

            LocalDateTime start = value.with(TemporalAdjusters.firstDayOfMonth());

            LocalDateTime end = value.with(TemporalAdjusters.lastDayOfMonth());

            parameters.put("start", start);

            parameters.put("end", end);

            int day = end.getDayOfMonth();

            List<String> timeList = new ArrayList<>();

            for (int i = 0; i <= day - 1; i++) {

                timeList.add(start.plusDays(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));

            }

            parameters.put("timeList", timeList);

        } else if ("day".equals(parameters.get("type"))) {

            String time = parameters.get("time") + "T00:00:00";

            LocalDateTime value = LocalDateTime.parse(time);

            LocalDateTime end = value.plusHours(23);

            parameters.put("timeUnits", "hourly");

            parameters.put("typeFormat", "%Y-%m-%d %H:%i");

            parameters.put("start", time);

            parameters.put("end", end);

            List<String> timeList = new ArrayList<>();

            for (int i = 0; i <= 23; i++) {

                timeList.add(value.plusHours(i).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm")));

            }

            parameters.put("timeList", timeList);

        }

        return historyMinutelyMapper.getDevicesSensorsAvgDataToExcel(parameters);

    }



}