利用jxl进行Excel的导出操作

web工程 利用jxl（JExcelApi）进行Excel的导出功能

jxl.jar版本 2.6.6 详见附件

  	public void exportYCItemPaid(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		response.setContentType("application/vnd.ms-excel");
		String fileName =  "赔付标的信息("+1+")";
		fileName = new String(fileName.getBytes("GBK"),"ISO8859-1");
		response.addHeader("Content-Disposition","attachment;filename="+fileName+".xls");   
		OutputStream os = response.getOutputStream();
		WritableWorkbook book = Workbook.createWorkbook(os);
	
		WritableSheet sheet = book.createSheet("test", 0);
		//生成详细数据
		
		WritableFont wf = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false);
		
		WritableCellFormat textFormat = new WritableCellFormat(NumberFormats.TEXT); //定义一个单元格样式
		textFormat.setAlignment(Alignment.CENTRE);
		textFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		
		
		WritableCellFormat doubleFormat = new WritableCellFormat(NumberFormats.FLOAT); //定义一个单元格样式
		doubleFormat.setAlignment(Alignment.CENTRE);
		doubleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		
		WritableCellFormat wcf = new WritableCellFormat(wf); 
		wcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
		
		WritableCellFeatures txcf0 = new WritableCellFeatures();
		txcf0.setComment("合同号请与承保系统中数据相符");
		
		WritableCellFeatures txcf1 = new WritableCellFeatures();
		txcf1.setComment("农户姓名请与承保系统中数据相符");
	
		WritableCellFeatures txcf2 = new WritableCellFeatures();
		txcf2.setComment("归属烟叶站名称请与承保系统中数据相符");
		
		WritableCellFeatures txcf3 = new WritableCellFeatures();
		txcf3.setComment("烟草生长期(1——团棵期、2——现蕾期、3——采烤期)");
		
		WritableCellFeatures txcf4 = new WritableCellFeatures();
		txcf4.setComment("当期单株有效叶片数(请录入数字 介于0.1到9999999.99)");
		
		WritableCellFeatures txcf5 = new WritableCellFeatures();
		txcf5.setComment("灾害类型(请录入字符)");
		
		WritableCellFeatures txcf6 = new WritableCellFeatures();
		txcf6.setComment("受灾地点(请录入字符)");
		
		WritableCellFeatures txcf7 = new WritableCellFeatures();
		txcf7.setComment("受损面积(请录入字符 介于0.10.1到9999999.99)");
		
		WritableCellFeatures txcf8 = new WritableCellFeatures();
		txcf8.setComment("是否绝收(0——不绝收，1——绝收;)");
		
		WritableCellFeatures txcf9 = new WritableCellFeatures();
		txcf9.setComment("受损程度(请录入数字介于 1到100)");
		
		WritableCellFeatures txcf10 = new WritableCellFeatures();
		txcf10.setComment("赔付金额(请录入数字)");
		
		WritableCellFeatures txcf11 = new WritableCellFeatures();
		txcf11.setComment("开户行账号(请录入字符)");
		
		WritableCellFeatures txcf12 = new WritableCellFeatures();
		txcf12.setComment("账户姓名(请录入字符)");
		
		WritableCellFeatures txcf13 = new WritableCellFeatures();
		txcf13.setComment("开户银行(请录入字符)");
		
		CellView cv = new CellView(); //定义一个列显示样式 
		cv.setFormat(wcf);//把定义的单元格格式初始化进去
		cv.setSize(10*265);//设置列宽度（不设置的话是0，不会显示）
	 
		String orgCode = "1";
		String orgName = "12";
		int columnIndex = 0;
	    sheet.setColumnView(columnIndex, 25); // 设置列的宽度 第一列
	    sheet.setColumnView(++columnIndex, 15); // 设置列的宽度 第二列
	    sheet.setColumnView(++columnIndex, 35); // 设置列的宽度 第三列
	    sheet.setColumnView(++columnIndex, 10); // 设置行的高度 第四列
	    sheet.setColumnView(++columnIndex, 10); // 设置行的高度 第五列
	    sheet.setColumnView(++columnIndex, 20); // 设置行的高度 第六列
	    sheet.setColumnView(++columnIndex, 25); // 设置行的高度 第七列
	    
	    sheet.setColumnView(++columnIndex, 10); // 设置行的高度 第八列
	    sheet.setColumnView(++columnIndex, 10); // 设置行的高度 第九列
	    
	    sheet.setColumnView(++columnIndex, 10); // 设置行的高度 第十列
	    sheet.setColumnView(++columnIndex, 25); // 设置行的高度 十一列
	    
	    sheet.setColumnView(++columnIndex, 30); // 设置行的高度 十二列
	    sheet.setColumnView(++columnIndex, 20); // 设置行的高度 十三列
	    sheet.setColumnView(++columnIndex, 20); // 设置行的高度 十四列
	    String[] titles = {	"合同号","农户姓名","归属烟叶站名称","烟草生长期",
				"当期单株有效叶片数","灾害类型","受灾地点","受损面积",
				"是否绝收","受损程度","赔付金额","开户行账号","账户姓名","开户银行"};
	
		//生成Title
		Label titleLabel = null;
		for (int i = 0; i < titles.length; i++) {
			titleLabel =   new Label(i, 0, titles[i],textFormat);
			switch (i) {
			case 0://合同号
				titleLabel.setCellFeatures(txcf0);
				break;
			case 1://农户姓名
				titleLabel.setCellFeatures(txcf1);
				break;
			case 2://归属烟叶站名称
				titleLabel.setCellFeatures(txcf2);
				break;
			case 3://烟草生长期
				titleLabel.setCellFeatures(txcf3);
				break;
			case 4://当期单株有效叶片数
				titleLabel.setCellFeatures(txcf4);
				break;
			case 5://灾害类型
				titleLabel.setCellFeatures(txcf5);
				break;
			case 6://受灾地点
				titleLabel.setCellFeatures(txcf6);
				break;
			case 7://受损面积
				titleLabel.setCellFeatures(txcf7);
				break;
			case 8://是否绝收
				titleLabel.setCellFeatures(txcf8);
				break;
			case 9://受损程度
				titleLabel.setCellFeatures(txcf9);
				break;
			case 10://赔付金额
				titleLabel.setCellFeatures(txcf10);
				break;
			case 11://开户行账号
				titleLabel.setCellFeatures(txcf11);
				break;
			case 12://账户姓名
				titleLabel.setCellFeatures(txcf12);
				break;
			case 13://开户银行
				titleLabel.setCellFeatures(txcf13);
				break;
			default:
				break;
			}
			sheet.addCell(titleLabel);
		}
	   
		
		
		
		List<String> jsList = new ArrayList<String>(); 
		jsList.add("1"); 
		jsList.add("0");
	
		//生长期下拉列表
		List<String> seasonList = new ArrayList<String>(); 
		seasonList.add("1"); 
		seasonList.add("2");
		seasonList.add("3");
		
		 
	//	for (int i = 0; i < lossInfoList.size(); i++) {
			jxl.write.Number titleNumber = null;
			int cellIndex = 0;
			int i=0;
			//合同号
			titleLabel =   new Label(cellIndex++ , i+1, "1121212",textFormat);
			sheet.addCell(titleLabel);
			
			//农户姓名
			titleLabel =   new Label(cellIndex++ , i+1, "1121212",textFormat);
			sheet.addCell(titleLabel);
			
			//归属烟叶站名称
			titleLabel =   new Label(cellIndex++ , i+1, orgName ,textFormat);//归属烟叶站名称
			sheet.addCell(titleLabel);
			
			//烟草生长期(1——团棵期、2——现蕾期、3——采烤期)
			titleNumber =   new jxl.write.Number(cellIndex++ , i+1,1 , textFormat);
			WritableCellFeatures cf1 = new WritableCellFeatures();
			cf1.setDataValidationList(seasonList);
			titleNumber.setCellFeatures(cf1);
			sheet.addCell(titleNumber);
			
			//当期单株有效叶片数
			titleNumber =   new jxl.write.Number(cellIndex++ , i+1, 1, doubleFormat);
			WritableCellFeatures cf2 = new WritableCellFeatures();
			cf2.setNumberValidation(0.1 ,9999999.99, BaseCellFeatures.BETWEEN);
			titleNumber.setCellFeatures(cf2);
			sheet.addCell(titleNumber);
			
			//灾害类型
			titleLabel =   new Label(cellIndex++ , i+1, "1121212" ,textFormat);
			sheet.addCell(titleLabel);
			
			//受灾地点
			titleLabel =   new Label(cellIndex++ , i+1, "1121212" ,textFormat);
			sheet.addCell(titleLabel);
	
			//受损面积
			titleNumber =   new jxl.write.Number(cellIndex++ , i+1,1, doubleFormat);
			WritableCellFeatures cf3 = new WritableCellFeatures();
			cf3.setNumberValidation(0.1 ,9999999.99, BaseCellFeatures.BETWEEN);
			titleNumber.setCellFeatures(cf3);
			sheet.addCell(titleNumber);
			
			
			//是否绝收 设置下拉列表取值范围 0,1	
			titleNumber =   new jxl.write.Number(cellIndex++ , i+1, Double.parseDouble("12") ,textFormat);
			WritableCellFeatures cf6 = new WritableCellFeatures();
			cf6.setDataValidationList(jsList);
			titleNumber.setCellFeatures(cf6);
			sheet.addCell(titleNumber);
			
			//受损程度 
			titleNumber   =   new jxl.write.Number (cellIndex++ , i+1,12  ,doubleFormat);
			WritableCellFeatures cf4 = new WritableCellFeatures();
			cf4.setNumberValidation(0.01 ,100, BaseCellFeatures.BETWEEN);
			titleNumber.setCellFeatures(cf4);
			sheet.addCell(titleNumber);
			
			//赔付金额
			titleNumber =   new jxl.write.Number(cellIndex++ , i+1, 0, doubleFormat);
			WritableCellFeatures cf5 = new WritableCellFeatures();
			cf5.setNumberValidation(0.01 ,9999999.99, BaseCellFeatures.BETWEEN);
			titleNumber.setCellFeatures(cf5);
			sheet.addCell(titleNumber);
			
			
	//	}
