参考: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: "没有权限" });
}*/
//成绩id
let 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.detail
let headers = score.config //表头
let show = score.show //总分配置
let ws_data=[] //存储生成的sheet数据
for(let item of detail){
let cell_obj = {}
let scores = item.scores
let ranges = item.ranges
cell_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": "澎恰恰"
}
],
}