单节数量统计导出

whyclxw

2025-06-05 1c74351afdec0db7e3afdb563d2fafd49f95631c

单节数量统计导出

 pom.xml

@@ -142,6 +142,12 @@
            <artifactId>spring-context-support</artifactId>
            <version>5.3.9</version>
        </dependency>
        <!--poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10.1</version>
        </dependency>

        <!--<dependency>
            <groupId>org.springframework.plugin</groupId>

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

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

import com.whyc.dto.Statistic.MonStic;
import com.whyc.service.ExportService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.ParseException;

@RestController
@Api(tags = "导出文件")
@RequestMapping("export")
public class ExportFileController {
    @Autowired
    private ExportService service;

    @PostMapping("exportBattTinfStatistic")
    @ApiOperation(value = "单节数量统计导出（1.2.14）")
    public void exportBattTinfStatistic(HttpServletRequest req, HttpServletResponse resp ) throws ParseException {
        service.exportBattTinfStatistic(req,resp);
    }
}

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

@@ -13,6 +13,8 @@
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Map;

@RestController
@Api(tags = "统计管理")
@RequestMapping("statistic")
@@ -22,6 +24,7 @@

    @Autowired
    private BatttestdataInfService battTinfService;

    @Autowired
    private BattInfService battService;

@@ -60,7 +63,8 @@
    public Response getMonStatistic(@RequestBody MonStic stic){
        User uinf= ActionUtil.getUser();
        stic.setUid(uinf.getId());
        return battTinfService.getMonStatistic(stic);
        Map<String,Object> map= battTinfService.getMonStatistic(stic);
        return new Response().setII(1,true,map,"单节数量统计");
    }



 src/main/java/com/whyc/dto/Statistic/SticMonRes.java

