一、使用node-xlsx、koa-multer插件
npm install node-xlsx koa-multer mockjs --save
二、excel 文件导出
1、前端请求数据，通过blob（Blob对象可以看做是存放二进制数据的容器）解析数据，下载对应的文件
exportExcel(e){
axios({
method : "get",
url : "/api/exportexcel",
responseType: 'blob'
}).then(async (res) => {
let uploadExcel = (fileName) => {
const blob = new Blob([res], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8'});
const url = URL.createObjectURL(blob);
const aLink = document.createElement('a');
aLink.setAttribute('download',fileName);
aLink.setAttribute('href',url);
document.body.appendChild(aLink)
aLink.click()
document.body.removeChild(aLink)
URL.revokeObjectURL(blob);
}
uploadExcel ( 'mySheetName.xlsx');
})
}
2、后端通过node-xlsx处理，返回buffer流数据
const NXlSX = require("node-xlsx");
const Mock = require("mockjs");
router.get('/api/exportexcel',async (ctx) => {
//表头
const _headers = ['序号', '姓名', '年龄', '省',"市","区"];
//表格数据
const _data = [...Mock.mock({"arr|30-30": [{
"id|+1": 1,
name: "@cname",
"age|18-34": 23,
"province" : "@province",
"city" : "@city",
"county" : "@county"
}]}).arr];
let data = [];
for (let i =0 ; i < _data.length; i++){
let arr = [];
for(let key in _data[i]){
arr.push(_data[i][key])
}
data.push(arr);
}
data.unshift(_headers);
// 数据格式为[["hello","223"],["22","23"]];
let buffer = NXlSX.build([{name: "sheetName", data: data}]);
// 返回buffer流到前端
ctx.body = buffer
});
三、excel 文件文件导入，后端解析返回前端
1、前端通过input[file] 件文件传给后端
importExcel(e){
let file = e.currentTarget.files[0];
let fileDir = file.name;
let formData = new FormData();
formData.append('file',file);
let config = {
headers: {
'Content-Type': 'multipart/form-data'
}
}
axios.post("/api/importexcel",formData,config).then((res) => {
console.log(res);
// 处理数据
})
}
2、后端存储文件，处理数据，再删除文件
const multer = require('koa-multer');
const NXlSX = require("node-xlsx");
let storage = multer.diskStorage({
//文件保存路径
destination: function (req, file, cb) {
cb(null, './server/data')
},
//修改文件名称
filename: function (req, file, cb) {
var fileFormat = (file.originalname).split(".");
cb(null,Date.now() + "." + fileFormat[fileFormat.length - 1]);
}
})
//加载配置
let upload = multer({ storage: storage })
router.post('/api/importexcel',upload.single('file'),async (ctx) => {
let file =ctx.req.file.filename;
const xlsxfile = "./server/data/" + file;
async function readExcel() {
return new Promise((resolve,reject)=>{
let obj = NXlSX.parse(xlsxfile);
resolve(obj);
});
}
async function getData(v) {
ctx.body = v[0].data;
fs.unlinkSync(xlsxfile);
}
let dataObj =await readExcel();
await getData(dataObj);
});