报表开发 --代码优化（第二版）

杜XX看到我发表的code 提出了宝贵意见，根据他所说的，昨晚上对代码进行了改进，哈哈 看着舒服多了

package javaservlets.production.mpexcel;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javaservlets.common.util.Debug;
import javaservlets.common.util.SimpleServlet;

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

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

@SuppressWarnings({ "unchecked", "serial" })
public class MP_PC_jxl extends HttpServlet {
	List specList = new ArrayList();
	Map <String,Object>headMap = new HashMap<String,Object>();
	int k = 3;
 		
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String no = null;
		Enumeration enumeration = request.getParameterNames();
		while (enumeration.hasMoreElements()) {
			String paramName = (String) enumeration.nextElement();
			if (Debug.Level > 0) {
				 System.out.println("request parameter [" + paramName + "]=" +
				 request.getParameter(paramName));
			}
		}
  		ArrayList skuList = (ArrayList<String>)request.getAttribute("columnDisp");
		ArrayList sku = (ArrayList<String>)request.getAttribute("column");
		ArrayList sku2 = new ArrayList();
		for(int j = 0;j<sku.size();j++){
			System.out.println("sku value ===>"+sku.get(j));
			String skutag = "_"+sku.get(j);
			sku2.add(skutag);
		}
		
		for(int j = 0;j<sku.size();j++){
			System.out.println("skuList value ===>"+skuList.get(j));
		}
		int mycol = skuList.size();
		no = request.getParameter("MP_ID");
			
		OutputStream out = null;
		Connection con = null;	
		Statement stmt = null;
		ResultSet rs = null;
		Map<String,Object>getStyleMap = this.excelStyle();
			
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment; filename=PC_List.xls");
		String myFmt=new SimpleDateFormat("yyyy年MM月dd日 HH時mm分ss秒").format(new Date());
		System.out.println("開始創建excel，創建時間為："+myFmt);
		WritableWorkbook w = Workbook.createWorkbook(response.getOutputStream());
		WritableSheet s = w.createSheet("PC list", 0);
		s.getSettings().setDefaultColumnWidth(20); //设置列的宽度默认为15
		
