一个js插件，用于导出生成Excel文件。

安装

npm install xlsx
Copy

解析excel文件

import { Message, Loading } from 'element-ui';
// 解析excel数据
export const resolveXLSX = e => {
  const loadingInstance = Loading.service({
    text: '导入中...',
    spinner: 'el-icon-loading',
    background: 'rgba(0, 0, 0, 0.8)',
  });
  return new Promise((resolve, rejected) => {
    const files = e.target.files;
    const fileReader = new FileReader();
    for (let i = 0; i < files.length; i++) {
      if (!/\.(xlsx|xls)$/.test(files[i].name)) {
        throw new Error('必须上传excle文件！');
      }
    }
    import(/* webpackChunkName: "xlsx" */ 'xlsx').then(XLSX => {
      fileReader.onload = ev => {
        try {
          const data = ev.target.result;
          const workbook = XLSX.read(data, {
            type: 'binary',
          }); // 以二进制流方式读取得到整份excel表格对象
          let dataList = []; // 存储获取到的数据
          for (const sheet in workbook.Sheets) {
            // eslint-disable-next-line no-prototype-builtins
            if (workbook.Sheets.hasOwnProperty(sheet)) {
              // eslint-disable-line
              dataList = dataList.concat(
                XLSX.utils.sheet_to_json(workbook.Sheets[sheet])
              );
              break; // 如果只取第一张表，就取消注释这行
            }
          }
          resolve(dataList);
        } catch (error) {
          rejected(error);
        }
      };
      // 以二进制方式打开文件
      if (files[0]) fileReader.readAsBinaryString(files[0]);
    });
  })
    .then(data => {
      loadingInstance && loadingInstance.close();
      // Message.success({
      //   message: '导入成功！'
      // });
      return data;
    })
    .catch(e => {
      loadingInstance && loadingInstance.close();
      Message.error({
        message: e.message || '导入失败！',
      });
      return Promise.reject(e);
    });
};
Copy

导出excel文件

/**
 * 导出excel
 * @param {*} data 数据
 * @param {*} name excel名称
 * @param {*} headerNameMap {prop:{value:'列明',width:20}}
 * @param {*} mergeCell 是否合并单元格
 */
export const exportExcel = (
  data,
  name,
  headerNameMap = null,
  mergeCell = false
) => {
  import(/* webpackChunkName: "xlsx" */ 'xlsx').then(XLSX => {
    let labeledData = [];
    // 生成的表格列标题根据headerNameMap生成中文
    if (
      headerNameMap &&
      Object.prototype.toString.call(headerNameMap) == '[object Object]'
    ) {
      labeledData = data.map(item => {
        let obj = {};
        for (let key in headerNameMap) {
          obj[headerNameMap[key].value] = item[key];
        }
        return obj;
      });
    } else {
      labeledData = data;
    }

    const ws = XLSX.utils.json_to_sheet(labeledData);
    const range = XLSX.utils.decode_range(ws['!ref']);
    // 解决导出纯数字以科学计数显示的问题
    for (var r = range.s.r; r <= range.e.r; r++) {
      for (var c = range.s.c; c <= range.e.c; c++) {
        var cellName = XLSX.utils.encode_cell({ c: c, r: r });
        ws[cellName].z = '@';
      }
    }
    //  设置列宽度
    if (
      headerNameMap &&
      Object.prototype.toString.call(headerNameMap) == '[object Object]'
    ) {
      let columnsWidthArr = [];
      for (let key in headerNameMap) {
        columnsWidthArr.push({
          wpx: headerNameMap[key].width || 120, // 字符长度
        });
      }
      ws['!cols'] = columnsWidthArr;
    }
    // 合并单元格，不建议合并，大数据量消耗较大
    if (mergeCell) {
      ws['!merges'] = collectMergeCellPos(labeledData);
    }

    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
    // 保存
    XLSX.writeFile(wb, `${name}.xlsx`);
  });
};
Copy

合并单元格

// 收集要合并的单元格位置
function collectMergeCellPos(arr) {
  let results = [];
  let fields = getRowFields(arr[0]);
  for (let c = 0; c < fields.length; c++) {
    let pos = computedMergeCellPos(0, c, arr, fields);
    if (pos) {
      results.push(...pos);
    }
  }
  return results;
}
/**
 * 第几行第几列
 * r: 行index
 * c: 列index
 * arr: 原数据
 */
function computedMergeCellPos(r, c, arr, fieldsKey = []) {
  let mergeCellsPos = [];
  let rowLength = arr.length;
  let startRowIndex = r;
  let endRowIndex = 0;
  let fieldKey = fieldsKey[c];
  getCellMergePos(r, c, arr, fieldsKey);

  function getCellMergePos(r, c, arr, fieldsKey = []) {
    if (r < rowLength - 1) {
      let nextCellRowIndex = r + 1;
      let nextCellValue = arr[nextCellRowIndex][fieldKey];
      if (
        arr[startRowIndex][fieldKey] === nextCellValue &&
        nextCellValue !== '' &&
        nextCellValue !== null
      ) {
        endRowIndex = nextCellRowIndex;
        getCellMergePos(nextCellRowIndex, c, arr, fieldsKey);
      } else {
        if (endRowIndex) {
          mergeCellsPos.push({
            s: { r: startRowIndex + 1, c: c },
            e: { r: endRowIndex + 1, c: c },
          });
        }
        startRowIndex = r + 1;
        endRowIndex = null;
        getCellMergePos(startRowIndex, c, arr, fieldsKey);
      }
    } else {
      // 最后一行的情况
      if (endRowIndex) {
        mergeCellsPos.push({
          s: { r: startRowIndex + 1, c: c },
          e: { r: endRowIndex + 1, c: c },
        });
      }
    }
  }

  if (mergeCellsPos.length > 0) {
    return mergeCellsPos;
  } else {
    return null;
  }
}

// 获取行字段，保证遍历的字段值对其
function getRowFields(rowObj) {
  let result = [];
  if (Object.prototype.toString.call(rowObj) == '[object Object]') {
    for (let key in rowObj) {
      result.push(key);
    }
  }
  return result;
}
Copy