导出设备某次记录详细的单体放电过程

whyclxw

2024-09-11 8ba76d907895e63dde497975b3902c369c3f6209

导出设备某次记录详细的单体放电过程

 .idea/encodings.xml

@@ -2,5 +2,6 @@
<project version="4">
  <component name="Encoding">
    <file url="file://$PROJECT_DIR$/src/main/java" charset="UTF-8" />
    <file url="PROJECT" charset="UTF-8" />
  </component>
</project>

 pom.xml

@@ -79,6 +79,12 @@
            <version>3.1.2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

        <!--knife4j-->
        <dependency>
            <groupId>com.github.xiaoymin</groupId>

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

@@ -3,15 +3,18 @@
import com.whyc.dto.Response;
import com.whyc.service.BattgroupBaojigroupService;
import com.whyc.service.BatttestdataInfService;
import com.whyc.service.ExcelExportService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpRequest;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDate;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Map;

@RestController
@@ -21,6 +24,8 @@
    @Autowired
    private BatttestdataInfService service;

    @Autowired
    private ExcelExportService exportService;

    @ApiOperation(value = "测试统计本年/本月")
    @GetMapping("getDevTinfByYearMonth")
@@ -44,4 +49,11 @@
    public Response getTdataById(@RequestParam Integer devId,@RequestParam Integer testRecordCount){
        return service.getTdataById(devId,testRecordCount);
    }

    @ApiOperation(value = "导出某次记录详细的单体放电记录")
    @GetMapping("exportData")
    public void exportData(@RequestParam Integer devId, @RequestParam Integer testRecordCount
            , HttpServletResponse resp){
         exportService.exportExcel(devId,testRecordCount,resp);
    }
}

 src/main/java/com/whyc/pojo/db_batt_testdata/BatttestdataId.java

@@ -90,7 +90,8 @@
    private Integer monState;

    @ApiModelProperty(value = "故障信息")
    private Integer monFault;
    private String monFault;


    @ApiModelProperty(value = "是否需要测试")
    private Integer needTest;
}

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

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

import com.whyc.pojo.db_batt_testdata.BatttestdataId;
import com.whyc.util.ActionUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

@Service
public class ExcelExportService {
    @Autowired(required = false)
    private SubTablePageInfoService subService;

    //导出文件
    public void exportExcel(Integer devId,  Integer testRecordCount,HttpServletResponse response) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("设备历史数据");
        String[] titiles=new String[]{"测试时间","电池组编号","单体编号","测试次数","测试类型","测试容量"
                                    ,"组端电压(V)","测试电流(A)","测试容量(AH)","单体电压(V)","单体温度(℃)"
                                    ,"单体电流(A)","单体容量(AH)","单体能量","单体状态","故障信息"};
        int rowNum=0;
        // 创建标题行
        Row row = sheet.createRow(rowNum);
        // 填充数据
        for(int i=0;i<titiles.length;i++){
            row.createCell(i).setCellValue(titiles[i]);
        }
        rowNum++;
        List<BatttestdataId> list=subService.getTdataById(devId,testRecordCount);
        //数据栏
        for (int i = 0; i < list.size(); i++) {
            sheet.createRow(rowNum);  //创建行
            BatttestdataId tdata=list.get(i);
            for(int j=0;j<titiles.length;j++){
                sheet.getRow(rowNum).createCell(0 ).setCellValue(ActionUtil.secToTime(tdata.getTestTimelong()));
                sheet.getRow(rowNum).createCell(1).setCellValue(tdata.getBattIdx()+1);
                sheet.getRow(rowNum).createCell(2 ).setCellValue(tdata.getMonNum());
                sheet.getRow(rowNum).createCell(3).setCellValue(tdata.getTestRecordCount());
                sheet.getRow(rowNum).createCell(4).setCellValue(tdata.getTestType());
                sheet.getRow(rowNum).createCell(5).setCellValue(tdata.getRecordNum());
                sheet.getRow(rowNum).createCell(6).setCellValue(tdata.getGroupVol());
                sheet.getRow(rowNum).createCell(7).setCellValue(tdata.getTestCurr());
                sheet.getRow(rowNum).createCell(8).setCellValue(tdata.getTestCap());
                sheet.getRow(rowNum).createCell(9 ).setCellValue(tdata.getMonVol());
                sheet.getRow(rowNum).createCell(10 ).setCellValue(tdata.getMonTmp());
                sheet.getRow(rowNum).createCell(11 ).setCellValue(tdata.getMonCurr());
                sheet.getRow(rowNum).createCell(12 ).setCellValue(tdata.getMonCap());
                sheet.getRow(rowNum).createCell(13 ).setCellValue(tdata.getMonWh());
                sheet.getRow(rowNum).createCell(14 ).setCellValue(tdata.getMonState());
                sheet.getRow(rowNum).createCell(15 ).setCellValue(tdata.getMonFault());
            }
            rowNum++;
        }
        try {
            // 写入到输出流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            workbook.write(byteArrayOutputStream);
            workbook.close();

            // 设置响应头
            String filename = "设备历史数据";
            /*response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename="+
                    new String(filename.getBytes("UTF-8"), "ISO8859-1")+
                    ".xlsx");*/
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + URLEncoder.encode (filename, "utf-8") + ".xlsx"+"\"");
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);

            // 写入响应流并关闭
            response.getOutputStream().write(byteArrayOutputStream.toByteArray());
            response.getOutputStream().flush();
            response.getOutputStream().close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

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

@@ -18,7 +18,7 @@
    //获取设备某次记录详细的单体放电过程
    public List<BatttestdataId> getTdataById(Integer devId, Integer testRecordCount) {
        String sql="select  * from db_batt_testdata.tb_batttestdata_" +devId
                +" where test_record_count="+testRecordCount;
                +" where need_test=1 and test_record_count="+testRecordCount+" ";
        sql+="  ORDER BY test_starttime asc ";
        System.out.println(sql);
        List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
@@ -46,7 +46,8 @@
                    tdata.setMonCap(rs.getDouble("mon_cap"));
                    tdata.setMonWh(rs.getDouble("mon_wh"));
                    tdata.setMonState(rs.getInt("mon_state"));
                    tdata.setMonFault(rs.getInt("mon_fault"));
                    tdata.setMonFault(rs.getString("mon_fault"));
                    tdata.setNeedTest(rs.getInt("need_test"));
                    list.add(tdata);
                }
                return list;