JXL工具包操作excel文件

下面的两个操作都有个Domain对象,分别是Smsreport,KeyWords ,根据实际需求去替换

 

通过jxl输入excel文件

 

/**返回所有用户所发信息*/
	public static String  getSentMsgTotaldBox_Result(String f,List<Smsreport> o){
		final File f2=new File(f+"/"+getRodomFileName()+".xls");
		String nstate="";
		String yystype="";
		try {
			WritableWorkbook  _workbook=Workbook.createWorkbook(f2);
			WritableSheet _sheet=_workbook.createSheet("第一页", 0);
			int size=o.size();
			List<Label> li=new ArrayList<Label>();
			li.add(new Label(0, 0, "ID"));
			li.add(new Label(1, 0, "企业名称"));
			li.add(new Label(2, 0, "手机号码"));
			li.add(new Label(3, 0, "内容"));
			li.add(new Label(4, 0, "省份"));
			li.add(new Label(5, 0, "状态"));
			li.add(new Label(6, 0, "通道名称"));
			li.add(new Label(7, 0, "短信单价"));
			li.add(new Label(8, 0, "短信数量"));
			li.add(new Label(9, 0, "短信费用"));
			li.add(new Label(10, 0, "发送时间"));
			li.add(new Label(11, 0, "提交时间"));
			li.add(new Label(12, 0, "报告时间"));
			li.add(new Label(13, 0, "归属用户"));
			for(Label la:li){
				_sheet.addCell(la);
			}
			for(int i=0;i<size;i++){
				Smsreport b=o.get(i);
				Label n1=new Label(0,i+1,b.getMsgid());
				Label n2=new Label(1,i+1,b.getUser().getEnterprise().getFirmname());
				Label n3=new Label(2,i+1,b.getSendto());
				Label n4=new Label(3,i+1,b.getMessgeContant().getMessage());
				Label n5=new Label(4,i+1,b.getProvince().getName());
				if(b.getNstate()==0){
					nstate="发送成功";
				}else if(b.getNstate()==1){
					nstate="发送失败";
				}else if(b.getNstate()==3){
					nstate="提交成功";
				}else {
					nstate="提交失败";
				}
				Label n6=new Label(5,i+1,nstate);
			    Label n7=new Label(6,i+1,b.getChannel().getChannelname());
			    Number n8=new Number(7,i+1,b.getSinglePrice());
			    Number n9=new Number(8,i+1,b.getAmount());
			    Number n10=new Number(9,i+1,b.getTotalPrice());
			    Label n11=null;
			    Label n12=null;
			    Label n13=null;
			    if(b.getSendTime()!=null){
				    n11=new Label(10,i+1,b.getSendTime().toString());
			    }else{
			    	n11=new Label(10,i+1,"");
			    }
			    if(b.getSubmitTime()!=null){
				    n12=new Label(11,i+1,b.getSubmitTime().toString());
			    }else{
			    	   n12=new Label(11,i+1,"");
			    }
			    if(b.getReportTime()!=null){
			    	  n13=new Label(12,i+1,b.getReportTime().toString());
			    }else{
			    	  n13=new Label(12,i+1,"");
			    }
			    Label n14=new Label(13,i+1,b.getUser().getName());
				
			    _sheet.addCell(n1);
			    _sheet.addCell(n2);
			    _sheet.addCell(n3);
			    _sheet.addCell(n4);
			    _sheet.addCell(n5);
			    _sheet.addCell(n6);
			    _sheet.addCell(n7);
			    _sheet.addCell(n8);
			    _sheet.addCell(n9);
			    _sheet.addCell(n10);
			    _sheet.addCell(n11);
			    _sheet.addCell(n12);
			    _sheet.addCell(n13);
			    _sheet.addCell(n14);
			}
			_workbook.write();
			_workbook.close();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		new Thread(new Runnable() {
			
			public void run() {
				try {
					// 线程睡20秒
					Thread.sleep(60000);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				f2.delete();
			}
		}).start();
		
		return f2.getPath()
	} 
	

 

 

通过jxl读取excel文件

/**返回过滤名单**/
	public  List<KeyWords>  getKeyWordsList(File f){
		List<KeyWords> list=new ArrayList<KeyWords>();
		Workbook workbook;
		KeyWords b=null;
		try {
			workbook=Workbook.getWorkbook(f);
			Sheet sheet=workbook.getSheet(0);
			Cell [] c1=sheet.getColumn(0);
			for(int i=1;i<c1.length;i++){
				Cell cb=sheet.getCell(1, i	);
				Cell ca=sheet.getCell(0, i	);
				if(ca.getContents().trim().length()>0){
					b=new KeyWords(ca.getContents(),cb.getContents(),true);
					list.add(b);
				}				
			}
		
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}