表格生成调整

whycxzp

2025-04-30 4fce1788cfb5bc37484060b068daf6303a584058

表格生成调整

 src/main/java/com/whyc/service/BattRealTimeDataHistoryService.java

@@ -269,4 +269,45 @@
        });
        return list;
    }

    /**
     * select * from tb_batt_realdata_100001_2025_04 where record_time in
     * (SELECT MIN(record_time) AS min_time FROM tb_batt_realdata_100001_2025_04
     * GROUP BY DATE(record_time))
     *
     * @param tableName
     * @param timeAgo
     * @return
     */
    public List<BattRealTimeDataHistory> getFirstRecordListOfDay(String tableName, Date timeAgo) {
        String sqlExtra;
        if(timeAgo!=null){
            sqlExtra = " where record_time > '" + timeAgo + "'";
        }else{
            sqlExtra = "";
        }
        String sql = "select * from db_power_history." + tableName + " where record_time in (SELECT MIN(record_time) FROM db_power_history." + tableName + sqlExtra +" GROUP BY DATE(record_time))";
        List<BattRealTimeDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {

            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<BattRealTimeDataHistory> list = new LinkedList<>();
                //如果存在记录
                while (rs.next()) {
                    BattRealTimeDataHistory his = new BattRealTimeDataHistory();
                    his.setTestStartTime(rs.getTimestamp("test_start_time"));
                    his.setRecordTime(rs.getTimestamp("record_time"));
                    his.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonRes(rs.getFloat("mon_res"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return list;
    }
}

 src/main/java/com/whyc/service/ExcelService.java

@@ -22,7 +22,9 @@
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

@Service
@@ -317,14 +319,14 @@
        XSSFCell cellOfSheet4 = sheet4.createRow(0).createCell(0);
        cellOfSheet4.setCellValue( battInfo.getPowerName()+ battInfo.getBattGroupName()+"温度数据");

        int columnCount = 2;
        //30天+2列其他列
        int columnCount = 30+2;
        //查询历史实时表. 存在的月份. 如果存在的话,取第一笔记录(104条)
        //首先查询当前月份对应的日期
        List<String> tableListLike = commonMapper.getTableListLike("db_power_history", "tb_batt_realdata_"+battGroupId);
        //当前月份往前推11个月
        //获取当前日期往前30天的凌晨零秒的数据. 往前推30天
        List<String> tableListInDB = new ArrayList<>();
        Calendar calendar = Calendar.getInstance();
        for (int i = 0; i < 12; i++) {
            int year = calendar.get(Calendar.YEAR);
            int month = calendar.get(Calendar.MONTH) + 1;
            String monthStr;
@@ -333,13 +335,49 @@
            } else {
                monthStr = String.valueOf(month);
            }
            if (tableListLike.contains("tb_batt_realdata_" + battGroupId + "_" + year + "_" + monthStr)) {
                //存在,列数+1
                columnCount++;
                tableListInDB.add("tb_batt_realdata_" + battGroupId + "_" + year + "_" + monthStr);
        //当前时间往前30天
        calendar.add(Calendar.DAY_OF_MONTH, -30);
        Date timeAgo = calendar.getTime();
        int yearAgo = calendar.get(Calendar.YEAR);
        int monthAgo = calendar.get(Calendar.MONTH) + 1;
        String monthAgoStr;
        if (monthAgo < 10) {
            monthAgoStr = "0" + (monthAgo);
        } else {
            monthAgoStr = String.valueOf(monthAgo);
            }
            calendar.add(Calendar.MONTH, -1);
        String tableName  = "tb_batt_realdata_" + battGroupId + "_" + year + "_" + monthStr;
        //30天前的时间
        String tableNameAgo = "tb_batt_realdata_" + battGroupId + "_" + yearAgo + "_" + monthAgoStr;
        if (tableListLike.contains(tableName)) {
            tableListInDB.add(tableName);
        }
        if (tableListLike.contains(tableNameAgo)) {
            tableListInDB.add(tableNameAgo);
        }

        //获取所有数据
        List<BattRealTimeDataHistory> dataList = new ArrayList<>();
        for (int i = 0; i < tableListInDB.size(); i++) {
            String tableNameTmp = tableListInDB.get(i);
            List<BattRealTimeDataHistory> list;
            if(tableNameTmp.equals(tableName)){
                list = battRTDataHisService.getFirstRecordListOfDay(tableNameTmp, null);
            }else{
                //说明是上个月的记录,需要记录时间大于30天前的时间
                list = battRTDataHisService.getFirstRecordListOfDay(tableNameTmp, timeAgo);
            }
            dataList.addAll(list);
        }
        //按时间排序和分组
        dataList.sort(Comparator.comparing(BattRealTimeDataHistory::getRecordTime));
        Map<Date, List<BattRealTimeDataHistory>> dataListMap = dataList.stream().collect(Collectors.groupingBy(BattRealTimeDataHistory::getRecordTime));
        Set<Date> dateKeySet = dataListMap.keySet();
        dataListMap.forEach((key,data)->{
            addStatistics(data, "tmp");
        });
        //给列数赋值
        columnCount = dateKeySet.size()+2;

        //列根据实际情况变动
        sheet4.addMergedRegion(new CellRangeAddress(0, 0,  0, columnCount-1));
@@ -348,7 +386,7 @@
            if(j==0){
                sheet4.setColumnWidth(j, 170 * 20);
            }else {
                sheet4.setColumnWidth(j, 128 * 20);
                sheet4.setColumnWidth(j, 138 * 20);
            }
        }
        //行宽
@@ -380,24 +418,42 @@
                } else if (i == 107) {
                    sheet4.createRow(i + 2).createCell(0).setCellValue("单节最小温度");
                }else if (i == 108) {
                    sheet4.createRow(i + 2).createCell(0).setCellValue("单节最大温度");
                    sheet4.createRow(i + 2).createCell(0).setCellValue("单节最大差值");
                }
            }
        }
        //创建第三行
        XSSFRow row2OfSheet4 = sheet4.createRow(2);

        row2OfSheet4.setHeightInPoints(40);
        sheet4.getRow(0).createCell(columnCount-1);
        for (int i = 0; i <= tableListInDB.size(); i++) { //这个是列的遍历
            if (i == tableListInDB.size()) {

        //填充数据列,key是列日期. value是列数据
        AtomicInteger count = new AtomicInteger();
        dataListMap.forEach((key, value) -> {
            //第三行时间列填充
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd\nHH:mm:ss");
            row2OfSheet4.createCell(count.get() + 1).setCellValue(format.format(key));
            for (int j = 1; j <= 108; j++) { //这个是行的遍历
                sheet4.getRow(j + 2).createCell(count.get() + 1).setCellValue(value.get(j - 1).getMonTmp());
            }
            count.getAndIncrement();
        });
        //最后一列备注:全部填空
        row2OfSheet4.createCell(columnCount-1);
        for (int j = 1; j <= 108; j++) { //这个是行的遍历
            sheet4.getRow(j + 2).createCell(columnCount-1);
        }

        /*for (int i = 0; i < columnCount; i++) { //这个是列的遍历
            if (i == columnCount - 1) {
                //最后一列备注:全部填空
                row2OfSheet4.createCell(i + 1);
                for (int j = 1; j <= 108; j++) { //这个是行的遍历
                    sheet4.getRow(j + 2).createCell(i + 1);
                    sheet4.getRow(j + 2).createCell(i );
                }
            } else {
                //获取数据
                List<BattRealTimeDataHistory> list = battRTDataHisService.getFirstRecordList(tableListInDB.get(i));
                List<BattRealTimeDataHistory> list = battRTDataHisService.getFirstRecordListOfDay(tableListInDB.get(i), timeAgo);
                list.sort(Comparator.comparing(BattRealTimeDataHistory::getMonNum));
                //第三行的时间列填充
                Date testStartTime = list.get(0).getTestStartTime();
@@ -409,7 +465,7 @@
                    sheet4.getRow(j + 2).createCell(i + 1).setCellValue((list.get(j - 1).getMonVol()));
                }
            }
        }
        }*/

        addGlobalStylesToAllCells(sheet4, workbook);

@@ -417,6 +473,16 @@
        XSSFCellStyle cellStyleOriginal = cellOfSheet4.getCellStyle();
        XSSFCellStyle cellStyleNew = getCellStyleFont(cellStyleOriginal,workbook, true, 15);
        cellOfSheet4.setCellStyle(cellStyleNew);

        //第三行 所有列水平垂直居中,自动换行
        for (int i = 1; i < columnCount; i++) {
            XSSFCell cell = sheet4.getRow(2).getCell(i);
            XSSFCellStyle cellStyle = cell.getCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setWrapText(true);
            cell.setCellStyle(cellStyle);
        }

        createChart(sheet4, columnCount,"温度折线图");

@@ -889,10 +955,19 @@
                //最大差值
                float tmpGap = tmpMax - tmpMin;

                Date now = new Date();

                hisAvg.setMonTmp(tmpAvg);
                hisAvg.setRecordTime(now);

                hisMax.setMonTmp(tmpMax);
                hisMax.setRecordTime(now);

                hisMin.setMonTmp(tmpMin);
                hisMin.setRecordTime(now);

                hisGap.setMonTmp(tmpGap);
                hisGap.setRecordTime(now);
            }