spring 上载 Excel
spring 下载 Excel
今天需从springMVC层导出excel数据,总结下相关的操作
搭建springMVC的环境
添加Excel的操作包 poi-3.0.1.jar。
控制器中的方法,这里用的是多动作控制器
Excel视图类,并将相关的写数据操作放里面,注意要继承AbstractExcelView,让它为Excel视图的子类,并实现 buildExcelDocument方法即可。
为了避免导出很多列时的重复工作,写了一个方法,将不用转换的数据列设置到行中,方法如下:
今天需从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("")); } } }