package com.ruoyi.biz.controller; import com.ruoyi.biz.domain.*; import com.ruoyi.biz.service.*; import com.ruoyi.biz.service.impl.AsyncServiceImpl; import com.ruoyi.biz.service.impl.BrokenYarnExportServiceImpl; import com.ruoyi.biz.tools.Tools; import com.ruoyi.common.constant.Constants; import com.ruoyi.common.core.controller.BaseController; import com.ruoyi.common.core.domain.R; import com.ruoyi.common.utils.CacheUtils; import com.ruoyi.common.utils.DateUtils; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xddf.usermodel.chart.BarDirection; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Value; import org.springframework.web.bind.annotation.*; import javax.annotation.Resource; import javax.servlet.http.HttpServletResponse; import java.io.BufferedOutputStream; import java.io.FileInputStream; import java.io.IOException; import java.io.OutputStream; import java.math.BigDecimal; import java.math.RoundingMode; import java.net.URLEncoder; import java.text.ParseException; import java.time.*; import java.util.*; import java.util.concurrent.ExecutionException; import java.util.concurrent.Future; import java.util.concurrent.atomic.AtomicInteger; import java.util.concurrent.atomic.AtomicReference; import java.util.stream.Collectors; /** * swagger 用户测试方法 * * @author ruoyi */ @Api("数据接口") @RestController @Slf4j @RequestMapping("/api/") public class ApiController extends BaseController { @Resource private ITwinCalcDayService twinCalcDayService; @Resource private ITwinCalcStopService stopService; @Resource private ITwinCalcAlarmsService alarmsService; @Resource private ITwinDeviceService deviceService; @Resource private IIotService iotService; @Resource private AsyncServiceImpl asyncService; @Resource private BrokenYarnExportServiceImpl exportService; @Value("${excel.total}") private String totalExcelTemplate; @Value("${excel.formula}") private String formulaExcelTemplate; @Value("${excel.gram}") private String gramExcelTemplate; @Value("${excel.stops}") private String stopsExcelTemplate; @Value("${excel.stopsType}") private String stopsTypeExcelTemplate; @Value("${excel.alarms}") private String alarmsExcelTemplate; @Value("${excel.yarn}") private String yarnExcelTemplate; @Value("${excel.brokenYarn}") private String brokenYarnExcelTemplate; @ApiOperation("在线设备") @GetMapping("/device/online") @CrossOrigin(origins = "*") public R> online() { TwinDevice searchDevice = new TwinDevice(); searchDevice.setOnline("1"); List list = deviceService.selectTwinDeviceList(searchDevice); List devices = new ArrayList<>(); list.forEach(d -> devices.add(d.getDeviceCode())); return R.ok(devices); } @ApiOperation("首页统计数据") @GetMapping("/index") @CrossOrigin(origins = "*") public R index() { Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_CALC); if (d != null) { IndexData indexData = (IndexData) d; return R.ok(indexData); } else { return R.fail(); } } @ApiOperation("首页告警数据") @GetMapping("/alarm") @CrossOrigin(origins = "*") public R alarm() { Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_ALARM); if (d != null) { IndexData indexData = (IndexData) d; return R.ok(indexData); } else { return R.fail(); } } @ApiOperation("设备具体数据") @GetMapping("/query/{device}") @CrossOrigin(origins = "*") public R query(@PathVariable String device, String week) { TwinDevice info = deviceService.selectTwinDeviceByCode(device); if (info == null) { return R.fail("该设备未接入"); } iotService.getToken(); Future> future = asyncService.currData(info); Map result = new HashMap<>(16); try { result.put("data", future.get()); } catch (InterruptedException e) { throw new RuntimeException(e); } catch (ExecutionException e) { throw new RuntimeException(e); } if (week != null) { /* *获取前面7天的数据 */ LocalDateTime ldt = LocalDateTime.now(); if (ldt.getHour() < 7) { //7点前是属于前一生产天 ldt = ldt.minusDays(1); } LocalDate localDate = ldt.toLocalDate().minusDays(7); Date date = Date.from(localDate.atStartOfDay(ZoneOffset.of("+8")).toInstant()); List list = twinCalcDayService.selectTwinCalcDayListByTime(date); List> alarms = new ArrayList<>(); Map> stopDeviceGroup = list.stream().collect(Collectors.groupingBy(TwinCalcDay::getTime, LinkedHashMap::new, Collectors.toList())); for (Map.Entry> entry : stopDeviceGroup.entrySet()) { Map temp = new HashMap<>(16); TwinCalcDay day = new TwinCalcDay(entry.getKey()); List days = entry.getValue(); day.calcDays(days); temp.put("time", DateUtils.parseDateToStr(entry.getKey())); temp.put("value", day.getAlarm()); alarms.add(temp); } result.put("alarms", alarms); } return R.ok(result); } @ApiOperation("送经量") @GetMapping("/export/warp-run-in") @CrossOrigin(origins = "*") public void warpRunInExport(HttpServletResponse response) { Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_WARP_RUN_IN); if (d != null) { List list = (List) d; try (Workbook wb = new XSSFWorkbook(); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) { Sheet sheet = wb.createSheet("送经量明细"); sheet.setDefaultColumnWidth(15); CellStyle style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); Row titleRow = sheet.createRow(0); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("设备名"); titleCell = titleRow.createCell(1); titleCell.setCellValue("L1梳栉送经量"); titleCell = titleRow.createCell(2); titleCell.setCellValue("L2梳栉送经量"); titleCell = titleRow.createCell(3); titleCell.setCellValue("L3梳栉送经量"); titleCell = titleRow.createCell(4); titleCell.setCellValue("L4梳栉送经量"); titleCell = titleRow.createCell(5); titleCell.setCellValue("L5梳栉送经量"); titleCell = titleRow.createCell(6); titleCell.setCellValue("牵拉密度"); titleCell = titleRow.createCell(7); titleCell.setCellValue("卷曲张力系数"); AtomicInteger rowNum = new AtomicInteger(1); list.forEach(warp -> { Row row = sheet.createRow(rowNum.get()); Cell[] cells = new Cell[8]; for (int i = 0; i < cells.length; i++) { cells[i] = row.createCell(i); } warp.setCells(cells, style); rowNum.getAndIncrement(); }); // 清空response response.reset(); // 设置response的Header response.setCharacterEncoding("UTF-8"); //Content-Disposition的作用:告知浏览器以何种方式显示响应返回的文件,用浏览器打开还是以附件的形式下载到本地保存 //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3" // filename表示文件的默认名称,因为网络传输只支持URL编码的相关支付,因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称 response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("送经量明细导出" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8")); response.setContentType("application/octet-stream"); wb.write(outputStream); outputStream.flush(); } catch (IOException ex) { ex.printStackTrace(); } } } @ApiOperation("纱线规格") @GetMapping("/export/yarn-specifications") @CrossOrigin(origins = "*") public void yarnSpecificationsExport(Integer day, HttpServletResponse response) { Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_FORMULA_DETAIL); if (d != null) { List list = (List) d; list.sort(Comparator.comparing(FormulaDetail::getFormula_data_4).thenComparing(FormulaDetail::getFormula_data_5).thenComparing(FormulaDetail::getFormula_data_6) .thenComparing(FormulaDetail::getFormula_data_7).thenComparing(FormulaDetail::getFormula_data_8).thenComparing(FormulaDetail::getFormula_data_9) .thenComparing(FormulaDetail::getFormula_data_15)); Map> map = list.stream().collect(Collectors.groupingBy(FormulaDetail::getYarnD, LinkedHashMap::new, Collectors.toList())); LocalDateTime localDateTime = LocalDateTime.now(); if (localDateTime.getHour() < 7) { //当天7点前,则是前天数据 localDateTime = localDateTime.minusDays(2); } else { //7点后,就是昨日数据 localDateTime = localDateTime.minusDays(1); } LocalDate localDate = localDateTime.toLocalDate(); Date sd = Date.from(localDate.atStartOfDay(ZoneOffset.of("+8")).toInstant()); List calcDayList = twinCalcDayService.selectTwinCalcDayListByTime(sd, sd); Map decimalMap = calcDayList.stream().collect(Collectors.toMap(TwinCalcDay::getDeviceId, TwinCalcDay::getLength)); try (FileInputStream inputStream = new FileInputStream(yarnExcelTemplate); XSSFWorkbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) { Sheet sheet = wb.getSheetAt(0); AtomicInteger rowNum = new AtomicInteger(2); CellStyle percentStyle = wb.createCellStyle(); percentStyle.setDataFormat(wb.createDataFormat().getFormat("0.00%")); // 垂直居中 percentStyle.setVerticalAlignment(VerticalAlignment.CENTER); CellStyle p2 = wb.createCellStyle(); p2.setDataFormat(wb.createDataFormat().getFormat("0.00")); for (String s : map.keySet()) { int sr = rowNum.get(); List fList = map.get(s); for (FormulaDetail detail : fList) { Row row = sheet.createRow(rowNum.get()); Cell[] cells = new Cell[39]; for (int i = 0; i < cells.length; i++) { cells[i] = row.createCell(i); } detail.setYarnCells(cells, p2); rowNum.getAndIncrement(); } if (fList.size() > 1) { int er = sr + fList.size() - 1; sheet.addMergedRegion(new CellRangeAddress(sr, er, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(sr, er, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(sr, er, 2, 2)); } } XSSFSheet sheet2 = wb.getSheetAt(1); wb.setSheetName(1, "未来产能预期(" + day + "天)"); AtomicInteger rn = new AtomicInteger(2); //1.先按底纱分组 Map> map2 = list.stream().collect(Collectors.groupingBy(FormulaDetail::getYarnDd, LinkedHashMap::new, Collectors.toList())); for (String s : map2.keySet()) { int sr = rn.get(); List l12list = map2.get(s); //2.再按规格分组 Map> d1map = l12list.stream().collect(Collectors.groupingBy(FormulaDetail::getFormula_data_6, LinkedHashMap::new, Collectors.toList())); for (Integer l3d : d1map.keySet()) { int ssr = rn.get(); List l3list = d1map.get(l3d); //3.再按盘头根数和毛高分组 Map> d2map = l3list.stream().collect(Collectors.groupingBy(FormulaDetail::getYarnPt, LinkedHashMap::new, Collectors.toList())); for (String pt : d2map.keySet()) { List ptList = d2map.get(pt); FormulaDetail detail = ptList.get(0); String device = ""; BigDecimal length = BigDecimal.ZERO; for (FormulaDetail dd : ptList) { BigDecimal temp = decimalMap.get(dd.getDeviceId()); if (temp != null) { length = length.add(temp); } // device += dd.getDevice() + "/"; } BigDecimal yLength = length.multiply(BigDecimal.valueOf(day)); Row row = sheet2.createRow(rn.get()); Cell[] cells = new Cell[12]; for (int i = 0; i < cells.length; i++) { cells[i] = row.createCell(i); } cells[0].setCellValue(rn.get() - 1); cells[1].setCellValue(detail.getFormula_data_4()); cells[2].setCellValue(detail.getFormula_data_5()); cells[3].setCellValue(detail.getFormula_data_6()); cells[4].setCellValue(detail.getFormula_data_10()); cells[5].setCellValue(detail.getFormula_data_11()); cells[6].setCellValue(detail.getFormula_data_12()); cells[7].setCellValue(detail.getFormula_data_15()); cells[8].setCellValue(length.doubleValue()); cells[8].setCellStyle(p2); cells[9].setCellValue(yLength.doubleValue()); cells[9].setCellStyle(p2); //规格占比 BigDecimal percent1 = BigDecimal.valueOf(ptList.size()).divide(BigDecimal.valueOf(list.size()), 4, RoundingMode.HALF_UP); cells[10].setCellValue(percent1.doubleValue()); cells[10].setCellStyle(percentStyle); //底纱占比 BigDecimal percent2 = BigDecimal.valueOf(l12list.size()).divide(BigDecimal.valueOf(list.size()), 4, RoundingMode.HALF_UP); cells[11].setCellValue(percent2.doubleValue()); cells[11].setCellStyle(percentStyle); rn.getAndIncrement(); } if (d2map.size() > 1) { int er = rn.get() - 1; sheet2.addMergedRegion(new CellRangeAddress(ssr, er, 1, 1)); sheet2.addMergedRegion(new CellRangeAddress(ssr, er, 2, 2)); sheet2.addMergedRegion(new CellRangeAddress(ssr, er, 3, 3)); } } if (d1map.size() > 1) { int er = rn.get() - 1; sheet2.addMergedRegion(new CellRangeAddress(sr, er, 11, 11)); } } int endRn = rn.get() - 1; int[] pos = {13, 1, 20, endRn + 1}; int[] xdata = {2, endRn, 0, 0}; int[] data = {2, endRn, 10, 10}; exportService.drawBar(sheet2, "规格占比", "规格序号", pos, xdata, data, BarDirection.COL); // 清空response response.reset(); // 设置response的Header response.setCharacterEncoding("UTF-8"); //Content-Disposition的作用:告知浏览器以何种方式显示响应返回的文件,用浏览器打开还是以附件的形式下载到本地保存 //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3" // filename表示文件的默认名称,因为网络传输只支持URL编码的相关支付,因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称 response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("纱线规格导出" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8")); response.setContentType("application/octet-stream"); wb.write(outputStream); outputStream.flush(); } catch (IOException ex) { ex.printStackTrace(); } } } @ApiOperation("平方米克重") @GetMapping("/gram-mass/total") @CrossOrigin(origins = "*") public R> gramMass() { Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_GRAM_MASS); if (d != null) { List list = (List) d; return R.ok(list); } else { return R.fail(); } } @ApiOperation("平方米克重详情") @GetMapping("/gram-mass/detail/{gramMass}") @CrossOrigin(origins = "*") public R> gramMassDetail(@PathVariable Float gramMass) { Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_GRAM_MASS_DETAIL); if (d != null) { List list = (List) d; list = list.stream().filter(detail -> detail.getGramMass().equals(gramMass)).collect(Collectors.toList()); return R.ok(list); } else { return R.fail(); } } @ApiOperation("平方米克重导出") @GetMapping("/export/gram-mass") @CrossOrigin(origins = "*") public void gramMassExport(HttpServletResponse response) { Object total = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_GRAM_MASS); Object detail = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_GRAM_MASS_DETAIL); if (total != null && detail != null) { List list = (List) total; List details = (List) detail; try (FileInputStream inputStream = new FileInputStream(gramExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) { Sheet sheet = wb.getSheetAt(0); CreationHelper creationHelper = wb.getCreationHelper(); CellStyle percentStyle = wb.createCellStyle(); percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%")); CellStyle p2 = wb.createCellStyle(); p2.setDataFormat(wb.createDataFormat().getFormat("0.00")); AtomicInteger rowNum = new AtomicInteger(1); list.forEach(gram -> { Row row = sheet.createRow(rowNum.get()); Cell[] cells = new Cell[3]; for (int i = 0; i < cells.length; i++) { cells[i] = row.createCell(i); } cells[0].setCellValue(gram.getGramMass()); cells[0].setCellStyle(p2); cells[1].setCellValue(gram.getNum()); cells[2].setCellValue(gram.getPercent()); cells[2].setCellStyle(percentStyle); rowNum.getAndIncrement(); Sheet ns = wb.cloneSheet(1); wb.setSheetName(wb.getSheetIndex(ns), gram.getGramMass() + "克明细"); AtomicInteger detailRowNum = new AtomicInteger(2); details.stream().filter(d -> d.getGramMass().equals(gram.getGramMass())).forEach(d -> { Row dRow = ns.createRow(detailRowNum.get()); Cell[] dCells = new Cell[24]; for (int i = 0; i < dCells.length; i++) { dCells[i] = dRow.createCell(i); } dCells[2].setCellStyle(p2); d.setCells(dCells); detailRowNum.getAndIncrement(); }); }); wb.removeSheetAt(1); // 清空response response.reset(); // 设置response的Header response.setCharacterEncoding("UTF-8"); //Content-Disposition的作用:告知浏览器以何种方式显示响应返回的文件,用浏览器打开还是以附件的形式下载到本地保存 //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3" // filename表示文件的默认名称,因为网络传输只支持URL编码的相关支付,因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称 response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("平方米克重明细导出" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8")); response.setContentType("application/octet-stream"); wb.write(outputStream); outputStream.flush(); } catch (IOException ex) { ex.printStackTrace(); } } } @ApiOperation("配方统计") @GetMapping("/formula/total") @CrossOrigin(origins = "*") public R> formulaTotal() { Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_FORMULA_TOTAL); if (d != null) { List list = (List) d; return R.ok(list); } else { return R.fail(); } } @ApiOperation("配方详情") @GetMapping("/formula/detail/{height}") @CrossOrigin(origins = "*") public R> formulaDetail(@PathVariable Float height) { Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_FORMULA_DETAIL); if (d != null) { List list = (List) d; list = list.stream().filter(detail -> detail.getFormula_data_15().equals(height)).collect(Collectors.toList()); return R.ok(list); } else { return R.fail(); } } @ApiOperation("配方详情导出") @GetMapping("/formula/export/{height}") @CrossOrigin(origins = "*") public void formulaExport(@PathVariable Float height, HttpServletResponse response) { Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_FORMULA_DETAIL); if (d != null) { List list = (List) d; list = list.stream().filter(detail -> detail.getFormula_data_15().equals(height)).collect(Collectors.toList()); try (FileInputStream inputStream = new FileInputStream(formulaExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) { Sheet sheet = wb.getSheetAt(0); AtomicInteger rowNum = new AtomicInteger(2); list.forEach(detail -> { Row row = sheet.createRow(rowNum.get()); Cell[] cells = new Cell[26]; for (int i = 0; i < cells.length; i++) { cells[i] = row.createCell(i); } detail.setCells(cells); rowNum.getAndIncrement(); }); // 清空response response.reset(); // 设置response的Header response.setCharacterEncoding("UTF-8"); //Content-Disposition的作用:告知浏览器以何种方式显示响应返回的文件,用浏览器打开还是以附件的形式下载到本地保存 //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3" // filename表示文件的默认名称,因为网络传输只支持URL编码的相关支付,因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称 response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("毛高" + height + "设备明细导出" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8")); response.setContentType("application/octet-stream"); wb.write(outputStream); outputStream.flush(); } catch (IOException ex) { ex.printStackTrace(); } } } @ApiOperation("导出产量数据") @GetMapping("/export/production") @CrossOrigin(origins = "*") public void productionExport(String start, String end, HttpServletResponse response) { LocalDate localDate = LocalDate.parse(start); Date sd = Date.from(localDate.atStartOfDay(ZoneOffset.of("+8")).toInstant()); localDate = LocalDate.parse(end); Date ed = Date.from(localDate.atStartOfDay(ZoneOffset.of("+8")).toInstant()); List list = twinCalcDayService.selectTwinCalcDayListByTime(sd, ed); try (FileInputStream inputStream = new FileInputStream(totalExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) { CreationHelper creationHelper = wb.getCreationHelper(); CellStyle percentStyle = wb.createCellStyle(); percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%")); CellStyle p2 = wb.createCellStyle(); p2.setDataFormat(wb.createDataFormat().getFormat("0.00")); Map> dayGroup = list.stream().collect(Collectors.groupingBy(TwinCalcDay::getTime, LinkedHashMap::new, Collectors.toList())); for (Map.Entry> entry : dayGroup.entrySet()) { Sheet sheet = wb.cloneSheet(0); wb.setSheetName(wb.getSheetIndex(sheet), DateUtils.parseDateToStr("yyyy-MM-dd", entry.getKey())); List days = entry.getValue(); AtomicInteger rowNum = new AtomicInteger(2); days.forEach(day -> { Row row = sheet.createRow(rowNum.get()); Cell[] cells = new Cell[25]; for (int i = 0; i < cells.length; i++) { cells[i] = row.createCell(i); } day.setCells(cells); rowNum.getAndIncrement(); }); } wb.removeSheetAt(0); // 清空response response.reset(); // 设置response的Header response.setCharacterEncoding("UTF-8"); //Content-Disposition的作用:告知浏览器以何种方式显示响应返回的文件,用浏览器打开还是以附件的形式下载到本地保存 //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3" // filename表示文件的默认名称,因为网络传输只支持URL编码的相关支付,因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称 response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("最近7天产量统计" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8")); response.setContentType("application/octet-stream"); wb.write(outputStream); outputStream.flush(); } catch (IOException ex) { ex.printStackTrace(); } } @ApiOperation("导出断纱分析") @GetMapping("/export/broken-yarn") @CrossOrigin(origins = "*") public void brokenYarnExport(String date, HttpServletResponse response) throws ParseException { LocalDate localDate = LocalDate.parse(date); try (FileInputStream inputStream = new FileInputStream(brokenYarnExcelTemplate); XSSFWorkbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) { //1.基本信息表 exportService.base(wb, localDate); //2.停机原因分析 exportService.stop(wb, localDate); //4.断纱停机TOP排名 exportService.top(wb, localDate); // 清空response response.reset(); // 设置response的Header response.setCharacterEncoding("UTF-8"); //Content-Disposition的作用:告知浏览器以何种方式显示响应返回的文件,用浏览器打开还是以附件的形式下载到本地保存 //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3" // filename表示文件的默认名称,因为网络传输只支持URL编码的相关支付,因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称 response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("断纱分析日报" + localDate + ".xlsx", "UTF-8")); response.setContentType("application/octet-stream"); wb.write(outputStream); outputStream.flush(); } catch (IOException ex) { ex.printStackTrace(); } } @ApiOperation("导出停机数据") @GetMapping("/export/stops") @CrossOrigin(origins = "*") public void stopsExport(String start, String end, HttpServletResponse response) throws ParseException { Date sTime = DateUtils.parseDate(start, DateUtils.YYYY_MM_DD_HH_MM_SS); Date eTime = DateUtils.parseDate(end, DateUtils.YYYY_MM_DD_HH_MM_SS); List list = stopService.selectTwinCalcStopListByDate(sTime, eTime); Map> stopDeviceGroup = list.stream().collect(Collectors.groupingBy(TwinCalcStop::getStopType, LinkedHashMap::new, Collectors.toList())); //1停经片停机,2-CCD停机(相机号+断纱/故障),3-人工停机,4-断电停机,5-设备故障停机,6-落布米数达到停机,7-盘头剩余圈数达到停机 String[] stopStr = {"停经片停机", "CCD停机(相机号+断纱/故障)", "人工停机", "断电停机", "设备故障停机", "落布米数达到停机", "盘头剩余圈数达到停机"}; //停机次数 Integer[] stopNum = new Integer[7]; Integer totalNum = 0; //停机时间 Long[] stopTime = new Long[7]; Arrays.fill(stopTime, 0L); AtomicReference totalTime = new AtomicReference<>(0L); Map deviceMap = deviceService.deviceMap(); for (Map.Entry> entry : stopDeviceGroup.entrySet()) { Integer stopType = entry.getKey(); int pos = stopType - 1; List stops = entry.getValue(); stopNum[pos] = stops.size(); totalNum += stops.size(); stops.forEach(stop -> { // 调整停机时间 if (stop.getEndTime().compareTo(eTime) > 0) { stop.setEndTime(eTime); } //调整开机时间 if (stop.getStartTime().compareTo(sTime) < 0) { stop.setStartTime(sTime); } long t = (stop.getEndTime().getTime() - stop.getStartTime().getTime()) / 1000; stopTime[pos] += t; totalTime.updateAndGet(v -> v + t); }); } try (FileInputStream inputStream = new FileInputStream(stopsExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) { CreationHelper creationHelper = wb.getCreationHelper(); CellStyle percentStyle = wb.createCellStyle(); percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%")); CellStyle timeStyle = wb.createCellStyle(); timeStyle.setDataFormat(creationHelper.createDataFormat().getFormat(DateUtils.YYYY_MM_DD_HH_MM_SS)); CellStyle rightStyle = wb.createCellStyle(); rightStyle.setAlignment(HorizontalAlignment.RIGHT); Sheet sheetAt = wb.getSheetAt(0); Row title = sheetAt.getRow(0); Cell cell = title.getCell(0); cell.setCellValue("停机原因分析(" + start + "至" + end + ")"); for (int i = 0; i < stopStr.length; i++) { Row row = sheetAt.createRow(i + 2); Cell[] cells = new Cell[5]; for (int j = 0; j < cells.length; j++) { cells[j] = row.createCell(j); } cells[0].setCellValue(stopStr[i]); cells[1].setCellValue(stopNum[i]); cells[2].setCellValue(Tools.convertHMS(stopTime[i])); cells[2].setCellStyle(rightStyle); float percentNum = BigDecimal.valueOf(stopNum[i]).divide(BigDecimal.valueOf(totalNum), 4, RoundingMode.HALF_UP).floatValue(); float percentTimes = BigDecimal.valueOf(stopTime[i]).divide(BigDecimal.valueOf(totalTime.get()), 4, RoundingMode.HALF_UP).floatValue(); cells[3].setCellValue(percentNum); cells[3].setCellStyle(percentStyle); cells[4].setCellValue(percentTimes); cells[4].setCellStyle(percentStyle); } Sheet sheet = wb.getSheetAt(1); AtomicInteger rowNum = new AtomicInteger(1); list.forEach(stop -> { Row row = sheet.createRow(rowNum.get()); Cell[] cells = new Cell[5]; for (int j = 0; j < cells.length; j++) { cells[j] = row.createCell(j); } cells[0].setCellValue(deviceMap.get(stop.getDeviceId()).getDeviceName()); cells[1].setCellValue(stopStr[stop.getStopType() - 1]); cells[2].setCellValue(stop.getStartTime()); cells[2].setCellStyle(timeStyle); cells[3].setCellValue(stop.getEndTime()); cells[3].setCellStyle(timeStyle); Duration duration = Duration.between(stop.getStartTime().toInstant(), stop.getEndTime().toInstant()); cells[4].setCellValue(Tools.convertHMS(duration.getSeconds())); cells[4].setCellStyle(rightStyle); rowNum.getAndIncrement(); }); // 清空response response.reset(); // 设置response的Header response.setCharacterEncoding("UTF-8"); //Content-Disposition的作用:告知浏览器以何种方式显示响应返回的文件,用浏览器打开还是以附件的形式下载到本地保存 //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3" // filename表示文件的默认名称,因为网络传输只支持URL编码的相关支付,因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称 response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("停机分析" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8")); response.setContentType("application/octet-stream"); wb.write(outputStream); outputStream.flush(); } catch (IOException ex) { ex.printStackTrace(); } } @ApiOperation("停机分类导出") @GetMapping("/export/stops/type/{type}") @CrossOrigin(origins = "*") public void stopsTypeExport(@PathVariable Integer type, String start, String end, Integer st, Integer et, HttpServletResponse response) { LocalDate s = LocalDate.parse(start); LocalDate e = LocalDate.parse(end); List list = new ArrayList<>(); do { LocalDateTime ldtS = LocalDateTime.of(s, LocalTime.MIN).plusHours(st); LocalDateTime ldtE = LocalDateTime.of(s, LocalTime.MIN).plusHours(et); Date date = Date.from(ldtS.toLocalDate().atStartOfDay(ZoneOffset.of("+8")).toInstant()); Date sTime = Date.from(ldtS.atZone(ZoneId.systemDefault()).toInstant()); Date eTime = Date.from(ldtE.atZone(ZoneId.systemDefault()).toInstant()); List stopList = stopService.selectTwinCalcStopListByDate(sTime, eTime); stopList.forEach(stop -> { stop.setDataDate(date); }); list.addAll(stopList); s = s.plusDays(1); } while (!s.isAfter(e)); List stopList = list.stream().filter(stop -> stop.getStopType().equals(type)).collect(Collectors.toList()); Map deviceMap = deviceService.deviceMap(); Map> stopDateGroup = stopList.stream().collect(Collectors.groupingBy(TwinCalcStop::getDataDate, LinkedHashMap::new, Collectors.toList())); //1停经片停机,2-CCD停机(相机号+断纱/故障),3-人工停机,4-断电停机,5-设备故障停机,6-落布米数达到停机,7-盘头剩余圈数达到停机 String[] stopStr = {"断纱", "断纱", "人工停机", "断电停机", "设备故障停机", "下卷", "叫料"}; try (FileInputStream inputStream = new FileInputStream(stopsTypeExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) { CreationHelper creationHelper = wb.getCreationHelper(); CellStyle percentStyle = wb.createCellStyle(); percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%")); CellStyle timeStyle = wb.createCellStyle(); timeStyle.setDataFormat(creationHelper.createDataFormat().getFormat(DateUtils.YYYY_MM_DD_HH_MM_SS)); CellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat(DateUtils.YYYY_MM_DD)); CellStyle p2 = wb.createCellStyle(); p2.setDataFormat(wb.createDataFormat().getFormat("0.00")); Sheet sheetAt = wb.getSheetAt(0); int rowNum = 1; AtomicInteger rs = new AtomicInteger(1); for (Map.Entry> entry : stopDateGroup.entrySet()) { List stops = entry.getValue(); Row row = sheetAt.createRow(rowNum); Cell[] cells = new Cell[7]; for (int j = 0; j < cells.length; j++) { cells[j] = row.createCell(j); } cells[0].setCellValue(entry.getKey()); cells[0].setCellStyle(dateStyle); cells[1].setCellValue(st + "-" + et); Map> stopDeviceGroup = stops.stream().collect(Collectors.groupingBy(TwinCalcStop::getDeviceId, LinkedHashMap::new, Collectors.toList())); cells[2].setCellValue(stopDeviceGroup.size()); int num = stops.size(); cells[3].setCellValue(num); //0.最小,1.最大,2.总时间 final long[] time = {999999L, 0, 0}; Sheet sheet = wb.getSheetAt(1); stops.forEach(stop -> { long t = (stop.getEndTime().getTime() - stop.getStartTime().getTime()) / 1000; if (t < time[0]) { time[0] = t; } if (t > time[1]) { time[1] = t; } time[2] += t; Row r1 = sheet.createRow(rs.get()); Cell[] cs = new Cell[6]; for (int j = 0; j < cs.length; j++) { cs[j] = r1.createCell(j); } cs[0].setCellValue(entry.getKey()); cs[0].setCellStyle(dateStyle); cs[1].setCellValue(st + "-" + et); cs[2].setCellValue(deviceMap.get(stop.getDeviceId()).getDeviceName()); cs[3].setCellValue(stopStr[stop.getStopType() - 1]); cs[4].setCellValue(stop.getStartTime()); cs[4].setCellStyle(timeStyle); cs[5].setCellValue(stop.getEndTime()); cs[5].setCellStyle(timeStyle); rs.getAndIncrement(); }); cells[4].setCellValue(time[1]); cells[5].setCellValue(time[0]); float avg = BigDecimal.valueOf(time[2]).divide(BigDecimal.valueOf(num), 2, RoundingMode.HALF_UP).floatValue(); cells[6].setCellValue(avg); cells[6].setCellStyle(p2); rowNum++; } // 清空response response.reset(); // 设置response的Header response.setCharacterEncoding("UTF-8"); //Content-Disposition的作用:告知浏览器以何种方式显示响应返回的文件,用浏览器打开还是以附件的形式下载到本地保存 //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3" // filename表示文件的默认名称,因为网络传输只支持URL编码的相关支付,因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称 response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("并发" + stopStr[type - 1] + "分析" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8")); response.setContentType("application/octet-stream"); wb.write(outputStream); outputStream.flush(); } catch (IOException ex) { ex.printStackTrace(); } } @ApiOperation("告警统计导出") @GetMapping("/export/alarms") @CrossOrigin(origins = "*") public void alarmsExport(String start, String end, HttpServletResponse response) throws ParseException { Date sTime = DateUtils.parseDate(start, DateUtils.YYYY_MM_DD_HH_MM_SS); Date eTime = DateUtils.parseDate(end, DateUtils.YYYY_MM_DD_HH_MM_SS); List list = alarmsService.selectTwinCalcAlarmsListByDate(sTime, eTime); list.sort((Comparator.comparing(TwinCalcAlarms::getDeviceId))); Map deviceMap = deviceService.deviceMap(); Map> alarmsGroup = list.stream().collect(Collectors.groupingBy(TwinCalcAlarms::getDeviceId, LinkedHashMap::new, Collectors.toList())); String[] alarmStr = {"GB1伺服通讯故障", "GB2伺服通讯故障", "GB3伺服通讯故障", "GB4伺服通讯故障", "GB5伺服通讯故障", "牵引伺服通讯故障", "卷曲伺服通讯故障", "断纱故障", "电源故障", "联动未完全啮合", "米长到达", "GB1剩余圈数报警", "GB2剩余圈数报警", "GB3剩余圈数报警", "GB4剩余圈数报警", "GB5剩余圈数报警", "主轴变频掉线", "主轴变频故障", "GB1伺服故障", "GB2伺服故障", "GB3伺服故障", "GB4伺服故障", "GB5伺服故障", "牵引伺服故障", "卷曲伺服故障", "拍照自停", "CCD位置报警信息显示"}; try (FileInputStream inputStream = new FileInputStream(alarmsExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) { CreationHelper creationHelper = wb.getCreationHelper(); CellStyle percentStyle = wb.createCellStyle(); percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%")); CellStyle timeStyle = wb.createCellStyle(); timeStyle.setDataFormat(creationHelper.createDataFormat().getFormat(DateUtils.YYYY_MM_DD_HH_MM_SS)); Sheet sheetAt = wb.getSheetAt(0); int rowNum = 2; for (Map.Entry> entry : alarmsGroup.entrySet()) { List alarms = entry.getValue(); Row row = sheetAt.createRow(rowNum); Cell[] cells = new Cell[29]; for (int j = 0; j < cells.length; j++) { cells[j] = row.createCell(j); } cells[0].setCellValue(deviceMap.get(entry.getKey()).getDeviceName()); cells[1].setCellValue(alarms.size()); Map temp = alarms.stream().collect(Collectors.groupingBy(TwinCalcAlarms::getAlarmType, Collectors.counting())); for (Integer v : temp.keySet()) { cells[v + 1].setCellValue(temp.get(v)); } rowNum++; } Sheet sheet = wb.getSheetAt(1); AtomicInteger rn = new AtomicInteger(1); list.forEach(alarms -> { Row row = sheet.createRow(rn.get()); Cell[] cells = new Cell[3]; for (int j = 0; j < cells.length; j++) { cells[j] = row.createCell(j); } cells[0].setCellValue(deviceMap.get(alarms.getDeviceId()).getDeviceName()); cells[1].setCellValue(alarmStr[alarms.getAlarmType() - 1]); cells[2].setCellValue(alarms.getStartTime()); cells[2].setCellStyle(timeStyle); rn.getAndIncrement(); }); // 清空response response.reset(); // 设置response的Header response.setCharacterEncoding("UTF-8"); //Content-Disposition的作用:告知浏览器以何种方式显示响应返回的文件,用浏览器打开还是以附件的形式下载到本地保存 //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3" // filename表示文件的默认名称,因为网络传输只支持URL编码的相关支付,因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称 response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("告警统计" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8")); response.setContentType("application/octet-stream"); wb.write(outputStream); outputStream.flush(); } catch (IOException ex) { ex.printStackTrace(); } } }