spring 下载 Excel

今天需从springMVC层导出excel数据，总结下相关的操作

搭建springMVC的环境
添加Excel的操作包 poi-3.0.1.jar。

控制器中的方法，这里用的是多动作控制器

	public ModelAndView downCardAccountExcel(HttpServletRequest request,
			HttpServletResponse response, Object command)
			throws ServletRequestBindingException {

		ModelAndView mv = new ModelAndView();
		ExcelCardAccountView mvExcel = new ExcelCardAccountView();
		
		String queryStr = ReqUtil.getUrl(request);
		Map<String,String> queryMap = ReqUtil.getParamMap(request);
		
		Map map = new HashMap();
		if(queryMap.size()>1) {//查询
			List<Cardaccount> cardAccounts = cardAccountService.queryCardAccountBy(queryMap,0,maxDownloadCount);
			map.put("records", cardAccounts);
		}
		
		return new ModelAndView(mvExcel,map);
	}

Excel视图类，并将相关的写数据操作放里面，注意要继承AbstractExcelView，让它为Excel视图的子类，并实现 buildExcelDocument方法即可。

public class ExcelCardAccountView extends AbstractExcelView {

	@Override
	protected void buildExcelDocument(Map map, HSSFWorkbook workbook,
			HttpServletRequest request, HttpServletResponse response) throws Exception {

		int sheetRowNum = 0;	//行控制
		
		//创建工作表
		HSSFSheet sheet = workbook.createSheet("卡账户查询");
		

		//取得模型数据
		List list = (List) map.get("records");
		if(list!=null && list.size()>0) {
			//创建标题
			HSSFRow titleRow = sheet.createRow((short) sheetRowNum++);
			HSSFCell titleCell = titleRow.createCell((short) 0);
			titleCell.setCellValue(new HSSFRichTextString("卡账户查询"));

			//创建一个空行
			sheet.createRow(sheetRowNum++);

			//创建数据表头
			String[] titles = { "账户号", "卡号","会员号","商户号","分店号","余额","次数","积分","创建时间" ,"状态"};
			HSSFRow dataTitleRow = sheet.createRow((short) sheetRowNum++);
			for (int i = 0; i < titles.length; i++) {
				HSSFCell cell = dataTitleRow.createCell((short) i);
				cell.setCellValue(new HSSFRichTextString(titles[i]));
			}
			
			String[] methodArray = { "getAccountId", "getCardId","getMemberId","getMerchantsId","getBranchId","getBalance","getTimes","getIntegral","getGmtCreate" };
			
			
			//数据模型转换:创建表格数据
			Iterator<Cardaccount> iter = list.iterator();
			for (int i = sheetRowNum; i < list.size() + sheetRowNum; i++) {
				if (iter.hasNext()) {
					Cardaccount item = iter.next();
					HSSFRow dataRow = sheet.createRow((short) (i));
					ServUtil.writeRowData(item,dataRow,methodArray);	//写入行
					
					//其他的数据转换
					HSSFCell cell_0 = dataRow.createCell((short) methodArray.length);
					if(item.getStatus()==1) {
						cell_0.setCellValue(new HSSFRichTextString("正常"));
					} else {
						cell_0.setCellValue(new HSSFRichTextString("异常"));
					}
				}
			}		
		} else {//没数据
			//创建提示
			HSSFRow titleRow = sheet.createRow((short) sheetRowNum++);
			HSSFCell titleCell = titleRow.createCell((short) 0);
			titleCell.setCellValue(new HSSFRichTextString("没有数据"));
		}
		
	}

}

为了避免导出很多列时的重复工作，写了一个方法，将不用转换的数据列设置到行中，方法如下：

	/**
	 * 将对象o按methodArray的顺序写入到dataRow的行中，在导出excel中用到
	 * @param o				写入的对象
	 * @param dataRow		写人的行
	 * @param methodArray	拿取o对象的get方法列表 如 String[] paraArray = { "getAccountId", "getCardId"};
	 * 效果是，将o的accountId和cardId值设置到dataRow中
	 */
	public static void writeRowData(Object o, HSSFRow dataRow,String[] methodArray)  {
		Class<?> c = o.getClass();
		
		for(int i=0;i<methodArray.length;i++) {
			HSSFCell cell = dataRow.createCell((short)i);	//创建一个行
			String methodName = methodArray[i];
			//根据paraArray中的顺序，依次取出o对应方法的值，设置到dataRow中
			Method m = null;
			try {
				m = c.getMethod(methodName);
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			}
			if(m!=null) {
				try {
					String returnType = m.getReturnType().toString(); 
					if(returnType.contains("String")) {
						cell.setCellValue(new HSSFRichTextString((String)m.invoke(o)));
					} else if(returnType.contains("Integer") || returnType.contains("int")) {
						cell.setCellValue(SimpleFilter.filterNull((Integer)m.invoke(o)));
					} else if(returnType.contains("Date")) {
						cell.setCellValue(new HSSFRichTextString(SimpleDateUtils.getDateString((Date)m.invoke(o), null)));
					}
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				}
			} else {
				cell.setCellValue(new HSSFRichTextString(""));
			}
		}
	}