导出excel数据

struts.xml配置

引用

<result name="exportData" type="stream">
<!-- 指定下载文件的文件类型 -->
<param name="contentType">xls</param>
<!-- 指定下载文件的文件位置 -->
<param name="inputName">targetFile</param>
<param name="contentDisposition">attachment;filename="CarInsModification.xls"</param>
<!-- 指定下载文件的缓冲大小 -->
<param name="bufferSize">2048</param>
</result>

JSP页面代码

 <input type="button" class="button" value="导出EXCEL" onClick="onExport()" onMouseOver="switchClass(this)" onMouseOut="switchClass(this)">
//导出全部记录
	        function onExport(){  
	        	var form=document.getElementById("searchForm"); 
                form.action="carinsapply/CarInsModificationAction!exportModification.action";
                //alert(getForm().action);
                form.submit(); 
	        }

Action层代码

public String exportModification() throws FacadeException{
		return Constants.Dispatcher.EXPORT_DATA; 
	}
	/* 
	下载用的Action应该返回一个InputStream实例， 
	该方法对应在result里的inputName属性值为targetFile 
	*/ 
	public InputStream getTargetFile() throws Exception 
	{ 
		filePath = "/exportdata";
		 filePath = "/exportdata";
			try{
				String fileName = carInsModificationFacade.exportModification(filePath); 
				if (!StringUtils.isBlank(fileName)){
					filePath = filePath + "/" + fileName;
					filePath = filePath.substring(1);//因为jsp页面中要加入<%= basePath%>所以要去掉前面的那个“/”
				}else{
					filePath = null;
				}
			}catch(Exception fe){
				fe.printStackTrace();
				throw new FacadeException("导出批单数据出错!"+fe.getMessage());
			}
	    return ServletActionContext.getServletContext().getResourceAsStream(filePath); 
	} 

Facade层代码

public String exportModification(String filePath) throws FacadeException{
	    	String fileName = "carInsModification.xls";
	    	try{
		    	List<CarInsModificationInfo> modificationList = carInsModificationDAO.findAllModification();
		    	if (modificationList.size() > 0){  
		    		//开始创建excel文件   
		    		filePath = ServletActionContext.getServletContext().getRealPath(filePath); 
		    		filePath = filePath + "\\" + fileName;
		    		log.info("filepath="+filePath);
		    		WritableWorkbook book = Workbook.createWorkbook(new File(filePath));  //注意new File只能读取本地的路径  
		            WritableSheet sheet = book.createSheet("Sheet_1", 0);
		            //先生成字段名 
		            //注意第一个是行，第二个是列 
		            sheet.addCell(new Label(0,0,"投保单号"));   
		            sheet.addCell(new Label(1,0,"批单号"));   
		            sheet.addCell(new Label(2,0,"批单状态")); 
		            sheet.addCell(new Label(3,0,"批改类型")); 
		            sheet.addCell(new Label(4,0,"保费变化"));  
		            sheet.addCell(new Label(5,0,"批改时间"));  
		            //开始读取数据
		            for (int i=0;i<modificationList.size();i++){
		            	String policyCode = modificationList.get(i).getPolicyCode();
		            	if (!StringUtils.isBlank(policyCode)){ 
		            		sheet.addCell(new Label(0,i+1,policyCode));
		            	}
		            	String modiCode = modificationList.get(i).getModificationCode();
		            	if (!StringUtils.isBlank(modiCode)){ 
		            		sheet.addCell(new Label(1,i+1,modiCode));
		            	} 
		            	String modiStateCode = modificationList.get(i).getMstateCode(); 
		            	if (!StringUtils.isBlank(modiStateCode)){
		            		String modiState = carInsModificationDAO.findNoteByCode("CpolicyState", modiStateCode);
		            		if (modiState == null){
		            			sheet.addCell(new Label(2,i+1,modiStateCode));
		            		}else{
			            		StringBuffer  modiStateBuf = new StringBuffer();
			            		modiStateBuf.append("(");
			            		modiStateBuf.append(modiStateCode);
			            		modiStateBuf.append(")");
			            		modiStateBuf.append(modiState);
			            		sheet.addCell(new Label(2,i+1,modiStateBuf.toString()));
		            		}
		            	}
		            	String modiTypeCode = modificationList.get(i).getCmodiType().getCode();
		            	if (!StringUtils.isBlank(modiTypeCode)){
		            		String modiType = carInsModificationDAO.findNoteByCode("CmodiType", modiTypeCode);
		            		if (modiType == null){
		            			sheet.addCell(new Label(3,i+1,modiTypeCode));
		            		}else{
			            		StringBuffer modiTypeBuf = new StringBuffer();
			            		modiTypeBuf.append("(");
			            		modiTypeBuf.append(modiTypeCode);
			            		modiTypeBuf.append(")");
			            		modiTypeBuf.append(modiType);
			            		sheet.addCell(new Label(3,i+1,modiTypeBuf.toString()));
		            		}
		            	} 
		            	Double insChange = modificationList.get(i).getInsChange(); 
		            	if (insChange != null){
		            		sheet.addCell(new jxl.write.Number(4,i+1,insChange));
		            	}
		            	String modifyTime = DateFormatUtil.transferDateToString10(modificationList.get(i).getModifyTime());
		            	if (!StringUtils.isBlank(modifyTime)){
		            		sheet.addCell(new Label(5,i+1,modifyTime));
		            	}
		            }
		            book.write();      
		            book.close();
		    	}else{
		    		fileName = "";
		    		throw new FacadeException("该批单信息不存在！");
		    	}
	    	}catch (Exception e){
				throw new FacadeException("生成excel数据失败"+e);
			}  
	    	return fileName;
	    }