项目笔记：导出Excel功能

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;
}