//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();
}
扫一扫
1322
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