@@ -5,6 +5,8 @@
@Data
public class SticMonRes {
    private Integer battgroupId;
    private String stationName;
    private String devName;
    private String battgroupName;
    private Integer monNum;
    private Float monVol;

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

@@ -122,7 +122,7 @@
      3然后找到判断优秀，劣化，损坏的参数，得到结果。
      4浮充电压图，需要显示单体+实时数据的单体电压，单体内阻
    */
    public Response getMonStatistic(MonStic stic) {
    public Map getMonStatistic(MonStic stic) {
        Map<String,Object> map=new HashMap<>();
        List<SticMonRes> goodlist=new ArrayList();
        List<SticMonRes> badlist=new ArrayList();
@@ -151,7 +151,7 @@
        List<BattInf> binfList=battInfService.getMonStatistic(stic);
        Float realCap=0f;
        if(binfList==null){
           return new Response().set(1,false,"当前用户未管理满足条件的电池组");
           return map;
        }
        for (BattInf binf:binfList) {
            BatttestdataInf tinf =getLastStandardTestData(binf.getBattgroupId());
@@ -174,6 +174,8 @@
                SticMonRes res=new SticMonRes();
                res.setBattgroupId(binf.getBattgroupId());
                res.setBattgroupName(binf.getBattgroupName());
                res.setStationName(binf.getStationName());
                res.setDevName(binf.getDevName());
                res.setMonNum(data.getMonNum());
                //获取单体的实时数据
                BattRtdata rtdata=rtdataService.getRtdataByNum(binf.getBattgroupId(),data.getMonNum());
@@ -201,7 +203,8 @@
        map.put("goodlistNum",goodlist.size());
        map.put("badlistNum",badlist.size());
        map.put("damagelistNum",damagelist.size());
        return new Response().setII(1,true,map,"单节数量统计");
        return map;

    }
    //将不满足条件的电池组的所有单体放入damage中
    private void setDamage(List damagelist, BattInf binf) {
@@ -212,6 +215,8 @@
                SticMonRes res=new SticMonRes();
                res.setBattgroupId(binf.getBattgroupId());
                res.setBattgroupName(binf.getBattgroupName());
                res.setStationName(binf.getStationName());
                res.setDevName(binf.getDevName());
                res.setMonNum(i+1);
                res.setMonVol(0f);
                res.setMonRes(0f);
@@ -222,6 +227,8 @@
                SticMonRes res=new SticMonRes();
                res.setBattgroupId(binf.getBattgroupId());
                res.setBattgroupName(binf.getBattgroupName());
                res.setStationName(binf.getStationName());
                res.setDevName(binf.getDevName());
                res.setMonNum(rtdata.getMonNum());
                res.setMonVol(rtdata.getMonVol());
                res.setMonRes(rtdata.getMonRes());

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

New file
@@ -0,0 +1,174 @@
package com.whyc.service;

import com.whyc.dto.Statistic.MonStic;
import com.whyc.dto.Statistic.SticMonRes;
import com.whyc.pojo.db_user.User;
import com.whyc.util.ActionUtil;
import com.whyc.util.ServletUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import sun.misc.BASE64Decoder;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class ExportService {

    @Autowired
    private BatttestdataInfService battTinfService;


    //单节数量统计导出（1.2.14）
    public void exportBattTinfStatistic(HttpServletRequest req, HttpServletResponse resp) throws ParseException {
        String topPic = req.getParameter("topPic");            //头部柱状图
        String goodVolPic = req.getParameter("goodVolPic");            //优秀电压图
        String goodResPic = req.getParameter("goodResPic");        //优秀内阻
        String badVolPic = req.getParameter("badVolPic");            //劣化电压图
        String badResPic = req.getParameter("badResPic");        //劣化内阻
        String damageVolPic = req.getParameter("damageVolPic");            //损坏电压图
        String damageResPic = req.getParameter("damageResPic");        //损坏内阻
        MonStic stic=ActionUtil.getGson("yyyy-MM-dd").fromJson(req.getParameter("stic"),  MonStic.class);
        User uinf= ActionUtil.getUser();
        stic.setUid(uinf.getId());
        Map<String,Object> map= battTinfService.getMonStatistic(stic);
        List<byte[]> bytes = new ArrayList<byte[]>();
        //处理图片
        try {
            if (ServletUtils.isNotNull(topPic)) {
                String[] url = topPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(goodVolPic)) {
                String[] url = goodVolPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(goodResPic)) {
                String[] url = goodResPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(badVolPic)) {
                String[] url = badVolPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(badResPic)) {
                String[] url = badResPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(damageVolPic)) {
                String[] url = damageVolPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(damageResPic)) {
                String[] url = damageResPic.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        //当前日期
        String nowFormat = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String excelName = "单节数量统计-"+nowFormat;

        HSSFWorkbook wb = new HSSFWorkbook();
        //单体性能统计表
        createMonSicPic(wb, bytes);
        List<SticMonRes> goodList=(List<SticMonRes>) map.get("goodlist");
        //创建优秀单体数据表
        createMonSicTable(wb, goodList,"优秀单体数据表");
        List<SticMonRes> badList=(List<SticMonRes>) map.get("badlist");
        //创建劣化单体数据表
        createMonSicTable(wb, badList,"劣化单体数据表");
        List<SticMonRes> damageList=(List<SticMonRes>) map.get("damagelist");
        //创建损坏单体数据表
        createMonSicTable(wb, damageList,"损坏单体数据表");

        try {
            // 转码防止乱码
            resp.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode ( excelName+".xlsx", "utf-8"));
            OutputStream out = resp.getOutputStream();
            wb.write(out);
            out.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //创建优秀/劣化/损坏单体数据表
    private void createMonSicTable(HSSFWorkbook wb, List<SticMonRes> list,String sheetName) {
        HSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setDefaultColumnWidth(15);    //设置默认列宽，实际上回多出2个字符，不知道为什么
        sheet.setDefaultRowHeight((short) (1 * 256));//设置默认行高，表示2个字符的高度
        int rownum = 1;

        String[] arrTh = new String[]{
                "机房名称","设备名称","电池组名称","单体编号","浮充电压(V)","单体内阻(mΩ)"
        };
        sheet.createRow(rownum);
        for(int i = 0 ;i<arrTh.length;i++){
            sheet.getRow(rownum).createCell(i+1).setCellValue(arrTh[i]);
        }
        rownum++;
        if(list!=null&&list.size()>=0){
            for(SticMonRes res:list){
                sheet.createRow(rownum);
                sheet.getRow(rownum).createCell(1).setCellValue(res.getStationName());
                sheet.getRow(rownum).createCell(2).setCellValue(res.getDevName());
                sheet.getRow(rownum).createCell(3).setCellValue(res.getBattgroupName());
                sheet.getRow(rownum).createCell(4).setCellValue(res.getMonNum());
                sheet.getRow(rownum).createCell(5).setCellValue(res.getMonVol());
                sheet.getRow(rownum).createCell(6).setCellValue(res.getMonRes());
                rownum++;
            }
        }
    }

    //单体性能统计表
    private void createMonSicPic(HSSFWorkbook wb, List<byte[]> bytes) {

        HSSFSheet sheet = wb.createSheet("单体性能统计表");
        sheet.setDefaultColumnWidth(15);    //设置默认列宽，实际上回多出2个字符，不知道为什么
        sheet.setDefaultRowHeight((short) (2 * 256)); //设置默认行高，表示2个字符的高度
        //字体格式-加粗
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setBold(true);
        cellStyle.setFont(font);

        //图片元素
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        int rowNum = 1;
        int rowNumAdd=27;
        //插入图片
        int picNum = 0;
        String[] picName = new String[]{
                "单体性能统计图", "优秀单体电压图", "优秀单体内阻图", "劣化单体电压图", "劣化单体内阻图", "损坏单体电压图", "损坏单体内阻图"
        };
        for (int i = 0; i < picName.length; i++) {
            sheet.createRow(rowNum - 1);
            sheet.getRow(rowNum - 1).createCell(0).setCellValue(picName[i]);
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255, (short) 0, rowNum, (short) 10, rowNum + rowNumAdd);
            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
            patriarch.createPicture(anchor, wb.addPicture(bytes.get(picNum), HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
            picNum++;
            rowNum += rowNumAdd;
        }
        rowNum++;
    }
}

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

New file
@@ -0,0 +1,14 @@
package com.whyc.util;

public class ServletUtils {
    public static boolean isNotNull(Object obj){
        boolean flag=false;
        if(obj!=null){
            if(obj.toString().trim().length()>0){
                flag=true;
            }
        }
        return flag;
    }
	
}

 src/main/resources/config/application-dev.yml

@@ -17,6 +17,7 @@
        max-age: -1
  tomcat:
    max-http-form-post-size: 102400000
    max-http-header-size: 655360000
http:
  port: -1
#数据库

 src/main/resources/mapper/BattInfMapper.xml

@@ -183,8 +183,9 @@
        </where>
    </select>
    <select id="getMonStatistic" resultType="com.whyc.pojo.db_station.BattInf">
        select distinct tb_batt_inf.* from db_station.tb_batt_inf
        select distinct tb_batt_inf.*,tb_station_inf.station_name from db_station.tb_batt_inf,db_station.tb_station_inf
        <where>
            tb_batt_inf.station_id=tb_station_inf.station_id
            <if test="stic.product!=null">
                and tb_batt_inf.product=#{stic.product}
            </if>