powerIntelligenceSystem.git

			@@ -1,17 +1,30 @@
			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.RealDataStatic.ResRealDataAc;
			import com.whyc.dto.RealDataStatic.ResRealExportAc;
			import com.whyc.dto.Statistic.BattCompareStic;
			import com.whyc.dto.Statistic.MonStic;
			import com.whyc.dto.Statistic.SticCompare15Res;
			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;
			@@ -20,16 +33,25 @@
			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;
			import java.util.*;

			@Service
			public class ExportService {

			@Autowired
			private BatttestdataInfService battTinfService;

			@Autowired
			private SubTablePageInfoService subTablePageInfoService;

			@Resource
			private CommonMapper commonMapper;

			@Autowired
			private PwrdevHistorydataIdService pwrdevHistorydataIdService;

			@Autowired
			private BattRealdataIdService battRealdataIdService;


			//单节数量统计导出（1.2.14）
			@@ -153,7 +175,7 @@
			//图片元素
			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
			int rowNum = 1;
			int rowNumAdd=27;
			int rowNumAdd=25;
			//插入图片
			int picNum = 0;
			String[] picName = new String[]{
			@@ -163,11 +185,196 @@
			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);
			anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
			patriarch.createPicture(anchor, wb.addPicture(bytes.get(picNum), HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
			picNum++;
			rowNum += rowNumAdd;
			rowNum = rowNum+rowNumAdd+5;
			}
			rowNum++;
			}
			//历史测试数据导出
			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();
			}
			}
			//蓄电池组对比分析导出(2.5.1/2/3)
			public void exportBattCompare(HttpServletRequest req, HttpServletResponse resp, BattCompareStic stic) throws IOException {
			Map<String,Object> map=new HashMap<>();
			map.put("avgCap",stic.getAvgCap());
			map.put("standCapDiff",stic.getStandCapDiff());
			//表格数据
			List<SticCompare15Res> dataList= battTinfService.exportBattCompare(stic);
			map.put("dataList",dataList);
			BASE64Decoder decoder = new BASE64Decoder();
			if(!stic.getPicCap().equals("")) {
			ImageEntity volImage = new ImageEntity();
			volImage.setData(decoder.decodeBuffer(stic.getPicCap().substring(stic.getPicCap().indexOf(",") + 1)));
			volImage.setColspan(6);
			volImage.setRowspan(17);
			map.put("picCap",volImage);
			}
			if(!stic.getPicRes().equals("")) {
			ImageEntity volImage = new ImageEntity();
			volImage.setData(decoder.decodeBuffer(stic.getPicRes().substring(stic.getPicRes().indexOf(",") + 1)));
			volImage.setColspan(6);
			volImage.setRowspan(17);
			map.put("picRes",volImage);
			}
			if(!stic.getPicFlotVol().equals("")) {
			ImageEntity volImage = new ImageEntity();
			volImage.setData(decoder.decodeBuffer(stic.getPicFlotVol().substring(stic.getPicFlotVol().indexOf(",") + 1)));
			volImage.setColspan(6);
			volImage.setRowspan(17);
			map.put("picFlotVol",volImage);
			}
			//获取导出模板地址
			ClassPathResource classPathResource = new ClassPathResource("excel_templates/batt_compare_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();
			}
			}
			//实时数据统计曲线-交流单元导出(2.5.2)
			public void exportRealStaticAc(HttpServletRequest req, HttpServletResponse resp, ResRealExportAc exportAc) throws IOException, ParseException, InterruptedException {
			List<String> picList=exportAc.getPicList();
			List<byte[]> bytes = new ArrayList<byte[]>();
			for (int i = 0; i < picList.size(); i++) {
			String picStr=picList.get(i);
			if (ServletUtils.isNotNull(picStr)) {
			String[] url = picStr.split(",");
			bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			}
			List<ResRealDataAc> dataListSorted=pwrdevHistorydataIdService.getSticRealAcDataForExport(exportAc);
			HSSFWorkbook wb = new HSSFWorkbook();
			//折线图表
			createRealSticPic(wb, bytes);
			//数据表
			createealSticTable(wb, dataListSorted,exportAc.getPropertyInfo(),"数据表");
			String excelName="实时数据统计曲线";
			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 createealSticTable(HSSFWorkbook wb, List<ResRealDataAc> list,Map<String,List<String>> propertyInfo, String sheetName) {
			HSSFSheet sheet = wb.createSheet(sheetName);
			sheet.setDefaultColumnWidth(15); //设置默认列宽，实际上回多出2个字符，不知道为什么
			sheet.setDefaultRowHeight((short) (1 * 256));//设置默认行高，表示2个字符的高度
			int rownum = 0;

			String[] arrTh = new String[]{
			"时间(HH:mm:ss)","机房名称","设备名称","电池组名称","单体编号","浮充电压(V)","单体内阻(mΩ)"
			};
			sheet.createRow(rownum);
			for(int i = 0 ;i<arrTh.length;i++){
			sheet.getRow(rownum).createCell(i).setCellValue(arrTh[i]);
			}
			rownum++;
			if(list!=null&&list.size()>=0){
			for(ResRealDataAc res:list){
			sheet.createRow(rownum);
			int i=0;
			sheet.getRow(rownum).createCell(i).setCellValue(res.getRecordDatetime());
			for (Map.Entry<String, List<String>> entry : propertyInfo.entrySet()) {
			String param = entry.getKey();
			List<String> valueList = entry.getValue();
			Map<String,Float> paramMap=res.getPropertyInfo().get(param);
			for (String value : valueList) {
			Float propertyValue = paramMap.get(value);
			sheet.getRow(rownum).createCell(i).setCellValue(propertyValue);
			i++;
			}
			}
			rownum++;
			}
			}
			}

			//折线图表
			private void createRealSticPic(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;
			}
			}
			}