表格生成调整

whycxzp

2025-06-05 926a8b4621713460dd5622a5847edecda1e905ea

表格生成调整

 pom.xml

@@ -161,11 +161,43 @@
            <artifactId>commons-lang3</artifactId>
            <version>3.4</version>
        </dependency>
        <dependency>
        <!--<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-full</artifactId>
            <version>5.2.3</version>
        </dependency>
        <!-- OOXML Schemas -->
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>5.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-collections4</artifactId>
            <version>4.4</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.18.0</version>
        </dependency>

        <!--外部引入-人脸识别sdk-->
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>

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

@@ -245,4 +245,28 @@
        }
    }

    public List<BattRealTimeDataHistory> getFirstRecordList(String tableName) {
        String sql = "select * from db_power_history." + tableName + " limit 104";
        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.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

@@ -1,11 +1,20 @@
package com.whyc.service;

import com.whyc.mapper.CommonMapper;
import com.whyc.pojo.db_batt.PowerInf;
import com.whyc.pojo.db_dis_batt.BattTestInf;
import com.whyc.pojo.db_power_history.BattRealTimeDataHistory;
import com.whyc.util.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.CompoundLine;
import org.apache.poi.xddf.usermodel.XDDFLineProperties;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import org.openxmlformats.schemas.drawingml.x2006.main.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@@ -29,6 +38,9 @@
    //private static BattRealTimeDataHistoryService battRTDataHisService;
    private BattRealTimeDataHistoryService battRTDataHisService;

    @Autowired
    private CommonMapper  commonMapper;


    /**
     * 将电池组放电充电数据写入excel
@@ -42,7 +54,12 @@
        //创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建sheet1
        createSheet1(workbook,battGroupId,testRecordCount);
        //createSheet1(workbook,battGroupId,testRecordCount);
        //创建sheet2 -- 每月电压,12个月,取每月1号零点零分
        //创建sheet3 -- 每月内阻, 12个月,取每月1号零点零分
        createSheet2And3(workbook,battGroupId);
        //创建sheet4 -- 每天温度, 30天, 取每天零点零分
        createSheet4(workbook,battGroupId);

        // 将工作簿写入文件
        FileOutputStream fileOut = new FileOutputStream(filePath);
@@ -51,6 +68,379 @@
        // 关闭工作簿
        workbook.close();

    }

    private void createSheet2And3(XSSFWorkbook workbook, int battGroupId) {
        XSSFSheet sheet2 = workbook.createSheet("每月电压");
        XSSFSheet sheet3 = workbook.createSheet("每月内阻");
        //数据准备
        PowerInf battInfo = powerInfService.getByBattGroupId(battGroupId);
        //创建第一行
        XSSFCell cellOfSheet2 = sheet2.createRow(0).createCell(0);
        XSSFCell cellOfSheet3 = sheet3.createRow(0).createCell(0);
        cellOfSheet2.setCellValue( battInfo.getPowerName()+ battInfo.getBattGroupName()+"浮充电压");
        cellOfSheet3.setCellValue( battInfo.getPowerName()+ battInfo.getBattGroupName()+"内阻数据");

        int columnCount = 2;
        //查询历史实时表. 存在的月份. 如果存在的话,取第一笔记录(104条)
        //首先查询当前月份对应的日期
        List<String> tableListLike = commonMapper.getTableListLike("db_power_history", "tb_batt_realdata_"+battGroupId);
        //当前月份往前推11个月
        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;
             if (month < 10) {
                 monthStr = "0" + month;
             } else {
                 monthStr = String.valueOf(month);
             }
             if (tableListLike.contains("tb_batt_realdata_" + battGroupId + "_" + year + "_" + monthStr)) {
                 //存在,列数+1
                 columnCount++;
                 tableListInDB.add("tb_batt_realdata_" + battGroupId + "_" + year + "_" + monthStr);
             }
             calendar.add(Calendar.MONTH, -1);
         }

        //列根据实际情况变动
        sheet2.addMergedRegion(new CellRangeAddress(0, 0,  0, columnCount-1));
        sheet3.addMergedRegion(new CellRangeAddress(0, 0,  0, columnCount-1));
        //列宽
        for (int j = 0; j < columnCount; j++ ) {
            if(j==0){
                sheet2.setColumnWidth(j, 170 * 20);
                sheet3.setColumnWidth(j, 170 * 20);
            }else {
                sheet2.setColumnWidth(j, 128 * 20);
                sheet3.setColumnWidth(j, 128 * 20);
            }
        }
        //行宽
        sheet2.getRow(0).setHeightInPoints(40);
        sheet3.getRow(0).setHeightInPoints(40);
        //List元素升序排序
        tableListInDB.sort(Comparator.comparing(String::toString));

        //创建第二行
        XSSFRow row1OfSheet2 = sheet2.createRow(1);
        XSSFRow row1OfSheet3 = sheet3.createRow(1);
        row1OfSheet2.createCell(0).setCellValue("蓄电池号（#）");
        row1OfSheet3.createCell(0).setCellValue("蓄电池号（#）");
        //总列数-1
         for (int i = 0; i < columnCount - 1; i++) {
             if(i == columnCount-2){
                 row1OfSheet2.createCell(i+1).setCellValue("备注");
                 row1OfSheet3.createCell(i+1).setCellValue("备注");
             }else {
                 row1OfSheet2.createCell(i + 1).setCellValue("记录" + (i + 1));
                 row1OfSheet3.createCell(i + 1).setCellValue("记录" + (i + 1));
             }
        }

        //填充数据
        for (int i = 1; i <= 109; i++) {
            if(i<=104) {
                sheet2.createRow(i + 2).createCell(0).setCellValue(i + "#");
                sheet3.createRow(i + 2).createCell(0).setCellValue(i + "#");
            }else{
                if(i == 105){
                    sheet2.createRow(i + 2).createCell(0).setCellValue("单节平均电压");
                    sheet3.createRow(i + 2).createCell(0).setCellValue("单节平均内阻");
                }else if (i == 106){
                    sheet2.createRow(i + 2).createCell(0).setCellValue("单节最大电压");
                    sheet3.createRow(i + 2).createCell(0).setCellValue("单节最大内阻");
                } else if (i == 107) {
                    sheet2.createRow(i + 2).createCell(0).setCellValue("单节最小电压");
                    sheet3.createRow(i + 2).createCell(0).setCellValue("单节最小内阻");
                }else if (i == 108) {
                    sheet2.createRow(i + 2).createCell(0).setCellValue("单节最大差值");
                    sheet3.createRow(i + 2).createCell(0).setCellValue("单节最大内阻差值");
                }else {
                    sheet2.createRow(i + 2).createCell(0).setCellValue("均压系数");
                }
            }
        }
        //创建第三行
        XSSFRow row2OfSheet2 = sheet2.createRow(2);
        XSSFRow row2OfSheet3 = sheet3.createRow(2);

        sheet2.getRow(0).createCell(columnCount-1);
        sheet3.getRow(0).createCell(columnCount-1);
         for (int i = 0; i <= tableListInDB.size(); i++) { //这个是列的遍历
             if (i == tableListInDB.size()) {
                 //最后一列备注:全部填空
                 row2OfSheet2.createCell(i + 1);
                 row2OfSheet3.createCell(i + 1);
                 for (int j = 1; j <= 108; j++) { //这个是行的遍历
                     sheet2.getRow(j + 2).createCell(i + 1);
                     sheet3.getRow(j + 2).createCell(i + 1);
                 }
                 sheet2.getRow(108 + 2 + 1).createCell(i + 1);
             } else {
                 //获取数据
                 List<BattRealTimeDataHistory> list = battRTDataHisService.getFirstRecordList(tableListInDB.get(i));
                 list.sort(Comparator.comparing(BattRealTimeDataHistory::getMonNum));
                 //第三行的时间列填充
                 Date testStartTime = list.get(0).getTestStartTime();
                 //获取年月
                 String testMonth = DateUtil.YYYY_DOT_MM.format(testStartTime);
                 row2OfSheet2.createCell(i + 1).setCellValue(testMonth);
                 row2OfSheet3.createCell(i + 1).setCellValue(testMonth);
                 addStatistics(list, "vol", "res");
                 for (int j = 1; j <= 108; j++) { //这个是行的遍历
                     sheet2.getRow(j + 2).createCell(i + 1).setCellValue((list.get(j - 1).getMonVol()));
                     sheet3.getRow(j + 2).createCell(i + 1).setCellValue((list.get(j - 1).getMonRes()));
                 }
                 Float volAvg = list.get(104).getMonVol();
                 Float volMax = list.get(105).getMonVol();
                 sheet2.getRow(108 + 2 + 1).createCell(i + 1).setCellValue(new BigDecimal(volAvg).divide(new BigDecimal(volMax), 4, RoundingMode.HALF_UP).floatValue());
             }
         }

         addGlobalStylesToAllCells(sheet2, workbook);
         addGlobalStylesToAllCells(sheet3, workbook);

         //设置sheet2和sheet3的标题样式
        XSSFCellStyle cellStyleOriginal = cellOfSheet2.getCellStyle();
        XSSFCellStyle cellStyleNew = getCellStyleFont(cellStyleOriginal,workbook, true, 15);
        cellOfSheet2.setCellStyle(cellStyleNew);

        XSSFCellStyle cellStyleOriginal2 = cellOfSheet3.getCellStyle();
        XSSFCellStyle cellStyleNew2 = getCellStyleFont(cellStyleOriginal2,workbook, true, 15);
        cellOfSheet3.setCellStyle(cellStyleNew2);


        // 创建绘图工具
        createChart(sheet2, columnCount,"电压折线图");
        createChart(sheet3, columnCount,"内阻折线图");

    }

    private void createChart(XSSFSheet sheet2, int columnCount,  String titleTextSuffix) {
        XSSFDrawing drawing = sheet2.createDrawingPatriarch();
        //108个单体,10个单体一个图标
        int chartCol = 1;

        for (int i = 0; i < 104; i += 10) {
            //XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 15, chartCol, 25, chartCol+12);
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, columnCount+1, chartCol, columnCount+11, chartCol+12);
            //图表间每十二列一个间隔
            chartCol += 12;
            String titleText;
            if(i == 100){
                titleText = "101~104#蓄电池"+titleTextSuffix;
            }else{
                titleText = (i+1)+"~"+(i+10)+"#蓄电池"+titleTextSuffix;
            }
            // 创建图表
            XSSFChart chart = drawing.createChart(anchor);
            //chart.setTitleText(titleText);
            CTChart ctChart = chart.getCTChart();
            CTTitle title = ctChart.getTitle();
            if (title == null) {
                title = ctChart.addNewTitle();
            }
            // 创建文本体（如果不存在）
            CTTx tx = title.addNewTx();
            CTTextBody txBody = tx.isSetStrRef() ? null : tx.addNewRich();

            if (txBody == null) {
                txBody = tx.getRich();
            }

            // 清空已有段落
            txBody.setPArray(new CTTextParagraph[0]);

            // 添加新的段落和文本运行
            CTTextParagraph p = txBody.addNewP();
            CTRegularTextRun r = p.addNewR();
            r.setT(titleText);

            // 设置字体大小（14pt -> 1400）
            CTTextCharacterProperties rPr = r.addNewRPr();
            rPr.setSz(900); // 字号：14pt
            // 设置坐标轴
            XDDFCategoryAxis categoryAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            //categoryAxis.setTitle("电池编号");
            CTCatAx ctCatAx = getCTCatAx(categoryAxis, chart);
            if(ctCatAx.isSetTitle()){
                ctCatAx.unsetTitle();
            }

            XDDFValueAxis valueAxis = chart.createValueAxis(AxisPosition.LEFT);
            valueAxis.setCrosses(AxisCrosses.AUTO_ZERO);
            CTValAx ctValAx = getCTValAx(valueAxis, chart);
            if(ctValAx.isSetTitle()){
                ctValAx.unsetTitle();
            }

            //增加 网格线和图例
            XDDFShapeProperties gridProperties = valueAxis.getOrAddMajorGridProperties();
            XDDFLineProperties  lineProperties = new XDDFLineProperties();
            lineProperties.setCompoundLine(CompoundLine.SINGLE);
            lineProperties.setWidth(0.5);
            gridProperties.setLineProperties(lineProperties);

            chart.getOrAddLegend().setPosition(LegendPosition.TOP_RIGHT);
            chart.setTitleOverlay(true);

            // X轴数据源-记录编号
            //XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet2, new CellRangeAddress(2, 2, 1, 12));
            XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet2, new CellRangeAddress(2, 2, 1, columnCount-2));
            // 每个电池单体作为一个系列,对应一条折线
            // 创建折线图系列
            XDDFLineChartData dataChart = (XDDFLineChartData) chart.createData(ChartTypes.LINE, categoryAxis, valueAxis);
            for (int j = i  ; j < i+10; j++) {
                if(j<104) {
                    XDDFNumericalDataSource<Double> ys = XDDFDataSourcesFactory.fromNumericCellRange(sheet2, new CellRangeAddress(j + 3, j + 3, 1, columnCount -2));
                    XDDFLineChartData.Series series = (XDDFLineChartData.Series) dataChart.addSeries(xs, ys);
                    //选择布局1
                    series.setTitle(((XSSFRow) sheet2.getRow(j + 3)).getCell(0).getStringCellValue(), new CellReference(sheet2.getSheetName(), j + 3, 0, true, true));
                    series.setMarkerStyle(MarkerStyle.CIRCLE); // 设置标记点
                }

            }

            // 将数据填充到图表中
            chart.plot(dataChart);
        }
    }

    private void createSheet4(XSSFWorkbook workbook, int battGroupId) {
        XSSFSheet sheet4 = workbook.createSheet("每天温度");
        //数据准备
        PowerInf battInfo = powerInfService.getByBattGroupId(battGroupId);
        //创建第一行
        XSSFCell cellOfSheet4 = sheet4.createRow(0).createCell(0);
        cellOfSheet4.setCellValue( battInfo.getPowerName()+ battInfo.getBattGroupName()+"温度数据");

        int columnCount = 2;
        //查询历史实时表. 存在的月份. 如果存在的话,取第一笔记录(104条)
        //首先查询当前月份对应的日期
        List<String> tableListLike = commonMapper.getTableListLike("db_power_history", "tb_batt_realdata_"+battGroupId);
        //当前月份往前推11个月
        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;
            if (month < 10) {
                monthStr = "0" + month;
            } else {
                monthStr = String.valueOf(month);
            }
            if (tableListLike.contains("tb_batt_realdata_" + battGroupId + "_" + year + "_" + monthStr)) {
                //存在,列数+1
                columnCount++;
                tableListInDB.add("tb_batt_realdata_" + battGroupId + "_" + year + "_" + monthStr);
            }
            calendar.add(Calendar.MONTH, -1);
        }

        //列根据实际情况变动
        sheet4.addMergedRegion(new CellRangeAddress(0, 0,  0, columnCount-1));
        //列宽
        for (int j = 0; j < columnCount; j++ ) {
            if(j==0){
                sheet4.setColumnWidth(j, 170 * 20);
            }else {
                sheet4.setColumnWidth(j, 128 * 20);
            }
        }
        //行宽
        sheet4.getRow(0).setHeightInPoints(40);
        //List元素升序排序
        tableListInDB.sort(Comparator.comparing(String::toString));

        //创建第二行
        XSSFRow row1OfSheet4 = sheet4.createRow(1);
        row1OfSheet4.createCell(0).setCellValue("蓄电池号（#）");
        //总列数-1
        for (int i = 0; i < columnCount - 1; i++) {
            if(i == columnCount-2){
                row1OfSheet4.createCell(i+1).setCellValue("备注");
            }else {
                row1OfSheet4.createCell(i + 1).setCellValue("记录" + (i + 1));
            }
        }

        //填充数据
        for (int i = 1; i <= 109; i++) {
            if(i<=104) {
                sheet4.createRow(i + 2).createCell(0).setCellValue(i + "#");
            }else{
                if(i == 105){
                    sheet4.createRow(i + 2).createCell(0).setCellValue("单节平均温度");
                }else if (i == 106){
                    sheet4.createRow(i + 2).createCell(0).setCellValue("单节最大温度");
                } else if (i == 107) {
                    sheet4.createRow(i + 2).createCell(0).setCellValue("单节最小温度");
                }else if (i == 108) {
                    sheet4.createRow(i + 2).createCell(0).setCellValue("单节最大温度");
                }
            }
        }
        //创建第三行
        XSSFRow row2OfSheet4 = sheet4.createRow(2);

        sheet4.getRow(0).createCell(columnCount-1);
        for (int i = 0; i <= tableListInDB.size(); i++) { //这个是列的遍历
            if (i == tableListInDB.size()) {
                //最后一列备注:全部填空
                row2OfSheet4.createCell(i + 1);
                for (int j = 1; j <= 108; j++) { //这个是行的遍历
                    sheet4.getRow(j + 2).createCell(i + 1);
                }
            } else {
                //获取数据
                List<BattRealTimeDataHistory> list = battRTDataHisService.getFirstRecordList(tableListInDB.get(i));
                list.sort(Comparator.comparing(BattRealTimeDataHistory::getMonNum));
                //第三行的时间列填充
                Date testStartTime = list.get(0).getTestStartTime();
                //获取年月
                String testMonth = DateUtil.YYYY_DOT_MM.format(testStartTime);
                row2OfSheet4.createCell(i + 1).setCellValue(testMonth);
                addStatistics(list, "vol", "res");
                for (int j = 1; j <= 108; j++) { //这个是行的遍历
                    sheet4.getRow(j + 2).createCell(i + 1).setCellValue((list.get(j - 1).getMonVol()));
                }
            }
        }

        addGlobalStylesToAllCells(sheet4, workbook);

        //设置sheet4和sheet3的标题样式
        XSSFCellStyle cellStyleOriginal = cellOfSheet4.getCellStyle();
        XSSFCellStyle cellStyleNew = getCellStyleFont(cellStyleOriginal,workbook, true, 15);
        cellOfSheet4.setCellStyle(cellStyleNew);

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

    }
    public CTValAx getCTValAx(XDDFValueAxis valueAxis, XSSFChart chart) {
        // 使用反射获取私有字段
        try {
            java.lang.reflect.Field field = valueAxis.getClass().getDeclaredField("ctValAx");
            field.setAccessible(true);
            return (CTValAx) field.get(valueAxis);
        } catch (Exception e) {
            throw new RuntimeException("无法获取 CTValAx 实例", e);
        }
    }

    public CTCatAx getCTCatAx(XDDFCategoryAxis categoryAxis, XSSFChart chart) {
        // 使用反射获取私有字段
        try {
            java.lang.reflect.Field field = categoryAxis.getClass().getDeclaredField("ctCatAx");
            field.setAccessible(true);
            return (CTCatAx) field.get(categoryAxis);
        } catch (Exception e) {
            throw new RuntimeException("无法获取 CTCatAx 实例", e);
        }
    }

    public static void main(String[] args) throws IOException {
@@ -124,15 +514,11 @@
        //列数的计算= 蓄电池号(1行)+浮充电压值(2行)+放电前开路电压(2行)+放电特定时间电压(dischargeColumnCount行)+放电结束0.5h开路电压值(1行)+充电特定时间电压(chargeColumnCount行)+满电开路电压值(1行)+备注(1行)
        int columnCount = 1 + 2 + 2 + dischargeColumnCount + 1 + chargeColumnCount + 1 + 1;

        //字体加粗样式
        XSSFCellStyle cellStyleFontBold = workbook.createCellStyle();
        Font  font = workbook.createFont();
        font.setBold(true);
        cellStyleFontBold.setFont(font);
        //XSSFCellStyle cellStyleFontBold = getCellStyleFont(cellStyleOriginal, workbook,true,20);

        //创建第一行是标题行
        XSSFCell cellRow1 = sheet.createRow(0).createCell(0);
        setRowStyle(sheet.getRow(0),cellStyleFontBold);
        //setRowStyle(sheet.getRow(0),cellStyleFontBold);

        cellRow1.setCellValue(battInfo.getPowerName()+battInfo.getBattGroupName()+"核对性放电、充电试验记录表");
        //合并行列,占据的列是columnCount列合并为1列并居中
@@ -199,7 +585,7 @@
        cell6Row2.setCellValue("环境温度（℃）：24.5\n" +
                "环境湿度（%）：46.7");
        //第二行设置加粗
        setRowStyle(sheet.getRow(1),cellStyleFontBold);
        //setRowStyle(sheet.getRow(1),cellStyleFontBold);

        //第三行是电池号和电压等相关抬头
        XSSFRow row2 = sheet.createRow(2);
@@ -258,8 +644,8 @@
        List<BattRealTimeDataHistory> preVolList = new ArrayList<>();
        preVolList = battRTDataHisService.getPreVolList(battGroupId,dischargeStartTime);

        addStatistics(fcVolList);
        addStatistics(preVolList);
        addStatistics(fcVolList,"vol");
        addStatistics(preVolList,"vol");

        //一共104节单体,再加上统计数据4列,一共108
        for(int j = 0; j < 108; j++){
@@ -299,7 +685,7 @@
                List<BattRealTimeDataHistory> dischargeListOne = battRTDataHisService.getRecordList(battGroupId,recordTime);
                //按单体编号排序,按编号升序
                dischargeListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
                addStatistics(dischargeListOne);
                addStatistics(dischargeListOne,"vol");
                //rowTemp.createCell(5).setCellValue("放电0.5h电压值（V）:"+(j+1)+"#");
                //rowTemp.createCell(6).setCellValue("放电0.5h温度值:"+(j+1)+"#");
                rowTemp.createCell(5).setCellValue(dischargeListOne.get(j).getMonVol());
@@ -312,7 +698,7 @@
                    Date recordTimeTwo = dischargeCalendar.getTime();
                    List<BattRealTimeDataHistory> dischargeListTwo = battRTDataHisService.getRecordList(battGroupId,recordTimeTwo);
                    dischargeListTwo.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
                    addStatistics(dischargeListTwo);
                    addStatistics(dischargeListTwo,"vol");
                    //rowTemp.createCell(7).setCellValue("放电1h电压值（V）:"+(j+1)+"#");
                    //rowTemp.createCell(8).setCellValue("放电1h温度值:"+(j+1)+"#");
                    rowTemp.createCell(7).setCellValue(dischargeListTwo.get(j).getMonVol());
@@ -329,7 +715,7 @@
                            Date recordTimeN = dischargeCalendar.getTime();
                            List<BattRealTimeDataHistory> dischargeListN = battRTDataHisService.getRecordList(battGroupId,recordTimeN);
                            dischargeListN.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
                            addStatistics(dischargeListN);
                            addStatistics(dischargeListN,"vol");
                            //rowTemp.createCell(i+5).setCellValue("放电" + dischargeHour + "h蓄电池电压值（V）");
                            rowTemp.createCell(i+5).setCellValue(dischargeListN.get(j).getMonVol());
                            columnIndexTemp++;
@@ -345,7 +731,7 @@
            dischargeEndCalendar.add(Calendar.MINUTE,30);
            List<BattRealTimeDataHistory> dischargeSetVolListOne = battRTDataHisService.getRecordList(battGroupId, dischargeEndCalendar.getTime());
            dischargeSetVolListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
            addStatistics(dischargeSetVolListOne);
            addStatistics(dischargeSetVolListOne,"vol");
            rowTemp.createCell(++columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getMonVol());

            //定位到充电开始时间,状态变为充电的记录时间
@@ -356,7 +742,7 @@
                chargeCalendar.add(Calendar.MINUTE,30);
                List<BattRealTimeDataHistory> chargeVolListOne = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
                chargeVolListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
                addStatistics(chargeVolListOne);
                addStatistics(chargeVolListOne,"vol");
                columnIndexTemp++;
                //rowTemp.createCell(columnIndexTemp).setCellValue("充电0.5h");
                rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListOne.get(j).getMonVol());
@@ -370,7 +756,7 @@
                        chargeCalendar.add(Calendar.HOUR,i);
                        List<BattRealTimeDataHistory> chargeVolListN = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
                        chargeVolListN.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
                        addStatistics(chargeVolListN);
                        addStatistics(chargeVolListN,"vol");
                        //rowTemp.createCell(columnIndexTemp).setCellValue("充电" + (i) + "h蓄电池电压值（V）");
                        rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListN.get(j).getMonVol());
                    }
@@ -382,7 +768,7 @@
            chargeCalendar.setTime(chargeStartTime);
            List<BattRealTimeDataHistory> fcVolListAfter = battRTDataHisService.getFcVolListAfter(battGroupId, chargeCalendar.getTime());
            fcVolListAfter.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
            addStatistics(fcVolListAfter);
            addStatistics(fcVolListAfter,"vol");
            //rowTemp.createCell(++columnIndexTemp).setCellValue("充满后蓄电池电压值（V)（电流为0）");
            if (fcVolListAfter.size() > 0){
                rowTemp.createCell(++columnIndexTemp).setCellValue(fcVolListAfter.get(j).getMonVol());
@@ -399,41 +785,116 @@
        }
        //对整个表格进行全局设置,暂为设置边框
        addGlobalStylesToAllCells(sheet, workbook);

        //创建第一行是标题行
        setRowStyle(workbook,sheet.getRow(0),true,15);
        //第二行设置加粗
        setRowStyle(workbook,sheet.getRow(1),true,15);
    }

    //对传入的BattRealTimeDataHistory列表进行统计,将统计结果也追加到列表中
    public void addStatistics(List<BattRealTimeDataHistory> battRealTimeDataHistoryList) {
    private XSSFCellStyle getCellStyleFont(XSSFCellStyle cellStyleOriginal, XSSFWorkbook workbook, boolean isFontBold, int fontSize) {
        //字体加粗样式
        Font  font = workbook.createFont();
        font.setFontHeightInPoints((short) (fontSize));
        font.setBold(isFontBold);

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.cloneStyleFrom(cellStyleOriginal);
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }

    //对传入的BattRealTimeDataHistory列表进行统计,将统计结果也追加到列表中. 统计的为电压
    public void addStatistics(List<BattRealTimeDataHistory> battRealTimeDataHistoryList,String... fields) {
        List<String> fieldList = Arrays.asList(fields);
        if(battRealTimeDataHistoryList != null && battRealTimeDataHistoryList.size() > 0){
            //电压平均值,最大值,最小值,最大差值
            List<Float> volList = battRealTimeDataHistoryList.stream().map(BattRealTimeDataHistory::getMonVol).collect(Collectors.toList());
            Double volAvgDouble = volList.stream().collect(Collectors.averagingDouble(Float::floatValue));
            BigDecimal avg = new BigDecimal(volAvgDouble).setScale(4, RoundingMode.HALF_UP);
            float volAvg = avg.floatValue();

            //最大值
            float volMax = volList.stream().max(Float::compareTo).get();
            BigDecimal max = new BigDecimal(volMax).setScale(4, RoundingMode.HALF_UP);
            volMax = max.floatValue();

            //最小值
            float volMin = volList.stream().min(Float::compareTo).get();
            BigDecimal min = new BigDecimal(volMin).setScale(4, RoundingMode.HALF_UP);
            volMin = min.floatValue();

            //最大差值
            float volGap = volMax - volMin;

            BattRealTimeDataHistory hisAvg = new BattRealTimeDataHistory();
            hisAvg.setMonVol(volAvg);

            BattRealTimeDataHistory hisMax = new BattRealTimeDataHistory();
            hisMax.setMonVol(volMax);

            BattRealTimeDataHistory hisMin = new BattRealTimeDataHistory();
            hisMin.setMonVol(volMin);

            BattRealTimeDataHistory hisGap = new BattRealTimeDataHistory();
            hisGap.setMonVol(volGap);
            if(fieldList.contains("vol")) {
                //电压平均值,最大值,最小值,最大差值
                List<Float> volList = battRealTimeDataHistoryList.stream().map(BattRealTimeDataHistory::getMonVol).collect(Collectors.toList());
                Double volAvgDouble = volList.stream().collect(Collectors.averagingDouble(Float::floatValue));
                BigDecimal avg = new BigDecimal(volAvgDouble).setScale(4, RoundingMode.HALF_UP);
                float volAvg = avg.floatValue();

                //最大值
                float volMax = volList.stream().max(Float::compareTo).get();
                BigDecimal max = new BigDecimal(volMax).setScale(4, RoundingMode.HALF_UP);
                volMax = max.floatValue();

                //最小值
                float volMin = volList.stream().min(Float::compareTo).get();
                BigDecimal min = new BigDecimal(volMin).setScale(4, RoundingMode.HALF_UP);
                volMin = min.floatValue();

                //最大差值
                float volGap = volMax - volMin;

                hisAvg.setMonVol(volAvg);

                hisMax.setMonVol(volMax);

                hisMin.setMonVol(volMin);

                hisGap.setMonVol(volGap);
            }

            if(fieldList.contains("res")) {
                //平均值,最大值,最小值,最大差值
                List<Float> resList = battRealTimeDataHistoryList.stream().map(BattRealTimeDataHistory::getMonRes).collect(Collectors.toList());
                Double resAvgDouble = resList.stream().collect(Collectors.averagingDouble(Float::floatValue));
                BigDecimal avg = new BigDecimal(resAvgDouble).setScale(4, RoundingMode.HALF_UP);
                float resAvg = avg.floatValue();

                //最大值
                float resMax = resList.stream().max(Float::compareTo).get();
                BigDecimal max = new BigDecimal(resMax).setScale(4, RoundingMode.HALF_UP);
                resMax = max.floatValue();

                //最小值
                float resMin = resList.stream().min(Float::compareTo).get();
                BigDecimal min = new BigDecimal(resMin).setScale(4, RoundingMode.HALF_UP);
                resMin = min.floatValue();

                //最大差值
                float resGap = resMax - resMin;

                hisAvg.setMonRes(resAvg);
                hisMax.setMonRes(resMax);
                hisMin.setMonRes(resMin);
                hisGap.setMonRes(resGap);
            }

            if(fieldList.contains("tmp")) {
                //平均值,最大值,最小值,最大差值
                List<Float> tmpList = battRealTimeDataHistoryList.stream().map(BattRealTimeDataHistory::getMonTmp).collect(Collectors.toList());
                Double tmpAvgDouble = tmpList.stream().collect(Collectors.averagingDouble(Float::floatValue));
                BigDecimal avg = new BigDecimal(tmpAvgDouble).setScale(4, RoundingMode.HALF_UP);
                float tmpAvg = avg.floatValue();

                //最大值
                float tmpMax = tmpList.stream().max(Float::compareTo).get();
                BigDecimal max = new BigDecimal(tmpMax).setScale(4, RoundingMode.HALF_UP);
                tmpMax = max.floatValue();

                //最小值
                float tmpMin = tmpList.stream().min(Float::compareTo).get();
                BigDecimal min = new BigDecimal(tmpMin).setScale(4, RoundingMode.HALF_UP);
                tmpMin = min.floatValue();

                //最大差值
                float tmpGap = tmpMax - tmpMin;

                hisAvg.setMonTmp(tmpAvg);
                hisMax.setMonTmp(tmpMax);
                hisMin.setMonTmp(tmpMin);
                hisGap.setMonTmp(tmpGap);
            }


            battRealTimeDataHistoryList.add(hisAvg);
            battRealTimeDataHistoryList.add(hisMax);
@@ -442,9 +903,19 @@
        }
    }

    public void setRowStyle(Row row, CellStyle style) {
    public void setRowStyle(Workbook workbook,Row row,boolean isFontBold,int fontSize) {
        for (Cell cell : row) {
            cell.setCellStyle(style);
            //先克隆原来的属性
            CellStyle cellStyleNew = workbook.createCellStyle();
            cellStyleNew.cloneStyleFrom(cell.getCellStyle());

            //设置字体和加粗
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) (fontSize));
            font.setBold(isFontBold);

            cellStyleNew.setFont(font);
            cell.setCellStyle(cellStyleNew);
        }
    }


 src/main/java/com/whyc/util/DateUtil.java

@@ -14,6 +14,7 @@
    public static SimpleDateFormat YYYY_MM_DD_HH_MM_SS_UNION=new SimpleDateFormat("yyyyMMddHHmmss");
    public static SimpleDateFormat YYYY_MM_DD=new SimpleDateFormat("yyyy-MM-dd");
    public static SimpleDateFormat YYYY_MM=new SimpleDateFormat("yyyy-MM");
    public static SimpleDateFormat YYYY_DOT_MM=new SimpleDateFormat("yyyy.MM");
    public static SimpleDateFormat YYYY_MM_TABLE=new SimpleDateFormat("yyyy_MM");

    public static int compare(String date1Str,String date2Str) throws ParseException {