XinYu_RZMES/.svn/pristine/1e/1e0458842260acd62b21b35a7811f9f43957c6a8.svn-base

package xin.glue.ui.B;

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

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

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;

import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class PosCertificate 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;
		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_NO").toString().equals(coilNo)) {
				if (count > pageSize) break;
				coilNo = row.get("COIL_NO").toString();
				pageCoilNo = coilNo;
				curRow = insertRow(sheet, curRow, rs, 8);
				sheet.mergeCells(2, curRow, 5, curRow);//炉号
				sheet.mergeCells(6, curRow, 8, curRow); // 牌号
				sheet.mergeCells(9, curRow, 10, curRow); // 厚度
				sheet.mergeCells(11, curRow, 12, curRow); // 宽度
				sheet.mergeCells(13, curRow, 14, curRow); // 长度
				sheet.mergeCells(15, curRow, 16, curRow); // 重量
				sheet.mergeCells(42, curRow, 43, curRow); // 车号
				setValue(sheet, curRow, 0, String.valueOf(++count)); // NO.
			}
			list.remove(i);

			for (int curCol = 1; curCol < TheCols; 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);
		}
		addBlankRow(sheet, curRow, rs, 8, count);
		for (int i = count, j = 1; i < pageSize; i++, j++) {
			sheet.mergeCells(2, curRow+j, 5, curRow+j);//炉号
			sheet.mergeCells(6, curRow + j, 8, curRow + j); // 牌号
			sheet.mergeCells(9, 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(42, curRow + j, 43, 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_NO").equals(coilNo)) {
				canExit = true;
				String chemName = row.get("CHEM_CD").toString();
				String DISPLAY_LEN = row.get("DISPLAY_LEN").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);
						/*
						//保留位数
						WritableFont superscript = new WritableFont(WritableFont.ARIAL,   
				                WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false,   
				                UnderlineStyle.NO_UNDERLINE, Colour.BLACK,   
				                ScriptStyle.SUPERSCRIPT);   
				        WritableCellFormat superscriptFormat = new WritableCellFormat(   
				                superscript);   
				        
						cell = rs.getCell(curCol, 7);
						label = new Label(curCol, 7, DISPLAY_LEN, superscriptFormat);
						
						sheet.addCell(label);
						label = new Label(curCol, 7, "10", superscriptFormat);
						sheet.addCell(label);
						*/  
						/*
						NumberFormat dp3 = new NumberFormat("#.###");   
				        WritableCellFormat dp3cell = new WritableCellFormat(dp3);   
				        jxl.write.Number n = new jxl.write.Number(curCol, 7, 3.1TotalCols5926535,dp3cell);
						sheet.addCell(n);
						*/
						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_NO").equals(coilNo)) {
				if (row.get("COIL_NO").equals(pageCoilNo))
					canExit = true;
				if (canExit && count > pageSize) break;
				coilNo = row.get("COIL_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(TheCols, 12); // 交货状态
				setValue(sheet, curRow, TheCols, 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(37 , 12);
				setValue(sheet, curRow, 37, row.get("QLTY_VAL_WK"));
			}
			else if(qltyName.equals("非金属夹杂物B"))
			{
				cell	=	rs.getCell(38 , 12);
				setValue(sheet, curRow, 38, row.get("QLTY_VAL_WK"));
			}
			else if(qltyName.equals("非金属夹杂物C"))
			{
				cell	=	rs.getCell(39 , 12);
				setValue(sheet, curRow, 39, row.get("QLTY_VAL_WK"));
			}
			else if(qltyName.equals("非金属夹杂物D"))
			{
				cell	=	rs.getCell(40 , 12);
				setValue(sheet, curRow, 40, row.get("QLTY_VAL_WK"));
			}
			else if(qltyName.indexOf("夏比冲击剪切面积") != -1 )
			{
	
				cell = rs.getCell(18, 12);
				setValue(sheet, curRow, 18, row.get(cell.getContents().trim()));
				cell = rs.getCell(21, 12); // AVE
				setValue(sheet, curRow, 21, row.get("QLTY_VAL_WK"));
				setQlty(rs, sheet, coilNo, qltyCD, curRow);
			
			}
			else if(qltyName.equals("晶粒度"))
			{
				cell	=	rs.getCell(22 , 12);
				setValue(sheet, curRow, 22, row.get("QLTY_VAL_WK"));
			}
			else if(qltyName.equals("带状组织"))
			{
				cell	=	rs.getCell(23 , 12);
				setValue(sheet, curRow, 23, 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 = 11; curCol < 14; 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(17, 12); // AVE
					setValue(sheet, curRow, 17, 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 = 30; curCol < 33; 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(36, 12); // AVE
					setValue(sheet, curRow, 36, row.get("QLTY_VAL_WK"));
					setQlty(rs, sheet, coilNo, qltyCD, curRow);
					
					label = new Label(33, fixedRow, qltyName, cell.getCellFormat());
					label.setString(qltyName);
					sheet.addCell(label);
				}
			}else if (qltyCD.equals("?")) {
				cell = rs.getCell(18, 12);
				setValue(sheet, curRow, 18, row.get(cell.getContents().trim()));
				cell = rs.getCell(22, 12); // AVE
				setValue(sheet, curRow, 22, row.get("QLTY_VAL_WK"));
				setQlty(rs, sheet, coilNo, qltyCD, curRow);
			}else if (qltyCD.equals("HD")) {
				cell = rs.getCell(26, 12);
				setValue(sheet, curRow, 26, row.get(cell.getContents().trim()));
				cell = rs.getCell(29, 12); // AVE
				setValue(sheet, curRow, 29, row.get("QLTY_VAL_WK"));
				setQlty(rs, sheet, coilNo, qltyCD, curRow);
				//////////////////////////
				label = new Label(26, fixedRow, qltyName, cell.getCellFormat());
				label.setString(qltyName);
				sheet.addCell(label);
			} else if (qltyCD.equals("CB")) {
				cell = rs.getCell(24, 12); // CB
				setValue(sheet, curRow, 24, row.get("QLTY_VAL_WK"));
				setQlty(rs, sheet, coilNo, qltyCD, curRow);
			} 
//			else if(qltyCD.equals("TT"))//----------------------------------------
			
//			else if("晶粒度".equals(qltyName))
//			{
//				cell = rs.getCell(32, 12); 
//				setValue(sheet, curRow, 32, row.get("QLTY_VAL_WK"));
//				setQlty(rs, sheet, coilNo, qltyCD, curRow);
//			}
			else {
//				qltyName = (String)row.get("QLTY_CD_CFNM");
				for (curCol = 41; curCol < TheCols; 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());
			label = new Label(10, 33, 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);
		}
	}

	public static WritableCellFormat getCommStyle()
	{
		WritableFont	wf	=null;
		WritableCellFormat	wcf	=null;
		try
		{
			wf	=	new WritableFont(WritableFont.createFont("黑体") , 9);//设置字体大小
	//		wf.setColour(Colour.RED);//设置颜色
			wcf	=	new WritableCellFormat(wf);
			wcf.setBorder(Border.ALL, BorderLineStyle.THIN);//设置边框
			wcf.setAlignment(Alignment.LEFT);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
	//		wcf.setWrap(true);//自动换行
			
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return wcf;
	}
	
	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(); ) {
			Map row = (Map)list.get(i);
			if (row.get("COIL_NO").equals(coilNo)) {
				canExit = true;
				String xxx = (String)row.get("QLTY_CD");
				
				if (row.get("QLTY_CD").equals(qltyCD)) {
					qltySeq = (String)row.get("QLTY_SEQ");
					if ("IM".equals(qltyCD)) {
						if("H".equals(row.get("MS_QLTY_DIR"))||"Z".equals(row.get("MS_QLTY_DIR")))
						{
							for (curCol = 14; curCol < 17; curCol++) {
								cell = rs.getCell(curCol, 12);
								if (cell.getContents().trim().equals(qltySeq)) {
									setValue(sheet, curRow, curCol, row.get("QLTY_VAL_WK"));
									break;
								}
							}
						}
						if("L".equals(row.get("MS_QLTY_DIR"))||"X".equals(row.get("MS_QLTY_DIR")))
						{	
							for (curCol = 21; curCol < 24; curCol++) {
								cell = rs.getCell(curCol, 12);
								if (cell.getContents().trim().equals(qltySeq)) {
									setValue(sheet, curRow, curCol, row.get("QLTY_VAL_WK"));
									break;
								}
							}
						}
						
					} else if ("?".equals(qltyCD)) {
						for (curCol = 19; curCol < 22; curCol++) {
							cell = rs.getCell(curCol, 12);
							if (cell.getContents().trim().equals(qltySeq)) {
								setValue(sheet, curRow, curCol, row.get("QLTY_VAL_WK"));
								break;
							}
						}
					}else if ("HD".equals(qltyCD)) {
						for (curCol = 26; curCol < 29; curCol++) {
							cell = rs.getCell(curCol, 12);
							if (cell.getContents().trim().equals(qltySeq)) {
								setValue(sheet, curRow, curCol, row.get("QLTY_VAL_WK"));
								break;
							}
						}
					}
					list.remove(i);
				}
			} else {
				System.out.println("xxxxxxxxx");
				if (canExit) break;
				i++;
			}
		}*/
		
		for (int i = 0;i < list.size();i++) {
			Map row = (Map)list.get(i);
			if (row.get("COIL_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)
					{
//						System.out.println("#################"+row.get("QLTY_CD_CFNM"));
						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 = 14; curCol < 17; 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 = 33; curCol < 36; 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 ("?".equals(qltyCD)) {
						for (curCol = 19; curCol < 22; 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 = 26; curCol < 29; 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;
							}
						}
					}
				}
		}
	}
}
}