一、excel解析的初步配置

(1)npm i xlsx --save

(2) 一个封装好的export插件

 * create by lwj
 * @file 导出export插件封装
 */

import * as styleXLSX from 'xlsx-style'

/**
 * 将 String 转换成 ArrayBuffer 
 * @method 类型转换
 * @param {String} [s] wordBook内容
 * @return {Array} 二进制流数组
 */
function s2ab (s) {
    let buf = null;

    if (typeof ArrayBuffer !== 'undefined') {
        buf = new ArrayBuffer(s.length);
        let view = new Uint8Array(buf);

        for (let i = 0; i != s.length; ++i) {
            view[i] = s.charCodeAt(i) & 0xFF;
        }

        return buf;
    }

    buf = new Array(s.length);

    for (let i = 0; i != s.length; ++i) {

        // 转换成二进制流
        buf[i] = s.charCodeAt(i) & 0xFF;
    }

    return buf;
}

/**
 * 方案一：利用 URL.createObjectURL 下载 （以下选用）
 * 方案二：通过 file-saver 插件实现文件下载
 * @method 文件下载
 * @param {Object} [obj] 导出内容 Blob 对象
 * @param {String} [fileName] 文件名 下载是生成的文件名
 * @return {void}
 */ 
function saveAs (obj, fileName) {
    let aLink = document.createElement("a");

    if (typeof obj == 'object' && obj instanceof Blob) {
        aLink.href = URL.createObjectURL(obj); // 创建blob地址
    }
    
    aLink.download = fileName;
    aLink.click();
    setTimeout(function () {
        URL.revokeObjectURL(obj);
    }, 100);
}

/**
 * @method 数据导出excel
 * @param {Object} [worksheets] 工作表数据内容
 * @param {String} [fileName='ExcelFile'] 导出excel文件名
 * @param {String} [type='xlsx'] 导出文件类型
 */
export default function export2Excel ({
    worksheets, 
    fileName = 'ExcelFile',
    type = 'xlsx',
    load,
} = {}) {

    let sheetNames = Object.keys(worksheets);
    let workbook = {
        SheetNames: sheetNames, //保存的工作表名
        Sheets: worksheets
    };

    // excel的配置项
    let wopts = {  
        bookType: type,  // 生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: 'binary'  
    }

    // attempts to write the workbook
    let wbout = styleXLSX.write(workbook, wopts);
    let wbBlob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    });

    saveAs(wbBlob, fileName + '.' + type);
    load.close()
}

(2)写一个按钮，之后复制一个element-ui的el-upload组件放在dialog框中

<el-button
      style="margin-left: 16px"
      size="mini"
      type="primary"
      @click="handleUpload"
      plain
      icon="el-icon-upload2"
      v-hasPermi="['table:rtInfo:import']"
    >
      校验
    </el-button>

    <el-dialog
      :title="upload.title"
      :visible.sync="upload.open"
      width="400px"
      append-to-body
    >
      <el-button
        class="download"
        icon="el-icon-download"
        type="danger"
        @click="frontDownload"
        >下载模板</el-button
      >
      <el-upload
        ref="upload"
        :limit="1"
        accept=".xlsx, .xls"
        :headers="upload.headers"
        :action="upload.url"
        :disabled="upload.isUploading"
        :on-progress="handleFileUploadProgress"
        :auto-upload="false"
        :on-success="handleFileSuccess"
        drag
      >
        <i class="el-icon-upload"></i>
        <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
        <div class="el-upload__tip text-center" slot="tip">
          <span>仅允许导入xls、xlsx格式文件。</span>
        </div>
      </el-upload>

      <div slot="footer" class="dialog-footer">
        <el-button type="primary" @click="submitFileForm">确 定</el-button>
        <el-button @click="upload.open = false">取 消</el-button>
      </div>
    </el-dialog>

二、校验功能的实现

1.通过xlsx的一些方法实现将二进制文件转换成对象进行解析

generateData({ header, results }) {
      this.excelData.header = header;
      this.excelData.results = results;
      this.onSuccess && this.onSuccess(this.excelData);
    },
    handleUpload() {
      this.upload.title = "校验excel";
      this.upload.open = true;
    },
    readerData(rawFile) {
      this.loading = true;
      return new Promise((resolve, reject) => {
        const reader = new FileReader();
        reader.onload = (e) => {
          const data = e.target.result;
          const workbook = XLSX.read(data, { type: "array" });
          const firstSheetName = workbook.SheetNames[0];
          const worksheet = workbook.Sheets[firstSheetName];
          const header = this.getHeaderRow(worksheet);
          const results = XLSX.utils.sheet_to_json(worksheet, { defval: "" });
          this.generateData({ header, results });
          this.loading = false;
          resolve();
        };
        reader.readAsArrayBuffer(rawFile);
      });
    },
    getHeaderRow(sheet) {
      const headers = [];
      const range = XLSX.utils.decode_range(sheet["!ref"]);
      let C;
      const R = range.s.r;
      /* start in the first row */
      for (C = range.s.c; C <= range.e.c; ++C) {
        /* walk every column in the range */
        const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })];
        /* find the cell in the first row */
        let hdr = "UNKNOWN " + C; // <-- replace with your desired default
        if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);
        headers.push(hdr);
      }
      return headers;
    },
    isExcel(file) {
      return /\.(xlsx|xls|csv)$/.test(file.name);
    },

3.在el-upload的导入成功的回调函数中，将导入的excel进行二进制文件转换

    // 文件上传成功处理
    handleFileSuccess(response, file, fileList) {
     this.readerData(fileList[0].raw);
     }

2.调用xlsx插件的方法实现校验后excel的导出功能

export2Excel({
        worksheets: {
          sheet1: workSheet1,
        }, // 导出excel的数据，key表示工作表名，value表示对应工作表的 sheet 数据，支持导出多个工作表
        fileName: "我的excel", // 导出文件名
        type: "xlsx", // 文件导出类型
        load: loading,
      });

3.校验实现自定义样式

使用Vue+xlsx+xlsx-style实现导出自定义样式的Excel文件_vue xlsx-style-CSDN博客