测试调整格式

whycxzp

2025-04-30 1024d7a5b86ae091e131bedcc2f5bcf45866fb76

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

@@ -60,9 +60,9 @@
        createSheet1(workbook,battGroupId,testRecordCount);
        //创建sheet2 -- 每月电压,12个月,取每月1号零点零分
        //创建sheet3 -- 每月内阻, 12个月,取每月1号零点零分
        //createSheet2And3(workbook,battGroupId);
        createSheet2And3(workbook,battGroupId);
        //创建sheet4 -- 每天温度, 30天, 取每天零点零分
        //createSheet4(workbook,battGroupId);
        createSheet4(workbook,battGroupId);

        // 将工作簿写入文件
        FileOutputStream fileOut = new FileOutputStream(filePath);
@@ -214,6 +214,10 @@
        XSSFCellStyle cellStyleOriginal2 = cellOfSheet3.getCellStyle();
        XSSFCellStyle cellStyleNew2 = getCellStyleFont(cellStyleOriginal2,workbook, true, 15);
        cellOfSheet3.setCellStyle(cellStyleNew2);

        //设置数值类型保留位数
        setDateFormat(workbook,sheet2,3,110,1,columnCount-2, "0.0000");
        setDateFormat(workbook,sheet3,3,110,1,columnCount-2, "0.0000");


        // 创建绘图工具
@@ -465,6 +469,8 @@
            cell.setCellStyle(cellStyle);
        }

        //设置数值类型单元格保留位数
        setDateFormat(workbook,sheet4,3,110,1,columnCount-2, "0.0000");
        createChart(sheet4, columnCount,3,"温度折线图");

    }
@@ -572,22 +578,6 @@
        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);

        //第三行,第五行,第六行的合并列,前两个单元模块是固定的,其中1个合并单元
        CellRangeAddress region31 = new CellRangeAddress(2, 2,  1, 2);
        CellRangeAddress region32 = new CellRangeAddress(2, 2,  3, 4);
@@ -611,45 +601,56 @@
        cellRow1.setCellStyle(style);

        //第二行是放电基本信息
        //因为是动态的,没办法像模板一样.
        //蓄电池组号:第一列
        //工作票信息:二三列
        //放电日期:第四列起步-放电结束静置0.5h
        //放电电流,时长,容量:放电结束后静置0.5h起到充满电
        //环境温湿度:备注
        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" +
        XSSFCell cell2Row2 = row1.createCell(1);
        cell2Row2.setCellValue("工作票号：2273925\n" +
                "工作负责人：马云燕\n" +
                "工作成员：周素文、李志标、王月灿等");

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

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

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

        //二行的合并列是根据变动的
        row1.setHeightInPoints(57);
        sheet.getRow(0).setHeightInPoints(42);
        CellRangeAddress region22 = new CellRangeAddress(1, 1,  1, 2);
        sheet.addMergedRegion(region22);

        CellRangeAddress region23 = new CellRangeAddress(1, 1,  3, 4);
        sheet.addMergedRegion(region23);

        CellRangeAddress region24 = new CellRangeAddress(1, 1,  5, columnCount-2);
        sheet.addMergedRegion(region24);

        //第三行是电池号和电压等相关抬头
        //第四行是电压列和温度列标识
        XSSFRow row2 = sheet.createRow(2);
        XSSFRow row3 = sheet.createRow(3);
        XSSFRow row4 = sheet.createRow(4);
        XSSFRow row5 = sheet.createRow(5);

        row2.createCell(0).setCellValue("蓄电池号（#）");
        row2.createCell(1).setCellValue("蓄电池浮充电压值（V）");
        row2.createCell(3).setCellValue("放电前蓄电池开路电压值（V）");
        row2.createCell(4);

        row3.createCell(0).setCellValue("项目");
        row3.createCell(1).setCellValue("-1（电压）");
@@ -730,7 +731,7 @@
        List<BattRealTimeDataHistory> fcVolListOriginal = battRTDataHisService.getFcVolList(battGroupId,dischargeStartTime);
        //按单体编号排序,按编号升序
        fcVolListOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
        addStatistics(fcVolListOriginal,"vol");
        addStatistics(fcVolListOriginal,"vol","tmp");
        //总电流总电压列入
        Float fcGroupVol = fcVolListOriginal.get(0).getGroupVol();
        Float fcGroupCurr = fcVolListOriginal.get(0).getGroupCurr();
