XinYu_RZMES/.svn/pristine/07/079eafc131ce7526dd8cf8c49193076f663c937f.svn-base

package xin.glue.ui.B;

import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import xin.glue.ui.common.blob.PosExcelEngine;

/**
 * 
 * @Description  板加质保书生成
 * @author       梅贵平{meiguiping}
 * @date         2010-1-19 下午07:02:27
 * @JDK Version  jdk1.4.2
 */
public class BjCertBookBuilder extends PosExcelEngine 
{
	private  int TotalCols  = 44;//原始38更改为44
//	private	 int TheCols	= 43;//原始37改为43 	
	private String pageCoilNo = null;

	protected void setData(Workbook rwb, WritableWorkbook wb) throws RowsExceededException, WriteException {
			Sheet rs = rwb.getSheet(0);
			setData(wb, rs);
	}

	private void setData(WritableWorkbook wb, Sheet rs) throws WriteException, RowsExceededException {
		this.addRows = 0;
		if (pageCount > 0)
			wb.importSheet("sheet" + (pageCount + 1), pageCount, rs);
		WritableSheet sheet = wb.getSheet(pageCount++);
		sheet.removeRow(12);
		sheet.removeRow(8);
		setHeader(rs, sheet);
		setSize(rs, sheet);
		setSample(rs, sheet);
		if (dataList.size() > 3) {
			if (((List)dataList.get(1)).size() > 0 || ((List)dataList.get(3)).size() > 0)
				setData(wb, rs);
		}
	}

	private void setHeader(Sheet rs, WritableSheet sheet) throws WriteException, RowsExceededException {
		if (dataList == null || dataList.size() < 1) return;
		List list = (List)dataList.get(0);
		if (list.size() > 0) {
			Map row = (Map)list.get(0);
			Cell cell;
			Label label;
			String fieldName;
			for (int curCol = 2; curCol < TotalCols; curCol++) {
				for (int curRow = 1; curRow < 4; curRow++) {
					cell = rs.getCell(curCol, curRow);
					if (cell.getType() == CellType.EMPTY) continue;
					fieldName = cell.getContents().trim();
					if (fieldName.startsWith("[") && fieldName.endsWith("]")) {
						fieldName = fieldName.substring(1, fieldName.length() - 1);
						if (row.get(fieldName) != null) {
							label = (Label)sheet.getWritableCell(curCol, curRow);
							label.setString(row.get(fieldName).toString());
						}
						else//当结果中没有该元素时，将该格子的值置为空
						{
							label = (Label)sheet.getWritableCell(curCol, curRow);
							label.setString("");
						}
					}
				}
			}
			// 订货单位、收货单位、订单号、产品名称、标准、发行指示编号
			// 车号、数量合计、重量合计、证明书编号、签发日期、发货日期
		}
	}
	
	protected void addBlankRow(WritableSheet sheet, int curRow, Sheet rs, int refRow, int rows) throws WriteException {
		if (autoBlank) {
			while (rows < pageSize) {
				curRow = insertRow(sheet, curRow, rs, refRow);
				setValue(sheet, curRow, 0, String.valueOf(++rows)); // NO.
			}
		}
	}
	
