package com.fgkj.servlets;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.fgkj.actions.ActionUtil;
import com.fgkj.dao.BattTestData;
import com.fgkj.dto.BattInf;
import com.fgkj.dto.Batt_Maint_Dealarm;
import com.fgkj.dto.Battalarm_data;
import com.fgkj.dto.Batttestdata;
import com.fgkj.dto.Ld9testdata;
import com.fgkj.dto.Ld9testdata_inf;
import com.google.gson.reflect.TypeToken;

import sun.misc.BASE64Decoder;

public class EchartPictureDowloadServlet extends HttpServlet {
	private String excelName = "Echarts";
	private List<byte[]> bytes;
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		String pageName = req.getParameter("pageName");
		if (ServletUtils.isNotNull(pageName)) {
			if ("charge-test".equalsIgnoreCase(pageName)) {
				chargeExcelExprot(req, resp);				//导出单个图表				
			}else if("control".equalsIgnoreCase(pageName)){
				controlExcelExprot(req,resp);				//实时监测导出图表
			}else if("exportTbal".equalsIgnoreCase(pageName)){
				ExprotReport(req,resp);						//导出报表
			}else if("charge-test-monData".equalsIgnoreCase(pageName)){
				ExprotLD9MonTestData(req,resp);
			}
		}
	}
	
	/**
	 * 历史放电数据页面中导出LD9设备的放电数据
	 * @param req
	 * @param resp
	 */
	private void ExprotLD9MonTestData(HttpServletRequest req,
			HttpServletResponse resp) {
		String mon_vol_line = req.getParameter("mon_vol_line");			//单体电压折线图
		String group_vol_line = req.getParameter("group_vol_line");		//组端/在线电压折线图
		String test_curr_line = req.getParameter("test_curr_line");		//测试电流折线图
		String batt_real_cap = req.getParameter("batt_real_cap");		//单体实际容量柱状图
		
		String battinf = req.getParameter("battinf");					//电池组信息
		String mon_test_data = req.getParameter("mon_test_data");		//单体测试数据
		String testdatainfo = req.getParameter("testdatainfo");		//单体测试数据
		
		
		bytes=new ArrayList<byte[]>();
		
		try {
			//单体电压折线图
			if (ServletUtils.isNotNull(mon_vol_line)) {
				String[] url = mon_vol_line.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
				//System.out.println("1111111111111111111111111");
			}
			
			//组端/在线电压折线图
			if (ServletUtils.isNotNull(group_vol_line)) {
				String[] url = group_vol_line.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
				//System.out.println("2222222222222222222222222");
			}
			
			//单体电流曲线图
			if (ServletUtils.isNotNull(test_curr_line)) {
				String[] url = test_curr_line.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
				//System.out.println("3333333333333333333333333333");
			}
			
			//单体电流曲线图
			if (ServletUtils.isNotNull(batt_real_cap)) {
				String[] url = batt_real_cap.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
				//System.out.println("3333333333333333333333333333");
			}
			BattInf binf = ActionUtil.getGson().fromJson(battinf, BattInf.class);
			Ld9testdata_inf mondatainfo = ActionUtil.getGson().fromJson(testdatainfo, Ld9testdata_inf.class);
			List<Ld9testdata> montestdata = ActionUtil.getGson().fromJson(mon_test_data, new TypeToken<List<Ld9testdata>>(){}.getType());
			createMonTestDataExcel(bytes,binf,montestdata,mondatainfo,resp,req);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
	
	//生成历史数据中LD9单体数据的报表
	private void createMonTestDataExcel(List<byte[]> bytes,BattInf binf,List<Ld9testdata> montestdata,Ld9testdata_inf mondatainfo,HttpServletResponse resp,HttpServletRequest req) {
		try {
			// 创建一个工作薄
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet1 = wb.createSheet("数据总表");
			// HSSFRow row = sheet1.createRow(2);
			HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
			int rownum = 1;
		
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue("电池组名称");
			sheet1.getRow(rownum).createCell(2).setCellValue(binf.getStationName()+"-"+binf.getBattGroupName());
			rownum++;
			
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue( "电池品牌："+binf.getBattProducer());
			sheet1.getRow(rownum).createCell(2).setCellValue( "安装日期："+formartDate(binf.getBattInUseDate(),"yyyy-MM-dd"));
			sheet1.getRow(rownum).createCell(3).setCellValue( "单体数量："+binf.getMonCount());
			sheet1.getRow(rownum).createCell(4).setCellValue( "单体电压："+binf.getMonVolStd()+"V");
			sheet1.getRow(rownum).createCell(5).setCellValue( "单体标称容量："+formartDouble(binf.getMonCapStd(),0)+"AH");
			sheet1.getRow(rownum).createCell(6).setCellValue( "单体标称内阻："+formartDouble(binf.getMonResStd(),3)+"mΩ");
			sheet1.getRow(rownum).createCell(7).setCellValue( "单体标称电导："+formartDouble(binf.getMonSerStd(),0));
			
			rownum++;
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue( "被测单体编号：#"+mondatainfo.getMon_num());
			sheet1.getRow(rownum).createCell(2).setCellValue( "测试类型："+binf.getBattGroupName5());
			sheet1.getRow(rownum).createCell(3).setCellValue( "测试时间："+formartDate(mondatainfo.getTest_starttime(),"yyyy-MM-dd HH:mm:ss"));
			sheet1.getRow(rownum).createCell(4).setCellValue( "测试电流："+mondatainfo.getTest_curr()+"A");
			sheet1.getRow(rownum).createCell(5).setCellValue( "测试时长："+formatTestLong(mondatainfo.getTest_timelong()));
			sheet1.getRow(rownum).createCell(6).setCellValue( "测试容量："+formartDouble(mondatainfo.getTest_cap(),0)+"AH");
			sheet1.getRow(rownum).createCell(7).setCellValue( "终止原因："+binf.getBattGroupName6());
			
			rownum++;
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue( "报告日期：");
			sheet1.getRow(rownum).createCell(2).setCellValue(formartDate(new Date(),"yyyy-MM-dd HH:mm:ss"));
			rownum+=2;
			
			int picnum = 0;
			String[] picName = new String[]{
					"单体电压曲线图","端电压曲线图","单体电流曲线图","单体实际容量柱状图"
			};
			for(int i=0;i<picName.length;i++){
				sheet1.createRow(rownum);
				sheet1.getRow(rownum).createCell(1).setCellValue(picName[i]);
				rownum++;
				HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
				anchor.setAnchorType(3);
				patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
				picnum ++;rownum+=25;
			}

			createNewLD9DataSheet(wb,montestdata);
			
			// 转码防止乱码
			resp.addHeader("Content-Disposition", "attachment;filename="
					+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
					+ ".xls");
			OutputStream out = resp.getOutputStream();
			wb.write(out);
			out.close();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	
	public void createNewLD9DataSheet(HSSFWorkbook wb,final List<Ld9testdata> montestdata){
		HSSFSheet sheet = wb.createSheet("单体数据表");
		if(montestdata != null && montestdata.size()>0){
				
			int mon_count = 1;						//总单体数量
			int total_col = montestdata.size();		//总记录条数
			
			
			List<String> tabTh = new ArrayList<String>(){{
				this.add("时间(HH:MM:SS)");this.add("组端电压(V)");this.add("在线电压(V)");this.add("电流(A)");this.add("测试容量(AH)");this.add("单体电压"+montestdata.get(0).getTest_monnum()+"(V)");
			}};
			//for(int i=1;i<=mon_count;i++){
			//	tabTh.add("单体电压"+i+"(V)");
			//}
			
			String[][] datas = new String[total_col][tabTh.size()];
			
			//System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
			
			for(int j=0;j < datas.length;j++){
				int currnum = 0;	
				datas[j][currnum++] = formatTestLong(montestdata.get(j).getTest_timelong());
				datas[j][currnum++] = formartDouble(montestdata.get(j).getGroup_vol(),1)+"";
				datas[j][currnum++] = formartDouble(montestdata.get(j).getOnline_vol(),1)+"";
				datas[j][currnum++] = formartDouble(montestdata.get(j).getTest_curr(),3)+"";
				datas[j][currnum++] = formartDouble(montestdata.get(j).getTest_cap(),1)+"";
				
				
				datas[j][currnum++] =formartDouble(montestdata.get(j).getMon_vol(),3)+"";
				
				//datas[j][currnum++] = "";
				
			}	
			//System.out.println("mon_vol_list"+mon_vol_list.length+"\t mon_tmp_list:"+mon_tmp_list.length);
			
			int maxcol = 254;			//设置最大列数
			
			int countReLine = (int) Math.ceil((double)total_col/maxcol);
			int totalrow = countReLine *(tabTh.size()+1);					//excel总行数
			for(int i=0;i<totalrow;i++){
				sheet.createRow(i);
			}
			
			//System.out.println("countReLine:"+countReLine);
			
			int currRow = 0;			//当前行
			int arr_index = 0;			//当前列
			for(int i=0;i<countReLine;i++){
				for(int col = 0;col <= maxcol && col<=datas.length && arr_index<datas.length;col++){					
					if(col == 0){
						for(int k=0;k<tabTh.size();k++){
							sheet.getRow(k+currRow).createCell(col).setCellValue(tabTh.get(k));
						}
					}else{
						for(int k = 0;k<datas[arr_index].length;k++){
							sheet.getRow(k+currRow).createCell(col).setCellValue(datas[arr_index][k]);
						}
						arr_index++;
					}
				}
				currRow += tabTh.size()+1;
			}		
			
			//System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
		}
	}
	

	/**
	 * 导出报表
	 * @param req
	 * @param resp
	 */
	private void ExprotReport(HttpServletRequest req, HttpServletResponse resp) {		
		String ltop_echart = req.getParameter("ltop_echart");			//组端电压折线图
		String rtop_echart = req.getParameter("rtop_echart");			//电池电流折线图
		String lbottom_echart = req.getParameter("lbottom_echart");		//单体电压折线图
		String rbottom_echart = req.getParameter("rbottom_echart");		//单体电压柱状图
		String actucap_echart = req.getParameter("actucap_echart");		//实际容量折线图
		String restcap_echart = req.getParameter("restcap_echart");		//实际容量折线图
		String capperc_echart = req.getParameter("capperc_echart");		//实际容量折线图
		String montmp_echart = req.getParameter("tmp_echart");			//单体温度折线图
		String mon_res_echart = req.getParameter("mon_res");			//单体内阻折线图
		String last_vol_echart = req.getParameter("last_vol");			//单体终止电压折线图
		String last_tmp_echart = req.getParameter("last_tmp");			//单体终止电压折线图
		
		
		bytes=new ArrayList<byte[]>();
		Batt_Maint_Dealarm bmd = ActionUtil.getGson("yyyy-MM-dd HH:mm:ss").fromJson(req.getParameter("obj-bmd"), Batt_Maint_Dealarm.class);
		Title echarttitle = ActionUtil.getGson().fromJson(req.getParameter("obj-title"), Title.class);
		String[][] datas = ActionUtil.getGson().fromJson(req.getParameter("arr-data"), String[][].class);
		
		String[][] mon_vol_list = ActionUtil.getGson().fromJson(req.getParameter("mon-vol-list"), String[][].class);
		String[][] mon_tmp_list = ActionUtil.getGson().fromJson(req.getParameter("mon-tmp-list"), String[][].class);
		List<Batttestdata> groupinfo = ActionUtil.getGson().fromJson(req.getParameter("mon-group-list"),  new TypeToken<List<Batttestdata>>(){}.getType());
		
		//System.out.println("mon_vol_list:"+mon_vol_list.length+"\t mon_tmp_list:"+mon_tmp_list.length+"\t groupinfo:"+groupinfo.size());
		
		//System.out.println(groupinfo);
		//System.out.println(echarttitle);
		//System.out.println(datas);
		try {
			//组端电压曲线图
			if (ServletUtils.isNotNull(ltop_echart)) {
				String[] url = ltop_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			//电池电流曲线图
			if (ServletUtils.isNotNull(rtop_echart)) {
				String[] url = rtop_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			//电池单体终止电压曲线图
			if (ServletUtils.isNotNull(last_vol_echart)) {
				String[] url = last_vol_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			//电池单体终止温度曲线图
			if (ServletUtils.isNotNull(last_tmp_echart)) {
				String[] url = last_tmp_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			
			//单体内阻柱状图
			if (ServletUtils.isNotNull(mon_res_echart)) {
				String[] url = mon_res_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			
			//单体电压柱状图
			if (ServletUtils.isNotNull(rbottom_echart)) {
				String[] url = rbottom_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			//单体实际容量柱状图
			if(ServletUtils.isNotNull(actucap_echart)){
				String[] url = actucap_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			//单体剩余容量柱状图
			if(ServletUtils.isNotNull(restcap_echart)){
				String[] url = restcap_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			//单体容量百分比柱状图
			if(ServletUtils.isNotNull(capperc_echart)){
				String[] url = capperc_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			//单体电压曲线图
			if (ServletUtils.isNotNull(lbottom_echart)) {
				String[] url = lbottom_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			
			//单体温度折线图
			if(ServletUtils.isNotNull(montmp_echart)){
				String[] url = montmp_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		if(bytes.size()>0){
			createExcel(bytes, echarttitle, datas, bmd,mon_vol_list,mon_tmp_list,groupinfo, resp, req);
		}	
		
	}

	
	private void controlExcelExprot(HttpServletRequest req,
			HttpServletResponse resp) {
		String only_echart=req.getParameter("only_echart");
		bytes=new ArrayList<byte[]>();
		if(ServletUtils.isNotNull(only_echart)){
			try {
				String[] url = only_echart.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
				createExcel(bytes, resp, req);
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	private void chargeExcelExprot(HttpServletRequest req,
			HttpServletResponse resp) {
		// String fileName = req.getParameter("filename");
		
		String Big_picture=req.getParameter("big_pic");
		// System.out.println(ltop_echart+"***");
		bytes = new ArrayList<byte[]>();
		try {
			
			if(ServletUtils.isNotNull(Big_picture)){
				String[] url = Big_picture.split(",");
				bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
			}
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		if (bytes.size() > 0) {
			
			createExcel(bytes, resp, req);
		}
	}

	private void createExcel(List<byte[]> bytes, HttpServletResponse resp,
			HttpServletRequest req) {
		try {
			// 创建一个工作薄
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet1 = wb.createSheet("new sheet");
			// HSSFRow row = sheet1.createRow(2);
			HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
			for (int i = 0; i < bytes.size(); i++) {
				HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, 25 * i + 1, (short) 10, 25 * (i + 1));
				anchor.setAnchorType(3);
				// 插入图片
				patriarch.createPicture(
						anchor,
						wb.addPicture(bytes.get(i),
								HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
			}

			// 转码防止乱码
			resp.addHeader("Content-Disposition", "attachment;filename="
					+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
					+ ".xls");
			OutputStream out = resp.getOutputStream();
			wb.write(out);
			out.close();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	//生成历史数据中的报表
	private void createExcel(List<byte[]> bytes,Title title,String[][] datas,Batt_Maint_Dealarm bmd,String[][] mon_vol_list ,String[][] mon_tmp_list,List<Batttestdata> groupinfo, HttpServletResponse resp,
			HttpServletRequest req) {
		String[] arrTh = new String[]{
				"单体编号","起始单体电压(V)","截止单体电压(V)","实际容量(AH)","剩余容量(AH)","实际容量百分比(%)","单体内阻(mΩ)","起始单体温度(℃)","终止单体温度(℃)"
		};
		String[] picName = new String[]{
				"组端电压曲线","电池电流曲线","终止单体电压","终止单体温度","单体内阻","单体电压","单体实际容量","单体剩余容量","单体实际容量百分比","单体电压曲线","单体温度曲线"
		};
		try {
			// 创建一个工作薄
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet1 = wb.createSheet("数据总表");
			// HSSFRow row = sheet1.createRow(2);
			HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
			int rownum = 1;
		
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue("电池组名称");
			sheet1.getRow(rownum).createCell(2).setCellValue(bmd.getBinf().getStationName()+"-"+bmd.getBinf().getBattGroupName());
			rownum++;
			
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue( "电池品牌："+bmd.getBinf().getBattProducer());
			sheet1.getRow(rownum).createCell(2).setCellValue( "安装日期："+formartDate(bmd.getBinf().getBattInUseDate(),"yyyy-MM-dd"));
			sheet1.getRow(rownum).createCell(3).setCellValue( "单体数量："+bmd.getBinf().getMonCount());
			sheet1.getRow(rownum).createCell(4).setCellValue( "单体电压："+bmd.getBinf().getMonVolStd()+"V");
			sheet1.getRow(rownum).createCell(5).setCellValue( "单体标称容量："+formartDouble(bmd.getBinf().getMonCapStd(),0)+"AH");
			sheet1.getRow(rownum).createCell(6).setCellValue( "单体标称内阻："+formartDouble(bmd.getBinf().getMonResStd(),3)+"mΩ");
			sheet1.getRow(rownum).createCell(7).setCellValue( "单体标称电导："+formartDouble(bmd.getBinf().getMonSerStd(),0));
			
			rownum++;
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue( "测试类型："+BattTestData.battState(bmd.getSdata().getTest_type()));
			sheet1.getRow(rownum).createCell(2).setCellValue( "测试时间："+formartDate(bmd.getSdata().getTest_starttime(),"yyyy-MM-dd HH:mm:ss"));
			sheet1.getRow(rownum).createCell(3).setCellValue( "测试电流："+bmd.getSdata().getTest_curr()+"A");
			sheet1.getRow(rownum).createCell(4).setCellValue( "测试时长："+formatTestLong(bmd.getSdata().getTest_timelong()));
			sheet1.getRow(rownum).createCell(5).setCellValue( "测试容量："+formartDouble(bmd.getSdata().getTest_cap(),0)+"AH");
			sheet1.getRow(rownum).createCell(6).setCellValue( "终止原因："+(bmd.getSdata().getTest_type()==2?"":bmd.getBinf().getStationIp()));
			
			rownum++;
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue( "报告日期：");
			sheet1.getRow(rownum).createCell(2).setCellValue(formartDate(new Date(),"yyyy-MM-dd HH:mm:ss"));
			rownum+=2;
			
			int picnum = 0;
			
			for(int i=0;i<picName.length;i++){
				sheet1.createRow(rownum);
				sheet1.getRow(rownum).createCell(1).setCellValue(picName[i]);
				rownum++;
				HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
				anchor.setAnchorType(3);
				patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
				picnum ++;rownum+=25;
			}

			/*sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue("单体实际容量");
			rownum++;
			anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
			anchor.setAnchorType(3);
			patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
			picnum ++;rownum+=25;
			
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue("单体剩余容量");
			rownum++;
			anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
			anchor.setAnchorType(3);
			patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
			picnum ++;rownum+=25;
			
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue("单体实际容量百分比");
			rownum++;
			anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
			anchor.setAnchorType(3);
			patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
			picnum ++;rownum+=25;
			
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue("单体电压曲线");
			rownum++;
			anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
			anchor.setAnchorType(3);
			patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
			picnum ++;rownum+=25;
			
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue("组端电压曲线");
			rownum++;
			anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
			anchor.setAnchorType(3);
			patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
			picnum ++;rownum+=25;
			
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue("电池电流曲线");
			rownum++;
			anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (picnum + 1));
			anchor.setAnchorType(3);
			patriarch.createPicture(anchor,wb.addPicture(bytes.get(picnum),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
			picnum ++;rownum+=25;*/
			
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue("最高单体电压");
			sheet1.getRow(rownum).createCell(2).setCellValue("最低单体电压");
			sheet1.getRow(rownum).createCell(3).setCellValue("平均单体电压");
			sheet1.getRow(rownum).createCell(4).setCellValue("落后单体电压值");
			sheet1.getRow(rownum).createCell(5).setCellValue("落后单体数量");
			sheet1.getRow(rownum).createCell(6).setCellValue("落后单体数量百分比");
			rownum++;
			
			sheet1.createRow(rownum);
			sheet1.getRow(rownum).createCell(1).setCellValue(title.getMax());
			sheet1.getRow(rownum).createCell(2).setCellValue(title.getMin());
			sheet1.getRow(rownum).createCell(3).setCellValue(title.getAvg());
			sheet1.getRow(rownum).createCell(4).setCellValue(title.getClow());
			sheet1.getRow(rownum).createCell(5).setCellValue(title.getLc());
			sheet1.getRow(rownum).createCell(6).setCellValue(title.getLp()+"%");
			rownum+=2;
			
			sheet1.createRow(rownum);
			for(int i = 0 ;i<arrTh.length;i++){
				sheet1.getRow(rownum).createCell(i+1).setCellValue(arrTh[i]);
			}
			rownum++;
			
			if(datas!=null && datas.length>0){
				for(int i=0;i<datas[0].length;i++){
					sheet1.createRow(rownum);
					for(int j=0;j<datas.length;j++){
						sheet1.getRow(rownum).createCell(j+1).setCellValue(datas[j][i]);
					}
					rownum++;
				}
			}
			
			createMonInfoSheet(wb,mon_vol_list ,mon_tmp_list,groupinfo);
			
			// 转码防止乱码
			resp.addHeader("Content-Disposition", "attachment;filename="
					+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
					+ ".xls");
			OutputStream out = resp.getOutputStream();
			wb.write(out);
			out.close();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public void createMonInfoSheet(HSSFWorkbook wb,String[][] mon_vol_list ,String[][] mon_tmp_list,List<Batttestdata> groupinfo){
		HSSFSheet sheet = wb.createSheet("单体数据表");
		if(mon_vol_list != null && mon_tmp_list != null && groupinfo != null){
			int mon_count = mon_tmp_list.length;		//总单体数量
			int total_col = mon_vol_list.length;		//总记录条数
			
			
			List<String> tabTh = new ArrayList<String>(){{
				this.add("时间(HH:MM:SS)");this.add("组端电压(V)");this.add("电流(A)");this.add("测试容量(AH)");
			}};
			for(int i=1;i<=mon_count;i++){
				tabTh.add("单体电压"+i+"(V)");
			}
			for(int i=0;i<=mon_count;i++){
				if(i == 0){
					tabTh.add("");
					continue;
				}
				tabTh.add("单体温度"+i+"(℃)");
			}
			
			String[][] datas = new String[total_col][tabTh.size()];
			
			//System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
			
			for(int j=0;j < datas.length;j++){
				int currnum = 0;	
				datas[j][currnum++] = formatTestLong(groupinfo.get(j).getTest_timelong());
				datas[j][currnum++] = formartDouble(groupinfo.get(j).getGroup_vol(),1)+"";
				datas[j][currnum++] = formartDouble(groupinfo.get(j).getTest_curr(),1)+"";
				datas[j][currnum++] = formartDouble(groupinfo.get(j).getTest_cap(),1)+"";
				
				for(int i=0;i<mon_vol_list[j].length;i++){
					datas[j][currnum++] = mon_vol_list[j][i];
				}
				datas[j][currnum++] = "";
				for(int i=0;i<mon_tmp_list.length;i++){
					datas[j][currnum++] = mon_tmp_list[i][j];
				}
			}	
			//System.out.println("mon_vol_list"+mon_vol_list.length+"\t mon_tmp_list:"+mon_tmp_list.length);
			
			int maxcol = 254;			//设置最大列数
			
			int countReLine = (int) Math.ceil((double)total_col/maxcol);
			int totalrow = countReLine *(tabTh.size()+1);					//excel总行数
			for(int i=0;i<totalrow;i++){
				sheet.createRow(i);
			}
			
			//System.out.println("countReLine:"+countReLine);
			
			int currRow = 0;			//当前行
			int arr_index = 0;			//当前列
			for(int i=0;i<countReLine;i++){
				for(int col = 0;col <= maxcol && col<=datas.length && arr_index<datas.length;col++){					
					if(col == 0){
						for(int k=0;k<tabTh.size();k++){
							sheet.getRow(k+currRow).createCell(col).setCellValue(tabTh.get(k));
						}
					}else{
						for(int k = 0;k<datas[arr_index].length;k++){
							sheet.getRow(k+currRow).createCell(col).setCellValue(datas[arr_index][k]);
						}
						arr_index++;
					}
				}
				currRow += tabTh.size()+1;
			}		
			
			//System.out.println("data.length"+datas.length+"\t datas[0].length:"+datas[0].length);
		}
	}
	
	/**
	 * 将日期格式转换成指定的字符串格式
	 * @param date	日期
	 * @param str	字符串的格式
	 * @return
	 */
	public static String formartDate(Date date,String str){
		return new SimpleDateFormat(str).format(date);
	}
	
	//将秒转化成时:分:秒
	public static String formatTestLong(long theTime) {
		long theTime1 = 0;// 分
		long theTime2 = 0;// 小时
		// alert(theTime);
		if(theTime >= 60) {
			theTime1 = theTime/60;
			theTime = theTime%60;
			//alert(theTime1+"-"+theTime);
			if(theTime1 >= 60) {
				theTime2 = theTime1/60;
				theTime1 = theTime1%60;
			}
		}
		String result = (theTime<10?"0":"")+theTime;
		if(theTime1 >= 0) {
			result =(theTime1<10?"0":"")+theTime1+":"+result;
		}
		if(theTime2 >= 0) {
			result =(theTime2<10?"0":"")+theTime2+":"+result;
		}
		//console.info(result);
		return result;
	}
	
	public static double formartDouble(double b , int places){
		return new   BigDecimal(b).setScale(places,   BigDecimal.ROUND_HALF_UP).doubleValue();  
	}
}