vue、koa 导入导出excel文件

一、使用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);
});

评论