nodej实现Excel解析上传、导出

        <el-upload
          ref="upload"
          :limit="limit"
          :auto-upload="false"
          :on-exceed="handleExceed"
          :file-list="filelist"
          :on-change="handleChansge"
          :on-remove="removeFile"
          name="file"
          action="接口地址"
          accept=".xls,.xlsx"
        >
          <el-button slot="trigger" size="small" type="primary">选取文件</el-button>
          <el-button
            :disabled="btn.disable"
            style="margin-left: 10px;"
            size="small"
            type="success"
            @click="postfile"
          >{{ btn.message }}</el-button>
          <div slot="tip" class="el-upload__tip">上传文件只能为excel文件，且为xlsx,xls格式</div>
        </el-upload>

data() {
	return {
	  file: '',
      filename: '',
      filelist: [],
      limit: 1,
      errmesg: [],
      btn: {
        disable: false,
        message: '上传模板'
      },
	}
}

    handleExceed(e) {
      // 文件超出个数限制时提示报错
      this.$message.error('只能上传一个文件')
    },
    handleChansge(file, fileList) {
      if (!/\.(xlsx|xls)$/i.test(file.name)) {
        this.$message.error('上传文件只能为excel文件，且为xlsx,xls格式')
        this.filelist = []
        this.file = ''
        return false
      }
      this.file = file.raw
      this.filename = file.name
    },
    removeFile(file, fileList) {
      this.file = ''
      this.filename = ''
    },
    postfile() {
      if (this.file === '') {
        this.$message.error('上传文件不能为空')
        return false
      }
      // 文件形式的需要用 formData形式
      const formData = new FormData()
      formData.append('file', this.file)
      this.btn.disable = true
      this.btn.message = '上传中，请等待'
      parsingUploadExcel(formData).then(data => {
        this.btn.disable = false
        this.btn.message = '上传模板'
        this.$message.success(`上传成功`)
        this.matchList = data
      }).catch(err => {
        this.filelist = []
        this.btn.disable = false
        this.btn.message = '上传模板'
        this.matchList = []
        console.log(err)
      })
    },

import * as R from 'ramda';
const XLSX = require('xlsx');
import xlsx from 'node-xlsx';
/**
* @description: 下载模板
* @param {*}
* @return {*}
*/
  async download() {
    const options = {
      '!cols': [
        { wpx: 140 },
        { wpx: 140 },
        { wpx: 140 },
        { wpx: 140 },
        { wpx: 140 }
      ]
    }
    const { ctx } = this;
    ctx.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    ctx.response.attachment(`召回通道填写模板.xlsx`);
    const data = [['channel_id', 'name', 'guarantee', 'pri', 'size']];
    const buffer = xlsx.build([{ name: 'sheet', data }], options);
    ctx.body = buffer;
}
/**
* @description: 导出
* @param {*}
* @return {*}
*/
async exportDetail() {
    const options = {
      '!cols': [
        { wpx: 140 },
        { wpx: 140 },
        { wpx: 140 },
        { wpx: 140 },
        { wpx: 140 }
      ]
    }
    const { ctx, service } = this;
    const results: any[] = await service.content.delivery.channelWhite.detail(ctx.query);
    const table = R.map(item => ({ channel_id: item.channel_id, name: item.name, guarantee: item.guarantee, pri: item.pri, size: item.size }), results)
    const firstRecord = table[0] || {}; // 第一条记录
    const columns = R.keys(firstRecord); // 提取出列
    const values = R.map(R.values, table); // 提取行
    const data = R.prepend(columns, values); // 组装表格数据
    ctx.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    ctx.response.attachment(`${ctx.query.name}.xlsx`);
    const buffer = xlsx.build([{ name: 'sheet', data }], options);
    ctx.body = buffer;
}
/**
* @description: 解析上传的Excel内容
* @param {*}
* @return {*}
*/
async parsingExcel() {
    const { ctx } = this;
    // try {
    //   // 存储获取到的数据
    //   let exceldata = [];
    //   const file = this.ctx.request.files[0];
    //   const workbook = XLSX.readFile(file.filepath);
    //   // 获取 Excel 中所有表名
    //   const sheetNames = workbook.SheetNames; // 返回 ['sheet1', 'sheet2']
    //   // 根据表名获取对应某张表
    //   const worksheet = workbook.Sheets[sheetNames[0]];
    //   // 获取 A1 B1 ... 单元格值
    //   let A1 = worksheet['A1'].v
    //   let B1 = worksheet['B1'].v
    //   let C1 = worksheet['C1'].v
    //   let D1 = worksheet['D1'].v
    //   let E1 = worksheet['E1'].v
    //   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; // 如果只取第一张表，就取消注释这行
    //     }
    //   }
    //   if (A1 !== 'channel_id' || B1 !== 'name' || C1 !== 'guarantee' || D1 !== 'pri' || E1 !== 'size') {
    //     ctx.helper.error(1,'请使用正确的模板！')
    //     return false
    //   }
    //   ctx.helper.success(exceldata);
    // } catch (error) {
    //   ctx.helper.error(1,'请使用正确的模板！')
    // }

    try {
      const file = this.ctx.request.files[0];
      const excelObj = xlsx.parse(file.filepath);
      let _data: any[] = []
      if(excelObj[0]) {
        const workSheets = excelObj[0].data
        const tableTitle = workSheets[0]
        if (tableTitle[0] !== 'channel_id' || tableTitle[1] !== 'name' || tableTitle[2] !== 'guarantee' || tableTitle[3] !== 'pri' || tableTitle[4] !== 'size') {
          ctx.helper.error(1,'请使用正确的模板！')
          return false
        }
        workSheets.forEach((item: any[],index: Number) => {
          //从第二行开始插入，避免连表头也插入_data里面
          if ( index > 0) {
            let channelItem = {
              channel_id: item[0],
              name: item[1],
              guarantee: item[2],
              pri: item[3],
              size: item[4]
            }
            //往_data插入单元格个值
            _data.push(channelItem)
          }
        });
      }
      ctx.helper.success(_data);
    } catch (error) {
      ctx.helper.error(1,'请使用正确的模板！')
    }
}