vue、koa 导入导出excel文件

野性酷女 2021-11-23 12:12 647阅读 0赞

一、使用node-xlsx、koa-multer插件  
　　npm install node-xlsx koa-multer mockjs --save

二、excel 文件导出

1、前端请求数据，通过blob（Blob对象可以看做是存放二进制数据的容器）解析数据，下载对应的文件

1 exportExcel(e){
     2    axios({
     3       method : "get",
     4       url : "/api/exportexcel",
     5       responseType: 'blob'
     6    }).then(async (res) => {
     7       let uploadExcel = (fileName) => {
     8             const blob = new Blob([res], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8'});
     9             const url = URL.createObjectURL(blob);
    10             const aLink = document.createElement('a');
    11             aLink.setAttribute('download',fileName);
    12             aLink.setAttribute('href',url);
    13             document.body.appendChild(aLink)
    14             aLink.click()
    15             document.body.removeChild(aLink)
    16             URL.revokeObjectURL(blob);
    17       }
    18       uploadExcel ( 'mySheetName.xlsx');
    19   })
    20}

2、后端通过node-xlsx处理，返回buffer流数据

1 const NXlSX = require("node-xlsx");
     2 const Mock = require("mockjs");
     3 router.get('/api/exportexcel',async (ctx) => {
     4     //表头
     5     const _headers = ['序号', '姓名', '年龄', '省',"市","区"];
     6     //表格数据
     7     const _data = [...Mock.mock({"arr|30-30": [{
     8         "id|+1": 1,
     9         name: "@cname",
    10         "age|18-34": 23,
    11         "province" : "@province",
    12         "city" : "@city",
    13         "county" : "@county"
    14       }]}).arr];
    15     let data = [];
    16     for (let i =0 ; i < _data.length; i++){
    17       let arr = [];
    18       for(let key in _data[i]){
    19         arr.push(_data[i][key])
    20       }
    21       data.push(arr);
    22     }
    23     data.unshift(_headers);  
    24    // 数据格式为[["hello","223"],["22","23"]];
    25     let buffer = NXlSX.build([{name: "sheetName", data: data}]);
    26     // 返回buffer流到前端
    27     ctx.body = buffer
    28 });

三、excel 文件文件导入，后端解析返回前端

1、前端通过input\[file\] 件文件传给后端

1 importExcel(e){
          3     let file = e.currentTarget.files[0];
     4     let fileDir = file.name;
     5     let formData = new FormData();
     6     formData.append('file',file);
     7     let config = {
     8        headers: {
     9           'Content-Type': 'multipart/form-data'
    10        }
    11     }
    12     axios.post("/api/importexcel",formData,config).then((res) => {
    13          console.log(res);
    14          // 处理数据
    15
    16     })
    17 }

2、后端存储文件，处理数据，再删除文件

1 const multer = require('koa-multer');
     2 const NXlSX = require("node-xlsx");
     3 let storage = multer.diskStorage({
     4     //文件保存路径
     5     destination: function (req, file, cb) {
     6       cb(null, './server/data')
     7     },
     8     //修改文件名称
     9     filename: function (req, file, cb) {
    10       var fileFormat = (file.originalname).split(".");
    11       cb(null,Date.now() + "." + fileFormat[fileFormat.length - 1]);
    12     }
    13   })
    14 //加载配置
    15 let upload = multer({ storage: storage })
    16 router.post('/api/importexcel',upload.single('file'),async (ctx) => {
    17     let file =ctx.req.file.filename;
    18     const xlsxfile = "./server/data/" + file;
    19     async function readExcel() {
    20       return new Promise((resolve,reject)=>{
    21         let obj = NXlSX.parse(xlsxfile);
    22         resolve(obj);
    23       });
    24     }
    25     async function getData(v) {
    26       ctx.body = v[0].data;
    27       fs.unlinkSync(xlsxfile);
    28     }
    29     let dataObj =await readExcel();
    30     await getData(dataObj);
    31 });

转载于:https://www.cnblogs.com/laixihongblog/p/11208756.html