screen-api/pom.xml
@@ -37,6 +37,17 @@ <artifactId>poi-ooxml-schemas</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.6</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.66</version> </dependency> </dependencies> <build> screen-api/src/main/java/com/moral/api/controller/MonitorPointController.java
@@ -5,6 +5,8 @@ import com.moral.api.pojo.vo.monitorPoint.MonitorPointsVO; import com.moral.api.service.HistoryFiveMinutelyService; import com.moral.api.service.MonitorPointService; import com.moral.api.utils.EasyExcelUtils; import com.moral.api.utils.NoModelWriteData; import com.moral.constant.ResponseCodeEnum; import com.moral.constant.ResultMessage; import com.moral.util.WebUtils; @@ -14,6 +16,8 @@ import io.swagger.annotations.ApiImplicitParams; import io.swagger.annotations.ApiOperation; import lombok.extern.slf4j.Slf4j; import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.ss.formula.functions.T; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.CrossOrigin; import org.springframework.web.bind.annotation.GetMapping; @@ -22,13 +26,11 @@ import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import java.util.Map; import java.util.*; import java.util.stream.Collectors; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; /** * @ClassName DeviceController @@ -137,4 +139,51 @@ return ResultMessage.ok(resList); } @PostMapping("getHourlyDataExcelNew") public ResultMessage getHourlyDataExcelNew(@RequestBody Map<String, Object> params) { if (!params.containsKey("macs") || !params.containsKey("sensors") || !params.containsKey("times") || !params.containsKey("type")) { return ResultMessage.fail(ResponseCodeEnum.PARAMETERS_IS_MISSING.getCode(), ResponseCodeEnum.PARAMETERS_IS_MISSING.getMsg()); } List<Map<String, Object>> resList = monitorPointService.getHourlyDataDataV3Excel(params); return ResultMessage.ok(resList); } @PostMapping("/exlOut") @ApiImplicitParams(value = { @ApiImplicitParam(name = "token", value = "token", required = true, paramType = "header", dataType = "String") }) public void exlOut(HttpServletResponse response) { //导出字段集合 // List<ExcelHeader> excelHeaders = Arrays.asList(new ExcelHeader("phone", "手机号"), new ExcelHeader("sexStr", "性别")); Map<String, Object> params = new HashMap<>(); params.put("macs", Arrays.asList("p5dnd7a0245446", "p5dnd7a0745450")); params.put("sensors", "a34004,a34002"); params.put("type", "hours"); params.put("times", Arrays.asList("2023-07-01 00", "2023-07-02 00")); //数据集合 List<Map<String, Object>> resList = monitorPointService.getHourlyDataDataV3Excel(params); if (CollectionUtils.isEmpty(resList)) { return; } Map<String, Object> map = resList.get(0); List<String> list = new ArrayList<>(); for (String key : map.keySet()) { list.add(key); } String[] s2 = new String[list.size()]; list.toArray(s2); NoModelWriteData d = new NoModelWriteData(); d.setFileName("数据导出"); d.setHeadMap(s2); d.setDataStrMap(s2); d.setDataList(resList); try { EasyExcelUtils easyExcelUtils = new EasyExcelUtils(); easyExcelUtils.noModleWrite(d, response); } catch (Exception e) { int i = 0; } } } screen-api/src/main/java/com/moral/api/service/HistoryHourlyService.java
@@ -92,6 +92,15 @@ List<HistoryHourly> getValueByMacAndTime(String mac, Date startDate, Date endDate); /** * @Description: 根据mac和时间获取小时值 * @Param: [mac, startDate, endDate] * @return: java.util.List<com.moral.api.entity.HistoryHourly> * @Author: 陈凯裕 * @Date: 2021/9/28 */ List<HistoryHourly> getValueByMacAndTime(List<String> mac, Date startDate, Date endDate); /** * @param macs List<String> * @param time String 例：2021-12-23 * @description: 通过设备和时间获取小时数据 screen-api/src/main/java/com/moral/api/service/MonitorPointService.java
@@ -45,6 +45,13 @@ */ List<Map<String, Object>> getHourlyDataDataV3(Map<String, Object> params); /** * 监测站点数据导出 * @param params * @return */ List<Map<String, Object>> getHourlyDataDataV3Excel(Map<String, Object> params); }
@@ -755,6 +755,15 @@ List<HistoryHourly> datas = multiTableQuery(wrapper, tableNames); return datas; } @Override public List<HistoryHourly> getValueByMacAndTime(List<String> mac, Date startDate, Date endDate) { QueryWrapper<HistoryHourly> wrapper = new QueryWrapper<>(); wrapper.in("mac", mac); wrapper.between("time", startDate, endDate); List<String> tableNames = MybatisPLUSUtils.getTableNamesByWrapper(startDate, endDate, SeparateTableType.MONTH); List<HistoryHourly> datas = multiTableQuery(wrapper, tableNames); return datas; } @Override public List<HistoryHourly> getValueByMacs(List<String> macs, String time) { screen-api/src/main/java/com/moral/api/service/impl/MonitorPointServiceImpl.java
@@ -314,6 +314,145 @@ return resultList; } @Override public List<Map<String, Object>> getHourlyDataDataV3Excel(Map<String, Object> params) { List<String> macs = (List<String>) params.remove("macs"); List<String> times = (List<String>) params.remove("times"); String type = params.get("type").toString(); String startTime = times.get(0); String endTime = times.get(1); // String[] macs = params.remove("macs").toString().split(","); // List<String> macsList = Arrays.asList(macs); QueryWrapper<Device> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("is_delete",Constants.NOT_DELETE); queryWrapper.in("mac", macs); List<Device> devices = deviceMapper.selectList(queryWrapper); if (devices.size()<=0){ return null; } String[] sensors = params.remove("sensors").toString().split(","); List<String> sensorsList = Arrays.asList(sensors); List<Map<String,Object>> resultList = new ArrayList<>(); // List<String> sensorsList = (List<String>) params.remove("sensors"); // String startTime = params.get("startTime").toString(); // String endTime = params.get("endTime").toString(); if (type.equals("hours")){ Date startDate = DateUtils.getDate(startTime,DateUtils.yyyy_MM_dd_HH_EN); Date endDate = DateUtils.getDate(endTime,DateUtils.yyyy_MM_dd_HH_EN); List<HistoryHourly> hourlies = historyHourlyService.getValueByMacAndTime(macs, DateUtils.getDate(startTime, DateUtils.yyyy_MM_dd_HH_EN), DateUtils.getDate(endTime, DateUtils.yyyy_MM_dd_HH_EN)); Map<String,Map<String,Object>> map = resultMap(hourlies); Map<Integer,String> pointMap = pointMap(devices); Map<String,String> sensorMap = sensorMap(sensorsList); for (Device device:devices) { Date ks = DateUtils.getDate(startTime,"yyyy-MM-dd HH"); Date js = DateUtils.getDateAddHour(DateUtils.getDate(endTime,"yyyy-MM-dd HH"),1); while (DateUtils.isTimeBefor(js,ks)){ Map<String, Object> timeValueMap = new LinkedHashMap<>();//key为time，value为数据的json timeValueMap.put("监测站点",pointMap.get(device.getMonitorPointId())); timeValueMap.put("name",device.getName()); String c = DateUtils.dateToDateString(ks,DateUtils.yyyy_MM_dd_HH_EN); timeValueMap.put("日期",c); for (String sensor:sensorsList) { String k = c+"_"+device.getMac(); Double num = 0d; if(map.containsKey(k)){ Object o = map.get(k).get(sensor); num = Objects.nonNull(o)?Double.valueOf(o.toString()):0d; } timeValueMap.put(sensorMap.get(sensor),num); } resultList.add(timeValueMap); ks = DateUtils.getDateAddHour(ks,1); } } } else if("day".equals(type)){ Date startDate = DateUtils.getDate(startTime,DateUtils.yyyy_MM_dd_HH_EN); Date endDate = DateUtils.getDate(endTime,DateUtils.yyyy_MM_dd_HH_EN); List<HistoryDaily> historyDailyByMacAndTimeSlot = historyDailyService.getHistoryDailyByMacAndTimeSlot(macs, startDate, endDate); Map<String,Map<String,Object>> map = resultMap(historyDailyByMacAndTimeSlot,new ArrayList<>()); Map<Integer,String> pointMap = pointMap(devices); Map<String,String> sensorMap = sensorMap(sensorsList); for (Device device:devices) { Date ks = DateUtils.getDate(startTime,"yyyy-MM-dd"); Date js = DateUtils.getDateOfDay(DateUtils.getDate(endTime,"yyyy-MM-dd"),1); while (DateUtils.isTimeBefor(js,ks)){ Map<String, Object> timeValueMap = new LinkedHashMap<>();//key为time，value为数据的json timeValueMap.put("监测站点",pointMap.get(device.getMonitorPointId())); timeValueMap.put("name",device.getName()); String c = DateUtils.dateToDateString(ks,"yyyy-MM-dd"); timeValueMap.put("日期",c); for (String sensor:sensorsList) { String k = c+"_"+device.getMac(); Double num = 0d; if(map.containsKey(k)){ Object o = map.get(k).get(sensor); num = Objects.nonNull(o)?Double.valueOf(o.toString()):0d; } timeValueMap.put(sensorMap.get(sensor),num); } resultList.add(timeValueMap); ks = DateUtils.getDateOfDay(ks,1); } } }else { QueryWrapper<HistoryMonthly> HistoryMonthlyWrapper = new QueryWrapper<>(); HistoryMonthlyWrapper.in("mac",devices.stream().map(Device::getMac).collect(Collectors.toList())); HistoryMonthlyWrapper.between("time",startTime,endTime); List<HistoryMonthly> historyMonthlyList = historyMonthlyMapper.selectList(HistoryMonthlyWrapper); Map<String,Map<String,Object>> map = resultMap(new ArrayList<>(),historyMonthlyList); Map<Integer,String> pointMap = pointMap(devices); Map<String,String> sensorMap = sensorMap(sensorsList); for (Device device:devices) { Date ks = DateUtils.getDate(startTime,"yyyy-MM"); Date js = DateUtils.addMonths(DateUtils.getDate(endTime,"yyyy-MM"),1); while (DateUtils.isTimeBefor(js,ks)){ Map<String, Object> timeValueMap = new LinkedHashMap<>();//key为time，value为数据的json timeValueMap.put("监测站点",pointMap.get(device.getMonitorPointId())); timeValueMap.put("name",device.getName()); String c = DateUtils.dateToDateString(ks,"yyyy-MM"); timeValueMap.put("日期",c); for (String sensor:sensorsList) { String k = c+"_"+device.getMac(); Double num = 0d; if(map.containsKey(k)){ Object o = map.get(k).get(sensor); num = Objects.nonNull(o)?Double.valueOf(o.toString()):0d; } timeValueMap.put(sensorMap.get(sensor),num); } resultList.add(timeValueMap); ks = DateUtils.addMonths(ks,1); } /*for (String sensor:sensorsList) { Date ks = DateUtils.getDate(startTime,"yyyy-MM"); Date js = DateUtils.addMonths(DateUtils.getDate(endTime,"yyyy-MM"),1); Map<String, Object> timeValueMap = new LinkedHashMap<>();//key为time，value为数据的json timeValueMap.put("监测站点",pointMap.get(device.getMonitorPointId())); timeValueMap.put("name",device.getName()); timeValueMap.put("sensor",sensorMap.get(sensor)); while (DateUtils.isTimeBefor(js,ks)){ String c = DateUtils.dateToDateString(ks,"yyyy-MM"); String k = c+"_"+device.getMac(); if(map.containsKey(k)){ Object o = map.get(k).get(sensor); timeValueMap.put(c,Objects.nonNull(o)?Double.valueOf(o.toString()):0); }else { timeValueMap.put(c,0); } ks = DateUtils.addMonths(ks,1); } resultList.add(timeValueMap); }*/ } } return resultList; } private Map<String,Map<String,Object>> resultMap(List<HistoryDaily> list ,List<HistoryMonthly> historyMonthlyList){ Map<String,Map<String,Object>> map = new HashMap<>(); for(HistoryDaily h : list){ @@ -333,6 +472,18 @@ return map; } private Map<String,Map<String,Object>> resultMap(List<HistoryHourly> list){ Map<String,Map<String,Object>> map = new HashMap<>(); for(HistoryHourly h : list){ String dateStr = DateUtils.dateToDateString( h.getTime(), DateUtils.yyyy_MM_dd_HH_EN)+"_"+h.getMac(); Map<String,Object> jsonMap = new HashMap<>(); JSONObject jsonObject = JSONObject.parseObject(h.getValue()); jsonMap = jsonObject.getInnerMap(); map.put(dateStr,jsonMap); } return map; } private Map<String,String> sensorMap(List<String> sensorsList){ Map<String,String> map = new HashMap<>(); QueryWrapper<Sensor> sensorQueryWrapper = new QueryWrapper<>(); screen-api/src/main/java/com/moral/api/utils/EasyExcelUtils.java
New file @@ -0,0 +1,77 @@ package com.moral.api.utils; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.support.ExcelTypeEnum; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.fastjson.JSON; import com.google.common.net.HttpHeaders; import org.apache.poi.ss.formula.functions.T; import org.springframework.web.bind.annotation.RequestBody; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.*; public class EasyExcelUtils { //不创建对象的导出 public void noModleWrite(@RequestBody NoModelWriteData data, HttpServletResponse response) throws IOException { // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman try { // response.setContentType("application/vnd.ms-excel"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode(data.getFileName(), "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); // 这里需要设置不关闭流 EasyExcel.write(response.getOutputStream()).head(head(data.getHeadMap())).sheet(data.getFileName()).doWrite(dataList(data.getDataList(), data.getDataStrMap())); } catch (Exception e) { // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map<String, String> map = new HashMap<String, String>(); map.put("status", "failure"); map.put("message", "下载文件失败" + e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); } } //创建对象的导出 public <T> void simpleWrite(@RequestBody SimpleWriteData data,Class<T> clazz, HttpServletResponse response) throws IOException { // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman // response.setContentType("application/vnd.ms-excel"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode(data.getFileName(), "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), clazz).sheet(data.getFileName()).doWrite(data.getDataList()); } //设置表头 private List<List<String>> head(String[] headMap) { List<List<String>> list = new ArrayList<List<String>>(); for (String head : headMap) { List<String> headList = new ArrayList<String>(); headList.add(head); list.add(headList); } return list; } //设置导出的数据内容 private List<List<Object>> dataList(List<Map<String, Object>> dataList, String[] dataStrMap) { List<List<Object>> list = new ArrayList<List<Object>>(); for (Map<String, Object> map : dataList) { List<Object> data = new ArrayList<Object>(); for (int i = 0; i < dataStrMap.length; i++) { data.add(map.get(dataStrMap[i])); } list.add(data); } return list; } } screen-api/src/main/java/com/moral/api/utils/NoModelWriteData.java
New file @@ -0,0 +1,15 @@ package com.moral.api.utils; import lombok.Data; import java.io.Serializable; import java.util.List; import java.util.Map; @Data public class NoModelWriteData implements Serializable { private String fileName;//文件名 private String[] headMap;//表头数组 private String[] dataStrMap;//对应数据字段数组 private List<Map<String, Object>> dataList;//数据集合 } screen-api/src/main/java/com/moral/api/utils/SimpleWriteData.java
New file @@ -0,0 +1,11 @@ package com.moral.api.utils; import lombok.Data; import java.io.Serializable; import java.util.List; @Data public class SimpleWriteData implements Serializable { private String fileName;//文件名 private List<?> dataList;//数据列表 } screen-common/src/main/java/com/moral/util/DateUtils.java
@@ -622,6 +622,22 @@ return dateToDateString(now.getTime(), formatStr); } /** * 获取指定日期day天后的一个(formatStr)的字符串 * * @param date * @param date, * @param hours * @return */ public static Date getDateAddHour(Date date,int hours) { Calendar now = Calendar.getInstance(TimeZone.getDefault()); now.setTime(date); now.add(Calendar.HOUR_OF_DAY, hours); return now.getTime(); } /** * @Description: 获取指定日期day天后的日期 * @Param: [date, day]
