excel导出

whycxzp

2025-05-30 ab0fbf96697a57d293111135ef46d735e17895b9

excel导出

 src/main/java/com/whyc/controller/ExcelController.java

New file
@@ -0,0 +1,28 @@
package com.whyc.controller;

import com.whyc.service.ExcelService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.IOException;

@RequestMapping("excel")
@RestController
@Api("excel导出")
public class ExcelController {

    @Autowired
    private ExcelService service;

    @GetMapping("dcReport")
    @ApiOperation(value = "直流报表")
    public void dcReport(int battGroupId,int testRecordCount) throws IOException {
        service.dcReport(battGroupId,testRecordCount);
    }


}

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

@@ -2,19 +2,29 @@

import com.github.pagehelper.PageInfo;
import com.whyc.dto.Response;
import com.whyc.mapper.CallBack;
import com.whyc.pojo.db_power_history.BattRealTimeDataHistory;
import com.whyc.util.SubTablePageInfoUtil;
import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

@Service
public class BattRealTimeDataHistoryService {

    @Autowired
    private SubTablePageInfoUtil subTablePageInfoUtil;

    @Autowired
    private MybatisSqlExecuteService sqlExecuteService;

    public Response<PageInfo> getPage(int pageNum, int pageSize, int battGroupId,
                                                               String startTime, String endTime) throws ParseException, InterruptedException {
@@ -28,4 +38,211 @@
        return new Response<PageInfo>().set(1, pageInfo);
    }

