导出Excel文件

 

 

导出Excel（导出Excel的最长时间跨度不可超过两个月）：

<a href="javascript:void(0);" onclick="exportOrder()" style="color:#000;"><center>导出Excel</center></a>

 

JS：

function exportOrder(){
		var strOrderCode = $("#strOrderCode").val();
		var strCustomerCode = $("#strCustomerCode").val();
		var strPhone = $("#strPhone").val();
		var strLinkMan = $("#strLinkMan").val();
		var dtOrderTime = $("#dtOrderTime").val();
					
		var dtBeginTime = $("#dtBeginTime").val();
		var dtEndTime = $("#dtEndTime").val();

		if(dtBeginTime != "" && dtBeginTime != undefined && dtEndTime !="" && dtEndTime != undefined){
			var d = new Date(dtBeginTime.replace(/-/g,"/"));  
			var d1 = new Date(dtEndTime.replace(/-/g,"/"));  
			if(!checkDate(d,d1)){
				return;
			}
		}else if(dtBeginTime != "" && dtBeginTime != undefined && (dtEndTime =="" || dtEndTime == undefined)  ){
			var d = new Date(dtBeginTime.replace(/-/g,"/"));  
			var d1 = new Date();  
			if(!checkDate(d,d1)){
				return;
			}

		}else if(dtEndTime != "" && dtEndTime != undefined && (dtBeginTime =="" || dtBeginTime == undefined)  ){
			var d1 = new Date(dtEndTime.replace(/-/g,"/"));  
			var d = new Date(d1.getTime() - 1000*60*60*24*62);  

			var y = d.getFullYear().toString();
			var m = (d.getMonth()+1) < 10 ? "0" +(d.getMonth()+1) : (d.getMonth()+1).toString();
			var d = d.getDate() < 10 ? "0"+d1.getDate() : d.getDate().toString(); 
				
			dtBeginTime = y +"-"+ m+"-" + d;
			$("#dtBeginTime").val(dtBeginTime);
			
			if(!checkDate(d,d1)){
				return;
			}
		}
		
		
		var url = '/purchase/exportExcel.do?strOrderCode='+strOrderCode;
		url += '&strCustomerCode='+strCustomerCode;
		url += '&strPhone='+strPhone;
		url += '&strLinkMan='+strLinkMan;
		
		if(dtOrderTime != "" && dtOrderTime != undefined){
			url += '&dtOrderTime='+dtOrderTime;
		}
		if(dtBeginTime != "" && dtBeginTime != undefined){
			url += '&dtBeginTime='+dtBeginTime;
		}
		if(dtEndTime != "" && dtEndTime != undefined){
			url += '&dtEndTime='+dtEndTime;
		}
		url += '&idd='+Math.random();

		redirect(url);
	}
	
//大于2个月
function checkDate(d,d1){
	if(d1.getTime()-d.getTime() > 1000*60*60*24*62){
		alert("对不起，导出Excel的最长时间跨度不可超过两个月");
	return false;
	}

	return true;
}	
	

 

后台：

