[nodejs]excel表格导出

要达到的效果：

说明：点击导出按钮，实现表格的excel导出。

使用的插件：

nodeExcel = require(‘excel-export’)

代码：

sql.saveTidExcelFile = function(req, res){
  var id = req.params['id'];
  var filename = "The_"+id+"_group_randomOut";
  if(id.split('.').length>1){
    var trid = id;
    var selSQL = "select *from csp_random_allocated_results where trid='"+trid+"'order by gid, CAST(cindex as SIGNED) asc"
    var nameSql ="select gid, gname from csp_group where gid in (select gid from csp_random_allocated_results where trid = '"+trid+"')";
    csp.db.query(nameSql, function(err, nameData){
      if(!err){
        var name = nameData;
        csp.db.query(selSQL,function(err,data){
          if(!err){
            var tidRandomData = data;
            var cols = []
            var rows = []
            var titles = [
                "序号","证券代码","证券名称","心愿数量","实际分券数量","所属小组"
            ]

              //规定列数和类别
                for(var i=0; i<titles.length; i++){
                    var item = {}
                    item.caption = titles[i];
                    item.type = 'string'
                    cols.push(item)
                }
                console.log(JSON.stringify(tidRandomData))
                for(var a=0;a<tidRandomData.length;a++){
                  for(var b=0;b<name.length;b++){
                    if(tidRandomData[a].gid == name[b].gid){
                      tidRandomData[a].gid = name[b].gname+'('+name[b].gid+')';
                    }
                  }
                }
                console.log(JSON.stringify(tidRandomData))
                for(var i=0; i<tidRandomData.length; i++){
                    var item = []
                    item.push((tidRandomData[i].cindex).toString())
                    item.push(tidRandomData[i].cid)
                    item.push(tidRandomData[i].cname)
                    item.push(tidRandomData[i].amount)
                    item.push((tidRandomData[i].post_allocated_amount).toString())
                    item.push((tidRandomData[i].gid))
                    rows.push(item)
                }

               var conf = { };
               conf.cols = cols
               conf.rows = rows

               var result = nodeExcel.execute(conf);

               if(typeof filename === 'undefined'){
                   filename = new Date();
               }
               // console.log("uyuyuyu",filename)
               res.setHeader('Content-Type', 'application/vnd.openxmlformats');
               res.setHeader("Content-Disposition", "attachment; filename=" + filename +  ".xlsx");
               res.end(result, 'binary');
          }

        })
      }
    })
  }
  else{
    var gid = id;
    var selSQL="select *from csp_random_allocated_results where gid="+gid+" order by cindex*1"
    var nameSql = "select gid,gname from csp_group where gid = '"+gid+"'"
    csp.db.query(nameSql,function(err,nameData){
      var name =nameData;
      if(!err){
        csp.db.query(selSQL, function(err, data){
          if(!err){
            var tidRandomData = data;
            var cols = []
            var rows = []
            var titles = [
                "序号","证券代码","证券名称","心愿数量","实际分券数量","所属小组"
            ]
            for(var a=0;a<tidRandomData.length;a++){
              for(var b=0;b<name.length;b++){
                if(tidRandomData[a].gid == name[b].gid){
                  tidRandomData[a].gid = name[b].gname+'('+name[b].gid+')';
                }
              }
            }
              //规定列数和类别
                for(var i=0; i<titles.length; i++){
                    var item = {}
                    item.caption = titles[i];
                    item.type = 'string'
                    cols.push(item)
                }
                for(var i=0; i<tidRandomData.length; i++){
                    var item = []
                    item.push((tidRandomData[i].cindex).toString())
                    item.push(tidRandomData[i].cid)
                    item.push(tidRandomData[i].cname)
                    item.push(tidRandomData[i].amount)
                    item.push((tidRandomData[i].post_allocated_amount).toString())
                    item.push((tidRandomData[i].gid))
                    rows.push(item)
                }

               var conf = { };
               conf.cols = cols
               conf.rows = rows

               var result = nodeExcel.execute(conf);

               if(typeof filename === 'undefined'){
                   filename = new Date();
               }
               // console.log("uyuyuyu",filename)
               res.setHeader('Content-Type', 'application/vnd.openxmlformats');
               res.setHeader("Content-Disposition", "attachment; filename=" + filename +  ".xlsx");
               res.end(result, 'binary');
          }


        })
      }



    })
  }

前端url控制

window.location = ‘/r/excel’+id