		try {
			con = SimpleServlet.axaptaCP.getConnection();
			stmt = con.createStatement();
		    stmt.execute("{call " + SimpleServlet.axaptaCP.elbaseDB + ".dbo.el_mp_pc_report_view( '" +no + "')}");
		    System.out.println("調用存儲過程：--->"+"{call " + SimpleServlet.axaptaCP.elbaseDB + ".dbo.el_mp_pc_report_view( '" +no + "')}");
		   
			//获取表头显示信息
			Statement stmt2 = null;
			ResultSet rs2 = null;
			stmt2 = con.createStatement();
			rs2 = stmt2.executeQuery("select PROJECT_ID, PROJECT_DESC from " + SimpleServlet.axaptaCP.elbaseDB + ".DBO.mp_header where mp_id = '" + no + "'");
			
			while(rs2.next()){
				s.mergeCells(3, 0, 6, 1);
				s.addCell(new Label(3, 0, rs2.getString(1),(CellFormat)getStyleMap.get("tablehead")));
				s.mergeCells(7, 0, 7+mycol, 1);
				s.addCell(new Label(7, 0, rs2.getString(2),(CellFormat)getStyleMap.get("tablehead")));
			}
		    
		    int tag = 1;
		    Boolean label = true;
		    while(label){
				int i = 0;
				i = stmt.getUpdateCount();//包含更新计数的 int 值。如果返回的结果是一个结果集对象或没有更多结果，则返回 -1。
				boolean up_label = false;
				if(i != -1){
					up_label = stmt.getMoreResults();//移动到此SQLServerStatement 对象的下一个结果。如果返回一个结果及
					System.out.println("tset getMoreResults()返回值："+up_label); //false false true
					continue;
				}
				boolean flag_label = false;
				rs = stmt.getResultSet();
			    if(rs != null){
			    ResultSetMetaData rsmd = rs.getMetaData();//獲取列名 和列對應的類型
			    	if(tag == 1){
			    			//處理第一個結果集
			    			headMap = this.getInfoFromRs1(rs,s,mycol);
			    			System.out.println("dataInfo"+headMap.get("dataInfo"));
			    			System.out.println("headInfo"+headMap.get("headInfo"));
			    			System.out.println("isHaveCosmeticCode"+headMap.get("isHaveCosmeticCode"));
			    	}else if(tag == 2 ){ 
			    		List<String> speColumnlist = new ArrayList<String>();
			    		System.out.println("當前rs 為："+rs);
			    		specList = this.specTypeColumn(speColumnlist, rs);
				    	for(int j = 0 ;j<specList.size();j++){
				    		//處理第二個結果集
				    		System.out.println("specList value is :"+ specList.get(j));
			    		}
			    	}else if(tag ==3){
		    			//處理第三個結果集
		    			System.out.println("rs3 :"+this.returnPDate(rs,s,mycol));
			    	}else if(tag ==4){
			    		System.out.println("rs4 :"+this.returnCDate(rs,s,mycol));
			    	}else if(tag ==5){
			    		Map<String,Object> getColumnMap = this.setColumnToExcel(s,mycol,skuList,sku,sku2);
			    		this.setValueToExcel(getColumnMap,rs,s);
			    	}
			    	tag ++;
			    	flag_label = stmt.getMoreResults();
			    	continue;
			    }
			    label = up_label|| flag_label;
		    }
		    
		    w.write();
			String myFmt2=new SimpleDateFormat("yyyy年MM月dd日 HH時mm分ss秒").format(new Date());
			System.out.println("excel創建完成，結束時間為："+myFmt2);
			w.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//释放资源
			SimpleServlet.axaptaCP.free(con);			
			if (out != null)
				out.close();
			if (stmt != null){
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	//---------------------------------------------------------style---------------------------------------------------
	public Map<String,Object> excelStyle(){
		Map<String,Object> style = new HashMap<String,Object>();
		try {
			//table head
			jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.GREEN);
			jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(wfc);
			wchB.setAlignment(jxl.format.Alignment.CENTRE);
			style.put("tablehead", wchB);
			
			jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.TIMES,10,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
			jxl.write.WritableCellFormat wchB1 = new jxl.write.WritableCellFormat(wfc1);
			wchB1.setAlignment(jxl.format.Alignment.RIGHT);
			wchB1.setBackground(jxl.format.Colour.GRAY_25);
			style.put("tag", wchB1);
			
			jxl.write.WritableCellFormat wcsB = new jxl.write.WritableCellFormat();
			wcsB.setWrap(true);
			wcsB.setAlignment(jxl.format.Alignment.CENTRE);
			wcsB.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			wcsB.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
			style.put("titleStyle", wcsB);
			
			jxl.write.WritableCellFormat wcsB0 = new jxl.write.WritableCellFormat();
			wcsB0.setWrap(true); 
			wcsB0.setAlignment(jxl.format.Alignment.CENTRE);
			wcsB0.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			wcsB0.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
			wcsB0.setBackground(jxl.format.Colour.SKY_BLUE);
			style.put("cellbackcolorstyle", wcsB0);
			
			jxl.write.WritableCellFormat wcsB2 = new jxl.write.WritableCellFormat();
			wcsB2.setAlignment(jxl.format.Alignment.CENTRE);
			wcsB2.setWrap(true);
			wcsB2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
			style.put("voidStyle", wcsB2);
			//date smalldatetime 
			jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd");//yyy-MM-dd hh:mm:ss 年月日时分秒
			jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
			wcfDF.setAlignment(jxl.format.Alignment.CENTRE);
			wcfDF.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
			style.put("dateStyle", wcfDF);
			
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return style;
	}
	//---------------------------------------------------------style---------------------------------------------------
	//獲取第一個結果集中的標頭信息
	public Map<String,Object> getInfoFromRs1(ResultSet rs,WritableSheet s,int mycol){
		Map <String,Object> map = new HashMap<String,Object>();
		try {
			ResultSetMetaData rs_meta = rs.getMetaData();
			int rsCount = rs_meta.getColumnCount();
			
			if(rs.next()){
				for(int i = 0;i<rsCount;i++){
					if(i == 0){
						String dataInfo = rs.getString(i+1).substring(0, 10);
						System.out.println("截取字符串為：---》"+dataInfo);
						map.put("dataInfo", dataInfo);
						try {
							s.mergeCells(13+mycol, 2, 15+mycol, 2);
							s.addCell(new Label(13+mycol,2,dataInfo));
						} catch (RowsExceededException e1) {
							e1.printStackTrace();
						} catch (WriteException e1) {
							e1.printStackTrace();
						}
					}else if(i ==1){
						String headInfo = rs.getString(i+1);
						map.put("headInfo", headInfo);
						try {
							s.mergeCells(0, 0, 1, 0);
							s.addCell(new Label(0,0,rs.getString(i+1)));
						} catch (RowsExceededException e) {
							e.printStackTrace();
						} catch (WriteException e) {
							e.printStackTrace();
						}
					}else {
						String isHaveCosmeticCode = rs.getString(i+1);
						map.put("isHaveCosmeticCode", isHaveCosmeticCode);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}
	//处理第二个结果集
	@SuppressWarnings("unchecked")
	public List specTypeColumn(List speColumnlist, ResultSet rs){
		try {
			int i = 0;
			while(rs.next()){
				System.out.println("函數中的rs 結果集為："+rs);
				i++;
				String specColumn = rs.getString(1);
				speColumnlist.add(specColumn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return speColumnlist;
	}
	
	//处理第三个結果集
	public int returnPDate(ResultSet rs,WritableSheet s,int mycol){
		int pDate = 0;
		try {
			while(rs.next()){
				pDate = rs.getInt(2);
				try {
					s.mergeCells(16+mycol, 0, 17+mycol, 2);
					s.addCell(new Label(16+mycol,0,"Pkg + Version release within \n"+pDate +" Days"));
				} catch (RowsExceededException e) {
					e.printStackTrace();
				} catch (WriteException e) {
					e.printStackTrace();
				}
			}
			return pDate;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}	
	
	//处理第四个结果集
	public int returnCDate (ResultSet rs,WritableSheet s,int mycol){
		int cDate = 0;
		try {
			while(rs.next()){
				cDate = rs.getInt(2);
				if(headMap.get("isHaveCosmeticCode").equals("yes")){
					try {
						s.mergeCells(18+mycol, 0, 19+mycol, 2);
						s.addCell(new Label(18+mycol,0,"Cosmetic release within \n"+cDate+" Days"));
					} catch (RowsExceededException e) {
						e.printStackTrace();
					} catch (WriteException e) {
						e.printStackTrace();
					}
				}
			}
			return cDate;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	//寫入excel 列名
	public Map<String,Object> setColumnToExcel(WritableSheet s,int mycol,List skuList,List sku,List sku2){
		
		Map<String,Object> columnMap = new HashMap<String,Object>();
		s.getSettings().setDefaultColumnWidth(20); //设置列的宽度默认为15
		Map<String,Object>styleMap = this.excelStyle();
		try {
			s.setRowView(3, 750);
		} catch (RowsExceededException e1) {
			e1.printStackTrace();
		}  
	//  列名
		String []cellColumn = {"PC#","Revision","CustP/O#","Custmer","Country","Mktg Share","Item#","MIX"};
		String []sqlColumn = {"PC_NO","PC_VERSION","PO_CUSTOMER","NAME_CUSTOMER","country","MO_MARKET_ID","ITEM","MIX"};
		List<String> cellColumnList = new ArrayList<String>();
		List<String> sqlColumnList = new ArrayList<String>();
		for(int i = 0; i<cellColumn.length;i++){
			cellColumnList.add(cellColumn[i]);
			sqlColumnList.add(sqlColumn[i]);
		}
		cellColumnList.addAll(skuList);
		sqlColumnList.addAll(sku2);
		
		String []cellColumn2 = {"Inn","Out","PC qty","Ctn","Rev.Details","Request Ship Date","Insp.Date","Customer Insp.Date"};
		String []sqlColumn2 = {"QTY_INNER","QTY_OUTER","PC_QTY","ctn","REV_DESC","SHIP_DATE","INSP_DATE","CUSTOMER_INSP_DATE"};
		for(int i = 0 ;i<cellColumn2.length;i++){
			cellColumnList.add(cellColumn2[i]);
			sqlColumnList.add(sqlColumn2[i]);
		}
		cellColumnList.add("Packaging");
		cellColumnList.add("Packaging Release Status");
		
		sqlColumnList.add("packageingcode");
		sqlColumnList.add("Packaging release status");
		//判斷欄位cosmeticcode對應的value 是否全部為空，若否添加以下兩個欄位
		if(headMap.get("isHaveCosmeticCode").equals("yes")){
			cellColumnList.add("Cosmetic Version");
			cellColumnList.add("Cosmetic Release Status");
			
			sqlColumnList.add("cosmeticcode");
			sqlColumnList.add("Cosmetic release status");
		}
		//添加动态列
		cellColumnList.addAll(specList);
		sqlColumnList.addAll(specList);
		//对应添加sku
		cellColumnList.addAll(skuList);
		sqlColumnList.addAll(sku);
		columnMap.put("cellColumnList", cellColumnList);
		columnMap.put("sqlColumnList", sqlColumnList);
		int lastcellcol = 0;
		for(int i = 0 ;i<cellColumnList.size();i++){
			try {
   				s.addCell(new Label(i,3,cellColumnList.get(i).toString(),(CellFormat) styleMap.get("titleStyle")));
			} catch (RowsExceededException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
			}
			if(i==cellColumnList.size()-1){
				lastcellcol= i;
				System.out.println("最後一個單元格的位置是："+lastcellcol);
			}
		}
		try {
			s.mergeCells(lastcellcol-mycol+1,0,lastcellcol,2);
			s.addCell(new Label(lastcellcol-mycol+1,0,"CONTENT BREAKDOWN",(CellFormat) styleMap.get("tag")));
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
		return columnMap;
	}
	
	//寫入數據
	@SuppressWarnings({ "unchecked", "deprecation" })
	public void setValueToExcel(Map getColumnMap,ResultSet rs,WritableSheet s){
		try {
			Map<String,Object>cellstyleMap = this.excelStyle();
			ResultSetMetaData rs_meta = rs.getMetaData();
			List<String> column = (List<String>) getColumnMap.get("sqlColumnList");
			Map<String,Object> sqltypeMap = new HashMap<String,Object>();
			List<String>sqllistTag = new ArrayList<String>();
			//获取所有列及列名类型
			for(int i = 0;i<rs_meta.getColumnCount(); i++){
				String sqlname = rs_meta.getColumnName(i+1);
				String sqltypeName = rs_meta.getColumnTypeName(i+1);//获取所有列及列名的类型
				System.out.println("列名为："+sqlname+"列名类型为："+sqltypeName);
				if(sqltypeName.equals("varchar")||sqltypeName.equals("nvarchar")){
					sqltypeMap.put(sqlname, "String");
					sqllistTag.add(sqlname);
				}else if(sqltypeName.equals("smalldatetime")||sqltypeName.equals("datetime")){
					sqltypeMap.put(sqlname, "Date");
					sqllistTag.add(sqlname);
				}else if(sqltypeName.equals("numeric")||sqltypeName.equals("int")){
					sqltypeMap.put(sqlname, "int");
					sqllistTag.add(sqlname);
				}
			}
			while(rs.next()){
				k++;
				for(int i = 0 ;i<column.size();i++){
					try {
						if(sqllistTag.contains(column.get(i))){
							if(rs.getString("PC_STATUS").equals("Void")){ //判斷狀態是否為Void 
								if(sqltypeMap.get(column.get(i))=="String"){
									s.addCell(new Label(i,k,rs.getString(column.get(i)),(CellFormat)cellstyleMap.get("cellbackcolorstyle")));
								}else if(sqltypeMap.get(column.get(i))=="Date"){
									System.out.println("測試date 字段"+column.get(i)+"value :"+rs.getDate(column.get(i)));
									s.addCell(new jxl.write.DateTime(i , k, rs.getDate(column.get(i)),(CellFormat)cellstyleMap.get("dateStyle")));
								}else if(sqltypeMap.get(column.get(i))=="int"){
									s.addCell(new Label(i,k,String.valueOf(rs.getInt(column.get(i))),(CellFormat)cellstyleMap.get("cellbackcolorstyle")));
								}
							}else{
								if(sqltypeMap.get(column.get(i))=="String"){
									s.addCell(new Label(i,k,rs.getString(column.get(i)),(CellFormat)cellstyleMap.get("titleStyle")));
								}else if(sqltypeMap.get(column.get(i))=="Date"){
									System.out.println("測試void狀態下的date 字段--》"+column.get(i)+"value :"+rs.getDate(column.get(i)));
									s.addCell(new jxl.write.DateTime(i , k, rs.getDate(column.get(i)),(CellFormat)cellstyleMap.get("dateStyle")));
								}else if(sqltypeMap.get(column.get(i))=="int"){
									s.addCell(new Label(i,k,String.valueOf(rs.getInt(column.get(i))),(CellFormat)cellstyleMap.get("titleStyle")));
								}
							}
						}
					} catch (RowsExceededException e) {
						e.printStackTrace();
					} catch (WriteException e) {
						e.printStackTrace();
					}
				}
			}
			k =3;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}