    /**
     * 充电结束时间的定位逻辑为:
     * _电池组id表中,记录时间大于dischargeEndTime且电流为0的记录中,顺序取出第一个记录.
     * 考虑到充电结束时间可能跨表,所以需要查询下一个月的表.
     * @return
     */
    public BattRealTimeDataHistory getChargeEnd(int battGroupId, Calendar dischargeEndTime) {
        //查询的年月为dischargeEndTime的年月
        int year = dischargeEndTime.get(Calendar.YEAR);
        int month = dischargeEndTime.get(Calendar.MONTH);
        //String tableName = "tb_batt_realdata_" + battGroupId + "_" + year + "_" + (month+1);
        //查询dischargeEndTime的年月加上一个月
        int yearOfNextMonth = year;
        int monthOfNextMonth = month;
        if (month == 11) {
            yearOfNextMonth++;
            monthOfNextMonth = 0;
        }else{
            monthOfNextMonth++;
        }
        String battGroupIdTable = getBattGroupIdTable(battGroupId, dischargeEndTime.getTime());
        String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time > '" + dischargeEndTime.getTime() + "' and group_curr = 0 limit 1";
        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.setBattGroupId(rs.getInt("binf_id"));
                    his.setRecordTime(rs.getTimestamp("record_time"));
                    his.setOnlineVol(rs.getFloat("online_vol"));
                    his.setGroupVol(rs.getFloat("group_vol"));
                    his.setGroupTmp(rs.getFloat("group_tmp"));
                    his.setGroupCurr(rs.getFloat("group_curr"));

                    list.add(his);
                }
                return list;

            }
        });
        //如果不存在记录,则查询下一个月的表
        if (list.size() == 0) {
            //String tableNameNextMonth = "tb_batt_realdata_" + battGroupId + "_" + yearOfNextMonth + "_" + (monthOfNextMonth+1);
            dischargeEndTime.add(Calendar.MONTH, 1);
            String battGroupITableNameNextMonth = getBattGroupIdTable(battGroupId, dischargeEndTime.getTime());
            //还原dischargeEndTime
            dischargeEndTime.add(Calendar.MONTH, -1);
            String sqlNextMonth = "select * from db_power_history." + battGroupITableNameNextMonth + " where record_time > '" + dischargeEndTime.getTime() + "' and group_curr = 0 limit 1";
            List<BattRealTimeDataHistory> listNextMonth = sqlExecuteService.executeQuery_call(sqlNextMonth, new CallBack() {

                @Override
                public List getResults(ResultSet rs) throws SQLException {
                    List<BattRealTimeDataHistory> list = new LinkedList<>();
                    //如果存在记录
                    while (rs.next()) {
                        BattRealTimeDataHistory his = new BattRealTimeDataHistory();
                        his.setBattGroupId(rs.getInt("binf_id"));
                        his.setRecordTime(rs.getTimestamp("record_time"));
                        his.setOnlineVol(rs.getFloat("online_vol"));
                        his.setGroupVol(rs.getFloat("group_vol"));
                        his.setGroupTmp(rs.getFloat("group_tmp"));
                        his.setGroupCurr(rs.getFloat("group_curr"));

                        list.add(his);
                    }
                    return list;

                }
            });
            return listNextMonth.size() == 0 ? null : listNextMonth.get(0);
        }else{
            return list.get(0);
        }
    }

    public List<BattRealTimeDataHistory> getFcVolList(int battGroupId, Date testStartTime) {
        String battGroupIdTable = getBattGroupIdTable(battGroupId, testStartTime);
        String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time < '" + testStartTime + "' and  batt_test_type = 1 order by num desc limit 104";
        List<BattRealTimeDataHistory> fcVolList = 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.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return fcVolList;
    }

    public List<BattRealTimeDataHistory> getPreVolList(int battGroupId, Date testStartTime) {
        String battGroupIdTable = getBattGroupIdTable(battGroupId, testStartTime);
        String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time = '" + testStartTime + "' order by mon_num asc limit 104";
        List<BattRealTimeDataHistory> fcVolList = 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.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return fcVolList;
    }

    public List<BattRealTimeDataHistory> getRecordList(int battGroupId, Date recordTime) {
        String battGroupIdTable = getBattGroupIdTable(battGroupId, recordTime);
        String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time >= '" + recordTime + "' order by num asc limit 104";
        List<BattRealTimeDataHistory> fcVolList = 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.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return fcVolList;
    }

    public Date getChargeStartTime(int battGroupId, Date recordTime) {
        String battGroupIdTable = getBattGroupIdTable(battGroupId, recordTime);
        String sql = "select record_time from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time > '" + recordTime + "' and batt_test_type = 2 order limit 1";
        List<Date> timeList = sqlExecuteService.executeQuery_call(sql, new CallBack() {

            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<Date> list = new LinkedList<>();
                //如果存在记录
                while (rs.next()) {
                    Date recordTime = rs.getTimestamp("record_time");

                    list.add(recordTime);
                }
                return list;

            }
        });
        return timeList.get(0);
    }

    public List<BattRealTimeDataHistory> getFcVolListAfter(int battGroupId, Date testStartTime) {
        String battGroupIdTable = getBattGroupIdTable(battGroupId, testStartTime);
        String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time > '" + testStartTime + "' and  batt_test_type = 1 order by num desc limit 104";
        List<BattRealTimeDataHistory> fcVolList = 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.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return fcVolList;
    }

    private String getBattGroupIdTable(int battGroupId, Date time) {
        Calendar timeCalendar = Calendar.getInstance();
        timeCalendar.setTime(time);
        int year = timeCalendar.get(Calendar.YEAR);
        int month = timeCalendar.get(Calendar.MONTH)+1;
        if (month < 10){
            return battGroupId + "_" + year + "_0" + month;
        }else{
            return battGroupId + "_" + month + "_" + month;
        }
    }

}

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

@@ -1,25 +1,48 @@
package com.whyc.service;

