[nodejs]excel表格导出

「爱情、让人受尽委屈。」 2022-06-03 04:08 421阅读 0赞

## 要达到的效果： ##

![excel导出][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

[excel]: /images/20220603/2cb9bf9eff1d493db438e183be9b90e5.png