}, function (err) {
if (err) console.log(err);
sheet.data = sheet.data.concat(rows);
var conBuffer = ew.build([sheet]);
fs.writeFileSync(filePath, conBuffer);
rows.unshift(columns);
// console.log(rows.length);
// rows = rows);
var dailyBuffer = ew.build([{name : 'result', data : rows}]);
fs.writeFileSync(dailyFilePath, dailyBuffer);
console.log('Everything was done');
});
Cache.prototype.writeXlsx = function writeXlsx(queryResult) {
const self = this
// loop through rows and build out an array of arrays
const resultArray = []
resultArray.push(queryResult.fields)
for (let i = 0; i < queryResult.rows.length; i++) {
const row = []
for (let c = 0; c < queryResult.fields.length; c++) {
const fieldName = queryResult.fields[c]
row.push(queryResult.rows[i][fieldName])
}
resultArray.push(row)
}
const xlsxBuffer = xlsx.build([{ name: 'query-results', data: resultArray }])
return new Promise((resolve, reject) => {
fs.writeFile(self.xlsxFilePath(), xlsxBuffer, function(err) {
// if there's an error log it but otherwise continue on
// we can still send results even if download file failed to create
if (err) {
console.log(err)
}
return resolve()
})
})
}
models[schema.name].find().exec().then(function (modelResponse) {
var keys = _.keys(models[schema.name].schema.tree)
var tab = []
tab.push(keys)
_.forEach(modelResponse, function (n, k) {
var tempKey = []
_.forEach(keys, function (key) {
tempKey.push(n[key])
})
tab.push(tempKey)
})
var buffer = xlsx.build([{
name: schema.name,
data: tab
}])
inquirer.prompt(pathXlsxQuestion).then(function (pathAnwser) {
if (!fs.existsSync(path.parse(pathAnwser.path).dir)) {
fs.mkdirSync(path.parse(pathAnwser.path).dir)
}
fs.writeFile(pathAnwser.path, buffer, function (err) {
if (err) return console.log(err)
console.log(chalk.green('Created \n Path:' + pathAnwser.path))
process.exit()
})
})
}) // END OF MODEL FIND()
dbappuser.find({}, function(err, docs){
if (err) {
return callback({ret: 2}); // RETURN: 查询出错
}
var th = new Array();
var data = new Array();
th = ["编号", "昵称", "电话", "邮箱", "第三方登录", "性别", "是否封停", "是否为员工(0表示非员工)"];
data.push(th);
for (var index=0; index<docs.length; index++){
var td = new Array();
td.push({value: docs[index]._id.toString(), formatCode: "General"});
td.push({value: docs[index].nickname , formatCode: "General"});
td.push({value: docs[index].tel , formatCode: "General"});
td.push({value: docs[index].email , formatCode: "General"});
td.push({value: docs[index].thirdpath , formatCode: "General"});
td.push({value: docs[index].sex , formatCode: "General"});
td.push({value: docs[index].disable , formatCode: "General"});
td.push({value: docs[index].isworker , formatCode: "General"});
data.push(td);
}
var buffer = xlsx.build({worksheets: [
{name: "userData", data: data}
]});
var appUserPath = config.appPath() + "/static/appuser/appuser.xlsx";
fs.writeFileSync(appUserPath, buffer);
return callback({ret: 1, appUserPath: appUserPath}); // RETURN: 处理成功
});
/**
* Write data to excel file.
*
* @param {Array<Object>} shoes
* @param {String} filename
*/
function writeFile(shoes, filename) {
var data = [
['id', 'color', 'size', 'price']
]
shoes.forEach(shoe => {
data.push([
shoe.id,
shoe.color,
shoe.size,
shoe.price
])
})
var buffer = xlsx.build([{
name: 'Shoes',
data: data
}])
fs.writeFile(filename, buffer, err => {
if (err) {
console.log('write file fail')
console.trace(err)
} else {
console.log('write file ok')
}
})
}
Limitation.getUnderLimatationGifts(function (items) {
if (items) {
var schema = {
worksheets: [
{
"name" : "礼品剩余数量提醒",
"data" : [
["礼品名称", "品牌", "价格", "剩余库存数量", "警戒线"]
]
}
]
};
for (var i = 0; i < items.length; i++) {
var item = items[i];
var arr = [];
arr.push(item.name);
arr.push(item.brand);
arr.push(item.price);
arr.push(item.num);
arr.push(item.limitNum);
schema.worksheets[0].data.push(arr);
}
var buffer = xlsx.build(schema);
callback(buffer);
}
});
var savaxlsx = function(sheets, filepath, callback) {
var buffer = xlsx.build(sheets);
fs.writeFile(filepath, buffer, 'utf-8', function(err) {
callback(err);
});
}
db.query(selectSQL, function(err, rows) {
var data = [];
//表头
if (rows.length != 0) {
var keyArr = [];
for (var key in rows[0]) {
keyArr.push(key);
}
data.push(keyArr);
}
//json数组遍历
for (var i in rows) {
var arr = [];
var value = rows[i];
//json key 遍历
for (var key in value) {
arr.push(value[key]);
}
data.push(arr);
}
var buffer = xlsx.build([
{
name:'sheet1',
data:data
}
]);
//将文件内容插入新的文件中
var fileName = new Date().getTime()+ '.xlsx';
fs.writeFileSync(fileName,buffer,{'flag':'w'});
});
router.get('/export1', wrap(function* (req, res, next) {
//var obj = {"worksheets":[{"data":[["姓名","性别","年龄"],["李晓龙","男","24"]]}]};
//var file = xlsx.build(obj);
//var data = [[1,2,3],[true, false, null, 'sheetjs'],['foo','bar',new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']];
var userDatas = yield users.find().toArrayAsync();
var data = [];
var pros = [];
if (userDatas.length) {
for (var pro in userDatas[0]) {
pros.push(pro);
}
}
data.push(pros);
for (var i = 0; i < userDatas.length; i++) {
var row = [];
for (var j = 0; j < pros.length; j++) {
row.push(userDatas[i][pros[j]]);
}
data.push(row);
}
var box = xlsx1.parse('./modules/template2.xlsx');
//box[0].data = data;
//var buffer = xlsx1.build([{name: "mySheetName", data: data}]);
var buffer = xlsx1.build(box);
//fs.writeFileSync('./public/temp/user.xlsx', buffer, 'binary');
//res.send('user.xlsx');
res.setHeader('Content-Type', 'application/vnd.openxmlformats');
res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");
res.end(buffer, 'binary');
}));
}, function(err, res) {
fs.writeFileSync('book.xlsx', xlsx.build([{
name: "sheet",
data: result
}]), 'binary');
console.log('done');
});
jobDao.queryJobDetails(host,req.query.jobId,function(data){
//加载表格列配置文件
var columns = require('../configs/modules/jobDetailColumn');
var datas = [];
var total_time = 0;
var titles = [];
columns.forEach(function (obj) {
titles.push(obj.title);
});
datas.push(titles);
for (var i = 0; i < data.Tasks.length; i++) {
var row = [];
for (var j = 0; j < columns.length; j++) {
var formatter = columns[j].formatter_back ? columns[j].formatter_back : columns[j].formatter;
row.push(formatter ? formatter(data.Tasks[i][columns[j].field],data.Tasks[i]) : data.Tasks[i][columns[j].field]);
if(columns[j].field == 'singleTime'){
total_time += formatter(data.Tasks[i][columns[j].field],data.Tasks[i]);
}
}
datas.push(row);
}
//加载总机时
total_time = total_time / 3600;
total_time = total_time.toFixed(2);
var last_row = ['总机时(h)', total_time];
datas.push(last_row);
res.setHeader('Content-Type', 'application/vnd.openxmlformats');
res.setHeader("Content-Disposition", "attachment; filename=" + taskName+ ".xlsx");
var buffer = xlsx.build([{name: "mySheetName", data: datas}]);
res.end(buffer, 'binary');
});
function createNewExcel(dataFilePath) {
var xlsx = require('node-xlsx').default;
var fs = require('fs');
var path = require('path');
const distDataFilePath = path.join('upload', path.basename(dataFilePath, '.xlsx') + '韵达发中的值ERP没有的.xlsx');
const workSheetsFromBuffer = xlsx.parse(fs.readFileSync(dataFilePath));
// TODO 去重优化
var data1 = changeToSimpleArray(workSheetsFromBuffer[1]);
var data2 = changeToSimpleArray(workSheetsFromBuffer[0]);
var output = [];
// data1中的值data2没有
for(var i = 0; i < data1.length; i++) {
if (data2.indexOf(data1[i]) >= 0) {
// console.log(data1[i]);
} else {
var a = [];
a.push(data1[i]);
output.push(a);
}
}
function changeToSimpleArray(data) {
var result = [];
for(var i = data.data.length - 1; i >= 0; i--){
result.push(data.data[i][0] + '');
}
return result;
}
var buffer = xlsx.build([{name: "mySheetName", data: output}]); // Returns a buffer
fs.writeFileSync(distDataFilePath, buffer, {'flag':'w'}); // 如果文件存在，覆盖
}
exports.exportAll = async (req, res) => {
// Exporting users as XLSX .
if (!req.permissions.export) {
return errors.makeForbiddenError(res, 'You are not allowed to see statistics.');
}
const applications = await Application.findAll({ where: { event_id: req.event.id } });
const headersNames = helpers.getApplicationFields(req.event);
const headers = Object.keys(headersNames).map(field => headersNames[field]);
const resultArray = applications
.map(application => application.toJSON())
.map(application => helpers.flattenObject(application))
.map((application) => {
return Object.keys(headersNames).map(field => helpers.beautify(application[field]));
});
const resultBuffer = xlsx.build([
{
name: 'Application stats',
data: [
headers,
...resultArray
]
}
]);
res.setHeader('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
res.setHeader('Content-disposition', 'attachment; filename=stats.xlsx');
return res.send(resultBuffer);
};
}, function() {
row[1] = serviceString;
row[2] = noServiceString;
xlsxFile.cellListView.push(row);
//console.log(row);
var buffer = xlsx.build([{name: xlsxFile.name, data: xlsxFile.cellListView}]);
fs.writeFileSync(xlsxFile.name + '.xlsx', buffer, 'binary');
});
db.config.findOne({key: "allowCsvDownload"}, function (err, config) {
var preventDownload = config && config.value === "false";
if (!preventDownload) {
// loop through rows and build out an array of arrays
var resultArray = [];
resultArray.push(fields);
for (var i = 0; i < results.rows.length; i++) {
var row = [];
for (var c = 0; c < fields.length; c++) {
var fieldName = fields[c];
row.push(results.rows[i][fieldName]);
}
resultArray.push(row);
}
// build out and write xlsx file
var xlsxBuffer = xlsx.build([{name: "query-results", data: resultArray}]); // returns a buffer
var xlsxPath = path.join(app.get('dbPath'), "/cache/", cache.cacheKey + ".xlsx");
fs.writeFile(xlsxPath, xlsxBuffer, function (err) {
if (err) {
console.log(err);
}
json2csv({data: results.rows, fields: fields}, function (err, csv) {
if (err) {
console.log(err);
res.send({
success: true,
serverMs: end - start,
results: results.rows
});
} else {
var csvPath = path.join(app.get('dbPath'), "/cache/", cache.cacheKey + ".csv");
fs.writeFile(csvPath, csv, function (err) {
if (err) console.log(err);
res.send({
success: true,
serverMs: end - start,
meta: meta,
results: results.rows,
incomplete: results.incomplete,
csvUrl: '/query-results/' + cache.cacheKey + '.csv'
});
});
}
});
});
} else {
res.send({
success: true,
serverMs: end - start,
meta: meta,
results: results.rows,
incomplete: results.incomplete
});
}
});
function downloadData(name, data, res){
var buffer = xlsx.build([{name: name, data: data}]);
var filepath = temp.path({suffix: '.xlsx'});
fs.writeFile(filepath, buffer, function(err){
res.download(filepath, name + ".xlsx", function(){
fs.unlink(filepath);
});
});
}
var RegisterTextDownloadTask = function (url, texts, res_path) {
var xls_path = conf.store_base + path.sep + res_path + path.sep + format('yy-MM-dd') + path.sep;
var data = [];
for (var i = 0; i < texts.length; i++) {
data.push([url, texts[i]]);
}
var buffer = xlsx.build([{name: "scrapy_text", data: data}]);
fs.writeFile(xls_path + url.substr(-4) +'.xlsx', buffer, 'binary');
}
getRows().then(function() {
var rows = $scope.rows.d;
rows.unshift($scope.rows.f);
var buffer = xlsx.build([{name: "ListCommissionOpen", data: rows}]);
var id = helper.newUUID();
var fname = '/output/commission_open_list_'+id+'.xlsx';
fs.writeFileSync(path.normalize(__dirname + '/../../public'+fname), buffer);
res.send({
status:true,
file: fname
});
}).catch(function(e) {
ExcelWriter.prototype.build = function() {
this._buffer = xlsx.build([{name : this._sheetName, data : this._data}]);
fs.writeFile(this._file, this._buffer, function(err) {
console.error('Unable to write file ' + this._file);
});
// fs.open(this._file , 'w+', function(err, fd) {
// fs.write(fd, this._buffer, function(err, num, str) {
// if(err) {
// console.error(err);
// }
// });
// });
}
router.get('/exportExcel', function(req, res, next) {
var data = [
[1,2,3],
[true, false, null, 'sheetjs'],
['foo','bar',new Date('2014-02-19T14:30Z'), '0.3'],
['baz', null, 'qux']
];
var buffer = xlsx.build([{name: "mySheetName", data: data}]);
fs.writeFileSync('b.xlsx', buffer, 'binary');
res.send('export successfully!');
});
function(err, rs) {
if (err) {
res.type("text/plain").status(500).send("ERROR: " + err);
} else {
var out = [];
for (var i = 0; i < rs.length; i++) {
out.push([rs[i]["PurchaseOrderItemId"], rs[i]["ItemPos"], rs[i]["ProductID"], rs[i]["Amount"]]);
}
var result = excel.build([{
name: "Purchase Orders",
data: out
}]);
res.header("Content-Disposition", "attachment; filename=Excel.xlsx");
res.type("application/vnd.ms-excel").status(200).send(result);
}
});
serialize: function (req, res, next) {
debug('application/vnd.openxmlformats handler')
debug('Method: ', req.call_method)
var fields = req.fieldSelection
var header = req.fieldHeader
if (req.isDownload) {
res.attachment('PATRIC_' + req.call_collection + '.xlsx')
// res.set("content-disposition", 'attachment; filename="patric3_' + req.call_collection + '_query.xlsx"');
}
res.set('Content-Type', 'application/vnd.openxmlformats')
if (res.results && res.results.response && res.results.response.docs) {
if (!fields) {
fields = Object.keys(res.results.response.docs[0])
}
// debug("fields: ", fields);
const data = res.results.response.docs.map(function (o) {
return fields.map(function (field) {
if (typeof o[field] === 'object') {
if (o[field] instanceof Array) {
return o[field].join(';')
}
return JSON.stringify(o[field])
}
return o[field] || ''
})
// return row;
})
if (header) {
data.unshift(header)
} else {
data.unshift(fields)
}
var d = xlsx.build([{name: 'patric3_query', data: data}])
res.end(d, 'binary')
} else {
res.status(404)
// res.end();
}
}
async downloadAction() {
let data = this.post();
let codeModel = this.model('code');
let arr = data.date.split('-');
data.year = parseInt(arr[0]);
data.month = parseInt(arr[1]);
let result = await codeModel.searchCodeAllByDate(data);
let worker_name = [];
let local_name = [];
let code = [];
let entry_dt = [];
let create_dt = [];
let xlsxContent = [];
xlsxContent.push(['残值编码', '定损员', '部门', '录入时间', '生成时间']);
for(var i in result) {
let col = [];
col.push(result[i].code_code);
col.push(result[i].worker.name);
col.push(result[i].worker.local.name);
col.push(result[i].entry_dt);
col.push(result[i].create_dt);
xlsxContent.push(col);
}
var buffer = build([{name: "mySheetName", data: xlsxContent}]);
let fileName = uuid.v1();
fs.writeFileSync(`./www/static/download/${fileName}.xlsx`, buffer);
//const r = [[1, 2, 3], [true, false, null, 'sheetjs'],
// ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']];
//let xlsxContent = [];
//let xlsxTitle = ['生成数量'];
//xlsxContent.push(xlsxTitle);
//for(var i in result) {
// let col = [];
// for(var key in result[i]) {
// col.push(result[i]['count(id)']);
// }
// xlsxContent.push(col);
//}
//var buffer = build([{name: "mySheetName", data: xlsxContent}]);
//let fileName = uuid.v1();
//fs.writeFileSync(`./www/static/download/${fileName}.xlsx`, buffer);
this.json({
fileName: fileName
});
}
export: function (req, res) {
var data = [
[1,2,3],
[true, false, null, 'sheetjs'],
['foo','bar',new Date('2014-02-19'), '0.4'],
['baz', null, 'qux']
];
var fs = require('fs'),
buffer = xlsx.build([{name: "mySheetName", data: data}]),
filePath = './files/' + new Date().valueOf().toString() + Math.floor(Math.random() * 1e5) + '.xlsx';
fs.writeFileSync(filePath, buffer);
res.sendfile(filePath, function () {
fs.unlink(filePath);
});
}
function(err, results) {
if(err) return cb(err);
var sheets = [];
sheets.push({name: "20及以上等级球队表", data:results.db.level});
sheets.push({name: "A及以上品阶球员表", data:results.db.quality});
if(stash.version && stash.version == 2) { //简体IOS
sheets.push({name: "紫色及以上品质球员表", data:results.db.star});
sheets.push({name: "2次及以上钻石十连抽表", data:results.log.money});
} else {
sheets.push({name: "蓝色及以上品质球员表", data:results.db.star});
sheets.push({name: "3次及以上钻石十连抽表", data:results.log.money});
}
sheets.push({name: "5次及以上金币十连抽表", data:results.log.coin});
var buffer = xlsx.build(sheets);
res.setHeader('Content-Type', 'application/vnd.openxmlformats');
res.setHeader("Content-Disposition", "attachment; filename=" + "openServerDatas.xlsx");
res.end(buffer, 'binary');
cb(null);
});
getRows().then(function() {
var rows = $scope.rows.d; //console.log("$scope.rows.d = ", $scope.rows.d);
rows.unshift($scope.rows.f); //console.log("$scope.rows.f = ", $scope.rows.f);
try {
var buffer = xlsx.build([{name: "ListPaymentTracking", data: rows}]);
} catch (e) {
console.log('ERROR=', e);
}
var id = helper.newUUID();
var fname = '/output/payment_tracking_'+id+'.xlsx';
try {
fs.writeFileSync(path.normalize(__dirname + '/../../../public'+fname), buffer);
} catch (e) {
console.log('ERROR2=', e);
}
res.send({
status:true,
file: fname
});
}).catch(function(e) {
User.find({}, '-_id username regDate area', function (err, docs) {
if (err) {
console.log("出错了");
} else {
console.log(docs);
var userName = ['用户名'];
var userArea = ['用户地址'];
var userDate = ['用户注册时间'];
for(var i = 0 ; i<docs.length;i++)
{
userName.push(docs[i].username);
userArea.push(docs[i].area);
userDate.push(docs[i].regDate);
}
var node_xlsx = require('node-xlsx');
var obj = [{ //原来 name(sheetNames) 和 data（sheets） 是写死的 网上很多node-xlsx 都不符合规范都帖上来
'name':'我',
"data":[
["索引1","索引2","c"],
['第一','第二','第三']
]
},{
'name':'你',
"data":[
["sdf引1","ff引2","c"],
['ffdaf','fff二','dd三']
]
},{
'name':'用户',
"data":[
userName,
userArea,
userDate
]
}];
var fileBuf = node_xlsx.build(obj);
fs.writeFile('usddfser.xlsx', fileBuf, 'binary',function callback(err){
console.log(err);
});
}
});
function save(file, data) {
if (path.extname(file) === '.csv') {
var str = '';
for (var i = 0; i < data.length; i++) {
var row = '';
for (var j = 0; j < data[i].length; j++) {
row += ',"' + data[i][j] + '"';
}
row = row.slice(1);
str += row + '\n';
}
fs.writeFileSync(file, str, {
encoding: 'utf8'
});
} else {
var buffer = xlsx.build({worksheets: [
{"name": path.basename(file), "data": data}
]});
fs.writeFileSync(file, buffer);
}
}
function rowsToExcel(rows){
var data =[];
if(rows.length){
var keys=[];
for(let key in rows[0]){
keys.push(key);
}
data.push(keys);
}else{
return ;
}
rows.forEach(function(row){
var values=[];
for(let i=0;i<data[0].length;i++){
values.push(row[data[0][i]]);
}
data.push(values);
});
var buffer = xlsx.build([{name: "mySheetName", data: data}]);
return buffer;
}
NetprojService.getAll(function (err, netprojs) {
if (err) {
console.log(err);
netprojs = [];
}
var data = NetprojService.getDataForExcel(netprojs);
var buffer = xlsx.build([{name: _tmpFileName, data: data}]);
fs.writeFile(path.join(__dirname, "../../public/excel/", _tmpFileName), buffer, function (err) {
if (err) console.log(err);
res.download(path.join(__dirname, "../../public/excel/", _tmpFileName), "网络部项目.xlsx", function (err) {
if (err) console.log(err);
fse.remove(path.join(__dirname, "../../public/excel/", _tmpFileName), function (err) {
if (err) console.log(err)
});
});
});
});