var MongoClient = require(‘mongodb’).MongoClient; var DB_CONN_STR = ‘mongodb://demo:demo123@192.168.2.2:27017/traincenter’; //数据库连接字符串，带密码 var xlsx = require(‘node-xlsx’); var fs = require(‘fs’);

// 查询数据函数
var selectData = function(db, callback) {
    var collection = db.collection('customer_cash_log');                         //哪个表
    collection.find().limit(200000).skip(0).toArray(function(err, result) {  //读取20万条数据
 		if (err) {
		 	 console.log('出错：' + err);
		 	 return;
	    }
	    callback(result);
    });
}

//格式化时间函数
var formatDate = function(date, style) {
	var y = date.getFullYear();
	var M = "0" + (date.getMonth() + 1);
	M = M.substring(M.length - 2);
	var d = "0" + date.getDate();
	d = d.substring(d.length - 2);
	var h = "0" + date.getHours();
	h = h.substring(h.length - 2);
	var m = "0" + date.getMinutes();
	m = m.substring(m.length - 2);
	 var s = "0" + date.getSeconds();
	s = s.substring(s.length - 2);
    return style.replace('yyyy', y).replace('MM', M).replace('dd', d).replace('hh', h).replace('mm', m).replace('ss', s);
}

// 读取并写入excel文件
MongoClient.connect(DB_CONN_STR, function(err, db) {
	console.log('连接成功');
	selectData(db, function(result) {
		var data_content = [
			['期初余额', '收入', '支出', '期末余额', '交易单号', '交易类型', '交易时间']
		];  //JSON数组，第一行是Excel表头
		for (var i = 0; i < result.length; i++) {
			var arry = [result[i].ccl_before_money, result[i].ccl_income_money, result[i].ccl_expense_money,
				result[i].ccl_end_money, '"' + result[i].ccl_trade_id + '"', result[i].ccl_trade_type_name,
				formatDate(result[i].create_time, 'yyyy-MM-dd hh:mm:ss')
			];
			data_content.push(arry); 
			// 将读取的所需列加入到JSON数组，其中的ccl_trade_id（交易单号）的数据类型是长整型，不知道怎么转换，索性就加了引号，成字符串了
   		 };
	
	  	db.close();  // 关闭数据库连接
	  
		var file = xlsx.build([{
			name: 'mySheetName',
			data: data_content
		}]);   //构建xlsx对象
		fs.writeFileSync('test1.xlsx', file, 'binary'); // 写入
		console.log('写入完成');
	});
});