package com.whyc.service;
|
|
import cn.afterturn.easypoi.entity.ImageEntity;
|
import cn.afterturn.easypoi.excel.ExcelExportUtil;
|
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
|
import com.whyc.dto.Real.ExportTinfDataDto;
|
import com.whyc.dto.Response;
|
import com.whyc.dto.Statistic.MonStic;
|
import com.whyc.dto.Statistic.SticMonRes;
|
import com.whyc.mapper.CommonMapper;
|
import com.whyc.pojo.db_batt_testdata.BatttestdataId;
|
import com.whyc.pojo.db_batt_testdata.BatttestdataInf;
|
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.apache.poi.ss.usermodel.Workbook;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.core.io.ClassPathResource;
|
import org.springframework.stereotype.Service;
|
import sun.misc.BASE64Decoder;
|
|
import javax.annotation.Resource;
|
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.*;
|
|
@Service
|
public class ExportService {
|
|
@Autowired
|
private BatttestdataInfService battTinfService;
|
|
@Autowired
|
private SubTablePageInfoService subTablePageInfoService;
|
|
@Resource
|
private CommonMapper commonMapper;
|
|
|
//单节数量统计导出（1.2.14）
|
public void exportBattTinfStatistic(HttpServletRequest req, HttpServletResponse resp, MonStic stic) throws ParseException {
|
String topPic = stic.getTopPic(); //头部柱状图
|
String goodVolPic = stic.getGoodVolPic(); //优秀电压图
|
String goodResPic = stic.getGoodResPic(); //优秀内阻
|
String badVolPic = stic.getBadVolPic(); //劣化电压图
|
String badResPic = stic.getBadResPic(); //劣化内阻
|
String damageVolPic = stic.getDamageVolPic(); //损坏电压图
|
String damageResPic = stic.getDamageResPic(); //损坏内阻
|
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=25;
|
//插入图片
|
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.MOVE_DONT_RESIZE);
|
patriarch.createPicture(anchor, wb.addPicture(bytes.get(picNum), HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
|
picNum++;
|
rowNum = rowNum+rowNumAdd+5;
|
}
|
}
|
//历史测试数据导出
|
public void exportTinfDataByTestRecordCount(HttpServletRequest req, HttpServletResponse resp, ExportTinfDataDto dto) throws IOException {
|
Map<String,Object> map=new HashMap<>();
|
//历史测试数据导出
|
BatttestdataInf tinf = battTinfService.exportTinfDataByTestRecordCount(dto.getBattgroupId(), dto.getTestRecordCount());
|
String tableName ="db_batt_testdata.tb_batttestdata_"+dto.getBattgroupId();
|
//获取具体的放电数据
|
List<BatttestdataId> dataList= subTablePageInfoService.exportTinfDataByTestRecordCount(dto.getBattgroupId(),dto.getTestRecordCount(),tableName);
|
map.put("dataList",dataList);
|
BASE64Decoder decoder = new BASE64Decoder();
|
if(!dto.getPicGroupVol().equals("")) {
|
ImageEntity volImage = new ImageEntity();
|
volImage.setData(decoder.decodeBuffer(dto.getPicGroupVol().substring(dto.getPicGroupVol().indexOf(",") + 1)));
|
volImage.setColspan(6);
|
volImage.setRowspan(17);
|
map.put("picGroupVol",volImage);
|
}
|
if(!dto.getPicBattVol().equals("")) {
|
ImageEntity volImage = new ImageEntity();
|
volImage.setData(decoder.decodeBuffer(dto.getPicGroupVol().substring(dto.getPicBattVol().indexOf(",") + 1)));
|
volImage.setColspan(6);
|
volImage.setRowspan(17);
|
map.put("picBattVol",volImage);
|
}
|
if(!dto.getPicGroupCurr().equals("")) {
|
ImageEntity volImage = new ImageEntity();
|
volImage.setData(decoder.decodeBuffer(dto.getPicGroupVol().substring(dto.getPicGroupCurr().indexOf(",") + 1)));
|
volImage.setColspan(6);
|
volImage.setRowspan(17);
|
map.put("picGroupCurr",volImage);
|
}
|
//获取导出模板地址
|
ClassPathResource classPathResource = new ClassPathResource("excel_templates/batt_mon_static_template.xlsx");
|
String path = classPathResource.getPath();
|
TemplateExportParams templateExportParams1 = new TemplateExportParams(path,true);
|
Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
|
try {
|
String fileName = "历史测试数据表.xls";
|
resp.setContentType("application/vnd.ms-excel");
|
resp.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
|
resp.flushBuffer();
|
wb.write(resp.getOutputStream());
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
