java jxl实现excle导出

//path:导出文件的路径地址
public String excelDerive(String path,String sql) {

	String[] title = {"商机编号","报备日期","产品组","主责人","地市","区县","项目名称","项目总额","厂家客户经理","预计采购时间","商机状态","更新日期","产品方案","产品明细","型号","配置参数","显示器","单台报价","数量","金额小计","参与经销商名称","经销商主责人","经销商联系方式","地址","跟进时间","跟进人","跟进记录"};   
	//下载文件路径
	//导出文件路径
	String filename="testxls.xls";
	String filePath=path+filename;  
	Connection  conn=Mysql.getInstance().getConnection();
	try {
		// 获得开始时间     
		long start = System.currentTimeMillis();     
		// 创建Excel工作薄     
		WritableWorkbook wwb;     
		// 新建立一个jxl文件,即在e盘下生成testJXL.xls     
		OutputStream os = new FileOutputStream(filePath);     
		wwb=Workbook.createWorkbook(os);
		//long creatend = System.currentTimeMillis();
		//System.out.println("-----------创建完成-------------:"+(creatend-start)/1000);

		// 添加第一个工作表并设置第一个Sheet的名字     
		WritableSheet sheet = wwb.createSheet("商机明细表", 0);     
		Label label;     
		for(int i=0;i<title.length;i++){     
			// Label(x,y,z) 代表单元格的第x+1列,第y+1行, 内容z     
			// 在Label对象的子对象中指明单元格的位置和内容     
			label = new Label(i,0,title[i]);     
			// 将定义好的单元格添加到工作表中     
			sheet.addCell(label);     
		}     
		// 下面是填充数据     
		/*    
		 * 保存数字到单元格,需要使用jxl.write.Number  
		 * 必须使用其完整路径,否则会出现错误  
		 * */   
		// 填充产品编号     
		//String sql="select * from sj.tlk_商机管理";
		
		PreparedStatement pstmt=null;  //商机管理的结果集
		PreparedStatement pstmt1=null; //产品明细
		PreparedStatement pstmt2=null;   //经销商
		PreparedStatement pstmt3=null;  //跟进记录

		ResultSet ret = null; //商机管理的结果集
		ResultSet ret1 = null;  //产品明细
		ResultSet ret2 = null;  //经销商
		ResultSet ret3 = null;  //跟进记录

		ResultSet re = null;  //主责人
		PreparedStatement ps=null;
		ResultSet re1 = null;  //项目总额
		PreparedStatement ps1=null;

		ResultSet re3 = null;  //跟进人
		PreparedStatement ps3=null; //跟进人
		//商机管理中的所有数据
		pstmt=conn.prepareStatement(sql);
		ret = pstmt.executeQuery();


		//long creatend5 = System.currentTimeMillis();
		//System.out.println("-----------查询商机管理表-------------:"+(creatend5-start)/1000);
		int j=1;
		while(ret.next()) {
			String id=ret.getString("id");
			String sjbh=ret.getString("item_sjbh"); //商机编号
			label = new Label(0,j,sjbh);     
			sheet.addCell(label); 
			
			String bbrq=ret.getString("item_bbrq"); //报备日期
			label = new Label(1,j,bbrq);     
			sheet.addCell(label);

			String cpz=ret.getString("item_cpz"); //产品组
			label = new Label(2,j,cpz);     
			sheet.addCell(label);

			String zzr=ret.getString("item_zzr"); //主责人
			String sq="select name from sjoaobpm.t_user where id='"+zzr+"'";				
			ps=conn.prepareStatement(sq);
			re=ps.executeQuery();
			String s="";
			while(re.next()){
				s=re.getString("name"); //主责人
			}
			label = new Label(3,j,s);     
			sheet.addCell(label);

			String ds=ret.getString("item_ds");  //地市
			label = new Label(4,j,ds);     
			sheet.addCell(label);

			String qx=ret.getString("item_qx");//区县
			label = new Label(5,j,qx);     
			sheet.addCell(label);

			String xmmc=ret.getString("item_xmmc"); //项目名称
			label = new Label(6,j,xmmc);     
			sheet.addCell(label);


			String sq1="select sum(item_jexj) from sj.tlk_产品明细  where parent='"+id+"'"; //查询这个项目的项目总额
			ps1=conn.prepareStatement(sq1);
			re1=ps1.executeQuery();
			double xmze=0;
			while(re1.next()){
				xmze=re1.getDouble("sum(item_jexj)");
			}
			//Double xmze=ret.getDouble("item_xmze");		          
			jxl.write.Number number = new jxl.write.Number(7,j,xmze);     
			sheet.addCell(number);      //项目总额

			String khjl=ret.getString("item_khjl");  //厂家客户经理
			label = new Label(8,j,khjl);     
			sheet.addCell(label);

			String yjsj=ret.getString("item_yjsj"); //预计采购时间
			label = new Label(9,j,yjsj);     
			sheet.addCell(label);

			String sjzt=ret.getString("item_sjzt"); //商机状态
			label = new Label(10,j,sjzt);     
			sheet.addCell(label);

			String gxrq=ret.getString("item_gxrq"); //更新日期
			label = new Label(11,j,gxrq);     
			sheet.addCell(label);

			
			String sql1="select item_faz,item_cpmx,item_xh,item_pz1,item_pz2,item_dtbj,item_sl,item_jexj from sj.tlk_产品明细  where parent='"+id+"'";
			pstmt1=conn.prepareStatement(sql1);
			ret1=pstmt1.executeQuery();
			int x=j;
			while(ret1.next()){
				String cpfz=ret1.getString("item_faz"); //产品方案
				label = new Label(12,x,cpfz);     
				sheet.addCell(label);

				String cpmx=ret1.getString("item_cpmx");//产品明细
				label = new Label(13,x,cpmx);     
				sheet.addCell(label);

				String xh=ret1.getString("item_xh"); //型号
				label = new Label(14,x,xh);     
				sheet.addCell(label);

				String pzcs=ret1.getString("item_pz1"); //配置参数
				label = new Label(15,x,pzcs);     
				sheet.addCell(label);

				String xsq=ret1.getString("item_pz2"); //显示器
				label = new Label(16,x,xsq);     
				sheet.addCell(label);

				Double dtbj=ret1.getDouble("item_dtbj");		//单台报价
				jxl.write.Number number1 = new jxl.write.Number(17,x,dtbj);
				sheet.addCell(number1);

				int sl=ret1.getInt("item_sl");		//数量
				jxl.write.Number number2 = new jxl.write.Number(18,x,sl);     
				sheet.addCell(number2);

				Double jexj=ret1.getDouble("item_jexj");		//金额小计
				jxl.write.Number number3 = new jxl.write.Number(19,x,jexj);     
				sheet.addCell(number3);
				x++;
			}



			String sql2="select item_jxsmc,item_jxszzr,item_lxfs,item_dz from sj.tlk_经销商   where parent='"+id+"'";
			pstmt2=conn.prepareStatement(sql2);
			ret2=pstmt2.executeQuery();
			int y=j;
			while(ret2.next()){

				String jxsmc=ret2.getString("item_jxsmc");  //经销商名称
				label = new Label(20,y,jxsmc);     
				sheet.addCell(label);

				String jxszzr=ret2.getString("item_jxszzr"); //经销商主责人
				label = new Label(21,y,jxszzr);     
				sheet.addCell(label);

				String lxfs=ret2.getString("item_lxfs"); //经销商联系方式
				label = new Label(22,y,lxfs);     
				sheet.addCell(label);

				String dz=ret2.getString("item_dz"); //地址
				label = new Label(23,y,dz);     
				sheet.addCell(label);
				y++;
			}


			String sql3="select item_gjsj,item_gjr,item_gjjl from sj.tlk_跟进记录   where parent='"+id+"'";
			pstmt3=conn.prepareStatement(sql3);
			ret3=pstmt3.executeQuery();
			int z=j;
			while(ret3.next()){

				String gjsj=ret3.getString("item_gjsj"); //跟进时间
				label = new Label(24,z,gjsj);     
				sheet.addCell(label);
                  
				String gjr=ret3.getString("item_gjr"); //跟进人
				String sq2="select name from sjoaobpm.t_user where id='"+gjr+"'";				
				ps3=conn.prepareStatement(sq2);
				re3=ps3.executeQuery();
				String s1="";
				while(re3.next()){
					s1=re3.getString("name");
				}
				
				label = new Label(25,z,s1);     
				sheet.addCell(label);
				String gjjl=ret3.getString("item_gjjl");
				label = new Label(26,z,gjjl);     
				sheet.addCell(label);

				z++;
			}

			int max=j;  
			if(j<x){
				max=x;
			}else if(j<y){
				max=y;
			}else if(j<z){
				max=z;
			}
			j=max;
			//j++;
		}
		//long end7 = System.currentTimeMillis();
		//System.out.println("----未写入数据之前的时间:"+(end7-start)/1000);
		// 写入数据     
		wwb.write();     
		// 关闭文件     
		wwb.close();     
		long end = System.currentTimeMillis();     
		System.out.println("----完成该操作共用的时间是:"+(end-start)/1000);   
        ret.close();
        ret1.close();
        ret2.close();
        ret3.close();
        re.close();
        re1.close();
        re3.close();
        ps.close();
        ps1.close();
        ps3.close();
        pstmt.close();
        pstmt1.close();
        pstmt2.close();
        pstmt3.close();

	} catch (Exception e) {     
		System.out.println("---出现异常---");     
		e.printStackTrace();     
	} finally{
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	return filename;
}

public boolean deleteFile(String path){

	File file=new File(path);
	String fileName=file.getName();
	if (file.exists() && file.isFile()) {
		if (file.delete()) {
			System.out.println("删除单个文件" + fileName + "成功!");

		} else {
			System.out.println("删除单个文件" + fileName + "失败!");
		}
	}else {
		System.out.println("删除单个文件失败:" + fileName + "不存在!");

	}
	return file.delete();
}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值