package com.whyc.service;
|
|
import com.whyc.dto.ActmStopReason;
|
import com.whyc.pojo.db_lithium_testdata.BattLithiumTestData;
|
import com.whyc.pojo.db_lithium_testdata.BattLithiumTestDataInf;
|
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;
|
|
@Autowired(required = false)
|
private BattLithiumTestDataInfService infService;
|
|
//导出文件
|
public void exportExcel1(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<BattLithiumTestData> list=subService.getTdataById(devId,testRecordCount);
|
if(list!=null){
|
//数据栏
|
for (int i = 0; i < list.size(); i++) {
|
sheet.createRow(rowNum); //创建行
|
BattLithiumTestData 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();
|
}
|
}
|
|
//导出文件A200
|
public void exportExcelA200(Integer devId, Integer testRecordCount,HttpServletResponse response) {
|
Workbook wb = new XSSFWorkbook();
|
//测试报告封面
|
exportExcelCoverA200(devId,testRecordCount,wb);
|
Sheet sheet = wb.createSheet("测试数据详情");
|
// 创建一个单元格样式，并设置字体大小为5
|
CellStyle cellStyle = wb.createCellStyle();
|
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
|
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
|
|
Font font = wb.createFont();
|
font.setFontHeightInPoints((short)20);
|
cellStyle.setFont(font);
|
|
String[] titiles=new String[]{"Time(H:M:S)","Group V(V)","Group A(A)","Cap (AH)","Temp H(°C)","Temp L(°C)"};
|
List<BattLithiumTestData> list=subService.getTdataByIdWithListA200(devId,testRecordCount);
|
|
int rowNum=0;
|
// 创建标题行
|
Row row = sheet.createRow(rowNum);
|
row.setHeightInPoints(20.0f);
|
// 填充数据
|
for(int i=0;i<titiles.length;i++){
|
row.createCell(i).setCellValue(titiles[i]);
|
}
|
if(list!=null&&list.size()>0){
|
List<Integer> monNums=list.get(0).getMonNums();
|
for (int i=0;i<monNums.size();i++){
|
row.createCell(i+titiles.length).setCellValue("V"+monNums.get(i));
|
}
|
}
|
rowNum++;
|
|
if(list!=null){
|
//数据栏
|
for (int i = 0; i < list.size(); i++) {
|
Row row1 = sheet.createRow(rowNum); //创建行
|
row1.setHeightInPoints(20.0f);
|
BattLithiumTestData tdata=list.get(i);
|
sheet.getRow(rowNum).createCell(0 ).setCellValue(ActionUtil.secToTime(tdata.getTestTimelong()));
|
sheet.getRow(rowNum).createCell(1).setCellValue(String.format("%.1f",tdata.getGroupVol()));
|
sheet.getRow(rowNum).createCell(2).setCellValue(String.format("%.1f",tdata.getTestCurr()));
|
sheet.getRow(rowNum).createCell(3).setCellValue(String.format("%.1f",tdata.getTestCap()));
|
sheet.getRow(rowNum).createCell(4).setCellValue(String.format("%.1f",tdata.getMaxTemp()));
|
sheet.getRow(rowNum).createCell(5).setCellValue(String.format("%.1f",tdata.getMinTemp()));
|
if (tdata.getMonVols() != null) {
|
List<Float> monVols = tdata.getMonVols();
|
for (int j = 0; j < monVols.size(); j++) {
|
sheet.getRow(rowNum).createCell(6 + j).setCellValue(String.format("%.3f", monVols.get(j)));
|
}
|
}
|
rowNum++;
|
}
|
}
|
try {
|
// 写入到输出流
|
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
|
wb.write(byteArrayOutputStream);
|
wb.close();
|
|
// 设置响应头
|
String filename = "设备历史数据";
|
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();
|
}
|
}
|
//测试报告封面
|
private void exportExcelCoverA200(Integer devId, Integer testRecordCount, Workbook wb) {
|
Sheet sheet = wb.createSheet("测试报告封面");
|
// 创建一个单元格样式，并设置字体大小为5
|
CellStyle cellStyle = wb.createCellStyle();
|
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
|
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
|
|
Font font = wb.createFont();
|
font.setFontHeightInPoints((short)20);
|
cellStyle.setFont(font);
|
|
//获取放电inf
|
BattLithiumTestDataInf tinf=infService.getTinfExport(devId,testRecordCount);
|
String[] titiles33=new String[]{"PACK/模组号:","测试开始时间:","测试结束时间:","数据类型:","停止原因:","预放容量(AH):"
|
,"放电电流(A):","测试时长(h:m:s):","单串下限(V):","组端下限(V):","电芯串数:","最高温度(°C):","最低温度(°C):","采集频率(秒):"};
|
String[] titiles34=new String[]{tinf.getBatteryName(),ActionUtil.sdf.format(tinf.getTestStarttime()),ActionUtil.sdf.format(tinf.getRecordTime()), "放电数据",tinf.getStopTypeReason(),String.format("%.1f",tinf.getTestCap())
|
,String.format("%.1f",tinf.getTestCurr()),ActionUtil.secToTime(tinf.getTestTimelong()),String.format("%.1f",tinf.getMonVolLow()),String.format("%.1f",tinf.getGroupVolLow()),String.valueOf(tinf.getBatteryNumber())
|
,String.format("%.1f",tinf.getMaxTemp()),String.format("%.1f",tinf.getMinTemp()),String.valueOf(tinf.getSaveInverter())};
|
|
String[] titiles23=new String[]{"PACK/模组号:","测试开始时间:","测试结束时间:","数据类型:","停止原因:","充电限流(A):"
|
,"测试时长(h:m:s):","单串上限(V):","电流阀值(A):","电芯串数:","充电限压(V)","最高温度(°C):","最低温度(°C):","采集频率(秒):"};
|
String[] titiles24=new String[]{tinf.getBatteryName(),ActionUtil.sdf.format(tinf.getTestStarttime()),ActionUtil.sdf.format(tinf.getRecordTime()), "充电数据",tinf.getStopTypeReason(),String.format("%.1f",tinf.getTestCurr())
|
,ActionUtil.secToTime(tinf.getTestTimelong()),String.format("%.1f",tinf.getMonVolHigh()),String.format("%.1f",tinf.getTestcurrLimit()),String.valueOf(tinf.getBatteryNumber())
|
,String.format("%.1f",tinf.getCharVoltage()),String.format("%.1f",tinf.getMaxTemp()),String.format("%.1f",tinf.getMinTemp()),String.valueOf(tinf.getSaveInverter())};
|
|
|
int rowNum=5;
|
if(tinf.getTestType()==3){//放电
|
for (int i=0;i<titiles33.length;i++){
|
Row row = sheet.createRow(rowNum); //创建行
|
row.setHeightInPoints(20.0f);
|
row.createCell(3).setCellValue(titiles33[i]);
|
row.createCell(4).setCellValue(titiles34[i]);
|
rowNum++;
|
}
|
}else{
|
for (int i=0;i<titiles23.length;i++){
|
Row row = sheet.createRow(rowNum); //创建行
|
row.setHeightInPoints(20.0f);
|
row.createCell(3).setCellValue(titiles23[i]);
|
row.createCell(4).setCellValue(titiles24[i]);
|
rowNum++;
|
}
|
}
|
}
|
//ACTM导出
|
public void exportExcelActm(Integer devId, Integer testRecordCount, HttpServletResponse response) {
|
Workbook wb = new XSSFWorkbook();
|
//测试报告封面
|
exportExcelCoverActm(devId,testRecordCount,wb);
|
Sheet sheet = wb.createSheet("测试数据");
|
// 创建一个单元格样式，并设置字体大小为5
|
CellStyle cellStyle = wb.createCellStyle();
|
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
|
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
|
|
Font font = wb.createFont();
|
font.setFontHeightInPoints((short)20);
|
cellStyle.setFont(font);
|
|
String[] titiles=new String[]{"测试时长"};
|
List<BattLithiumTestData> list=subService.getTdataByIdWithListActm(devId,testRecordCount);
|
|
int rowNum=0;
|
// 创建标题行
|
Row row = sheet.createRow(rowNum);
|
row.setHeightInPoints(20.0f);
|
// 填充数据
|
for(int i=0;i<titiles.length;i++){
|
row.createCell(i).setCellValue(titiles[i]);
|
}
|
if(list!=null&&list.size()>0){
|
List<Integer> monNums=list.get(0).getMonNums();
|
for (int i=0;i<monNums.size();i++){
|
row.createCell(4*i+titiles.length).setCellValue(monNums.get(i)+"#电压");
|
row.createCell(4*i+titiles.length+1).setCellValue(monNums.get(i)+"#电流");
|
row.createCell(4*i+titiles.length+2).setCellValue(monNums.get(i)+"#容量（AH）");
|
row.createCell(4*i+titiles.length+3).setCellValue(monNums.get(i)+"#能量（WH）");
|
}
|
}
|
rowNum++;
|
|
if(list!=null){
|
//数据栏
|
for (int i = 0; i < list.size(); i++) {
|
Row row1 = sheet.createRow(rowNum); //创建行
|
row1.setHeightInPoints(20.0f);
|
BattLithiumTestData tdata=list.get(i);
|
sheet.getRow(rowNum).createCell(0 ).setCellValue(ActionUtil.secToTime(tdata.getTestTimelong()));
|
List<Float> monVols = tdata.getMonVols();
|
List<Float> monCurrs = tdata.getMonCurrs();
|
List<Float> monCaps = tdata.getMonCaps();
|
List<Float> monWhs = tdata.getMonWhs();
|
List<Integer> monNums=list.get(i).getMonNums();
|
for (int j = 0; j < monNums.size(); j++) {
|
sheet.getRow(rowNum).createCell(4*j+1 ).setCellValue(String.format("%.3f", monVols.get(j)));
|
sheet.getRow(rowNum).createCell(4*j+1+1).setCellValue(String.format("%.3f", monCurrs.get(j)));
|
sheet.getRow(rowNum).createCell(4*j+1+2).setCellValue(String.format("%.3f", monCaps.get(j)));
|
sheet.getRow(rowNum).createCell(4*j+1+3).setCellValue(String.format("%.3f", monWhs.get(j)));
|
|
|
}
|
rowNum++;
|
}
|
}
|
try {
|
// 写入到输出流
|
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
|
wb.write(byteArrayOutputStream);
|
wb.close();
|
|
// 设置响应头
|
String filename = "设备历史数据";
|
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();
|
}
|
}
|
//测试报告封面
|
private void exportExcelCoverActm(Integer devId, Integer testRecordCount, Workbook wb) {
|
Sheet sheet = wb.createSheet("模组配置");
|
// 创建一个单元格样式，并设置字体大小为5
|
CellStyle cellStyle = wb.createCellStyle();
|
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
|
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
|
|
Font font = wb.createFont();
|
font.setFontHeightInPoints((short)20);
|
cellStyle.setFont(font);
|
|
//获取放电inf
|
BattLithiumTestDataInf tinf=infService.getTinfExport(devId,testRecordCount);
|
|
String[] titiles3=new String[]{"电池包序号:","模组编号:","工作模式:","电池类型:","电池串数:","电压阀值(v):"
|
,"工作电流(A):","开始测试时间:","结束测试时间:","停止原因:"};
|
String[] titiles4=new String[]{String.valueOf(tinf.getBattIdx()+1),tinf.getBatteryName(), ActmStopReason.getTestType(tinf.getTestType()), ActmStopReason.getBattType(tinf.getBattType()),String.valueOf(tinf.getBatteryNumber()),String.format("%.1f",tinf.getVolThreshold())
|
,String.format("%.1f",tinf.getTestCurr()), ActionUtil.sdf.format(tinf.getTestStarttime()),ActionUtil.sdf.format(tinf.getRecordTime()), tinf.getStopTypeReason()};
|
|
int rowNum=5;
|
for (int i=0;i<titiles3.length;i++){
|
Row row = sheet.createRow(rowNum); //创建行
|
row.setHeightInPoints(20.0f);
|
row.createCell(3).setCellValue(titiles3[i]);
|
row.createCell(4).setCellValue(titiles4[i]);
|
rowNum++;
|
}
|
}
|
}
|
