J2EE Excel导出

其实网上有很多关于Excel的操作的问题，我在这个地方也就是记录一下自己所做项目用到的对Excel的操作，以此方便日后遇到同样的问题 。

前台很简单，直接一个<a href="地址">下载</a>就行了，主要是后台的操作，所运用到的JAR包为 jxl.jar 

   

def exporXls = {
		println params
		def list = MaterialPlan.findAllByFromDeptAndAuditFlag(Department.get(session["department"].id),"已通过")
//		def list = MaterialPlan.findAllWhere([dept:Department.get(session["department"].id),auditFlag:"已通过",finallFlag:"已通过"])
		String headName = """
		序号@物资名称@物资标识@物资资源类型@物资类型@计量单位@年度名称@申报起始时间@申报截止时间@申报数量@
		发改委审批数量@联会决定数量@执行数量@申报单价@执行单价@发改委审批情况@联会决定审批情况@申请单位@
		申报时间@执行时间@最近更新时间@申报储备方式@发改委审批储备方式@联会决定储备方式@执行储备方式@备注
		"""

		String fileName = "年度物资储备计划.xls";// 导出文件名
		response.setHeader("Content-disposition","attachment;filename=" + new String(fileName.getBytes(), "ISO-8859-1"));
		response.setContentType("application/msexcel"); // 定义输出类型
		WritableWorkbook workbook = null;

		try {
			workbook = Workbook.createWorkbook(response.getOutputStream());
			WritableSheet book0 = workbook.createSheet("本单位申报年度物质储备计划", 0);
			writeExecl(book0, list, headName.split("@"));
			workbook.write();
		} catch (Exception e) {
			println e.getMessage();
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}


	//写出excel
	public void writeExecl(WritableSheet sheet, List<Object[]> dataList, String[] headName) {
		try {
			if (headName != null) {
				for (int i = 0; i < headName.length; i++) {
					sheet.addCell(new Label(i, 0, headName[i].trim()));
				}
			}
			if (dataList != null) {
				for (int i = 0; i < dataList.size(); i++) {
					def materialP = dataList.get(i);
					Format format = new SimpleDateFormat("yyyy-MM-dd");
					sheet.addCell(new Label(0,i + 1,i + 1 +""))
					sheet.addCell(new Label(1,i + 1,materialP.material?.name ? materialP.material?.name : ""))
					sheet.addCell(new Label(2,i + 1,materialP.material?.materialType ? materialP.material?.materialType : ""))
					sheet.addCell(new Label(3,i + 1,materialP.material?.resourceType?.typeName ? materialP.material?.resourceType?.typeName : ""))
					sheet.addCell(new Label(4,i + 1,materialP.material?.drType?.typeName ? materialP.material?.drType?.typeName : ""))
					sheet.addCell(new Label(5,i + 1,materialP.material?.unit ? materialP.material?.unit : ""))
					sheet.addCell(new Label(6,i + 1,materialP.annualPlanDate?.annual ? materialP.annualPlanDate?.annual + "" : ""))
					
					sheet.addCell(new Label(7,i + 1,materialP.annualPlanDate?.reportStartDate ? format.format(materialP.annualPlanDate?.reportStartDate) : ""))
					sheet.addCell(new Label(8,i + 1,materialP.annualPlanDate?.reportEndDate ? format.format(materialP.annualPlanDate?.reportEndDate) : ""))
					
					sheet.addCell(new Label(9,i + 1,materialP.reportMaterialNumber ? materialP.reportMaterialNumber + "" : ""))
					sheet.addCell(new Label(10,i + 1,materialP.reviewMaterialNumber ? materialP.reviewMaterialNumber + "" : ""))
					sheet.addCell(new Label(11,i + 1,materialP.finalMaterialNumber ? materialP.finalMaterialNumber + "" : ""))
					sheet.addCell(new Label(12,i + 1,materialP.executeMaterialNumber ? materialP.executeMaterialNumber + "" : ""))
					sheet.addCell(new Label(13,i + 1,materialP.executeMaterialNumber ? materialP.reportPrice + "" : ""))
					sheet.addCell(new Label(14,i + 1,materialP.executePrice ? materialP.executePrice + "" : ""))
					sheet.addCell(new Label(15,i + 1,materialP.auditFlag ? materialP.auditFlag : ""))
					sheet.addCell(new Label(16,i + 1,materialP.finallFlag ? materialP.finallFlag : ""))
					sheet.addCell(new Label(17,i + 1,materialP.fromDept?.name ? materialP.fromDept?.name : ""))
					
					sheet.addCell(new Label(18,i + 1,materialP.reportDate ? format.format(materialP.reportDate) : ""))
					sheet.addCell(new Label(19,i + 1,materialP.executeDate ? format.format(materialP.executeDate) : ""))
					sheet.addCell(new Label(20,i + 1,materialP.lastUpdated ? format.format(materialP.lastUpdated) : ""))
					
					sheet.addCell(new Label(21,i + 1,materialP.reportStoreType ? materialP.reportStoreType : ""))
					sheet.addCell(new Label(22,i + 1,materialP.reviewStoreType ? materialP.reviewStoreType : ""))
					sheet.addCell(new Label(23,i + 1,materialP.finalStoreType ? materialP.finalStoreType : ""))
					sheet.addCell(new Label(24,i + 1,materialP.executeStoreType ? materialP.executeStoreType : ""))
					sheet.addCell(new Label(25,i + 1,materialP.detail ? materialP.detail : ""))
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

到前台发送一个请求，通过后台的输出流然后在前端调用浏览器的下载功能就可以实现了。

在这里附上EXT3.4中给一个按钮加上一个下载的功能的代码

/**下载文档*/  
function toDownload(){  
    var simple = new Ext.FormPanel({  
	    fileUpload :true,  
	    defaults: {width: 230},  
	    defaultType: 'hidden'
    });  
    simple.render(document.body);  
              
    simple.getForm().submit({  
  		url:getRootPath()+'/materialPlan/exporXls'
    });  
	Ext.MessageBox.show({  
		msg: '正在导出, 请稍等...',  
        waitConfig : {text : '导出中...'},  
        width:300,  
        wait :true  
	});  
	window.οnblur=function(){  
		Ext.MessageBox.hide();  
		window.onblur = null;  
     	simple.destroy();  
    }  
};

定义一个按钮

var exportButton = new Ext.Button({导出按钮定义
    	    text : "导出到excel",
    	    style : {
    	        marginRight : '20px'
    	    },
    	    handler : toDownload
    	});

将这个按钮组装在一个panel上即可使用