java excel导出自己做的实例

我就是我 2022-06-18 13:10 184阅读 0赞

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是6
    					sheet.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";
    		}