	private void setSize(Sheet rs, WritableSheet sheet) throws WriteException, RowsExceededException {
		if (dataList == null || dataList.size() < 2) return;//因为Size的查询存储在第二个list中
		List list = (List)dataList.get(1);
		Cell cell;
		int count = 0, curRow = 7;
		String coilNo = null;
		for (int i = 0; i < list.size(); ) {
			Map row = (Map)list.get(i);
			if (!row.get("COIL_PAKG_NO").toString().equals(coilNo)) {
				if (count > pageSize) break;//行数不允许超过最大容量
				coilNo = row.get("COIL_PAKG_NO").toString();
				pageCoilNo = coilNo;
				curRow = insertRow(sheet, curRow, rs, 8);//-------------------------增加一行-----------------------
				//合并单元格
				sheet.mergeCells(3, curRow, 6, curRow);//炉号
				sheet.mergeCells(7, curRow, 9, curRow); // 牌号
				sheet.mergeCells(10, curRow, 10, curRow); // 厚度
				sheet.mergeCells(11, curRow, 12, curRow); // 宽度
				sheet.mergeCells(13, curRow, 14, curRow); // 长度
				sheet.mergeCells(15, curRow, 15, curRow); // 长度
				sheet.mergeCells(16, curRow, 16, curRow); // 重量
				sheet.mergeCells(43, curRow, 44, curRow); // 车号
				//注意，非填值，仅仅是增加了序号（1、2、3、4、5、6、7、8.....）
				setValue(sheet, curRow, 0, String.valueOf(++count)); // NO.
			}
			list.remove(i);
			
			//填值（牌号、炉号、厚度、宽度、长度等）
			for (int curCol = 1; curCol < TotalCols; curCol++) {
				cell = rs.getCell(curCol, 8);
				if (cell.getType() != CellType.EMPTY)//？？？？？？？？？？？？？？？？？？？？？？？？？？？？
					setValue(sheet, curRow, curCol, row.get(cell.getContents().trim()));
			}
			// CHARGE_NO、SMP_NO、COIL_THK、COIL_WTK、COIL_LEN、COIL_WGT、TP
			setChem(rs, sheet, coilNo, curRow);
		}// for end
		//多余的行以空行补齐
		addBlankRow(sheet, curRow, rs, 8, count);
		for (int i = count, j = 1; i < pageSize; i++, j++) {
			sheet.mergeCells(3, curRow+j, 6, curRow+j);//炉号
			sheet.mergeCells(7, curRow + j, 9, curRow + j); // 牌号
			sheet.mergeCells(10, curRow + j, 10, curRow + j); // 厚度
			sheet.mergeCells(11, curRow + j, 12, curRow + j); // 宽度
			sheet.mergeCells(13, curRow + j, 14, curRow + j); // 长度
			sheet.mergeCells(15, curRow + j, 16, curRow + j); // 重量
			sheet.mergeCells(43, curRow + j, 44, curRow + j); // 车号
		}
	}
	
