package com.fgkj.servlets;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import sun.misc.BASE64Decoder;

public class ExportTableServlet extends HttpServlet {
	private String excelName = "Echarts";
	private String pictureName;
	private String excelTitle;
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doPost(req, resp);
	}
	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		String pageName=req.getParameter("PageName");
		
		String exp_KeyWord = req.getParameter("exp_normal");			//通用到处表格标识符
		excelTitle = req.getParameter("excelTitle");					//获取excelTitel标题

		if(ServletUtils.isNotNull(pageName)){
			if("ele_statistics.jsp".equals(pageName)){
				excelTitle="电池信息统计查询";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("eleAnalyse.jsp".equals(pageName)){
				excelTitle="电池组统计查询";
				pictureName="电池组统计分析图";
				Exportele_eleAnalyseTable(resp,req);
			}else if("eleBrdwMaint.jsp".equals(pageName)){
				excelTitle="电池故障维护记录查询";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("eleBrdwDeal.jsp".equals(pageName)){
				excelTitle="电池故障处理统计查询";
				Exportele_statisticsTable(resp,req);
			}else if("infor_manage.jsp".equals(pageName)){
				excelTitle="用户信息管理";
				Exporttele_eleBrdwMaintTable(resp, req);
			}else if("elewarn.jsp".equals(pageName)){
				excelTitle="电池告警实时查询";
				Exportele_statisticsTable(resp, req);
			}else if("eleHistoryWarn.jsp".equals(pageName)){
				excelTitle="电池告警历史记录查询";
				Exportele_statisticsTable(resp, req);
			}else if("eleMonomer.jsp".equals(pageName)){
				excelTitle="电池单体统计分析查询";
				pictureName="单体趋势图";
				Exportele_eleAnalyseTable(resp,req);
			}else if("dataManage.jsp".equals(pageName)){
				excelTitle="电池充放电数据查询";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("jobCheckMgr.jsp".equals(pageName)){
				excelTitle="作业抽查管理";
				Exporttele_eleBrdwMaintTable(resp, req);
			}else if("jobRpt.jsp".equals(pageName)){
				excelTitle="作业统计分析查询";
				Exporttele_eleBrdwMaintTable(resp, req);
			}else if("eleAssess.jsp".equals(pageName)){
				excelTitle="电池组后评估表";
				Exporttele_eleBrdwMaintTable(resp,req);
			} else if("myJob.jsp".equals(pageName)){
				excelTitle="作业明细列表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("jobMgr_up".equals(pageName)){
				excelTitle="作业列表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("jobMgr_down".equals(pageName)){
				excelTitle="作业明细列表";
				Exporttele_eleBrdwMaintTable(resp,req);				
			}else if("inforpt.jsp".equalsIgnoreCase(pageName)){
				excelTitle=req.getParameter("title");
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("taskplan.jsp".equalsIgnoreCase(pageName)){
				excelTitle="电池落后单体列表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("dischargeplan.jsp".equalsIgnoreCase(pageName)){
				excelTitle="放电计划列表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("equipment.jsp".equalsIgnoreCase(pageName)){
				excelTitle="设备用电量统计查询列表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("elestatus.jsp".equalsIgnoreCase(pageName)){
				excelTitle="电池状态列表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("endure.jsp".equalsIgnoreCase(pageName)){
				excelTitle="电池组续航能力表";
				Exporttele_eleBrdwMaintTable(resp,req);		
			}else if("powercut.jsp".equalsIgnoreCase(pageName)){
				excelTitle="机房停电列表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("equipWarn1.jsp".equalsIgnoreCase(pageName)){
				excelTitle="设备实时告警记录表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("equipHistWarn1.jsp".equalsIgnoreCase(pageName)){
				excelTitle="设备历史告警记录表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("equip-charge-param.jsp".equalsIgnoreCase(pageName)){
				excelTitle="设备充放电模块参数表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("batt-attention.jsp".equalsIgnoreCase(pageName)){
				excelTitle="电池单体关注表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("ele-param.jsp".equalsIgnoreCase(pageName)){
				excelTitle="电池组信息表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("equip-stats-change.jsp".equalsIgnoreCase(pageName)){
				excelTitle="设备状态变化记录表";
				Exporttele_eleBrdwMaintTable(resp,req);
			}else if("autoPageName".equalsIgnoreCase(pageName)){
				if(!ServletUtils.isNotNull(excelTitle)){
					excelTitle = "";
				}
				Exporttele_eleBrdwMaintTable(resp,req);
			}
		}else if(ServletUtils.isNotNull(exp_KeyWord)){
			if("1".equals(exp_KeyWord)){
				ExportGeneralExcelTable(resp,req);					//导出通用的表格
			}
		}
	}
	
	private void ExportGeneralExcelTable(HttpServletResponse resp,
			HttpServletRequest req) {
		String exp_thead_str = req.getParameter("exp_thead");
		String exp_tbody_str = req.getParameter("exp_tbody");
		
		String exp_page_break = "\\"+req.getParameter("exp_page_break");
		String exp_data_break = "\\"+req.getParameter("exp_data_break");
		
		String exp_page_names_str =  req.getParameter("exp_page_names");
		
		String[] exp_page_names = new String[]{};
		
		
		
		
		
		
		try {
			//定义一个工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			
			if(ServletUtils.isNotNull(exp_thead_str)){
				exp_page_names = exp_page_names_str.split(exp_page_break);			//分割工作簿的名称
				
				String[] exp_thead_sheets = exp_thead_str.split(exp_page_break);
				String[] exp_tbody_sheets = exp_tbody_str.split(exp_page_break);	
				if(!ServletUtils.isNotNull(exp_page_names_str)){
					exp_page_names = new String[]{};
					for(int s=0;s<exp_tbody_sheets.length;s++){
						exp_page_names[s] = "sheet"+s;
					}
				}					
					
				for (int i = 0; i < exp_thead_sheets.length; i++) {
					HSSFSheet sheet = i<exp_page_names.length?workbook.createSheet(exp_page_names[i]):workbook.createSheet();
					String[] exp_thead = exp_thead_sheets[i].split(exp_data_break);
					String[] exp_tbody = exp_tbody_sheets[i].split(exp_data_break);
					
					int maxColNum = exp_thead.length;			//最大列数
					int nowRowNum = 0;							//当前行
					int nowColNum = 0;							//当前列
					HSSFRow row = null;
					for(int j=0;j <= exp_tbody.length;j++){
						//System.out.println("j="+j+"\t nowRowNum="+nowRowNum);
						if(j == 0){
							row = sheet.createRow(nowRowNum++); 
							for(int k=0;k<exp_thead.length;k++){	
								HSSFCell cell = row.createCell(k);
								HSSFRichTextString text = new HSSFRichTextString(exp_thead[k]);
								//System.out.print(text+"\t");
								cell.setCellValue(text);
							}
							continue;
						}else if(j%maxColNum == 1){
							nowColNum = 0;
							row = sheet.createRow(nowRowNum++);
						}
						HSSFCell cell = row.createCell(nowColNum++);
						HSSFRichTextString text = new HSSFRichTextString(exp_tbody[j-1]);
						//HSSFRichTextString text = new HSSFRichTextString("*****");
						//System.out.print(text+"\t");
						cell.setCellValue(text);						
					}					
				}					
				
			}
					
			// 转码防止乱码
			resp.addHeader("Content-Disposition", "attachment;filename="
					+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
					+ ".xls");
			OutputStream out = resp.getOutputStream();
			workbook.write(out);
			out.close();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private void Exporttele_eleBrdwMaintTable(HttpServletResponse resp,
			HttpServletRequest req) {
		String[] th_arr=req.getParameter("table_th_arr").split(",");
		String[] td_arr=req.getParameter("table_td_arr").split("&");
		
		//int len=(int)Math.ceil((double)td_arr.length/th_arr.length);
//		
		//System.out.println(th_arr.length+"==="+len);
		//System.out.println(td_arr.length+"==="+len);
		
		
		try {
			//定义一个工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet(excelTitle);
			// 产生表格标题行  
			HSSFRow title = sheet.createRow(0);
			HSSFCell titlecell = title.createCell(0);
			HSSFRichTextString tname = new HSSFRichTextString(excelTitle);
			//System.out.print(text+"\t");
			titlecell.setCellValue(tname);
			int rowNum = 0;
			if(th_arr!=null && th_arr.length>0){				
				HSSFRow row0 = sheet.createRow(1);         
				for(int i=0;i<th_arr.length;i++){
					HSSFCell cell = row0.createCell(i);
					HSSFRichTextString text = new HSSFRichTextString(th_arr[i]);
					//System.out.print(text+"\t");
					cell.setCellValue(text);
				}
				
				if(td_arr!=null && td_arr.length>0){
					rowNum=(int)Math.ceil((double)td_arr.length/th_arr.length);		//总行数
					int index=0;
					//System.out.println("td_arr.length"+td_arr.length);
					//System.out.println("th_arr.length"+th_arr.length);
					for(int i=1;i<=rowNum;i++){
						HSSFRow row = sheet.createRow(i+1);
						//System.out.println(i);
						for(int j=0;j<th_arr.length && index<td_arr.length;j++){
							HSSFCell cell = row.createCell(j);
							HSSFRichTextString text = new HSSFRichTextString(td_arr[index++]);
							//System.out.print((i-1)*rowNum+j+"\t");
							//System.out.print(text+"\t");
							cell.setCellValue(text);
						}
						//System.out.println(index);
					}
					
				}
			}
			
			String exp_picture_str = req.getParameter("exp_picture_str");
			if(ServletUtils.isNotNull(exp_picture_str)){
				rowNum = rowNum+3;
				HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
				//当页面传入图片时
				//System.out.println(exp_picture_str);
				String[] url = exp_picture_str.split(",");
				byte[] picbyte= new BASE64Decoder().decodeBuffer(url[1]);
				HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 0, rowNum, (short) 10, 25 * (rowNum + 1));
				anchor.setAnchorType(3);
				patriarch.createPicture(anchor,workbook.addPicture(picbyte,HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
				
			}
			// 转码防止乱码
			resp.addHeader("Content-Disposition", "attachment;filename="
					+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
					+ ".xls");
			OutputStream out = resp.getOutputStream();
			workbook.write(out);
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}     
	}

	//导出eleAnalyse.jsp页面中的表格和图表到Excel中
	private void Exportele_eleAnalyseTable(HttpServletResponse resp,
			HttpServletRequest req) {
		String[] arr_Th=req.getParameter("arr_Th").split(",");
		String[] arr_Td=req.getParameter("arr_Td").split(",");
		String[] echart_url=req.getParameter("echart_url").split(",");
		int rowindex=4;
		//System.out.println(arr_Td.length+"##"+arr_Th.length);
		//定义一个工作簿
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(excelTitle);
		// 产生表格标题行  
		HSSFRow title = sheet.createRow(0);
		HSSFCell titlecell = title.createCell(0);
		HSSFRichTextString tname = new HSSFRichTextString(excelTitle);
		//System.out.print(text+"\t");
		titlecell.setCellValue(tname);		
		if(arr_Th!=null && arr_Th.length>0){
			HSSFRow row0 = sheet.createRow(1);         
			for(int i=0;i<arr_Th.length;i++){
				HSSFCell cell = row0.createCell(i);
				HSSFRichTextString text = new HSSFRichTextString(arr_Th[i]);
				//System.out.print(text+"\t");
				cell.setCellValue(text);
			}
			
			if(arr_Td!=null && arr_Td.length>0){
				int rowNum=(int)(Math.ceil((double)arr_Td.length/arr_Th.length));		//总行数
				int index=0;
				//System.out.println("总共有"+rowNum+"行");
				for(int i=1;i<=rowNum;i++){
					HSSFRow row = sheet.createRow(i+1); 
					for(int j=0;j<arr_Th.length && index<arr_Td.length;j++){
						HSSFCell cell = row.createCell(j);
						HSSFRichTextString text = new HSSFRichTextString(arr_Td[index++]);
						//System.out.print((i-1)*rowNum+j+"\t");
						//System.out.print(text+"\t");
						cell.setCellValue(text);
					}
				}
				rowindex=rowNum+3;
			}
		}
		HSSFRow row = sheet.createRow(rowindex); 
		HSSFCell cell = row.createCell(0);
		HSSFRichTextString text = new HSSFRichTextString(pictureName);
		//System.out.print((i-1)*rowNum+j+"\t");
		//System.out.print(text+"\t");
		cell.setCellValue(text);
		if(echart_url!=null && echart_url.length>0){
			List<byte[]> bytes=new ArrayList<byte[]>();
			try {
				bytes.add(new BASE64Decoder().decodeBuffer(echart_url[1]));
				HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
				for (int i = 0; i < bytes.size(); i++) {
					HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,
							(short) 0, rowindex+1, (short) 0, 1000 * (2 + 1));
					anchor.setAnchorType(3);
					// 插入图片
					patriarch.createPicture(
							anchor,
							workbook.addPicture(bytes.get(i),
									HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
				}

			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		// 转码防止乱码
		try {
			resp.addHeader("Content-Disposition", "attachment;filename="
					+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
					+ ".xls");
			OutputStream out = resp.getOutputStream();
			workbook.write(out);
			out.close();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
	
	//导出ele_statistics.jsp页面中的表格数据到Excel
	private void Exportele_statisticsTable(HttpServletResponse resp,
			HttpServletRequest req) {
		String[] th_arr=req.getParameter("table_th_arr").split(",");
		String[] td_arr=req.getParameter("table_td_arr").split(",");
		
		//int len=td_arr.length/th_arr.length;
		
		//System.out.println(th_arr.length+"==="+len);
		//System.out.println(td_arr.length+"==="+len);
		
		
		try {
			//定义一个工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet(excelTitle);
			// 产生表格标题行  
			HSSFRow title = sheet.createRow(0);
			HSSFCell titlecell = title.createCell(0);
			HSSFRichTextString tname = new HSSFRichTextString(excelTitle);
			//System.out.print(text+"\t");
			titlecell.setCellValue(tname);
			
			if(th_arr!=null && th_arr.length>0){				
				HSSFRow row0 = sheet.createRow(1);         
				for(int i=0;i<th_arr.length;i++){
					HSSFCell cell = row0.createCell(i);
					HSSFRichTextString text = new HSSFRichTextString(th_arr[i]);
					//System.out.print(text+"\t");
					cell.setCellValue(text);
				}
				
				if(td_arr!=null && td_arr.length>0){
					int rowNum=td_arr.length/th_arr.length;		//总行数
					int index=0;
					//System.out.println("th_arr.length"+td_arr.length);
					//System.out.println("th_arr.length"+th_arr.length);
					for(int i=1;i<=rowNum;i++){
						HSSFRow row = sheet.createRow(i+1);
						//System.out.println(i);
						for(int j=0;j<th_arr.length;j++){
							HSSFCell cell = row.createCell(j);
							HSSFRichTextString text = new HSSFRichTextString(td_arr[index++]);
							//System.out.print((i-1)*rowNum+j+"\t");
							//System.out.print(text+"\t");
							cell.setCellValue(text);
						}
						//System.out.println(index);
					}
					
				}
			}
			// 转码防止乱码
			resp.addHeader("Content-Disposition", "attachment;filename="
					+ new String(excelName.getBytes("gb2312"), "ISO8859-1")
					+ ".xls");
			OutputStream out = resp.getOutputStream();
			workbook.write(out);
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}     
       
	}
	
	public static void main(String[] args) {
		String ths = "h1&h2&h3&h4$xh1&xh2&xh3&xh4";
		System.out.println(ths.indexOf("$"));
		String[] sheets = ths.split("\\&");
		System.out.println(sheets.length);
	}
}