package com.jjt; import cn.hutool.json.JSONArray; import cn.hutool.json.JSONObject; import com.ruoyi.RuoYiApplication; import com.ruoyi.biz.service.IIotService; import com.ruoyi.common.utils.DateUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.jupiter.api.Test; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.jdbc.core.JdbcTemplate; import javax.annotation.Resource; import java.io.FileOutputStream; import java.io.IOException; import java.time.*; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.concurrent.atomic.AtomicInteger; /** * DataProcess$ * * @author wukai * @date 2024/5/7 11:49 */ @SpringBootTest(classes = RuoYiApplication.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT) public class DataProcess { @Resource private JdbcTemplate jdbcTemplate; @Resource private IIotService iotService; @Test void excel() { String code = "106"; String table = "root.tl.suxi.knittings" + code + "_plc1"; Workbook workbook = new XSSFWorkbook(); for (int i = 21; i <= 26; i++) { String date = "2024-05-" + i; System.err.println(date); List list = new ArrayList<>(); for (int j = 1; j <= 12; j++) { List pList = get(table, date, j); list.addAll(pList); } Sheet sheet = workbook.createSheet(date); toExcel(list, sheet); } try (FileOutputStream outputStream = new FileOutputStream("D:\\SYSTEM\\Desktop\\temp\\excel\\小经编机" + code + "号.xlsx")) { workbook.write(outputStream); } catch (IOException e) { e.printStackTrace(); } finally { try { workbook.close(); } catch (IOException e) { e.printStackTrace(); } } } public List get(String table, String date, int period) { List list = new ArrayList<>(); LocalDate localDate = LocalDate.parse(date); LocalDateTime ldt = LocalDateTime.of(localDate, LocalTime.MIN); LocalDateTime start = ldt.plusHours(2 * period).minusHours(2); LocalDateTime end = start.plusHours(2); start = start.minusSeconds(1); Long startTime = start.toInstant(ZoneOffset.of("+8")).toEpochMilli(); Long endTime = end.toInstant(ZoneOffset.of("+8")).toEpochMilli(); String sql = "select Capacity_data_5,Capacity_data_6,Capacity_data_7,Capacity_data_8,Capacity_data_9,Capacity_data_10,Capacity_data_11,Capacity_data_12,Capacity_data_13,Capacity_data_14,Capacity_data_15,Capacity_data_16,Capacity_data_17,Capacity_data_18,Capacity_data_19" + " from %s where time>%s and time <=%s"; sql = String.format(sql, table, startTime, endTime); iotService.query(sql); JSONObject jsonObject = iotService.query(sql); JSONObject data = jsonObject.getJSONObject("data"); JSONArray values = data.getJSONArray("values"); JSONArray timestamps = data.getJSONArray("timestamps"); for (int i = 0; i < values.size(); i++) { String[] temp = new String[16]; temp[0] = timestamps.getStr(i); JSONArray da = values.getJSONArray(i); for (int j = 1; j < temp.length; j++) { temp[j] = da.getStr(j - 1); } list.add(temp); } return list; } public void toExcel(List list, Sheet sheet) { String[] names = {"时间", "GB1剩余时间", "GB2剩余时间", "GB3剩余时间", "GB4剩余时间", "GB5剩余时间", "GB1盘头剩余周长", "GB2盘头剩余周长", "GB3盘头剩余周长", "GB4盘头剩余周长", "GB5盘头剩余周长", "GB1盘头剩余圈数", "GB2盘头剩余圈数", "GB3盘头剩余圈数", "GB4盘头剩余圈数", "GB5盘头剩余圈数"}; Row title = sheet.createRow(0); for (int i = 0; i < names.length; i++) { Cell cell = title.createCell(i); cell.setCellValue(names[i]); } AtomicInteger index = new AtomicInteger(1); list.forEach(d -> { Row row = sheet.createRow(index.get()); Long time = Long.parseLong(d[0]); String date = DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS, new Date(time)); Cell cell = row.createCell(0); cell.setCellValue(date); for (int j = 1; j <= 10; j++) { cell = row.createCell(j); cell.setCellValue(Float.parseFloat(d[j])); } for (int j = 11; j < d.length; j++) { cell = row.createCell(j); cell.setCellValue(Integer.parseInt(d[j])); } index.getAndIncrement(); }); } @Test void data() { String table = "root.tl.suxi.knittings98_plc1"; String[] arr = { "time>2024-05-03T00:00:00 and time <=2024-05-03T02:00:00", "time>2024-05-03T02:00:00 and time <=2024-05-03T04:00:00", "time>2024-05-03T04:00:00 and time <=2024-05-03T06:00:00", "time>2024-05-03T06:00:00 and time <=2024-05-03T08:00:00", "time>2024-05-03T08:00:00 and time <=2024-05-03T10:00:00", "time>2024-05-03T10:00:00 and time <=2024-05-03T12:00:00", "time>2024-05-03T12:00:00 and time <=2024-05-03T14:00:00", "time>2024-05-03T14:00:00 and time <=2024-05-03T16:00:00", "time>2024-05-03T16:00:00 and time <=2024-05-03T18:00:00", "time>2024-05-03T18:00:00 and time <=2024-05-03T20:00:00", "time>2024-05-03T20:00:00 and time <=2024-05-03T22:00:00", "time>2024-05-03T22:00:00 and time <=2024-05-04T00:00:00", "time>2024-05-04T00:00:00 and time <=2024-05-04T02:00:00", "time>2024-05-04T02:00:00 and time <=2024-05-04T04:00:00", "time>2024-05-04T04:00:00 and time <=2024-05-04T06:00:00", "time>2024-05-04T06:00:00 and time <=2024-05-04T08:00:00", "time>2024-05-04T08:00:00 and time <=2024-05-04T10:00:00", "time>2024-05-04T10:00:00 and time <=2024-05-04T12:00:00", "time>2024-05-04T12:00:00 and time <=2024-05-04T14:00:00", "time>2024-05-04T14:00:00 and time <=2024-05-04T16:00:00", "time>2024-05-04T16:00:00 and time <=2024-05-04T18:00:00", "time>2024-05-04T18:00:00 and time <=2024-05-04T20:00:00", "time>2024-05-04T20:00:00 and time <=2024-05-04T22:00:00", "time>2024-05-04T22:00:00 and time <=2024-05-05T00:00:00" }; // for (String where : arr) { // process(table, where); // } String where = "time>=2024-05-05T12:00:00 and time <=2024-05-05T14:00:00"; process(table, where); } @Test void process(String table, String where) { String sql = "select Alarm_unit_1,Alarm_unit_2,Alarm_unit_3,Alarm_unit_4,Alarm_unit_5,Alarm_unit_6,Alarm_unit_7,Alarm_unit_8,Alarm_unit_9,Alarm_unit_10,Alarm_unit_11,Alarm_unit_12,Alarm_unit_13,Alarm_unit_14,Alarm_unit_15,Alarm_unit_16,Alarm_unit_17,Alarm_unit_18,Alarm_unit_19,Alarm_unit_20,Alarm_unit_21,Alarm_unit_22,Alarm_unit_23,Alarm_unit_24,Alarm_unit_25,Alarm_unit_26,Alarm_unit_27,Capacity_data_1,Capacity_data_2,Capacity_data_3,Capacity_data_4,Capacity_data_5,Capacity_data_6,Capacity_data_7,Capacity_data_8,Capacity_data_9,Capacity_data_10,Capacity_data_11,Capacity_data_12,Capacity_data_13,Capacity_data_14,Capacity_data_15,Capacity_data_16,Capacity_data_17,Capacity_data_18,Capacity_data_19,Capacity_data_20,Capacity_data_21,Capacity_data_22,Capacity_data_23,Capacity_data_24,Capacity_data_25,Capacity_data_26,Capacity_data_27,Capacity_data_28,Capacity_data_29,Capacity_data_30,Capacity_data_31,Capacity_data_32,Capacity_data_33,Capacity_data_34,Capacity_data_35,Capacity_data_36,Capacity_data_37,Capacity_data_38,Capacity_data_39,Capacity_data_40,Capacity_data_41,Capacity_data_42,Capacity_data_43,Capacity_data_44,Capacity_data_45,Capacity_data_46,Capacity_data_47,Capacity_data_48,Capacity_data_49,Capacity_data_50,Capacity_data_51,Capacity_data_52,Capacity_data_53,System_data_1,System_data_2,System_data_3,System_data_4,System_data_5,System_data_6,System_data_7,System_data_8,System_data_9,System_data_10,System_data_11,System_data_12,System_data_13,System_data_14,System_data_15,System_data_16,System_data_17,System_data_18,System_data_19,System_data_20,System_data_21,System_data_22,System_data_23,System_data_24,System_data_25,System_data_26,System_data_27,System_data_28,System_data_29,System_data_30,System_data_31,System_data_32,System_data_33,System_data_34,System_data_35,System_data_36,System_data_37,System_data_38,System_data_39,System_data_40,Formula_data_1,Formula_data_2,Formula_data_3,Formula_data_4,Formula_data_5,Formula_data_6,Formula_data_7,Formula_data_8,Formula_data_9,Formula_data_10,Formula_data_11,Formula_data_12,Formula_data_13,Formula_data_14,Formula_data_15,Formula_data_16,Formula_data_17,Formula_data_18,Formula_data_19,Formula_data_20,Formula_data_21,Formula_data_22,Formula_data_23,Formula_data_24,Formula_data_25,Formula_data_26,Formula_data_27,Formula_data_28,Formula_data_29,Formula_data_30,Formula_data_31,Formula_data_32,Formula_data_33,Formula_data_34,Formula_data_35" + " from " + table + " where " + where; // System.err.println(res); JSONObject jsonObject = iotService.query(sql); JSONObject data = jsonObject.getJSONObject("data"); JSONArray array = data.getJSONArray("values"); JSONArray columnNames = data.getJSONArray("columnNames"); JSONArray timestamps = data.getJSONArray("timestamps"); List list = new ArrayList<>(); for (int i = 0; i < timestamps.size(); i++) { Object[] arr = new Object[columnNames.size() + 1]; arr[0] = LocalDateTime.ofInstant(Instant.ofEpochMilli(timestamps.getLong(i)), ZoneId.systemDefault()); JSONArray da = array.getJSONArray(i); for (int j = 0; j < da.size(); j++) { arr[j + 1] = da.get(j); } list.add(arr); } sql = "INSERT INTO TWIN_DATA(DATA_TIME,Alarm_unit_1,Alarm_unit_2,Alarm_unit_3,Alarm_unit_4,Alarm_unit_5,Alarm_unit_6,Alarm_unit_7,Alarm_unit_8,Alarm_unit_9,Alarm_unit_10,Alarm_unit_11,Alarm_unit_12,Alarm_unit_13,Alarm_unit_14,Alarm_unit_15,Alarm_unit_16,Alarm_unit_17,Alarm_unit_18,Alarm_unit_19,Alarm_unit_20,Alarm_unit_21,Alarm_unit_22,Alarm_unit_23,Alarm_unit_24,Alarm_unit_25,Alarm_unit_26,Alarm_unit_27,Capacity_data_1,Capacity_data_2,Capacity_data_3,Capacity_data_4,Capacity_data_5,Capacity_data_6,Capacity_data_7,Capacity_data_8,Capacity_data_9,Capacity_data_10,Capacity_data_11,Capacity_data_12,Capacity_data_13,Capacity_data_14,Capacity_data_15,Capacity_data_16,Capacity_data_17,Capacity_data_18,Capacity_data_19,Capacity_data_20,Capacity_data_21,Capacity_data_22,Capacity_data_23,Capacity_data_24,Capacity_data_25,Capacity_data_26,Capacity_data_27,Capacity_data_28,Capacity_data_29,Capacity_data_30,Capacity_data_31,Capacity_data_32,Capacity_data_33,Capacity_data_34,Capacity_data_35,Capacity_data_36,Capacity_data_37,Capacity_data_38,Capacity_data_39,Capacity_data_40,Capacity_data_41,Capacity_data_42,Capacity_data_43,Capacity_data_44,Capacity_data_45,Capacity_data_46,Capacity_data_47,Capacity_data_48,Capacity_data_49,Capacity_data_50,Capacity_data_51,Capacity_data_52,Capacity_data_53,System_data_1,System_data_2,System_data_3,System_data_4,System_data_5,System_data_6,System_data_7,System_data_8,System_data_9,System_data_10,System_data_11,System_data_12,System_data_13,System_data_14,System_data_15,System_data_16,System_data_17,System_data_18,System_data_19,System_data_20,System_data_21,System_data_22,System_data_23,System_data_24,System_data_25,System_data_26,System_data_27,System_data_28,System_data_29,System_data_30,System_data_31,System_data_32,System_data_33,System_data_34,System_data_35,System_data_36,System_data_37,System_data_38,System_data_39,System_data_40,Formula_data_1,Formula_data_2,Formula_data_3,Formula_data_4,Formula_data_5,Formula_data_6,Formula_data_7,Formula_data_8,Formula_data_9,Formula_data_10,Formula_data_11,Formula_data_12,Formula_data_13,Formula_data_14,Formula_data_15,Formula_data_16,Formula_data_17,Formula_data_18,Formula_data_19,Formula_data_20,Formula_data_21,Formula_data_22,Formula_data_23,Formula_data_24,Formula_data_25,Formula_data_26,Formula_data_27,Formula_data_28,Formula_data_29,Formula_data_30,Formula_data_31,Formula_data_32,Formula_data_33,Formula_data_34,Formula_data_35)" + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; jdbcTemplate.batchUpdate(sql, list); } }