//		//设置下拉列表取值范围
//		List angerlist = new ArrayList(); 
//		angerlist.add("是"); 
//		angerlist.add("否");
//		Label norFormat =  new Label(1, 0, "请选择");
//		WritableCellFeatures ws = new WritableCellFeatures();
//		ws.setDataValidationList(angerlist);
//		norFormat.setCellFeatures(ws);
//		sheet.addCell(norFormat);
//		
//		
//		
//		//设置数字有效性  取值是100, 200区间， 100和200是有效的
//		jxl.write.Number  number = new jxl.write.Number(3,3,100);
//		WritableCellFeatures cf = new WritableCellFeatures();
//		cf.setNumberValidation(100, 200,BaseCellFeatures.BETWEEN);
//		number.setCellFeatures(cf);
//		sheet.addCell(number);
//		
//		//设置数字有效性  大等于100
//		jxl.write.Number  number1 = new jxl.write.Number(3,4,0);
//		WritableCellFeatures cf1 = new WritableCellFeatures();
//		cf1.setNumberValidation(100 , BaseCellFeatures.GREATER_THAN);
//		number1.setCellFeatures(cf1);
//		sheet.addCell(number1);
//		
//		//设置数字有效性  小于100
//		jxl.write.Number  number2 = new jxl.write.Number(3,5,0);
//		WritableCellFeatures cf2 = new WritableCellFeatures();
//		cf2.setNumberValidation(100 , BaseCellFeatures.LESS_THAN);
//		number2.setCellFeatures(cf2);
//		sheet.addCell(number2);
//	
//		//设置数字有效性  不在100，200区间的数字
//		jxl.write.Number  number3 = new jxl.write.Number(3,6,0);
//		WritableCellFeatures cf3 = new WritableCellFeatures();
//		cf3.setNumberValidation(100,200 , BaseCellFeatures.NOT_BETWEEN);
//		number3.setCellFeatures(cf3);
//		sheet.addCell(number3);
//		
//		//设置数字有效性  必须等于 100
//		jxl.write.Number  number4 = new jxl.write.Number(3,7,0);
//		WritableCellFeatures cf4 = new WritableCellFeatures();
//		cf4.setNumberValidation(100 , BaseCellFeatures.EQUAL);
//		number4.setCellFeatures(cf4);
//		sheet.addCell(number4);
//		
//		//设置数字有效性  不等于 100
//		jxl.write.Number  number5 = new jxl.write.Number(3,8,0);
//		WritableCellFeatures cf5 = new WritableCellFeatures();
//		cf5.setNumberValidation(100 , BaseCellFeatures.NOT_EQUAL);
//		number5.setCellFeatures(cf5);
//		sheet.addCell(number5);
//		
//		//设置数字有效性  大于等于100
//		jxl.write.Number  number6 = new jxl.write.Number(3,9,0);
//		WritableCellFeatures cf6 = new WritableCellFeatures();
//		cf6.setNumberValidation(100  , BaseCellFeatures.GREATER_EQUAL);
//		number6.setCellFeatures(cf6);
//		sheet.addCell(number6);
//		
//		//设置数字有效性  小于等于100
//		jxl.write.Number  number7 = new jxl.write.Number(3,10,0);
//		WritableCellFeatures cf7 = new WritableCellFeatures();
//		cf7.setNumberValidation(100, BaseCellFeatures.LESS_EQUAL);
//		number7.setCellFeatures(cf7);
//		sheet.addCell(number7);
	//	
	
		book.write();
		book.close();
		os.close();
		response.flushBuffer();
	}