egg.js基于xlsx导出导入excel文件

npm install xlsx --save-dev

导入核心代码

async importExcel(){
//定义头部字段
 const keyMap = {
   表头1: 'id',
   表头2: 'name',
   表头3: 'other',

 };
//获取文件流
const stream = await ctx.getFileStream();
//
let exceldata = [];
stream.on('data', function(chunk) {
   const workbook = XLSX.read(chunk, { type: 'buffer' });
   for (const sheet in workbook.Sheets) {
     if (workbook.Sheets.hasOwnProperty(sheet)) {
        // 利用 sheet_to_json 方法将 excel 转成 json 数据
        exceldata = exceldata.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
        break; // 如果多表，注释这行
      }
    }
 });
//获得数据后替换key
for (let i = 0; i < exceldata.length; i++) {
   const obj = exceldata[i];
   for (const key in obj) {
     const newKey = keyMap[key];
     if (newKey) {
       obj[newKey] = obj[key];
       delete obj[key];
     }
   }
 }
console.log(exceldata);
}

导出核心代码

async exportExcel(){
 const { ctx } = this;
 //查询数据
 const list = await this.app.mysql.select('tabel_name');
 //转换表头
 const keyMap = {
    id: '表头1',
    name: '表头2',
    other: '表头3',

 };
  const weebook = XLSX.utils.book_new();
  const head = [ keyMap, ...list ];
  const worksheet = XLSX.utils.json_to_sheet(head, { skipHeader: true });
  XLSX.utils.book_append_sheet(weebook, worksheet, 'sheet1');
  ctx.set('Content-Type', 'application/vnd.openxmlformats');
  ctx.set('Content-Disposition', "attachment;filename*=UTF-8' '" + encodeURIComponent('文件名') + Date.now() + '.xlsx');
  ctx.body = await XLSX.write(weebook, {
    bookType: 'xlsx',
    type: 'buffer',
  });
}
//浏览器直接打开接口地址即可导出