String sql = "select tpq.twscm_punish_quality_no twscm_punish_quality_no,\n" +" tpq.vendor_name vendor_name,\n" + " tpq.vendor_linkman vendor_linkman,\n" + " tpq.vendor_telephone_no vendor_telephone_no,\n" + " tpq.problem_type problem_type,\n" +" tpq.reaction_time reaction_time,\n" + " tpq.department_put_forward department_put_forward,\n" + " tpq.person_put_forward person_put_forward,\n" + " tpq.dpf_telephone_no dpf_telephone_no,\n" + " tpq.tpq_type tpq_type\n" + " from twscm_punish_quality tpq\n" + " where tpq.twscm_punish_quality_id = "+this.Twscm_Punish_Quality_Id;Hashtable ht = this.getDao().oneRowSelect(sql);if(ht != null && ht.size() > 0){this.toObject(ht);}else{return;}try{com.B2B.common.PubFunction pub=new com.B2B.common.PubFunction();String path=pub.getRootPath();path+="\\web\\plug-in\\Excel\\"; path+="质量处罚单.xls";WritableWorkbook book=null;try{ book = Workbook.createWorkbook(new File(path));// 设置表名WritableSheet sheet = book.createSheet("质量处罚单",0); //合并单元格WritableFont fonttitle= new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD,false);WritableCellFormat wchB=new WritableCellFormat(fonttitle);wchB.setAlignment(jxl.format.Alignment.LEFT); wchB.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框WritableFont font6= new WritableFont(WritableFont.TIMES,14,WritableFont.BOLD,false);WritableCellFormat format6=new WritableCellFormat(font6);format6.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框format6.setBackground(Colour.LIGHT_GREEN);Label labelff = new Label(0, 0, "供方异常问题处罚、折价处理确认单"/*+this.Twscm_Punish_Quality_No*/, wchB);sheet.mergeCells(0, 0, 5, 0);sheet.addCell(labelff);Label labelC = new Label(0, 1, "单号："+this.Twscm_Punish_Quality_No, wchB);sheet.mergeCells(0, 1, 5, 1);sheet.addCell(labelC);// 生成表格题头WritableFont font1= new WritableFont(WritableFont.TIMES,14,WritableFont.BOLD,false);WritableCellFormat format1=new WritableCellFormat(font1);format1.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框 WritableFont font2= new WritableFont(WritableFont.TIMES,12);WritableCellFormat format2=new WritableCellFormat(font2);format2.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框 /*format2.setWrap(true);*/sheet.mergeCells(0, 2, 1, 2);sheet.mergeCells(2, 2, 3, 2);Label labet1 = new Label(0, 2, "供方名称" ,format6);Label labet2 = new Label(4, 2, "问题类型" ,format6);sheet.addCell(labet1);sheet.addCell(labet2);String problem_type1 = null;if(ht.get("problem_type").toString().equals("1")){ problem_type1="质量";}else if(ht.get("problem_type").toString().equals("2")){ problem_type1="交货期";}else if(ht.get("problem_type").toString().equals("3")){ problem_type1="行为规范";}else if(ht.get("problem_type").toString().equals("4")){ problem_type1="其他";};Label labet1_v = new Label(2, 2,ht.get("vendor_name").toString() ,format2);Label labet2_v = new Label(5, 2, problem_type1 ,format2);sheet.addCell(labet1_v);sheet.addCell(labet2_v);sheet.mergeCells(0, 3, 1, 3);sheet.mergeCells(2, 3, 3, 3);Label labet3 = new Label(0, 3, "提出部门" ,format6);Label labet4 = new Label(4, 3, "要求反馈时间" ,format6);sheet.addCell(labet3);sheet.addCell(labet4);Label labet3_v = new Label(2, 3, ht.get("department_put_forward").toString(),format2);if(this.Reaction_Time != null && ht.get("reaction_time").toString() != ""){Label labet4_v = new Label(5, 3, ht.get("reaction_time").toString().substring(0, 10),format2);sheet.addCell(labet4_v );}sheet.addCell(labet3_v);sheet.mergeCells(0, 4, 1, 4);sheet.mergeCells(2, 4, 3, 4);Label labet5 = new Label(0, 4, "提出人/联系电话" ,format6);Label labet6 = new Label(4, 4, "供方联系人/电话" ,format6);sheet.addCell(labet5);sheet.addCell(labet6);Label labet5_v = new Label(2, 4,ht.get("person_put_forward").toString()+"/"+ht.get("dpf_telephone_no").toString(),format2);Label labet6_v = new Label(5, 4,ht.get("vendor_linkman").toString()+"/"+ht.get("vendor_telephone_no").toString(),format2);sheet.addCell(labet5_v);sheet.addCell(labet6_v);WritableFont font3= new WritableFont(WritableFont.TIMES,14,WritableFont.BOLD,false);WritableCellFormat format3=new WritableCellFormat(font3);format3.setAlignment(jxl.format.Alignment.CENTRE);format3.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框 format3.setBackground(Colour.VERY_LIGHT_YELLOW); WritableFont font7= new WritableFont(WritableFont.TIMES,14,WritableFont.BOLD,false);WritableCellFormat format7=new WritableCellFormat(font7);format7.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框format7.setBackground(Colour.GRAY_25);/*format3.setWrap(true);*/Label labet25 = new Label(0, 5, "异常问题描述" ,format3);sheet.addCell(labet25);/*WritableCellFormat wchB2=new WritableCellFormat(format1);wchB2.setAlignment(jxl.format.Alignment.GENERAL); */ /*Label labelC1 = new Label(1, 10, this.Apply_Reason, wchB2);*/sheet.mergeCells(0, 5, 5, 5);/*sheet.addCell(labelC1);*///15采购明细Label labetc1 = new Label(0, 6, "序号" ,format7);Label labetc2 = new Label(1, 6, "日期" ,format7);Label labetc3 = new Label(2, 6, "物料编码" ,format7);Label labetc4 = new Label(3, 6, "物料类别" ,format7);Label labetc5 = new Label(4, 6, "不良描述" ,format7);Label labetc6 = new Label(5, 6, "处罚、折价(元)" ,format7);sheet.addCell(labetc1);sheet.addCell(labetc2);sheet.addCell(labetc3);sheet.addCell(labetc4);sheet.addCell(labetc5);sheet.addCell(labetc6);String sql11="select line_no,\n" +" line_time,\n" + " item_code,\n" + " item_big_kind_name,\n" + " problem_description,\n" + " penalty_amount\n" + " from twscm_punish_quality_line\n" + " where twscm_punish_quality_id = "+this.Twscm_Punish_Quality_Id;Vector htl = this.getDao().multiRowSelect(sql11);int sum= 0;int count99 =0;for(int i=0;i<htl.size();i++){Twscm_Punish_Quality_Line line = new Twscm_Punish_Quality_Line();Hashtable ht11 = (Hashtable) htl.elementAt(i);line.toObject(ht11);count99=i+1;String forlineno=""+count99;Label labetc1_v = new Label(0, 7+i, forlineno,format2);Label labetc2_v = new Label(1, 7+i, ht11.get("line_time").toString().substring(0, 10),format2);Label labetc3_v = new Label(2, 7+i, ht11.get("item_code").toString(),format2);Label labetc4_v = new Label(3, 7+i, ht11.get("item_big_kind_name").toString() ,format2);Label labetc5_v = new Label(4, 7+i, ht11.get("problem_description").toString() ,format2);Label labetc6_v = new Label(5, 7+i,"￥"+ht11.get("penalty_amount").toString() ,format2);sheet.addCell(labetc1_v);sheet.addCell(labetc2_v);sheet.addCell(labetc3_v);sheet.addCell(labetc4_v);sheet.addCell(labetc5_v);sheet.addCell(labetc6_v);sum=sum+line.Penalty_Amount;System.out.println("sum="+sum);}String s123=""+sum;System.out.println("kkkkkkkkkkkkkkkkkkkk="+s123);int count =htl.size();sheet.mergeCells(2, count+7, 4, count+7);sheet.mergeCells(0, count+7, 1, count+7);Label labets1 = new Label(0, count+7, "处理意见" ,format1);Label labets2 = new Label(2, count+7, "" ,format2);Label labets3 = new Label(5, count+7, "合计：￥"+s123 ,format2);sheet.addCell(labets1);sheet.addCell(labets2);sheet.addCell(labets3); WritableFont font4= new WritableFont(WritableFont.TIMES,14,WritableFont.BOLD,false); WritableCellFormat format4=new WritableCellFormat(font4);format4.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);format4.setWrap(true);//自动换行format4.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框 WritableFont font5= new WritableFont(WritableFont.TIMES,12); WritableCellFormat format5=new WritableCellFormat(font5);format5.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);format5.setWrap(true);//自动换行format5.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框 sql = "select vendor_disposal_view,\n" +" description_vdc,\n" + " vendor_sign,\n" + " vendor_sign_date,\n" + " follow_people_view,\n" + " description_fpv,\n" + " follow_people_sign,\n" + " follow_people_date,\n" + " description_soa,\n" + " company_sign,\n" + " company_sign_date\n" + " from twscm_retroaction_review\n" + " where twscm_retroaction_review_id = "+this.Twscm_Punish_Quality_Id;Hashtable htcl = this.getDao().oneRowSelect(sql);if(htcl != null && htcl.size() > 0){Twscm_Retroaction_Review contacts = new Twscm_Retroaction_Review();contacts.toObject(htcl);//如果是count是6sheet.mergeCells(0, count+8, 5, count+12);Label labetcc1_v = new Label(0, count+8, contacts.Description_Vdc ,format5);sheet.mergeCells(0, count+13, 2, count+13);sheet.mergeCells(3, count+13, 5, count+13);Label labetcc2_v = new Label(0, count+13, "注：请加盖公司印章 " ,format1);Label labetcc3_v = new Label(3, count+13, " 签字/日期：" ,format1);sheet.mergeCells(0, count+14, 5, count+14);sheet.mergeCells(0, count+15, 5, count+19);Label labetcc4_v = new Label(0, count+14, "利昌跟进人意见：" ,format1);//Label labetcc13_v = new Label(0, count+15, contacts.Description_Fpv ,format5);sheet.mergeCells(0, count+20, 2, count+20);sheet.mergeCells(3, count+20, 5, count+20);Label labetcc5_v = new Label(3, count+20, " 签字/日期：" ,format1);sheet.mergeCells(0, count+21, 5, count+21);sheet.mergeCells(0, count+22, 5, count+26);Label labetcc6_v = new Label(0, count+21, "利昌批准情况：" ,format1);//Label labetcc14_v = new Label(0, count+22, contacts.Description_Soa ,format5);sheet.mergeCells(0, count+27, 2, count+27);sheet.mergeCells(3, count+27, 5, count+27);Label labetcc7_v = new Label(3, count+27, " 签字/日期：" ,format1);sheet.mergeCells(0, count+28, 5, count+28);sheet.mergeCells(0, count+29, 5, count+33);String str123="说明：";String str124="1、超过要求反馈日期没有反馈，视为供应商同意，本公司将开出“供应商违约金扣罚通知单”，供方收到“供应商违约金扣罚通知单”时，罚款已经扣除；"; String str125="2、回复我司方式：填写供方意见栏，通过邮件发送到“shundelichang@21cn.com或qinqiongfen@126.com”用户处。"; String str126="3、对于已确定属于供应商责任造成的不合格，供方的申诉将不予回复与受理；"; String str127="4、编号方式：处罚项目编号＋年/月＋三位流水号，如：QF－0701－001（QF：表示质量问题类罚款，J：表示交货期类罚款，H：表示行为规范类罚款，R表示其它类罚款。）";/*sheet.mergeCells(0, count+27, 5, count+31);*/ Label labetcc8_v = new Label(0, count+28, str123,format1);Label labetcc9_v = new Label(0, count+29, str124+"\n"+str125+"\n"+str126+"\n"+str127,format4);/*Label labetcc10_v = new Label(0, count+30, str125,format4);Label labetcc11_v = new Label(0, count+31, str126,format4);Label labetcc12_v = new Label(0, count+32, str127,format4);*/sheet.addCell(labetcc1_v);sheet.addCell(labetcc2_v);sheet.addCell(labetcc3_v);sheet.addCell(labetcc4_v);sheet.addCell(labetcc5_v);sheet.addCell(labetcc6_v);sheet.addCell(labetcc7_v);sheet.addCell(labetcc8_v);sheet.addCell(labetcc9_v);/*sheet.addCell(labetcc10_v);sheet.addCell(labetcc11_v);sheet.addCell(labetcc12_v);*/sheet.addCell(labetcc13_v);sheet.addCell(labetcc14_v);}//设置行高sheet.setRowView(0,600); sheet.setRowView(count+29,600); sheet.setRowView(count+30,600); sheet.setRowView(count+31,600); sheet.setRowView(count+32,600); sheet.setRowView(count+33,600);//设置列宽度sheet.setColumnView(0, 6);sheet.setColumnView(1, 20);sheet.setColumnView(2, 20);sheet.setColumnView(3, 13);sheet.setColumnView(4, 22);sheet.setColumnView(5, 23);/*sheet.setColumnView(7, 20);*/book.write(); } catch (RowsExceededException e) {System.out.println(e.toString());}catch (WriteException e) { System.out.println(e.toString());}catch (Exception e) { System.out.println(e.toString());} finally{try{ if(book!=null)book.close();}catch(Exception e){System.out.println("exceptionwhen closing Connection in finally");System.out.println(e.getMessage().toString()); } } }catch(Exception ex){ ex.printStackTrace();}finally{this.Link_Name="/web/plug-in/Excel/User_Import_"+this.getUserBean().SysCompany_id+".xls";}