	//成分
	private void setChem(Sheet rs, WritableSheet sheet, String coilNo, int curRow) throws WriteException, RowsExceededException {
		if (dataList == null || dataList.size() < 3) return;
		List list = (List)dataList.get(2);
		Cell cell;
		Label label;
		int curCol;
		boolean canExit = false;
		for (int i = 0; i < list.size(); ) {
			Map row = (Map)list.get(i);
			if (row.get("COIL_PAKG_NO").equals(coilNo)) {
				canExit = true;
				String chemName = row.get("CHEM_CD").toString();
				for (curCol = 18; curCol < TotalCols; curCol++) {//元素存储从18位开始
					cell = sheet.getCell(curCol, 5);//第5行
					if (cell.getContents().equals(chemName)) break;
					if (cell.getType() == CellType.EMPTY) {
						cell = rs.getCell(curCol, 5);
						//
						label = new Label(curCol, 5, chemName, cell.getCellFormat());
						sheet.addCell(label);//将定义好的单元格添加到生成的工作表中
						break;
					}
				}
				if (curCol < TotalCols) {
					label = (Label)sheet.getWritableCell(curCol, curRow);
					label.setString(row.get("CHEM_VAL").toString());
				}
				list.remove(i);
			} else {
				if (canExit) break;
				i++;
			}
		}
	}
	
//	性能
	private void setSample(Sheet rs, WritableSheet sheet) throws WriteException, RowsExceededException {
		if (dataList == null || dataList.size() < 4) return;
		List list = (List)dataList.get(3);
		String coilNo = null, samplePos = null, qltyCD, qltyName;
		int curRow = 10 + this.addRows, fixedRow = 9 + this.addRows, curCol = 0;
		int count = 0;

		Cell cell;
		Label label;
		boolean canExit = false, rowChange = false;
		for (int i = 0; i < list.size(); ) {
			Map row = (Map)list.get(i);
			if (!row.get("COIL_PAKG_NO").equals(coilNo)) {
				if (row.get("COIL_PAKG_NO").equals(pageCoilNo))
					canExit = true;
				if (canExit && count > pageSize) break;
				coilNo = row.get("COIL_PAKG_NO").toString();
				curRow = insertRow(sheet, curRow, rs, 12);
				setValue(sheet, curRow, 0, String.valueOf(++count)); // NO.
				cell = rs.getCell(1, 12); // COIL NO. -> Sample NO.
				setValue(sheet, curRow, 1, row.get(cell.getContents().trim()));
				rowChange = true;
			}
			list.remove(i);
			if (rowChange || !row.get("SMP_NO").equals(samplePos)) {
				rowChange = false;
				samplePos = row.get("SMP_NO").toString();
				cell = rs.getCell(2, 12); // Sampling Position
				setValue(sheet, curRow, 2, row.get(cell.getContents().trim()));
				cell = rs.getCell(TotalCols, 12); // 交货状态
				setValue(sheet, curRow, TotalCols, row.get(cell.getContents().trim()));
			}

			qltyCD = row.get("QLTY_CD").toString();
			qltyName = row.get("QLTY_CD_CFNM").toString();
		
			if ((qltyCD.equals("YS") || qltyCD.equals("TE") ||
					qltyCD.equals("EL") || qltyCD.equals("RA") || qltyCD.equals("YR"))) {
				//试样位置、位置方向、温度
				for (curCol = 3; curCol < 6; curCol++) {
					if(curCol == 3&&qltyCD.equals("TE")) //取抗拉强度的方向做为试验方向
					{
						cell = rs.getCell(curCol, 12);
						String fangXiang	=	row.get(cell.getContents().trim()).toString();
//						if("H".equals(fangXiang))//横向
//						{
//							fangXiang	=	"T";
//						}
//						else if("Z".equals(fangXiang))//纵向
//						{
//							fangXiang	=	"L";
//						}
						setValue(sheet, curRow, curCol, fangXiang);
					}
				}
				//屈服、抗拉、等拉力实验
				for (curCol = 6; curCol < 11; curCol++) {
					cell = rs.getCell(curCol, 12);
					if (qltyCD.equals(cell.getContents().trim())) {
						setValue(sheet, curRow, curCol, row.get("QLTY_VAL_WK"));
						break;
					}
				}
				if(qltyCD.equals("YS") )
				{	
					cell = sheet.getCell(6, fixedRow+1);
					String ss   =    cell.getContents().toString();
					if("ReL".equals(ss))
					{
						Label ll = (Label)sheet.getWritableCell(6, fixedRow+1);
						ll.setString(qltyName.replaceAll("\\[矩形\\]", "").replaceAll("\\[圆形\\]", "").replaceAll("屈服强度", "")    );
					}	
				}
				else if(qltyCD.equals("EL"))
				{
					cell = sheet.getCell(8, fixedRow+1);
					String ss   =    cell.getContents().toString();
					if("A".equals(ss))
					{
						Label ll = (Label)sheet.getWritableCell(8, fixedRow+1);
						ll.setString(qltyName.replaceAll("\\[矩形\\]", "").replaceAll("\\[圆形\\]", "").replaceAll("伸长率", "")    );
					}	
				}
			}
			else if(qltyName.equals("非金属夹杂物A"))
			{
				cell	=	rs.getCell(38 , 12);
				setValue(sheet, curRow, 38, row.get("QLTY_VAL_WK"));
			}
			else if(qltyName.equals("非金属夹杂物B"))
			{
				cell	=	rs.getCell(39 , 12);
				setValue(sheet, curRow, 39, row.get("QLTY_VAL_WK"));
			}
			else if(qltyName.equals("非金属夹杂物C"))
			{
				cell	=	rs.getCell(40 , 12);
				setValue(sheet, curRow, 40, row.get("QLTY_VAL_WK"));
			}
			else if(qltyName.equals("非金属夹杂物D"))
			{
				cell	=	rs.getCell(41 , 12);
				setValue(sheet, curRow, 41, row.get("QLTY_VAL_WK"));
			}
			else if(qltyName.indexOf("夏比冲击剪切面积") != -1 )
			{
				int xiabiCol	=	19;
				cell = rs.getCell(xiabiCol, 12);
				setValue(sheet, curRow, xiabiCol, row.get(cell.getContents().trim()));
				cell = rs.getCell(xiabiCol+3, 12); // AVE
				setValue(sheet, curRow, xiabiCol+3, row.get("QLTY_VAL_WK"));
				setQlty(rs, sheet, coilNo, qltyCD, curRow);
			
			}
			else if(qltyName.equals("晶粒度"))
			{
				cell	=	rs.getCell(23 , 12);
				setValue(sheet, curRow, 23, row.get("QLTY_VAL_WK"));
			}
			else if(qltyName.equals("带状组织"))
			{
				cell	=	rs.getCell(24 , 12);
				setValue(sheet, curRow, 24, row.get("QLTY_VAL_WK"));
			}
			else if (qltyCD.equals("IM")) {
				if("S".equals(row.get("MS_QLTY_DIR")) ||"H".equals(row.get("MS_QLTY_DIR"))||"Z".equals(row.get("MS_QLTY_DIR")))//普通冲击
				{
					String str;
//					for (curCol = 15; curCol < 18; curCol++) {
					for (curCol = 12; curCol < 15; curCol++) {
						cell = rs.getCell(curCol, 12);
						if(row.get(cell.getContents().trim()) != null)
						{
							str	 = row.get(cell.getContents().trim()).toString();
						}
						else
						{
							str	=	"";
						}
						if("H".equals(row.get(cell.getContents().trim())))
						{
							str	=	"T";
						}
						else if("Z".equals(row.get(cell.getContents().trim())))
						{
							str	=	"L";
						}
						setValue(sheet, curRow, curCol, str);
					}
					cell = rs.getCell(18, 12); // AVE（平均值），列为18列
					setValue(sheet, curRow, 18, row.get("QLTY_VAL_WK"));
					setQlty(rs, sheet, coilNo, qltyCD, curRow);
				}
				if("L".equals(row.get("MS_QLTY_DIR"))||"X".equals(row.get("MS_QLTY_DIR")))//时效冲击
				{
					String str = "";
					//时冲的位置、尺寸、温度
					for (curCol = 31; curCol < 34; curCol++) {
						cell = rs.getCell(curCol, 12);
					//	str	 = row.get(cell.getContents().trim())== null ? "":row.get(cell.getContents().trim()).toString();
						if(row.get(cell.getContents().trim()) != null)
						{
							str	 = row.get(cell.getContents().trim()).toString();
						}
						else
						{
							str	=	"";
						}
						if("X".equals(row.get(cell.getContents().trim())))
						{
							str	=	"L";
						}
						else if("L".equals(row.get(cell.getContents().trim())))
						{
							str	=	"T";
						}
						setValue(sheet, curRow, curCol, str   );
					}
					cell = rs.getCell(37, 12); // AVE
					setValue(sheet, curRow, 37, row.get("QLTY_VAL_WK"));
					setQlty(rs, sheet, coilNo, qltyCD, curRow);
					
					//生成时效冲击的名称
					label = new Label(34, fixedRow, qltyName, cell.getCellFormat());
					label.setString(qltyName);
					sheet.addCell(label);
				}
			}else if (qltyCD.equals("HD")) {
				cell = rs.getCell(27, 12);//位置方向
				setValue(sheet, curRow, 27, row.get(cell.getContents().trim()));
				cell = rs.getCell(30, 12); // AVE
				setValue(sheet, curRow, 30, row.get("QLTY_VAL_WK"));
				setQlty(rs, sheet, coilNo, qltyCD, curRow);
				//生成硬度的名称
				label = new Label(27, fixedRow, qltyName, cell.getCellFormat());
				label.setString(qltyName);
				sheet.addCell(label);
			} else if (qltyCD.equals("CB")) {
				int  colCB	=	25;
				cell = rs.getCell(colCB, 12); // CB
				setValue(sheet, curRow, colCB, row.get("QLTY_VAL_WK"));
				setQlty(rs, sheet, coilNo, qltyCD, curRow);
			} 
			//对于其它的检验项目
			else {
//				qltyName = (String)row.get("QLTY_CD_CFNM");
				for (curCol = 42; curCol < TotalCols; curCol++) {
						cell = sheet.getCell(curCol, fixedRow);
						if (cell.getContents().equals(qltyName)) break;
						if (cell.getType() == CellType.EMPTY) {
							cell = rs.getCell(curCol, 10);
							label = new Label(curCol, fixedRow, qltyName, cell.getCellFormat());
							sheet.addCell(label);
							break;
						}
					
				}
				if (curCol < TotalCols)
					setValue(sheet, curRow, curCol, row.get("QLTY_VAL_WK"));
			}
		}
		addBlankRow(sheet, curRow, rs, 12, count);
		
		//制表人
		list = (List)dataList.get(0);
		if (list.size() > 0) {
			Map row = (Map)list.get(0);
			//注意，不能用rs.getRows()，rs代表的是原始模板
			label = new Label(10, sheet.getRows()-1, row.get("USERID").toString());
			sheet.addCell(label);
		}
		//备注
		list = (List)dataList.get(0);
		if (list.size() > 0) {
			Map row = (Map)list.get(0);
			//注意，不能用rs.getRows()，rs代表的是原始模板
//			Label	la	=	new Label(0,29 , "");
			label = new Label(2, 29, row.get("CRET_NO_RMK")==null?"": row.get("CRET_NO_RMK").toString() , PosCertificate.getCommStyle() );
			sheet.addCell(label);
		}
	}

