Nodejs获取网络数据导出Excel表格实例讲解

Nodejs的生成Excel表格的模块有很多，我们先来看一下node-xlsx

首先，先安装Excel的模块:

npm install node-xlsx

然后，在代码中引入模块：

var xlsx = require('node-xlsx');

最后，获取数据并写入Excel：

var fs = require('fs');
var xlsx = require('node-xlsx');
var ajax = require('./ajax.js');
start();
function start() {
    ajax.ajax({
        url: "http://yuntuapi.amap.com/datamanage/data/list",
        type: "GET",
        data: {
            tableid: "XXX",//53eacbe4e4b0693fbf5fd13b
            key: "XXX"
        },
        success: function (data) {
            var myDatas = [];
            var datas = (JSON.parse(data)).datas;
            var count = 0;
            for (var index in datas) {
                var account = datas[index];
                var colum = [];
                var names;
                if (index == 0) {
                    names = [];
                }
                for (var index2 in account) {
                    if (index == 0)
                        names.push(index2);
                    var value = account[index2];
                    if (value == null) {
                        value = "";
                    }
                    colum.push(value);
//                    console.log(account);
                }
                if (index == 0) {
                    myDatas.push(names);
                }
                myDatas.push(colum);
                if (index == datas.length - 1) {
                    writeXls(myDatas);
                }
            }
            console.log(myDatas.length);
        }
    });
}
function writeXls(datas) {
    var buffer = xlsx.build({worksheets: [
        {"name": "Group", "data": datas}
    ]});
    fs.writeFileSync("Group.csv", buffer, 'binary');
}
function parseXls() {
    var obj = xlsx.parse('myFile.xlsx');
    console.log(obj);
}

Ajax 部分的代码：

var https = require("https");
var http = require("http");
var Url = require("url");
var querystring = require('querystring');
// 默认值
var defaultSetting = {
    // 如果返回false可以取消本次请求
    beforeSend: function (req) {
    },
    complete: function (req) {
    },
    data: '', // Object, String
    dataType: 'JSON',
    error: function () {
    },
    headers: {}, // {k:v, ...}
    statusCode: {},
    success: function (data) {
    },
    timeout: 10,
    type: 'GET', // GET, POST
    url: "www.baidu.com"
};
/**
 *
 */
function ajax(settings) {
    // ajaxlbs.js(settings)
    if (typeof settings === "object") {
        // 处理默认值继承
        // todo ...
        for (key in defaultSetting) {
            if (settings[key] == null) {
                settings[key] = defaultSetting[key];
            }
        }
    }
    var params = Url.parse(settings.url, true);
    // params 解析出来的参数如下
    // {
    // "protocol":"http:",
    // "slashes":true,
    // "host":"localhost:3000",
    // "port":"3000",
    // "hostname":"localhost",
    // "href":"http://localhost:3000/?d=1",
    // "search":"?d=1",
    // "query":{"d":"1"},
    // "pathname":"/",
    // "path":"/?d=1"
    // }
    var options = {
        host: params.hostname,
        port: params.port || 80,
        path: params.path,
        method: settings.type
    };
    if (settings.data != null) {
        options.path += "?"
        for (var key in settings.data) {
            options.path = options.path + "&" + key + "=" + settings.data[key];
        }
        console.log(options.path);
    }
    var httpUnity = http;
    if (params.protocol == "https:") {
        options.port = 443;
        var httpUnity = https;
    }
    var req = httpUnity.request(options,function (res) {
        var data = '';
        res.on('data',function (chunk) {
            data += chunk;
        }).on('end', function () {
                if (settings.dataType === "json") {
                    try {
                        data = JSON.parse(data);
                    } catch (e) {
                        data = null;
                    }
                }
                settings.success(data);
                settings.complete(req);
            });
    }).on('error', function (e) {
            settings.error(e);
        });
//    if (typeof settings.beforeSend === "function") {
//        if (!settings.beforeSend(req)) {
//            settings.complete(req);
//            req.end();
//            return false;
//        }
//    }
    if (settings.type === "POST") {
        var dataStr = querystring.stringify(settings.data);
        req.setHeader("Content-Length", dataStr.length);
        req.write(dataStr);
    }
    req.setTimeout(settings.timeout);
    req.end();
}
exports.ajax = ajax;

生成的Excel内容：

node.js用excel-export插件导出excel表格实例

node.js只要安装好excel-export插件之后就可以很方便的将所需要的数据导出为excel表格。

1,在项目目录下安装excel-export插件

npm install excel-export

2，页面

合同导出
js：$("#exportExcel").click(function(){
console.info("exportExcel");
var id = $("#contractID").val();
console.info("id:"+id);
var url =  "/api/contracts/exportExcel/" + id;
console.info(url);
window.location = url;//这里不能使用get方法跳转，否则下载不成功

});

3，router.js
var nodeExcel = require('excel-export')；//关联excel-export模块
var contract = require('../app/controller/contract');
app.get('/api/contracts/exportExcel/:id',contract.exportExcel);//跳转到后台

4，后台

exports.exportExcel = function(req, res) {
 /**静态数据
* var conf ={};

    conf.cols = [
        {caption:'string', type:'string'},
        {caption:'date', type:'date'},
        {caption:'bool', type:'bool'},
        {caption:'number', type:'number'}               
    ];
    conf.rows = [
        ['pi', (new Date(2013, 4, 1)).getJulian(), true, 3.14],
        ["e", (new Date(2012, 4, 1)).getJulian(), false, 2.7182]
    ];
    var result = nodeExcel.execute(conf);
    res.setHeader('Content-Type', 'application/vnd.openxmlformats');
    res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");
    res.end(result, 'binary');


**/

console.log("req.params.id:"+req.params.id);
var queryId = req.params['id']+'';
var contract = new Contract();
var conf = {};
conf.cols = [
   {caption:'采购编号', type:'string'},
   {caption:'合同名称', type:'string'},
   {caption:'甲方', type:'string'},
   {caption:'甲方部门', type:'string'},
   {caption:'乙方', type:'string'},
   {caption:'乙方部门', type:'string'},
   {caption:'签订日期', type:'date'},
   {caption:'中标日期', type:'date'},
   {caption:'结束日期', type:'date'},
   {caption:'销售负责人', type:'string'},
   {caption:'商务负责人', type:'string'},
   {caption:'业绩归属部门', type:'string'},
   {caption:'金额', type:'number'},
   {caption:'状态', type:'string'}

];
var getId = {
_id: queryId
};
contract.checkIdData(getId,function(data){
  console.log("lijuanxia");
  console.log("data.lentht"+data.length);
         
          var m_data = [];
          var arry = [data[0].myId, data[0].name, data[0].partyA, data[0].partyADept, data[0].partyB, data[0].partyBDept, data[0].signDate, data[0].beginDate, data[0].endDate, "销售负责人", "商务负责人", "业绩归属部门", data[0].amount, data[0].state ];
          m_data[0] = arry;
          conf.rows = m_data;
          
          var result = nodeExcel.execute(conf);
         
          res.setHeader('Content-Type', 'application/vnd.openxmlformats');
         
          res.setHeader("Content-Disposition", "attachment; filename=" +data[0].name+ ".xlsx");
      
          res.end(result, 'binary');
});
}