NODEJS 使用 XLSX模块导出excel文件

参考：https://www.itranslater.com/qa/details/2582439815438402560

生成excel

/*** 用于排序* @param a* @param b* @returns {number}*/
function sortAlphaNum(a, b) {let reA = /[^a-zA-Z]/g;let reN = /[^0-9]/g;let aA = a.replace(reA, "");let bA = b.replace(reA, "");if (aA === bA) {var aN = parseInt(a.replace(reN, ""), 10);var bN = parseInt(b.replace(reN, ""), 10);return aN === bN ? 0 : aN > bN ? 1 : -1;} else {return aA > bA ? 1 : -1;}
}

exports.ocr_socre_export = function (req, res, next){/*var current_user = req.session.wxuser;if (!current_user) {return next({ code: 403, msg: "没有权限" });}*///成绩idlet sid = req.body.id;let cname = req.body.cname;let title = req.body.title;Score.getScore({_id:sid},function (err,score) {if (err || !score) {return res.json({status: "error",reason: "查询失败"});}let detail = score.detaillet headers = score.config //表头let show = score.show //总分配置let ws_data=[] //存储生成的sheet数据for(let item of detail){let cell_obj = {}let scores = item.scoreslet ranges = item.rangescell_obj['姓名']=item.name//判断是否做过分析if(ranges.length==0){//循环表头for(let i=0;i<headers.length;i++){cell_obj[headers[i]]=scores[i]}}else{//循环表头for(let i=0;i<headers.length;i++){cell_obj[headers[i]]=scores[i]//获取排名情况if(ranges[i]!='' && ranges[i]!=null){let r_name = headers[i]+'排名'cell_obj[r_name] = ranges[i]}}}//获取总分信息if(show && show.length>0){if(show.includes('score')){cell_obj['总分'] = item.total.score}if(show.includes('rank')){cell_obj['总分排名'] = item.total.rank}}ws_data.push(cell_obj)}let ss = XLSX.utils.json_to_sheet(ws_data); //通过工具将json转表对象let keys = Object.keys(ss).sort(sortAlphaNum); //排序 [需要注意，必须从A1开始]let ref = keys[0]+':'+keys[keys.length - 2]; //这个是定义一个字符串 也就是表的范围let workbook = { //定义操作文档SheetNames:['成绩信息'], //定义表明Sheets:{'成绩信息':Object.assign({},ss,{'!ref':ref}), //表对象[注意表明]},}// XLSX.writeFile(workbook,"./suzhenwei.xls"); //将数据写入文件var wopts = {bookType: 'xlsx',bookSST: false,type: 'binary'};let result;try {result = XLSX.write(workbook, wopts);client.useBucket('campusfile');let filename = cname+title+'.xlsx'client.put("score/" + filename, new Buffer(result, 'binary')).then(function (r1) {return res.json({status: 'ok',url: 'https://file.welife001.com/' + "score/" + filename});}).catch(function (err) {return res.json({status: "error"})})} catch (err) {return res.json({status: "error"})}})
}

原始数据格式为

// 1
{"config": ["级位次","语文","班位次","级位次","数学","班位次","级位次","英语","班位次","级位次","物理","班位次","级位次","生物"],"config_headers": [{"_id": ObjectId("6112416f9ef89d1354931f39"),"index": NumberInt("0"),"avg": "62.50","max": "114","show": ["avg","max","rank"]},{"_id": ObjectId("6112416f9ef89d1354931f38"),"index": NumberInt("1"),"avg": "97.00","max": "108","show": ["avg","max","rank"]},{"_id": ObjectId("6112416f9ef89d1354931f37"),"index": NumberInt("0"),"avg": "62.50","max": "114","show": ["avg","max","rank"]},{"_id": ObjectId("6112416f9ef89d1354931f36"),"index": NumberInt("4"),"avg": "103.10","max": "119","show": ["avg","max","rank"]},{"_id": ObjectId("6112416f9ef89d1354931f35"),"index": NumberInt("0"),"avg": "62.50","max": "114","show": ["avg","max","rank"]},{"_id": ObjectId("6112416f9ef89d1354931f34"),"index": NumberInt("7"),"avg": "92.90","max": "114","show": ["avg","max","rank"]},{"_id": ObjectId("6112416f9ef89d1354931f33"),"index": NumberInt("0"),"avg": "62.50","max": "114","show": ["avg","max","rank"]},{"_id": ObjectId("6112416f9ef89d1354931f32"),"index": NumberInt("10"),"avg": "86.35","max": "100","show": ["avg","max","rank"]},{"_id": ObjectId("6112416f9ef89d1354931f31"),"index": NumberInt("0"),"avg": "62.50","max": "114","show": ["avg","max","rank"]},{"_id": ObjectId("6112416f9ef89d1354931f30"),"index": NumberInt("13"),"avg": "79.80","max": "92","show": ["avg","max","rank"]}],"detail": [{"total": {"score": NumberInt("227"),"rank": NumberInt("1")},"status": NumberInt("0"),"read": NumberInt("0"),"scores": ["112","85","30","150","102","14","82","79.5","22","110","76","25","116","72"],"ranges": [NumberInt("3"),NumberInt("14"),null,null,NumberInt("10"),null,null,NumberInt("14"),null,null,NumberInt("10"),null,null,NumberInt("10")],"_id": ObjectId("6112416f9ef89d1354931f2f"),"name": "冯茹茹"},{"total": {"score": NumberInt("227"),"rank": NumberInt("1")},"status": NumberInt("0"),"read": NumberInt("0"),"scores": ["113","89","25","125","108","11","57","62","31","147","88","13","42","80"],"ranges": [NumberInt("2"),NumberInt("13"),null,null,NumberInt("8"),null,null,NumberInt("17"),null,null,NumberInt("6"),null,null,NumberInt("6")],"_id": ObjectId("6112416f9ef89d1354931f2e"),"name": "张志和"},{"total": {"score": NumberInt("226"),"rank": NumberInt("2")},"status": NumberInt("0"),"read": NumberInt("1"),"scores": ["114","108","4","114","115","4","114","100.5","4","114","100","4","114","90"],"ranges": [NumberInt("1"),NumberInt("1"),null,null,NumberInt("3"),null,null,NumberInt("6"),null,null,NumberInt("1"),null,null,NumberInt("2")],"_id": ObjectId("6112416f9ef89d1354931f1c"),"name": "李四","comment": "整体不错，值得表扬，属打呼打的大dasd大的大时代"},{"total": {"score": NumberInt("218"),"rank": NumberInt("3")},"status": NumberInt("0"),"read": NumberInt("0"),"scores": ["113","100","5","27","92","22","114","85","17","96","76","25","116","68"],"ranges": [NumberInt("2"),NumberInt("5"),null,null,NumberInt("13"),null,null,NumberInt("13"),null,null,NumberInt("10"),null,null,NumberInt("12")],"_id": ObjectId("6112416f9ef89d1354931f2d"),"name": "邓新"},{"total": {"score": NumberInt("217"),"rank": NumberInt("4")},"status": NumberInt("0"),"read": NumberInt("0"),"scores": ["109","95","13","79","76","29","140","87.5","16","89","76","25","116","70"],"ranges": [NumberInt("4"),NumberInt("9"),null,null,NumberInt("16"),null,null,NumberInt("12"),null,null,NumberInt("10"),null,null,NumberInt("11")],"_id": ObjectId("6112416f9ef89d1354931f2c"),"name": "澎恰恰"}],
}