import com.whyc.util.ThreadLocalUtil;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.whyc.pojo.db_batt.PowerInf;
import com.whyc.pojo.db_dis_batt.BattTestInf;
import com.whyc.pojo.db_power_history.BattRealTimeDataHistory;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class ExcelService {

    public void dcReport() throws IOException {
        String filePath = "C:\\Users\\29550\\Desktop\\当前项目\\2023\\0智能机器人运维系统\\测试.xlsx";
    @Autowired
    private PowerInfService powerInfService;

    @Autowired
    private BattTestInfService battTestInfService;

    @Autowired
    //private static BattRealTimeDataHistoryService battRTDataHisService;
    private BattRealTimeDataHistoryService battRTDataHisService;


    /**
     * 将电池组放电充电数据写入excel
     * 传入参数:
     *  电池组id,放电第几次
     *
     * @throws IOException
     */
    public void dcReport(int battGroupId,int testRecordCount) throws IOException {
        String filePath = "C:\\Users\\29550\\Desktop\\当前项目\\2023\\0乌东德系统\\测试.xlsx";
        //创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建sheet1
        createSheet1(workbook);


        createSheet1(workbook,battGroupId,testRecordCount);

        // 将工作簿写入文件
        FileOutputStream fileOut = new FileOutputStream(filePath);
@@ -30,13 +53,420 @@

    }

    private void createSheet1(XSSFWorkbook workbook) {
    public static void main(String[] args) throws IOException {
        //dcReport(1,1);
    }

    //private static void createSheet1(XSSFWorkbook workbook, int battGroupId, int testRecordCount) {
    private void createSheet1(XSSFWorkbook workbook, int battGroupId, int testRecordCount) {
        XSSFSheet sheet = workbook.createSheet("放电充电电压数据报表");
        //总的列数根据 放电时长和充电时长决定
        Date dischargeStartTime = ThreadLocalUtil.parse("2023-01-01 11:00:00", 1);
        Date dischargeEndTime = ThreadLocalUtil.parse("2023-01-02 01:00:00", 1);
        //查询时长,按小时
        //数据准备
        PowerInf battInfo = powerInfService.getByBattGroupId(battGroupId);
        //TODO 测试数据
        //PowerInf battInfo = new PowerInf();
        //battInfo.setPowerName("测试直流系统");
        //battInfo.setBattGroupName("蓄电池组1");
        BattTestInf battTestInf = battTestInfService.getDischargeRecord(battGroupId,testRecordCount);
        //TODO 测试数据
        //BattTestInf battTestInf = new BattTestInf();
        //battTestInf.setTestTimelong(36000);
        //battTestInf.setTestStarttime(new Date());
        //单位是秒,转化为分钟
        Integer testTimeLong = battTestInf.getTestTimelong();
        Integer testTimeMinutes =  testTimeLong /60;
        //testTimeLong转为小时.保留1位小数,取小进行切割
        Float testTimeHours = BigDecimal.valueOf(testTimeLong).divide(BigDecimal.valueOf(60 * 60), 1, RoundingMode.FLOOR).floatValue();
        //获取放电开始时间和终止时间
        Date dischargeStartTime = battTestInf.getTestStarttime();
        //终止时间为开始时间加 测试时长
        Calendar dischargeEndTime = Calendar.getInstance();
        dischargeEndTime.setTime(dischargeStartTime);
        dischargeEndTime.add(Calendar.SECOND, testTimeLong);
        //放电结束后的充电终止记录
        //BattRealTimeDataHistory hisChargeEnd = battRTDataHisService.getChargeEnd(battGroupId,dischargeEndTime);
        //TODO 测试数据
        //BattRealTimeDataHistory hisChargeEnd = new BattRealTimeDataHistory();
        //Calendar  chargeEndTime = Calendar.getInstance();
        //chargeEndTime.setTime(new Date());
        //chargeEndTime.add(Calendar.HOUR,15);
        //hisChargeEnd.setRecordTime(chargeEndTime.getTime());
        //充电时长=充电终止时间-放电终止时间
        long chargeTimeSeconds = (dischargeEndTime.getTime().getTime() - dischargeStartTime.getTime()) / 1000;
        //long chargeTimeSeconds = (hisChargeEnd.getRecordTime().getTime() - dischargeStartTime.getTime()) / 1000;
        //chargeTimeSeconds转为小时.保留1位小数,取小进行切割
        Float chargeTimeHours = BigDecimal.valueOf(chargeTimeSeconds).divide(BigDecimal.valueOf(60 * 60), 1, RoundingMode.FLOOR).floatValue();
        //行数是固定的.总列数是根据放电时长和充电时长决定的
        //放电时长导致的列数计算,列为0.5h,1h,2h,3h,...;
        //小于0.5h,没列. 0.5h~1h之间,1列-0.5h. 大于1h,1+ 放电时长的列数.
        int dischargeColumnCount;
        if (testTimeHours < 0.5) {
            dischargeColumnCount = 0;
        } else if (testTimeHours >= 0.5 && testTimeHours < 1) {
            //0.5h 占两列
            dischargeColumnCount = 2;
        }
        else if (testTimeHours >= 1 && testTimeHours < 2) {
            //0.5h 占两列,1h 占两列
            dischargeColumnCount = 4;
        } else {
            dischargeColumnCount = (int) (testTimeHours + 3);
        }
        //充电时长导致的列数计算,列为0.5h,1h,2h,3h,...;
        int chargeColumnCount;
        if (chargeTimeHours < 0.5) {
            chargeColumnCount = 0;
        } else if (chargeTimeHours >= 0.5 && chargeTimeHours < 1) {
            chargeColumnCount = 1;
        } else {
            chargeColumnCount = (int) (chargeTimeHours + 1);
        }
        //列数的计算= 蓄电池号(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);

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

        cellRow1.setCellValue(battInfo.getPowerName()+battInfo.getBattGroupName()+"核对性放电、充电试验记录表");
        //合并行列,占据的列是columnCount列合并为1列并居中
        CellRangeAddress region = new CellRangeAddress(0, 0,  0, columnCount-1);
        sheet.addMergedRegion(region);

        //TODO 第二行的合并列是根据变动的,后续调整
        CellRangeAddress region21 = new CellRangeAddress(1, 1,  0, 1);
        sheet.addMergedRegion(region21);

        CellRangeAddress region22 = new CellRangeAddress(1, 1,  3, 9);
        sheet.addMergedRegion(region22);

        CellRangeAddress region23 = new CellRangeAddress(1, 1,  10, 13);
        sheet.addMergedRegion(region23);

        CellRangeAddress region24 = new CellRangeAddress(1, 1,  14, 17);
        sheet.addMergedRegion(region24);

        CellRangeAddress region25 = new CellRangeAddress(1, 1,  18, 24);
        sheet.addMergedRegion(region25);

        //第三行的合并列
        CellRangeAddress region31 = new CellRangeAddress(2, 2,  1, 2);
        CellRangeAddress region32 = new CellRangeAddress(2, 2,  3, 4);
        CellRangeAddress region33 = new CellRangeAddress(2, 2,  5, 6);
        CellRangeAddress region34 = new CellRangeAddress(2, 2,  7, 8);
        sheet.addMergedRegion(region31);
        sheet.addMergedRegion(region32);
        sheet.addMergedRegion(region33);
        sheet.addMergedRegion(region34);

        // 创建居中样式
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 应用样式到单元格
        cellRow1.setCellStyle(style);

        //第二行是放电基本信息
        XSSFRow row1 = sheet.createRow(1);
        XSSFCell cell1Row2 = row1.createCell(0);
        cell1Row2.setCellValue("蓄电池组号:" + battInfo.getBattGroupName());

        XSSFCell cell2Row2 = row1.createCell(2);
        cell2Row2.setCellValue("");

        XSSFCell cell3Row2 = row1.createCell(3);
        cell3Row2.setCellValue("工作票号：2273925\n" +
                "工作负责人：马云燕\n" +
                "工作成员：周素文、李志标、王月灿等");

        XSSFCell cell4Row2 = row1.createCell(10);
        cell4Row2.setCellValue("放电日期：2021.10.30\n" +
                "放电开始时间：10:00\n" +
                "放电结束时间：19:47");

        XSSFCell cell5Row2 = row1.createCell(14);
        cell5Row2.setCellValue("放电电流（A）：35\n" +
                "放电时长（时.分）：600min\n" +
                "放电容量（Ah）：300");

        XSSFCell cell6Row2 = row1.createCell(18);
        cell6Row2.setCellValue("环境温度（℃）：24.5\n" +
                "环境湿度（%）：46.7");
        //第二行设置加粗
        setRowStyle(sheet.getRow(1),cellStyleFontBold);

        //第三行是电池号和电压等相关抬头
        XSSFRow row2 = sheet.createRow(2);
        row2.createCell(0).setCellValue("蓄电池号（#）");
        row2.createCell(1).setCellValue("蓄电池浮充电压值（V）");
        row2.createCell(3).setCellValue("放电前蓄电池开路电压值（V）");

        //放电特定电压值
        int columnIndex = 3;
        if(dischargeColumnCount > 0){
            row2.createCell(5).setCellValue("放电0.5h蓄电池电压值（V）");
            columnIndex+=2;
            if(dischargeColumnCount > 2){ //合并单元格了,所以索引+2
                row2.createCell(7).setCellValue("放电1h蓄电池电压值（V）");
                columnIndex+=2;
                if(dischargeColumnCount > 4){
                    columnIndex++;
                    //放电nh蓄电池电压值（V）,n从2开始
                    for(int i = 4;i < dischargeColumnCount;i++){
                        row2.createCell(i+5).setCellValue("放电" + (i-2) + "h蓄电池电压值（V）");
                        columnIndex++;
                    }

                }
            }
        }

        //充电特定电压值
        row2.createCell(++columnIndex).setCellValue("放电结束0.5h后蓄电池开路电压值（V）");
        if(chargeColumnCount > 0){
            columnIndex++;
            row2.createCell(columnIndex).setCellValue("充电0.5h");

            if(chargeColumnCount > 1){
                //充电nh,n从1开始
                for(int i = 1;i < chargeColumnCount;i++){
                    columnIndex++;
                    row2.createCell(columnIndex).setCellValue("充电" + (i) + "h蓄电池电压值（V）");
                }

            }
        }
        row2.createCell(++columnIndex).setCellValue("充满后蓄电池电压值（V)（电流为0）");
        row2.createCell(++columnIndex).setCellValue("备注");


        //从rowIndex=6开始
        int rowIndex = 6;
        // 共104节单体,104行. 进行遍历,填充
        //蓄电池浮充电压值列表
        List<BattRealTimeDataHistory> fcVolList = new ArrayList<>();
        fcVolList = battRTDataHisService.getFcVolList(battGroupId,dischargeStartTime);
        //按单体编号排序,按编号升序
        fcVolList.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
        //放点前蓄电池开路电压值
        List<BattRealTimeDataHistory> preVolList = new ArrayList<>();
        preVolList = battRTDataHisService.getPreVolList(battGroupId,dischargeStartTime);

        addStatistics(fcVolList);
        addStatistics(preVolList);

        //一共104节单体,再加上统计数据4列,一共108
        for(int j = 0; j < 108; j++){
            XSSFRow rowTemp = sheet.createRow(rowIndex++);
            if(j<104) {
                rowTemp.createCell(0).setCellValue((j + 1) + "#");
            }else{
                if(j == 104){
                    rowTemp.createCell(0).setCellValue("单节平均电压");
                }else if (j == 105){
                    rowTemp.createCell(0).setCellValue("单节最大电压");
                }else if (j == 106){
                    rowTemp.createCell(0).setCellValue("单节最小电压");
                }else{
                    rowTemp.createCell(0).setCellValue("单节最大差值");
                }
            }
            //rowTemp.createCell(1).setCellValue("蓄电池浮充电压值（V）:"+(j+1)+"#");
            //rowTemp.createCell(2).setCellValue("蓄电池浮充温度值:"+(j+1)+"#");
            //rowTemp.createCell(3).setCellValue("放电前蓄电池开路电压值（V）:"+(j+1)+"#");
            //rowTemp.createCell(4).setCellValue("放电前蓄电池开路温度值:"+(j+1)+"#");
            rowTemp.createCell(1).setCellValue(fcVolList.get(j).getMonVol());
            rowTemp.createCell(1).setCellValue(fcVolList.get(j).getMonTmp());

            rowTemp.createCell(1).setCellValue(preVolList.get(j).getMonVol());
            rowTemp.createCell(1).setCellValue(preVolList.get(j).getMonTmp());


            //放电特定电压值
            int columnIndexTemp = 3;
            Calendar dischargeCalendar = Calendar.getInstance();
            dischargeCalendar.setTime(dischargeStartTime);
            if(dischargeColumnCount > 0){
                //时间在测试开始时间+0.5h
                dischargeCalendar.add(Calendar.MINUTE,30);
                Date recordTime = dischargeCalendar.getTime();
                List<BattRealTimeDataHistory> dischargeListOne = battRTDataHisService.getRecordList(battGroupId,recordTime);
                //按单体编号排序,按编号升序
                dischargeListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
                addStatistics(dischargeListOne);
                //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());
                rowTemp.createCell(6).setCellValue(dischargeListOne.get(j).getMonTmp());
                columnIndexTemp+=2;
                if(dischargeColumnCount > 2){ //合并单元格了,所以索引+2
                    //时间在测试开始时间+1h
                    dischargeCalendar.setTime(dischargeStartTime);
                    dischargeCalendar.add(Calendar.HOUR,1);
                    Date recordTimeTwo = dischargeCalendar.getTime();
                    List<BattRealTimeDataHistory> dischargeListTwo = battRTDataHisService.getRecordList(battGroupId,recordTimeTwo);
                    dischargeListTwo.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
                    addStatistics(dischargeListTwo);
                    //rowTemp.createCell(7).setCellValue("放电1h电压值（V）:"+(j+1)+"#");
                    //rowTemp.createCell(8).setCellValue("放电1h温度值:"+(j+1)+"#");
                    rowTemp.createCell(7).setCellValue(dischargeListTwo.get(j).getMonVol());
                    rowTemp.createCell(8).setCellValue(dischargeListTwo.get(j).getMonTmp());
                    columnIndexTemp+=2;
                    if(dischargeColumnCount > 4){
                        columnIndexTemp++;
                        //放电nh蓄电池电压值（V）,n从2开始
                        for(int i = 4;i < dischargeColumnCount;i++){
                            int dischargeHour = i-2;
                            //时间在测试开始时间+nh
                            dischargeCalendar.setTime(dischargeStartTime);
                            dischargeCalendar.add(Calendar.HOUR,dischargeHour);
                            Date recordTimeN = dischargeCalendar.getTime();
                            List<BattRealTimeDataHistory> dischargeListN = battRTDataHisService.getRecordList(battGroupId,recordTimeN);
                            dischargeListN.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
                            addStatistics(dischargeListN);
                            //rowTemp.createCell(i+5).setCellValue("放电" + dischargeHour + "h蓄电池电压值（V）");
                            rowTemp.createCell(i+5).setCellValue(dischargeListN.get(j).getMonVol());
                            columnIndexTemp++;
                        }

                    }
                }
            }

            //充电特定电压值
            Calendar dischargeEndCalendar = Calendar.getInstance();
            dischargeEndCalendar.setTime(dischargeEndTime.getTime());
            dischargeEndCalendar.add(Calendar.MINUTE,30);
            List<BattRealTimeDataHistory> dischargeSetVolListOne = battRTDataHisService.getRecordList(battGroupId, dischargeEndCalendar.getTime());
            dischargeSetVolListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
            addStatistics(dischargeSetVolListOne);
            rowTemp.createCell(++columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getMonVol());

            //定位到充电开始时间,状态变为充电的记录时间
            Date chargeStartTime = battRTDataHisService.getChargeStartTime(battGroupId, dischargeEndTime.getTime());
            if(chargeColumnCount > 0){
                Calendar chargeCalendar = Calendar.getInstance();
                chargeCalendar.setTime(chargeStartTime);
                chargeCalendar.add(Calendar.MINUTE,30);
                List<BattRealTimeDataHistory> chargeVolListOne = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
                chargeVolListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
                addStatistics(chargeVolListOne);
                columnIndexTemp++;
                //rowTemp.createCell(columnIndexTemp).setCellValue("充电0.5h");
                rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListOne.get(j).getMonVol());

                if(chargeColumnCount > 1){
                    //充电nh,n从1开始
                    for(int i = 1;i < chargeColumnCount;i++){
                        columnIndexTemp++;

                        chargeCalendar.setTime(chargeStartTime);
                        chargeCalendar.add(Calendar.HOUR,i);
                        List<BattRealTimeDataHistory> chargeVolListN = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
                        chargeVolListN.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
                        addStatistics(chargeVolListN);
                        //rowTemp.createCell(columnIndexTemp).setCellValue("充电" + (i) + "h蓄电池电压值（V）");
                        rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListN.get(j).getMonVol());
                    }

                }
            }

            Calendar chargeCalendar = Calendar.getInstance();
            chargeCalendar.setTime(chargeStartTime);
            List<BattRealTimeDataHistory> fcVolListAfter = battRTDataHisService.getFcVolListAfter(battGroupId, chargeCalendar.getTime());
            fcVolListAfter.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
            addStatistics(fcVolListAfter);
            //rowTemp.createCell(++columnIndexTemp).setCellValue("充满后蓄电池电压值（V)（电流为0）");
            if (fcVolListAfter.size() > 0){
                rowTemp.createCell(++columnIndexTemp).setCellValue(fcVolListAfter.get(j).getMonVol());
            }else {
                rowTemp.createCell(++columnIndexTemp).setCellValue("-");
            }
            rowTemp.createCell(++columnIndexTemp).setCellValue("-");
        }

        //=====================格式设置=====================//
        //设置列宽
        for (int i = 0; i < columnCount; i++) {
            sheet.setColumnWidth(i, 256 * 20);
        }
        //对整个表格进行全局设置,暂为设置边框
        addGlobalStylesToAllCells(sheet, workbook);
    }

    //对传入的BattRealTimeDataHistory列表进行统计,将统计结果也追加到列表中
    public void addStatistics(List<BattRealTimeDataHistory> battRealTimeDataHistoryList) {
        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);

            battRealTimeDataHistoryList.add(hisAvg);
            battRealTimeDataHistoryList.add(hisMax);
            battRealTimeDataHistoryList.add(hisMin);
            battRealTimeDataHistoryList.add(hisGap);
        }
    }

    public void setRowStyle(Row row, CellStyle style) {
        for (Cell cell : row) {
            cell.setCellStyle(style);
        }
    }

    /**
     * 这个方法最后执行,用于对已被创建的行内的所有单元格添加边框.能自动识别被创建的所有列和所有单元格
     * @param sheet
     * @param workbook
     */
    public void addGlobalStylesToAllCells(Sheet sheet, Workbook workbook) {
        //所有边框样式
        CellStyle borderedStyle = workbook.createCellStyle();
        //borderedStyle.cloneStyleFrom(sheet.getRow(0).getCell(0).getCellStyle()); // 可选：复制已有样式
        borderedStyle.setBorderTop(BorderStyle.THIN);
        borderedStyle.setBorderBottom(BorderStyle.THIN);
        borderedStyle.setBorderLeft(BorderStyle.THIN);
        borderedStyle.setBorderRight(BorderStyle.THIN);

        for (Row row : sheet) {
            for (Cell cell : row) {
                cell.setCellStyle(borderedStyle);
            }
        }
    }