需求：根据不同考试，导入不同格式的excel，再将excel转成想要的格式
一、创建一个experss项目
express 文件名二、使用html模板
1. npm install ejs
2.app.js
var ejs = require("ejs"); // 引入ejs
app.engine("html", ejs.__express);
app.set("view engine", "html");三、界面文件 index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<title>Excel的导入导出</title>
<script src="http://libs.baidu.com/jquery/2.1.4/jquery.min.js"></script>
</head>
<body>
类型：
<select id="studentType">
<option value="xuekao">学考</option>
<option value="Add31">3+1</option>
<option value="jiaoshi">教师资格证</option>
</select>
<hr />
<input type="file" id="bp" />
转出编排数据：<button id="bpExcel">转换</button>
<button class="download" data-id="bp" style="margin-left:10px">
下载
</button>
<script>
$(function() {
// 下载
$(".download").on("click", function(e) {
window.open("/download?id=" + e.target.dataset.id);
});
// 编排数据转换
$("#bpExcel").on("click", function() {
createExcel("bp", "/importBp");
});
});
function createExcel(id, url) {
const file = $("#" + id)[0].files[0];
if (!file) {
alert("请选择文件");
return;
}
const formData = new FormData();
formData.append("file", file);
formData.append("type", $("#studentType").val());
$.ajax({
url: url,
type: "POST",
dataType: "json",
data: formData,
processData: false, // 使数据不做处理
contentType: false, // 不要设置Content-Type请求头
success: function(data) {
alert(data.msg);
},
error: function(err) {
console.log("err：", err);
}
});
}
</script>
</body>
</html>
四、后台接口文件 index.js
注意：
1.返回下载内容用到 res.download()，前端界面用window.open就可以打开下载
2.接收前端传来的文件，用formidable
3.excel转换用ndoe-xlsx
var filename = files.file.path;
var obj = xlsx.parse(filename);
const datas = initKsData(obj[0].data, fields.type);
let buffer = xlsx.build([{ name: "sheet1", data: datas }]);
fs.writeFileSync("./public/ks.xlsx", buffer, "binary");4.安装依赖和代码
npm install node-xlsx
npm install formidablevar express = require("express");
var router = express.Router();
const fs = require("fs");
const xlsx = require("node-xlsx");
const formidable = require("formidable");
const jsonData = require("./examJSON");
const studentJSON = jsonData.studentJSON;
const bpJSON = jsonData.bpJSON;
/* GET home page. */
router.get("/", function(req, res, next) {
res.render("index", { title: "Express" });
});
router.get("/download", function(req, res, next) {
res.download("./public/" + req.query.id + ".xlsx");
});
/* 编排转换 */
router.post("/importBp", function(req, res, next) {
try {
const form = formidable({ multiples: true });
form.parse(req, (err, fields, files) => {
if (err) {
res.writeHead(err.httpCode || 400, { "Content-Type": "text/plain" });
res.send(String(err));
return;
}
var filename = files.file.path;
var obj = xlsx.parse(filename);
const datas = initBpData(obj[0].data, fields.type);
let buffer = xlsx.build([{ name: "sheet1", data: datas }]);
fs.writeFileSync("./public/bp.xlsx", buffer, "binary");
res.json({ msg: "生成成功, 共" + datas.length + "条数据" });
});
} catch (error) {
console.log(error);
}
});
/* 编排数据 */
function initBpData(obj, type) {
const datas = [];
const dataArr = bpJSON[type];
obj.forEach((element, index) => {
if (index === 0) {
const keyArr = [];
dataArr.forEach(child => {
keyArr.push(child.name);
});
datas.push(keyArr);
} else {
const info = [];
for (let i = 0; i < dataArr.length; i++) {
if (dataArr[i].type === "formatter") {
info.push(dataArr[i].formatter(element));
} else {
info.push(
typeof dataArr[i].num === "number" ? element[dataArr[i].num] : ""
);
}
}
datas.push(info);
}
});
return datas;
}
module.exports = router;
五、excel对应字段文件 examJSON
const bpJSON = {
default: [
{ id: "kdbh", name: "考点编号", num: 7 },
{ id: "ljkch", name: "逻辑考场号", num: 9 },
{ id: "zwh", name: "坐位号", num: 10 }
],
jiaoshi: [
{ id: "kdbh", name: "考点编号", num: 6 },
{ id: "ljkch", name: "逻辑考场号", num: 4 },
{ id: "zwh", name: "坐位号", num: 5 },
{
id: "kmbh",
name: "科目编号",
type: "formatter",
formatter: item => {
switch (item[7]) {
case "13日上午第一场":
return "001";
case "13日下午第二场":
return "003";
case "13日下午第一场":
return "002";
}
}
}
]
};
module.exports = { bpJSON };