js-excel-func.js

import * as XLSX from 'xlsx'


// 将blob对象创建blob url，然后用a标签实现弹出下载框
const openDownloadDialog = (blob, fileName) => {
  if (typeof blob == "object" && blob instanceof Blob) {
    blob = URL.createObjectURL(blob); // 创建blob地址
  }
  let aLink = document.createElement("a");
  aLink.href = blob;
  // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，有时候 file:///模式下不会生效
  aLink.download = fileName || "";
  let event;
  if (window.MouseEvent) event = new MouseEvent("click");
  //   移动端
  else {
    event = document.createEvent("MouseEvents");
    event.initMouseEvent("click", true, false, window, 0, 0, 0, 0, 0, false,
      false, false, false, 0, null);
  }
  aLink.dispatchEvent(event);
}


// 将workbook装化成blob对象
const workbook2blob = (workbook) => {

  let wb_out = XLSX.write(workbook, {
    // 要生成的文件类型
    bookType: "xlsx",
    // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
    bookSST: false,
    type: "binary"
  });

  // 将字符串转ArrayBuffer
  function s2ab(s) {
    let 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;
  }

  // 返回blob
  return new Blob([s2ab(wb_out)], {
    type: "application/octet-stream"
  });
}


export const readExcel = (file) => {
  return new Promise(function (resolve, reject) {
    const reader = new FileReader()
    reader.onload = function (e) {
      let workBook = XLSX.read(e.target.result, {
        type: 'binary'
      })
      const result = []
      workBook.SheetNames.forEach((sheetName) => {
        result.push({
          sheetName: sheetName,
          sheetRows: XLSX.utils.sheet_to_json(workBook.Sheets[sheetName])
        })
      })
      resolve(result)
    }
    reader.readAsBinaryString(file) // 传统input方法
  })
}

export const exportExcel = (list, columns, exportExcelName = '下载') => {
  if (list.length === 0 && columns.length === 0) {
    return false;
  }

  if (columns.length === 0) {
    columns = Object.keys(list[0]).map(item => {
      return {
        field: item,
        label: item
      }
    })
  }
  if (list.length === 0) {
    list[0] = {}
    columns.forEach(item => {
      list[0][item.label] = item.label
    })
  }

  const sheetData1 = list.map(row => {
    let obj = {}
    columns.forEach((column) => {
      obj[column.label] = row[column.field]
    })
    return obj
  });

  const workBook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workBook, XLSX.utils.json_to_sheet(sheetData1), 'sheet1');

  const workbookBlob = workbook2blob(workBook);
  openDownloadDialog(workbookBlob, exportExcelName + '.xlsx');
}