1、前台这块：
var ids=""; $.post("${basePath}/assets/unRegDeviceAction_getDeviceIds.do",function(res){ ids=res; }) //创建Excel表
function btn_createExcel(){ ids=ids.replace("[","").replace("]",""); var data=ids.split(","); var newAction ='/assets/unRegDeviceAction_createExcel.do?ids='+data; createFun( newAction ); } function createFun( newAction ){ if($.checkSession()){ var $form = $("#excelForm"); var action = ""; try { action = $form.attr("action"); $form.attr("action","${basePath}"+newAction).submit(); }finally{ $form.attr("action",action); } } }
/** * 获取当前查询结果的设备的所有id * @return */
public void getDeviceIds(){ List<String> ids= new ArrayList<String>(); if(queryBean == null){ queryBean = new UnRegDeviceQueryBean(); } //-------数据过滤ST
log.info("数据过滤-ST"); @SuppressWarnings("unchecked") Map<String, List<String>> mgrOrgMap = (Map<String, List<String>>) getSession().getAttribute(OrganizationUtil.ORG_MGR); if(!OrganizationUtil.isNullMgrOrg(mgrOrgMap)){ Organization mgrOrg = new Organization(); mgrOrg.setMgrOrg(mgrOrgMap); queryBean.setOrganization(mgrOrg); } List<UnRegDevice> list= unRegDeviceService.query(queryBean);//查找UnRegDevice实体集合
for(UnRegDevice unRegDevice:list){ ids.add(unRegDevice.getId()); } print(ids.toString()); }
2、后台这块：
/*导出EXCEL*/ @SuppressWarnings("unused") public void createExcel() { log.info("导出Excel功能已经启动-BEGIN"); JxlUtil jsl = new JxlUtil(); List<UnRegDevice> dataList =new ArrayList<UnRegDevice>(); List<UnRegDeviceExport> list = new ArrayList<UnRegDeviceExport>(); List<Organization> organizations = null; // 构建路径
String downLoadPath = "/WEB-INF/download/asset/"; String rootPath = getServletContext().getRealPath(downLoadPath); String fileName = ""; File file=new File(rootPath); try { if(!(file.exists()||file.isDirectory())){ file.mkdirs(); } if (null != getRequest().getParameter("ids")) { String ids[] = getRequest().getParameter("ids").split(","); for (int i = 0; i < ids.length; i++) { unRegDevice = unRegDeviceService.queryById(ids[i].replaceAll(" +","")); dataList.add(unRegDevice); } } if(dataList!=null){ for (UnRegDevice unRegDevice : dataList) { UnRegDeviceExport unRegDeviceExport= new UnRegDeviceExport(); if(null!=unRegDevice.getId()){ unRegDeviceExport.setId(unRegDevice.getId()); } if(null!=unRegDevice.getIp()){ unRegDeviceExport.setIp(unRegDevice.getIp()); } if(null!=unRegDevice.getMac()){ unRegDeviceExport.setMac(unRegDevice.getMac()); } if(null!=unRegDevice.getOrganization()){ log.info("获取设备全路径组织机构-ST"); Organization organization=unRegDevice.getOrganization(); String Aname =organization.getName(); String name= getAname(organization, Aname); log.info("获取设备全路径组织机构-END--name："+name); unRegDeviceExport.setOrganizationName(name); } if(null!=unRegDevice.getHostName()){ unRegDeviceExport.setHostName(unRegDevice.getHostName()); } if(null!=unRegDevice.getGroupName()){ unRegDeviceExport.setGroupName(unRegDevice.getGroupName()); } if(null!=unRegDevice.getProtectState()){ unRegDeviceExport.setProtectState(unRegDevice.getProtectState()); } if(null!=unRegDevice.getUpdateTime()){ SimpleDateFormat dateFormater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String time=dateFormater.format(unRegDevice.getUpdateTime()); unRegDeviceExport.setUpdateTime(time); } if(null!=unRegDevice.getIsOpened()){ unRegDeviceExport.setIsOpened(unRegDevice.getIsOpened()); } if(null!=unRegDevice.getIsFireWall()){ unRegDeviceExport.setIsFireWall(unRegDevice.getIsFireWall()); } list.add(unRegDeviceExport); } } log.info("数据过滤--END"); String interBase = "sys.column.name.unRegDevice"; String inter_value_key = "#isOpened#isFireWall#protectState#"; String[] inter_value_ary = { "isOpened.0", "isOpened.1","isFireWall.0", "isFireWall.1","protectState.0", "protectState.1"}; fileName = jsl.getInter(interBase.replace("column", "table")) + new Date().getTime(); String targetfile = rootPath + System.getProperty("file.separator") + fileName + ".xls"; // 创建可写入的Excel工作薄
WritableWorkbook wwb; wwb = Workbook.createWorkbook(new File(targetfile)); // 创建Excel工作表
WritableSheet ws = wwb.createSheet("未注册设备", 1); // 获取需要内容国际化的字段
jsl.creatCemsExcel(ws, list, interBase, inter_value_key,inter_value_ary); // 写入Exel工作表
wwb.write(); // 关闭Excel工作薄对象
wwb.close(); getResponse().setContentType(getServletContext().getMimeType(fileName)); getResponse().setHeader("Content-Disposition", "attachment;fileName="+new String(fileName.getBytes("gb2312"), "ISO8859-1")+".xls"); String fullFileName = getServletContext().getRealPath(downLoadPath + fileName+ ".xls"); InputStream in = new FileInputStream(fullFileName); OutputStream out = getResponse().getOutputStream(); int b; while((b=in.read())!= -1){ out.write(b); } in.close(); out.close(); /* ServletActionContext.getRequest().setAttribute("downLoadPath", downLoadPath); ServletActionContext.getRequest().setAttribute("fileName", fileName + ".xls");*/
this.msg = RESULT_SUCCESS; log.info("导出EXCEL提示信息为:"+this.msg); } catch (Exception e) { log.error("export excel error:" + e.getMessage()); log.error("导出EXCEL失败"); } log.info("导出Excel功能已经启动-END"); log.info("导出Excel功能已经启动-END"); /*return "downLoadUI";*/ } /*递归获取全路径组织机构方法*/
public String getAname(Organization organization, String oName){ log.info("递归获取全路径组织机构方法-ST"); if( organization.getParent()!=null){ String tName=""; tName=organization.getParent().getName()+"/"+oName; log.info("递归获取全路径组织机构方法-END"); return getAname(organization.getParent(), tName); }else{ log.info("递归获取全路径组织机构方法-END"); return oName; } }
public void creatCemsExcel( WritableSheet ws, List<?> beanList, String internationalBase, String value_str, String[] inter_value_ary ) throws Exception { // -----------获取资源阶段----------------------------------------------------------------
String[] ary = internationalBase.split("\\."); String beanName = ary[ary.length - 1]; // 获得bean值(systable中保存的key)
beanName = beanName.substring(0, 1).toUpperCase()+ beanName.substring(1, beanName.length()); // 获得要展现的字段名
ArrayList<String> key_list = getBeanKeys(beanName); // 获取key的国际化用于国际化head
Map<String, String> key_inter = getInter(internationalBase, key_list); // 获得需要国际化内容的Map
Map<String, String> interValueMap = getInter(internationalBase,inter_value_ary);// {ret.1=安装, ret.0=未安装} // 标题
String title = getInter(internationalBase.replace("column", "table")); // -----------文件创建阶段----------------------------------------------------------------
try { JSONArray json=getColumnLength(beanList, key_list); // 写入表头 合并 （列 ->行 列->行）
ws.mergeCells(0, 0, key_list.size() - 1, 0); ws.addCell(new Label(0, 0, title, wcf_title)); int startHead = 1; // 写入head
for (int i = 0; i < key_list.size(); i++) { ws.addCell(new Label(i, startHead, key_inter.get(key_list.get(i)), wcf_head)); for (int j = 0; j < json.size(); j++) { JSONObject jsonObj=json.getJSONObject(i); if(jsonObj.get(key_list.get(i))!=null){ ws.setColumnView(i, Integer.parseInt(jsonObj.getString(key_list.get(i)))+5); } } } // 写入data
for (int i = 0, size = beanList.size(); i < size; i++) {// 循环行
for (int colNum = 0; colNum < key_list.size(); colNum++) {// 循环列
HashMap<String, String> queryBeanMap = BeanReflect.beanToMap(beanList.get(i)); if (value_str == null || value_str.equals("")) {// 没有内容需要国际化
ws.addCell(new Label(colNum, startHead + i + 1,queryBeanMap.get(key_list.get(colNum)),wcf_centre)); } else { if (value_str.indexOf(("#" + key_list.get(colNum) + "#")) > -1) {// 如果输入内容需要国际化 // 需要国际化
String interValue = interValueMap.get(key_list.get(colNum)+ "."+ queryBeanMap.get(key_list.get(colNum))); ws.addCell(new Label(colNum, startHead + i + 1,interValue, wcf_centre)); } else { ws.addCell(new Label(colNum, startHead + i + 1,queryBeanMap.get(key_list.get(colNum)),wcf_centre)); } } } } // 写入结尾
ManagerSession managerSession = ManagerSessionUtils.getManagerSession(ServletActionContext.getRequest().getSession()); String niceName=getInter("sys.column.name.baseLog.creater")+" : "+managerSession.getNiceName(); SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd kk:mm:ss "); String time=getInter("sys.column.name.baseLog.createTime")+" : "+sdf.format(new Date()); ws.addCell(new Label(key_list.size()-1, beanList.size()+2, niceName, wcf_feet)); ws.addCell(new Label(key_list.size()-1, beanList.size()+3, time, wcf_feet)); /*if("DeviceIllegalConnectLog".equals(beanName)){ String msgName="违规外联日志导入必填项:{使用人 ,所属机构 ,MAC地址 ,设备名称, IP地址 ,发生时间, 存档时间,设备注册人账号，使用人账号}:注，***作为模板导入的时候请删除该提示行***"; ws.addCell(new Label(key_list.size()-16, beanList.size()+4, msgName, wcf_feet)); }*/ } catch (Exception e) { e.printStackTrace(); } }
// systable.xml中的展现key
private static ArrayList<String> getBeanKeys(String beanName) { HttpServletRequest request = ServletActionContext.getRequest(); Map<?, ?> keyMap = (Map<?, ?>) request.getSession(false) .getServletContext().getAttribute("vrvSysTablesAttr"); ArrayList<String> list = new ArrayList<String>(); SysTable sysTable = (SysTable) keyMap.get(beanName); Set<SysColumn> set = sysTable.getColumns(); Iterator<SysColumn> it = set.iterator(); while (it.hasNext()) { SysColumn column = it.next(); // 如果显示
if (column.getIsExport().toString().equals("Y")) { list.add(column.getKey()); } } return list; }