	private void setQlty(Sheet rs, WritableSheet sheet, String coilNo, String qltyCD, int curRow) {
		if (dataList == null || dataList.size() < 5) return;
		List list = (List)dataList.get(4);

		String qltySeq;
		Cell cell;
		int curCol = 0;
		boolean canExit = false;
		for (int i = 0;i < list.size();i++) {
			Map row = (Map)list.get(i);
			if (row.get("COIL_PAKG_NO").equals(coilNo)) {
				if (row.get("QLTY_CD").equals(qltyCD)) {
					qltySeq = (String)row.get("QLTY_SEQ");
//					String	qltyName	=	row.get("QLTY_CD_CFNM").toString();
					if("IM".equals(qltyCD) && row.get("MS_QLTY_DIR") == null)
					{
//						for (curCol = 18; curCol < 21; curCol++) {
//							cell = rs.getCell(curCol, 12);
//							if (cell.getContents().trim().equals(qltySeq)) {
//								setValue(sheet, curRow, curCol, row.get("QLTY_VAL_WK"));
//								row.put("QLTY_CD", "isUsed");
//								break;
//							}
//						}
					}
					
					else if ("IM".equals(qltyCD)) {
						if("S".equals(row.get("MS_QLTY_DIR")) ||"H".equals(row.get("MS_QLTY_DIR"))||"Z".equals(row.get("MS_QLTY_DIR")))//普通冲击
						{
							for (curCol = 15; curCol < 18; curCol++) {
								cell = rs.getCell(curCol, 12);
								if (cell.getContents().trim().equals(qltySeq)) {
									setValue(sheet, curRow, curCol, row.get("QLTY_VAL_WK"));
									row.put("QLTY_CD", "isUsed");
									break;
								}
							}
						}
						if("L".equals(row.get("MS_QLTY_DIR"))||"X".equals(row.get("MS_QLTY_DIR")))//时效冲击
						{	
							for (curCol = 34; curCol < 37; curCol++) {
								cell = rs.getCell(curCol, 12);
								if (cell.getContents().trim().equals(qltySeq)) {
									setValue(sheet, curRow, curCol, row.get("QLTY_VAL_WK"));
									row.put("QLTY_CD", "isUsed");
									break;
								}
							}
						}
						
					} else if ("HD".equals(qltyCD)) {
						for (curCol = 27; curCol < 30; curCol++) {
							cell = rs.getCell(curCol, 12);
							if (cell.getContents().trim().equals(qltySeq)) {
								setValue(sheet, curRow, curCol, row.get("QLTY_VAL_WK"));
								row.put("QLTY_CD", "isUsed");
								break;
							}
						}
					}
				}
		}
	}
	}
}