public void exportExcel(){
		String strFilePathName = "importPurchaseOrderList"+System.currentTimeMillis()+".xls";
		
		HttpServletRequest request=ServletActionContext.getRequest();
	    HttpServletResponse response=ServletActionContext.getResponse();
	    
		response.setHeader("Content-disposition", "attachment; filename=" + strFilePathName);
		response.setContentType("application/vnd.ms-excel");
		
		cdoRequest.setStringValue(ITransService.TRANSNAME_KEY,"getExportPurchaseList");
		cdoRequest.setStringValue(ITransService.SERVICENAME_KEY,"purchaseService");
		
		if(StringUtil.isNotEmpty(strOrderCode)){
			cdoRequest.setStringValue("strOrderCode", likeWrap(strOrderCode));
		}
		
		if(StringUtil.isNotEmpty(strPhone)){
			cdoRequest.setStringValue("strPhone", likeWrap(strPhone));
		}
		...
		
		Return ret = getServiceBus().handleTrans(cdoRequest, cdoResponse);
      	if(ret.getCode() == 0){
      		codsPurchaseOrder = cdoResponse.getCDOArrayValue("codsPurchaseOrder");
      		      		
      		try
            {
      			//表头格式
      			WritableFont wf_title = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.DOUBLE, jxl.format.Colour.BLACK); 
                WritableCellFormat wcf_title = new WritableCellFormat(wf_title); // 单元格定义
                wcf_title.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式 
                // 设置边框线为实线
                wcf_title.setBorder(jxl.format.Border.LEFT, BorderLineStyle.THIN);
                wcf_title.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.THIN);
                wcf_title.setBorder(jxl.format.Border.TOP, BorderLineStyle.THIN);
                wcf_title.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.THIN);
                
                //内容左对齐格式
                WritableFont wf_left = new WritableFont(WritableFont.ARIAL, 12,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); 
                WritableCellFormat wcf_left = new WritableCellFormat(wf_left); // 单元格定义
                wcf_left.setAlignment(jxl.format.Alignment.LEFT); // 设置对齐方式 
                wcf_left.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 设置对齐方式（垂直居中）
                // 设置边框线为实线
                wcf_left.setBorder(jxl.format.Border.LEFT, BorderLineStyle.THIN);
                wcf_left.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.THIN);
                wcf_left.setBorder(jxl.format.Border.TOP, BorderLineStyle.THIN);
                wcf_left.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.THIN);
                
                //内容右对齐格式
                WritableFont wf_right = new WritableFont(WritableFont.ARIAL, 12,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); 
                WritableCellFormat wcf_right = new WritableCellFormat(wf_right); // 单元格定义
                wcf_right.setAlignment(jxl.format.Alignment.RIGHT); // 设置对齐方式（水平居右）
                wcf_right.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 设置对齐方式（垂直居中）
                // 设置边框线为实线
                wcf_right.setBorder(jxl.format.Border.LEFT, BorderLineStyle.THIN);
                wcf_right.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.THIN);
                wcf_right.setBorder(jxl.format.Border.TOP, BorderLineStyle.THIN);
                wcf_right.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.THIN);
              
                //内容居中对齐格式
                WritableFont wf_center = new WritableFont(WritableFont.ARIAL, 12,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); 
                WritableCellFormat wcf_center = new WritableCellFormat(wf_center); // 单元格定义
                wcf_center.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式 
                wcf_center.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 设置垂直对齐方式 
                wcf_center.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 设置对齐方式（垂直居中）
                // 设置边框线为实线
                wcf_center.setBorder(jxl.format.Border.LEFT, BorderLineStyle.THIN);
                wcf_center.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.THIN);
                wcf_center.setBorder(jxl.format.Border.TOP, BorderLineStyle.THIN);
                wcf_center.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.THIN);
                
                // 打开文件
				//jxl.write.WritableFont
                WritableWorkbook book= Workbook.createWorkbook(ServletActionContext.getResponse().getOutputStream());
//              WritableWorkbook book= Workbook.createWorkbook(new File(strFileName));

            	WritableSheet sheet = null;
                // 生成名为“第一页”的工作表，参数0表示这是第一页
            	sheet = book.createSheet("采购单", 0);

                Label labelTitle1 = new Label(0, 0, "单据编号" ,wcf_title);
                Label labelTitle2 = new Label(1, 0, "订单金额" ,wcf_title);
                Label labelTitle3 = new Label(2, 0, "供应商编号",wcf_title);
                Label labelTitle4 = new Label(3, 0, "供应商",wcf_title);
				...

                // 将定义好的单元格添加到工作表中
                sheet.addCell(labelTitle1);
                sheet.setColumnView(0, 30);
                sheet.addCell(labelTitle2);
                sheet.setColumnView(1, 25);
                sheet.addCell(labelTitle3);
                sheet.setColumnView(2, 20);
                sheet.addCell(labelTitle4);
                sheet.setColumnView(3, 20);
				...
                
            	for(int i = 0 ; i < codsPurchaseOrder.length ; i++){
            		
					//jxl.write.Label.Label(int c, int r, String cont, CellFormat st)

					Label label1 = new Label(0, i+1, codsPurchaseOrder[i].getStringValue("strOrderCode"), wcf_center);
					sheet.addCell(label1);
					
					Label label2 = new Label(1, i+1, codsPurchaseOrder[i].getStringValue("nAmount"), wcf_center);
					sheet.addCell(label2);
					
					Label label3 = new Label(2, i+1, codsPurchaseOrder[i].getStringValue("strCustomerCode"), wcf_center);
					sheet.addCell(label3);
					
					Label label4 = new Label(3, i+1, codsPurchaseOrder[i].getStringValue("strCustomerName"), wcf_center);
					sheet.addCell(label4);
					...
				}
            	

                // 写入数据并关闭文件
                book.write();
                book.close();
            }
            catch (Exception e)
            {
                return;
            }
      	}
}

 。。