update excel多个工作表导出

ZhuDongming

2020-04-26 dba54bf8c8f9299d61c05bf3a941708ea399c998

 src/main/java/com/moral/controller/ScreenController.java

@@ -20,6 +20,9 @@
import java.util.Optional;

import java.util.stream.Collectors;



import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;



import org.apache.commons.collections.CollectionUtils;

import org.apache.commons.collections.MapUtils;

import org.apache.commons.net.ftp.FTPClient;

@@ -99,9 +102,6 @@
import io.swagger.annotations.ApiImplicitParam;

import io.swagger.annotations.ApiImplicitParams;

import io.swagger.annotations.ApiOperation;



import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;



import static com.moral.common.util.ResourceUtil.getValue;

import static com.moral.common.util.WebUtils.getParametersStartingWith;

@@ -1068,7 +1068,6 @@
        Map<String, Object> map = list.get(0);

        String monitor_point_id = map.get("monitor_point_id").toString();

        List<Map<String, Object>> sensorUnitList = sensorUnitService.getSensorsByMonitPointId2(monitor_point_id);

        System.out.println(sensorUnitList);

        for (Map<String, Object> sensorMap : list) {

            for (Map<String, Object> sensorUnitMap : sensorUnitList) {

                if (sensorUnitMap.get("sensor_key").equals(sensorMap.get("sensor_key"))) {

@@ -1089,8 +1088,6 @@
        String monitor_point_id = (String) parameters.get("monitor_point_id");

        List<Map<String, Object>> list = sensorService.getSensorByMonitorPointId(monitor_point_id);

        List<Map<String, Object>> sensorUnitList = sensorUnitService.getSensorsByMonitPointId2(monitor_point_id);

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

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

        for (Map<String, Object> sensorMap : list) {

            for (Map<String, Object> sensorUnitMap : sensorUnitList) {

                if (sensorUnitMap.get("sensor_key").equals(sensorMap.get("sensorKey"))) {

@@ -1305,9 +1302,7 @@
        sensorMap.put("e10", Double.parseDouble(map.get("co").toString()));

        sensorMap.put("e15", Double.parseDouble(map.get("o3").toString()));

        Map<String, Object> StateControl = AQICalculation.hourlyAQI(sensorMap);

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

        Map<String, Object> hourAqi = historyService.gitHourlyAQIByMonitorPointIdAndTimeslot(parameters);

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

        Map<String, Object> returnMap = new HashMap<>();

        returnMap.put("MonitorPointAQI", hourAqi.get("AQI"));

        returnMap.put("StateControlAQI", StateControl.get("AQI"));

@@ -1339,4 +1334,75 @@
        return new ResultBean<List<Map<String, Object>>>(returnList);

    }



    @GetMapping("/carTrajectory")

    @ApiOperation(value = "显示走航车轨迹", notes = "显示走航车轨迹")

    @ApiImplicitParams(value = {

            @ApiImplicitParam(name = "mac", value = "设备mac（格式：p5dnd7a0392082）", required = true, paramType = "query", dataType = "String"),

            @ApiImplicitParam(name = "startTime", value = "开始时间(格式：2020-03-19-14)", required = true, paramType = "query", dataType = "String"),

            @ApiImplicitParam(name = "endTime", value = "结束时间(格式：2020-03-19-17)", required = true, paramType = "query", dataType = "String")

    })

    public ModelAndView carTrajectory(ModelAndView model, HttpServletRequest request) throws Exception {

        Map<String, Object> parameters = WebUtils.getParametersStartingWith(request, null);

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

        String startYearAndDay = st.substring(0, st.lastIndexOf("-"));

        String startHour = st.substring(st.lastIndexOf("-") + 1);

        String startTime = startYearAndDay + " " + startHour + ":00:00";

        String et = parameters.get("endTime").toString();

        String endYearAndDay = et.substring(0, et.lastIndexOf("-"));

        String endHour = et.substring(et.lastIndexOf("-") + 1);

        String endTime = endYearAndDay + " " + endHour + ":00:00";

        parameters.put("startTime", startTime);

        parameters.put("endTime", endTime);

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

        if (mac != null && mac.length() != 0) {

            List<List<Map<String, Object>>> sensorData = historyService.getCarSensorData(parameters);

            Map<String, String> sensorsMap = sensorService.getSensorsMap(parameters);

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

            if (sensorData.size() == 3) {

                sensorList = sensorData.get(0);

            }

            JSONObject params = new JSONObject();

            params.put("sensorInfo", sensorList);

            params.put("sensorsMap", sensorsMap);

            String paramsJson = params.toJSONString();

            model.addObject("carTrajectoryParams", paramsJson);

            model.setViewName("cartrajectory");

            return model;

        } else {

            StringBuilder msg = new StringBuilder();

            msg.append(" param[0] mac:");

            msg.append(mac);

            log.warn(msg);

            model.setViewName("403");

            return model;

        }

    }



    @GetMapping("device-excel")

    @ApiOperation(value = "获取设备数据报表", notes = "获取设备数据报表")

    @ApiImplicitParams(value = {

            @ApiImplicitParam(name = "sensorKey", value = "传感器key", required = true, paramType = "query", dataType = "String"),

            @ApiImplicitParam(name = "organizationId", defaultValue = "8", value = "登录账号的组织id", required = true, paramType = "query", dataType = "Integer"),

            @ApiImplicitParam(name = "regionCode", defaultValue = "320583", value = "区域码", required = true, paramType = "query", dataType = "String"),

            @ApiImplicitParam(name = "time", value = "时间(格式：2020-04-09或2019-04)", required = true, paramType = "query", dataType = "String")})

    public ResultBean<List<Map<String, Object>>> getDevicesDataToExcel(HttpServletRequest request) throws Exception {

        Map<String, Object> parameters = WebUtils.getParametersStartingWith(request, null);

        ParameterUtils.getTimeType4Time(parameters);

        Object sensorKey = parameters.get("sensorKey");

        parameters.put("sensorKeys", Arrays.asList(sensorKey));

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

        List<MonitorPoint> monitorPoints = monitorPointService.getMonitorPointsAndDevicesByRegion(parameters);

        for (MonitorPoint m : monitorPoints) {

            for (Device d : m.getDevices()) {

                macList.add(d.getMac());

            }

        }

        parameters.put("macs", macList);

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

        if (!CollectionUtils.isEmpty(macList)) {

            list = historyMinutelyService.getDevicesAvgDataToExcel(parameters);

        }

        return new ResultBean<List<Map<String, Object>>>(list);

    }



}