@@ -845,17 +846,13 @@
            //rowTemp.createCell(4).setCellValue("放电前蓄电池开路温度值:"+(j+1)+"#");
            if(j == 0){
                rowTemp.createCell(1).setCellValue(fcVolList.get(j).getGroupVol());
                rowTemp.createCell(2);
                rowTemp.createCell(3).setCellValue(preVolList.get(j).getGroupVol());
                rowTemp.createCell(4);
            }else if (j == 1){
                rowTemp.createCell(1).setCellValue(fcVolList.get(j).getGroupCurr());
                rowTemp.createCell(2);
                rowTemp.createCell(3).setCellValue(preVolList.get(j).getGroupCurr());
                rowTemp.createCell(4);
            }else {
                rowTemp.createCell(1).setCellValue(fcVolList.get(j).getMonVol());
                rowTemp.createCell(2).setCellValue(fcVolList.get(j).getMonVol());
                rowTemp.createCell(2).setCellValue(fcVolList.get(j).getMonTmp());
                rowTemp.createCell(3).setCellValue(preVolList.get(j).getMonVol());
                rowTemp.createCell(4).setCellValue(preVolList.get(j).getMonTmp());
            }
@@ -1066,6 +1063,16 @@
            rowTemp.createCell(columnIndexTemp);
        }

        //设置前6行所有的表格创建,避免出现无边框
        for (int i = 0; i < 6; i++) {
            XSSFRow rowTmp = sheet.getRow(i);
            for (int j = 0; j < columnCount; j++) {
                if(rowTmp.getCell(j) == null){
                    rowTmp.createCell(j);
                }
            }
        }

        //=====================格式设置=====================//
        //设置列宽
        for (int i = 0; i < columnCount; i++) {
@@ -1075,12 +1082,31 @@
        addGlobalStylesToAllCells(sheet, workbook);

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

        setRowStyle(workbook,sheet.getRow(1),true,11,HorizontalAlignment.LEFT);
        //第四行设置字体颜色
        setRowStyle(workbook,sheet.getRow(3),false,9,IndexedColors.GREY_40_PERCENT.getIndex());
        //第5行到最后一行,设置数值的小数点为4位
            setDateFormat(workbook,sheet,4,113,1,columnCount-1,"0.0000");
        //生成图表
        createChart(sheet, columnCount, 6,"");
    }

    private void setDateFormat(Workbook workbook,XSSFSheet sheet, int rowIndexStart, int rowIndexEnd,int columnIndexStart,int columnIndexEnd, String formatStr) {
        CellStyle decimalStyle = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        decimalStyle.setDataFormat(format.getFormat(formatStr));
        for (int i = rowIndexStart; i <= rowIndexEnd; i++) {
            XSSFRow row = sheet.getRow(i);
            for (int j = columnIndexStart; j <= columnIndexEnd; j++) {
                XSSFCell cell = row.getCell(j);
                if(cell.getCellType() == CellType.NUMERIC) {
                    cell.getCellStyle().setDataFormat(format.getFormat(formatStr));
                }
            }

        }
    }

    private XSSFCellStyle getCellStyleFont(XSSFCellStyle cellStyleOriginal, XSSFWorkbook workbook, boolean isFontBold, int fontSize) {
@@ -1212,6 +1238,7 @@
            //设置字体和加粗
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) (fontSize));
            font.setFontName("宋体");
            font.setBold(isFontBold);

            cellStyleNew.setFont(font);
@@ -1219,8 +1246,51 @@
        }
    }

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

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

            //设置水平对齐方式
            cellStyleNew.setAlignment(horizontalAlignment);

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

    public void setRowStyle(Workbook workbook,Row row,boolean isFontBold,int fontSize,short color) {
        for (Cell cell : row) {
            //跳过第一列
            if(cell.getColumnIndex() == 0) {
                continue;
            }
            //先克隆原来的属性
            CellStyle cellStyleNew = workbook.createCellStyle();
            cellStyleNew.cloneStyleFrom(cell.getCellStyle());

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

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

    /**
     * 这个方法最后执行,用于对已被创建的行内的所有单元格添加边框.能自动识别被创建的所有列和所有单元格
     * 设置全局的属性.这个方法在所有单元格创建后调用
     * 用于对已被创建的行内的所有单元格添加边框.能自动识别被创建的所有列和所有单元格
     * @param sheet
     * @param workbook
     */
@@ -1232,7 +1302,16 @@
        borderedStyle.setBorderBottom(BorderStyle.THIN);
        borderedStyle.setBorderLeft(BorderStyle.THIN);
        borderedStyle.setBorderRight(BorderStyle.THIN);

        Font font = workbook.createFont();
        //默认字体大小为9,宋体
        font.setFontHeightInPoints((short) 9);
        font.setFontName("宋体");
        borderedStyle.setFont(font);
        //默认横竖居中
        borderedStyle.setAlignment(HorizontalAlignment.CENTER);
        borderedStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //默认自动换行
        borderedStyle.setWrapText(true);
        for (Row row : sheet) {
            for (Cell cell : row) {
                cell.setCellStyle(borderedStyle);