导出所选行为excle
要实现的是将所选行导出。例如勾选这两条
导出为
我的前台是easyUI实现的。所以前台代码为：
{ { id:'btn_export', text : '导出所选行', iconCls : 'icon-print', handler : function() { var arr = $('#dayrec').datagrid( 'getSelections'); if (arr.length <= 0) { $.messager.show({ title : '温馨提示!', msg : '至少选择一行记录进行导出!' }); } else { $.messager.confirm('温馨提示','确认导出?',function(r) { if (r) { var ids = ''; for (var i = 0; i < arr.length; i++) { ids += arr[i].id+ ','; }//从前台取得所选行的ids,拼接成字符串，传入后台 ids = ids.substring(0,ids.length - 1); $('#downform').form('submit', { url : "<%=basePath%>dayrec/exportSelected",//交给指定的url处理，后台可以接收到requestparam ids method : "post", onSubmit: function(param){ param.ids = ids; }, error : function() { $.messager.alert('温馨提示', '导出失败'); }, }); $('#dayrec').datagrid('unselectAll'); } else { return; } }); } } }
后台处理逻辑为：
@RequestMapping(value = "/exportSelected", method = RequestMethod.POST) public @ResponseBody String exportSelectedUser(@RequestParam String ids, HttpSession session, HttpServletResponse response) { List<Object[]> dataset = dayRecruitService.exportByid(ids); String[] headers = new String[]{"日期","所属公司","招聘企业","面试人数","入职人数","入职率(%)","备注"}; if(dataset == null || dataset.size() < 1){ return "没有查找到相应的数据,请刷新数据后重试"; } response.setContentType("application/vnd.ms-excel");//;charset=utf-8 response.setHeader("Content-Disposition", "attachment;filename=dayRecruit.xls"); response.setHeader("Pragma","No-cache"); response.setHeader ( "Cache-Control", "no-store"); try { OutputStream sos = response.getOutputStream(); ExportExcelsUtil.exportExcel(headers, dataset, sos);//如果不需要额外数据exportExcel(headers,dataset, sos) // ExportExcelsUtil.exportExcel(2,1,2,"用户信息",headers,dataset, sos);//如果不需要额外数据exportExcel(headers,dataset, sos) response.flushBuffer(); } catch (IOException e) { e.printStackTrace(); } return "成功导出"+dataset.size()+"条用户数据。"; }
DayRecruitService的逻辑：
public List<Object[]> exportByid(String ids) { List<Object[]> objList = new ArrayList<Object[]>(); List<DayRecruit> dayrec_list=dayRecruitDAO.findById(ids); if(dayrec_list!=null&&dayrec_list.size()>0){ for(int i=0;i<dayrec_list.size();i++){//循环遍历查询到的数据结果列表 将每一条数据插入到对应行单元格 Object[] obj=new Object[7]; DayRecruit rec=new DayRecruit(); rec=dayrec_list.get(i); if(rec!=null){ obj[0]=rec.getDate(); obj[1]=rec.getCustomer().getOrganization().getOrganizationName(); obj[2]=rec.getCustomer().getEnterpriseName(); obj[3]=(rec.getViewerNu())==null?"":rec.getViewerNu(); obj[4]=(rec.getEntryNu()==null)?"":rec.getEntryNu(); if(rec.getViewerNu()!=null&&rec.getEntryNu()!=null) obj[5]=(Math.round(Double.parseDouble(rec.getEntryNu())/Double.parseDouble(rec.getViewerNu())*100))/1.0+"%"; else obj[5]=""; obj[6]=(rec.getRemarks()==null)?"":rec.getRemarks(); objList.add(obj); } } } return objList;//返回了对应的一行一行数据 }
DayRecruitDAO后台逻辑为：
public List<DayRecruit> findById(String ids) { // TODO Auto-generated method stub log.debug("get DayRecruit instance with id"+ids); try { List<DayRecruit> list = new ArrayList(); List arrList = new ArrayList(); if(ids == null || ids.trim().equals("")) {return list;} StringBuffer hql = new StringBuffer( "from DayRecruit where id in(?"); String[] id = ids.split(","); arrList.add(id[0]); for(int i=0;i<id.length;i++){ hql.append(",?"); arrList.add(id[i]); } hql.append(")"); list = (List<DayRecruit>) this.getListByHQL(-1, -1, hql.toString(), this.toStringArray(arrList)); return list; } catch (RuntimeException re) { // TODO: handle exception log.debug("get failed",re); throw re; } }
其中涉及到的hql语句模板是
public List<?> getListByHQL(int nStartRow, int nRowSize, String strHQL, String... strParams){ //log.debug("HQL：" + strHQL + "\n Params：" + ArrayUtils.toString(strParams)); Query query=getSession().createQuery(strHQL); for (int j = 0,i=0; j < strParams.length; j++) { if(strParams[j]!=null) { if(!strParams[j].equals("")){ query.setString(i, strParams[j]); i++; } } } if (nRowSize > 0 && nStartRow > -1) { query.setFirstResult((nStartRow-1)*nRowSize); query.setMaxResults(nRowSize); } //log.debug("BEGIN：" + DateUtils.getStrOfDateMinute()); List<?> objList = query.list(); //log.debug("END：" + DateUtils.getStrOfDateMinute()); return objList; }
此处还涉及到了工具类 代码为：
import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.util.Collection; import java.util.HashMap; import java.util.Iterator; import org.apache.commons.collections.map.ListOrderedMap; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFPicture; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; public class ExportExcelsUtil { /** * * @param dataset 数据 * @param out 流 */ public static <T> void exportExcel(Collection<T> dataset, OutputStream out) { exportExcel(0,0,0,"sheet1", null, dataset, out, "yyyy-MM-dd"); } /** * * @param headers 标题 * @param dataset 数据 * @param out */ public static <T> void exportExcel(String[] headers, Collection<T> dataset,OutputStream out) { exportExcel(0,0,0,"sheet1", headers, dataset, out, "yyyy-MM-dd"); } /** * * @param headers 标题 * @param dataset 数据 * @param out * @param pattern 日期格式 */ public static <T> void exportExcel(String[] headers, Collection<T> dataset,OutputStream out, String pattern) { exportExcel(0,0,0,"sheet1", headers, dataset, out, pattern); } /** * 导出数据 * @param colSplit 需要冻结的列数目，如果没有传0 * @param rowSplit 需要冻结的行数目，如果没有传0 * @param dataBeginIndex 数据从第几个字段开始导出，每一条的记录，如果没有传0 * @param sheetName 表格名字，sheet1的名称 * @param headers 标题 * @param dataset 数据 * @param out 输出流 */ public static <T> void exportExcel(int colSplit,int rowSplit,int dataBeginIndex,String sheetName,String[] headers, Collection<T> dataset,OutputStream out){ exportExcel(colSplit,rowSplit,dataBeginIndex,sheetName, headers, dataset, out, null); } /** * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 * @param <T> * * @param title * 表格标题名 * @param headers * 表格属性列名数组 * @param dataset * 1.需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) * 2.或者Map,key是string类型，value属性 * @param out * 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中 * @param pattern * 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"，目前不需要 */ @SuppressWarnings("unchecked") public static <T> void exportExcel(int colSplit,int rowSplit,int dataBeginIndex,String title, String[] headers,Collection<T> dataset, OutputStream out, String pattern) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); String sheetName = (title == null || title.equals("")) ? "sheet1" : title; // 生成一个表格 HSSFSheet sheet = workbook.createSheet(sheetName); try{ if(colSplit != 0 || rowSplit != 0){ sheet.createFreezePane( colSplit, rowSplit, colSplit, rowSplit );//冻结首行(0,1,0,1)；2,1 } // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); style.setWrapText(true);//设置自动换行 // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); style2.setWrapText(true);//设置自动换行 // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 //HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 //comment.setString(new HSSFRichTextString("可以在POI中添加注释！")); // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容. //comment.setAuthor("leno"); // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } // 遍历集合数据，产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); if(t instanceof Class){ // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值 Field[] fields = t.getClass().getDeclaredFields(); for (short i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style2); Field field = fields[i]; String fieldName = field.getName(); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); Class tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName, new Class[] {}); Object value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 String textValue = null; if (value instanceof byte[]) { // 有图片时，设置行高为60px; row.setHeightInPoints(600); // 设置图片所在列宽度为80px,注意这里单位的一个换算 sheet.setColumnWidth(i, (short) (35.7 * 400)); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) value; HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 600, i, index, (short) (i+1), index+1); anchor.setAnchorType(2); HSSFPicture pic = patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); pic.resize();//这句话一定不要，这是用图片原始大小来显示 } else { // 其它数据类型都当作字符串简单处理 textValue = value.toString(); } if (textValue != null){ HSSFRichTextString richString = new HSSFRichTextString(textValue); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } }else if(t instanceof ListOrderedMap || t instanceof HashMap){ //HashMap<String,String> map = null; ListOrderedMap map = (ListOrderedMap) t; Iterator<String> keys = map.keySet().iterator(); short i = 0; while(keys.hasNext()){ HSSFCell cell = row.createCell(i); cell.setCellStyle(style2); String keyname = keys.next(); //此处根据keyname生成标题 Object keyValue = map.get(keyname); String textValue = null; if(keyValue instanceof byte[]){ // 有图片时，设置行高为60px; row.setHeightInPoints(300); // 设置图片所在列宽度为80px,注意这里单位的一个换算 sheet.setColumnWidth(i, (short) (35.7 * 200)); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) keyValue; HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, i, index, (short) (i+1), index+1); anchor.setAnchorType(2); HSSFPicture pic = patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); //pic.resize();//这句话一定不要，这是用图片原始大小来显示 }else{ textValue = keyValue.toString(); } if (textValue != null){ HSSFRichTextString richString = new HSSFRichTextString(textValue); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } i++; }//end while(keys.hasNext()) }else if(t instanceof Object[]){ Object[] object = (Object[])t; //忽略前两个id字段，之后可以作为参数传入 for(int len = dataBeginIndex;len<object.length;len++){ String textValue = ""; if(object[len] != null){ textValue = object[len].toString(); } HSSFCell cell = row.createCell(len-dataBeginIndex); cell.setCellStyle(style2); HSSFRichTextString richString = new HSSFRichTextString(textValue); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } } workbook.write(out); out.flush(); out.close(); }catch (SecurityException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (NoSuchMethodException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IllegalArgumentException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IllegalAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (InvocationTargetException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { // 清理资源 } } }
